MySQL is an open-source most popular database nowadays. It comes with a variety of built-in replication features, which helps us to maintain ‘n’ no of copies of your database.
MySQL replication is a process that allows data to be replicated from one server to the other in real-time. It is mainly used as a disaster recovery solution. In the case of any hardware failure, MySQL replication ensures that an accurate backup exists all the time.
In this article, you will see step by step process to achieve MySQL master-slave replication.
To set up MySQL replication first prerequisite is to have two servers and should have connectivity in between them. Also assuming that you have MySQL already installed in both of the servers. In case you need any guide related to MySQL installation please visit my post.
Here is our details –
Master Server IP : 10.201.64.102
Slave Server IP : 10.201.64.103
Index
Configure the Master / Source Database
There are certain changes that need to be incorporated before the master or source database will start replicating the data into a slave in real-time. Let’s see what’s are those changes – you need to change some parameters in the my.cnf file.
binlog_format=mixed
server-id = 1bind-address = 10.201.64.102
Creating New Replication User In Master
The next step is to create a new user for the slave server. The command is as follows –
CREATE USER 'repl'@'10.201.64.103' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.201.64.103' IDENTIFIED BY 'repl';
FLUSH PRIVILEGES;Use below command to check Master status -
mysql> SHOW MASTER STATUS;
Take Database Backup and Transfer to Slave
Use the below command to take the backup of the master database and SCP to the slave node.
bin/mysqldump --defaults-file=/mysqldatabase/TEST/admin/my.cnf -uroot -pmysql --master-data=2 --quick --single-transaction -vvv --force -A > /mysqldatabase/dump/TEST_all.sqlRestore the Backup to Slave
By using below command you can restore the database backup to slave.
nohup /mysqldatabase/product/binary/5.7.35/bin/mysql -uroot -pmysql -S/mysqldatabase/RESTORE/admin/mysql.sock < /mysqldatabase/dump/TEST_all.sql &Foreign key related errors can be checked –
mysql> set global foreign_key_checks=0;Configure the Slave
Need to incorporate some changes in the my.cnf file and restart the slave.
server-id = 2 relay-log = /mysqldatabase/RESTORE/logs/target-relay-bin.index
To log in to slave use the below command which you will get from the import logfile.
CHANGE MASTER TO MASTER_LOG_FILE='TEST-mysql-bin.000005', MASTER_LOG_POS=2417,master_host='10.201.64.102', master_user='repl', master_password='repl',master_port=3306;Start the Slave
Use the below command to start the slave.
mysql> START SLAVE;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.168.190.15
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: TEST-mysql-bin.000002
Read_Master_Log_Pos: 7640
Relay_Log_File: target-relay-bin.000002
Relay_Log_Pos: 1879
Relay_Master_Log_File: TEST-mysql-bin.000002
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: 7640
Relay_Log_Space: 2087
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_UUID: 9d1367c4-238d-11ec-aeab-0800273cc1fa
Master_Info_File: /mysqldatabase/SLAVE/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Test the Replication
To check the replication you can now create a sample database and check whether its showing at the other end i.e. at the slave node.
Conclusion
The above article has provided you with enough information about how to set up a MySQL master-slave replication. The steps mentioned here in this article specifically to help beginners to understand the process of MySQL replication and start implementing the MySQL Master -Slave replication process in their environment.
By completing this tutorial, you will have set up a MySQL replication environment that uses MySQL’s binary log file position-based replication method with one source and one replica. Bear in mind, though, that the procedure outlined in this guide represents only one way of configuring replication in MySQL. MySQL provides a number of different replication options which you can use to produce a replication environment optimized for your needs. There are also a number of third-party tools, such as Galera Cluster, that you can use to expand upon MySQL’s built-in replication features.
Having a master-slave replication configured in your development environment is useful if you need it for a scale-out solution in the production environment. This way, you will also have separate data sources configured for write and read operations so you can test locally that everything works as expected before further deployment.
Additionally, you may want to have several slave instances configured on the same machine to test the load balancer that distributes the read operations to several slaves. In that case, you may use this same manual setup for other slave instances by repeating all the same steps.