Set Up MySQL/MariaDB Master-Slave Replication

MySQL Master-Slave replication allows for data to be automatically copied to multiple computers. Changes are made to the designated “master” node and replicated to one or more designated “slave” nodes for read-only applications.

This tutorial provides step-by-step instructions for setting up a MySQL/MariaDB master-slave database configuration. CentOS 8 and MariaDB 10.5 are used in this example.

Two servers are used in this tutorial, db01 (the master node) and db02 (the slave node). If you already have a database running on the master node, you must dump and restore a backup of the database into the slave node prior to enabling replication.

First, open port 3306 in the firewall.

# firewall-cmd --add-service=mysql
# firewall-cmd --add-service=mysql --permanent

Connect to the master server and create a replication user.

# mysql -uroot -p

MariaDB [(none)]> grant replication slave on *.* TO [user]@'[private IP of db02]' identified by '[some password]';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit

Edit the server.cnf file on the master server and add the following lines to the [mysqld] section. If you only want to replicate specific databases, add a binlog-do-db=[database] line with the database name to be replicated.

# vim /etc/my.cnf.d/server.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-ignore-db = "mysql"

Restart the service.

# systemctl restart mysqld

Connect to the master server and lock the tables to read-only.

# mysql -uroot -p

MariaDB [(none)]> flush tables with read lock;

Display the master status and make note of the file and position.

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 653
    Binlog_Do_DB:
Binlog_Ignore_DB: mysql
1 row in set (0.000 sec)

Backup any existing databases

# mysqldump -u root -p --databases [database-1] [database-2] ...  > /root/db_dump.sql

Connect to the master node and unlock the tables.

# mysql -uroot -p

MariaDB [(none)]> unlock tables;

Copy the database backups to the slave server.

# scp /root/db_dump.sql [private-IP-of-db02]:/root/

Edit the server.cnf file on the slave server and add the following lines to the [mysqld] section.

# vim /etc/my.cnf.d/server.cnf

[mysqld]
server-id = 2

Restart the service.

# systemctl restart mysqld

Import the backup copied from the master server.

# mysql -uroot -p < /root/db_dump.sql

Connect to the slave server and connect it to the master.

# mysql -uroot -p

MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to MASTER_HOST='[private-IP-of-db01]',
MASTER_USER='[replication-username]',
MASTER_PASSWORD='[replication-password]',
MASTER_LOG_FILE='[file-listed-on-master-status]',
MASTER_LOG_POS=[log-position-listed-on-master-status];
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.13.37.21
                   Master_User: replication
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 342
                Relay_Log_File: centos8b-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000004
              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: 342
               Relay_Log_Space: 867
               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: 0
    Slave_Transactional_Groups: 0

The Slave_IO state should show “Waiting for master to send event.” If it shows “Connecting to Master” please check the log file for any errors. By default, it is /var/log/mysqld.log but may be configured differently on your system.

Connect to the master server, create a database, and insert some test data. The database and records should be automatically replicated to the slave server.

# mysql -uroot -p

MariaDB [(none)]> create database testing;
MariaDB [(none)]> use testing;
MariaDB [testing]> create table users (id int not null auto_increment, primary key(id), username varchar(30) not null);
MariaDB [testing]> insert into users (username) values ('foo');
MariaDB [testing]> insert into users (username) values ('bar');
MariaDB [testing]> quit

 

 

Was this helpful?

YesNo

0 / 0

#centos #centos 8 #mysql #replication