[będzie aktualizowane w miarę postępu prac]

Dwa serwery:

• maria1.ping.local - primary,
• maria2.ping.local - replika, tylko do odczytu.

Sekcja [mariadb] z pliku /etc/my.cnf.d/mariadb-server.cnf

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed

Należy zrestartować MariaDB, żeby zaczytała zmiany. W shellu mysql:

MariaDB [(none)]> CREATE USER 'replication_user'@'%' IDENTIFIED BY 'qwerty123';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000004 |      344 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

Nazwa użytkownika oraz jego hasło może być inne. Należy wyeksportować wszystkie bazy danych do pliku:

# mysqldump --all-databases > /home/lukasz/bazy_danych.sql

Plik należy pobrać (na przykład programem scp) oraz wysłać na serwer maria2.

Sekcja [mariadb] z pliku /etc/my.cnf.d/mariadb-server.cnf

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
log-bin
server_id=2
#read_only=1 # potem odkomentowac

Należy zrestartować MariaDB, żeby zaczytała zmiany. Następnie trzeba zaimportować bazy danych z serwera maria1:

mysql < /home/lukasz/bazy_danych.sql

Potem w shellu mysql:

change master to master_host='maria1.ping.local', master_user='replication_user', master_password='qwerty123', master_port=3306, master_log_file='master1-bin.000004', master_log_pos=334, master_connect_retry=10;

Następnie należy odkomentować lnijkę #read_only=1 w pliku mariadb-server.conf, dzięki czemu użytkownicy bez uprawnień SUPER nie będą mogli modyfikować bazy danych. Po zamianie pliku należy zrestartować bazę danych.

W shellu bazy danych na serwerze maria1 można już zdjąć blokady:

MariaDB [(none)]> unlock tables;

W shellu bazy danych na serwerze maria2 upewnić się, że replikacja przebiega pomyślnie:

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status;
+----------------------------------+-------------------+------------------+-------------+---------------+--------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------+------------------+--------------------------------+----------------------------+
| Slave_IO_State                   | Master_Host       | Master_User      | Master_Port | Connect_Retry | Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File           | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_SSL_Crl | Master_SSL_Crlpath | Using_Gtid | Gtid_IO_Pos | Replicate_Do_Domain_Ids | Replicate_Ignore_Domain_Ids | Parallel_Mode | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Slave_DDL_Groups | Slave_Non_Transactional_Groups | Slave_Transactional_Groups |
+----------------------------------+-------------------+------------------+-------------+---------------+--------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------+------------------+--------------------------------+----------------------------+
| Waiting for master to send event | maria1.ping.local | replication_user |        3306 |            10 | master1-bin.000004 |                 344 | mariadb-relay-bin.000012 |           645 | master1-bin.000004    | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 344 |            1257 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 |                |                    | No         |             |                         |                             | optimistic    |         0 |                NULL | Slave has read all relay log; waiting for more updates |                0 |                              0 |                          0 |
+----------------------------------+-------------------+------------------+-------------+---------------+--------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+----------------+--------------------+------------+-------------+-------------------------+-----------------------------+---------------+-----------+---------------------+--------------------------------------------------------+------------------+--------------------------------+----------------------------+
1 row in set (0.000 sec)

Można dla pewności założyć nową bazę danych na serwerze maria1, utworzyć tabelę i dodać kilka wierszy.

W shellu bazy maria2, używając nieuprzywilejowanego użytkownika, efekt będzie taki:

# mysql -u statyk -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.9-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show slave status;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation
MariaDB [(none)]> use statyk
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [statyk]> select * from dane;
+------+--------+
| id   | nazwa  |
+------+--------+
|    3 | trzy   |
|    4 | cztery |
|    5 | piec   |
+------+--------+
3 rows in set (0.001 sec)

MariaDB [statyk]> insert into dane (id, nazwa) values (8, 'osiem');
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
MariaDB [statyk]> 

Jako, że MariaDB w bardzo ograniczonym stopniu pozwala na konfigurację dostępu dla użytkowników z określonych adresów IP, warto upewnić się, że firewall jest poprawnie skonfigurowany. Podczas tworzenia użytkownika replica_user można podać mu jeden adres IP serwera maria2, ale wówczas nie można dodać do tego zestawu kolejnych replik.