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?
0 / 0