MariaDB replica setup

MariaDB uses asynchronous replication based on binary logs (binlog). Master (source) writes changes to the binary log, slave (replica) reads the binlog from the master and replays events locally. Replication is one-way by default (master to slave).

Master configuration

Configure MariaDB by editing /etc/my.cnf.d/mariadb-server.cnf

[mysqld]
server-id=1
log_bin=binlog
binlog_format=ROW
bind-address = 0.0.0.0

bind-address = 0.0.0.0 assures that the server is accessible from the outnet. Check with netstat

netstat -plunt|grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      417359/mariadbd

Restart MariaDB, then check the binary log file name and the binary log position:

MariaDB [(none)]> SHOW MASTER STATUS;  
+---------------+----------+--------------+------------------+ 
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+---------------+----------+--------------+------------------+ 
| binlog.000001 |    14446 |              |                  | 
+---------------+----------+--------------+------------------+ 
1 row in set (0.000 sec)

Create the user for the replication from the slave:

CREATE USER 'replica'@'SlaveIP%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'SlaveIP';
FLUSH PRIVILEGES;

Dump the databases you want to backup (vpopmail, roundcubemail and spamassassin in my example):

mysqldump -u root -p --databases vpopmail roundcubemail spamassassin --single-transaction --master-data=2 > dump.sql

Slave configuration

Prepare the server by editing /etc/my.cnf.d/mariadb-server.cnf. Assign a unique id:

# replica 
server-id=2       # unique id
log_bin=binlog    # to revert master - slave 
read_only=ON      # cannot alter the database
# databases to replicate (it will read only these db from log)
replicate-do-db=vpopmail
replicate-do-db=roundcubemail
replicate-do-db=spamassassin

Log into MariaDB, stop the current slave (if it exists) and drop the databases to be cloned;

STOP SLAVE;
RESET SLAVE ALL;
DROP DATABASE IF EXISTS vpopmail;
DROP DATABASE IF EXISTS spamassassin;
DROP DATABASE IF EXISTS roundcubemail;

Use scp to copy the dump you have done earlier (here I am connecting via secure key):

scp -i '/root/.ssh/ed25519' root@MasterIP:/root/dump.sql .

Import the dump:

mysql -u root -p < dump.sql

Open the dump.sql file and identify the line holding the log file and the log position:

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=65327;

The same thing can be achieved by using grep

grep "CHANGE MASTER TO" dump.sql      
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=65327;

Enter the slave server and configure the master:

CHANGE MASTER TO MASTER_HOST='MasterIP', MASTER_USER='replica', MASTER_PASSWORD='replicaPWD', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=65327;

Then start the slave on MariaDB and verify its status:

MariaDB [(none)]> START SLAVE; 
Query OK, 0 rows affected (0.000 sec) 

MariaDB [(none)]> SHOW SLAVE STATUS\G 
*************************** 1. row *************************** 
               Slave_IO_State: Waiting for master to send event 
                  Master_Host: MasterIP
                  Master_User: replica 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: binlog.000001 
          Read_Master_Log_Pos: 719355 
               Relay_Log_File: mariadb-relay-bin.000003 
                Relay_Log_Pos: 357109 
        Relay_Master_Log_File: binlog.000001 
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes 
              Replicate_Do_DB:  
          Replicate_Ignore_DB:  
           Replicate_Do_Table:  
       Replicate_Ignore_Table:  
      Replicate_Wild_Do_Table:  
  Replicate_Wild_Ignore_Table:  
                   Last_Errno: 0 
                   Last_Error:  
                 Skip_Counter: 0 
          Exec_Master_Log_Pos: 719355 
              Relay_Log_Space: 357420 
              Until_Condition: None 
               Until_Log_File:  
                Until_Log_Pos: 0 
           Master_SSL_Allowed: No 
           Master_SSL_CA_File:  
           Master_SSL_CA_Path:  
              Master_SSL_Cert:  
            Master_SSL_Cipher:  
               Master_SSL_Key:  
        Seconds_Behind_Master: 0 
Master_SSL_Verify_Server_Cert: No 
                Last_IO_Errno: 0 
                Last_IO_Error:  
               Last_SQL_Errno: 0 
               Last_SQL_Error:  
  Replicate_Ignore_Server_Ids:  
             Master_Server_Id: 1 
               Master_SSL_Crl:  
           Master_SSL_Crlpath:  
                   Using_Gtid: No 
                  Gtid_IO_Pos:  
      Replicate_Do_Domain_Ids:  
  Replicate_Ignore_Domain_Ids:  
                Parallel_Mode: optimistic 
                    SQL_Delay: 0 
          SQL_Remaining_Delay: NULL 
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 
             Slave_DDL_Groups: 0 
Slave_Non_Transactional_Groups: 90 
   Slave_Transactional_Groups: 980 
         Replicate_Rewrite_DB:  
1 row in set (0.000 sec)

If Slave_IO_Running: Yes and Slave_SQL_Running: Yes it's ok. Seconds_Behind_Master inform us if the server is aligned.

You can insert data into Master and check if they are replicated no Slave.

Promoting the backup server to production

Connect to mariadb from command line and check the slave status and that the slave is synced with master (Seconds_Behind_Master: 0):

SHOW SLAVE STATUS\G

Check that:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

If slave is perfectly synced with master stop the replica

STOP SLAVE;
RESET SLAVE ALL;

Set the mariadb server writable

SET GLOBAL read_only=OFF;

Exit from MariaDB command line and modify the config file so that read_only is commented out.

[mysqld] 
# replica 
server-id=2 
log_bin=binlog 
#read_only=ON 
replicate-do-db=vpopmail 
replicate-do-db=roundcubemail

Restart the server. Now the database server is in production.

Add a comment