Mysql uses a Master-slave/Publisher-Subscriber model for Replication. MySQL replication is an asynchronous replication. In MySQL replication master keeps a log of all of the updates performed on the database. Then, one or more slaves connect to the Master(Publisher Server), read each log entry, and perform the indicated update on the slave (Subscriber) server databases. The master server is responsible for the track of log rotation and access control.
Each slave server has to keep the track of current position within the server’s transaction log. As new transactions occur on the server, they get logged on the master server and downloaded by each slave. Once the transaction has been committed by each slave, the slaves update their position in the server’s transaction log and wait for the next transaction.
In this article, I will show you the steps to configure the Master/Slave replication between two servers.
Step 1: Create a user on Master server which Slave server can use to connect. I have created the user named “repl_user”.
--Connect to MySQL Master server mysql -u root -proot --Execute the below code GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Step 2: We have to change the MySQL configuration file usually in the /etc/mysql.cnf location. Here we will add the replication configuration parameters.
log-bin – will be used to write a log on the desired location
binlog-do-db – will be used to enabled the database for writing log. I have used Publisher_Database, you have to specify your database name.
server-id – Specify the ID of the Master server
log-bin = /home/mysql/logs/mysql-bin.log binlog-do-db=publisher_database server-id=1
Step 3: Once you have added the above configuration parameters into the My.cnf, next step is restart the MySQL Master Instance.
You can use below command to restart the MySQl service.
/etc/init.d/mysqld restart service mysqld restart
Step 4: We have to configure the /etc/my.cnf file on the slave server. Here we will add the below parameters in the configuration file.
server-id – gives the Slave its unique ID
master-host – tells the Slave the I.P address of the Master server for connection. You can get the IP address using IPConfig command.
master-connect-retry – Here we will specify the connection retry interval.
master-user – Specify the user which has permission access the Master server
master-password – Specify the password of the replication user mentioned above
replicate-do-db – Specify the subscriber database name
relay-log – direct slave to use relay log
server-id=2 master-host=128.20.30.1 master-connect-retry=60 master-user=repl_user master-password=password replicate-do-db=subscriber_slave relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index
Step 5: Restart the slave MySQl instance
/etc/init.d/mysqld restart service mysqld restart
Step 6: If your Master MySQL instance is live instance, you have to do the backup/restore using MySQLDump utility.
--Connect to MySQL Master server mysql -u root -proot --Stop the write operation FLUSH TABLES WITH READ LOCK; --Generate the dump of the database (backup) --gzip command will compress the file and create the zip file name backup.sql.gz mysqldump publisher_master -u root -p > /home/my_home_dir/backup.sql; gzip /home/my_home_dir/backup.sql; --execute below copy command on slave to copy the backup file scp root@128.20.30.1:/home/my_home_dir/database.sql.gz /home/my_home_dir/ --Once copied, extract the file using gunzip gunzip /home/my_home_dir/backup.sql.gz --restore the databsae mysql -u root -p subscriber_slave </home/my_home_dir/backup.sql
Step 7: Execute the SHOW MASTER STATUS command on Master server. It will give you the bin log file name and position which we will use specify the slave.
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 707 | exampledb | |
+------------------+----------+--------------+------------------+
Step 8: Execute the below commands on slave.
--Connect to MySQL Slave server mysql -u root -proot --stop the slave slave stop; -- Execute the below command CHANGE MASTER TO MASTER_HOST='128.20.30.1', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=707; --Start the Slave slave start;
Step 9: Login to Master MySQL instance and unlock the tables.
--Connect to MySQL Master server mysql -u root -proot -- unlock the tables if you have executed lock tables command unlock tables;
You are all set. Master to Slave replication has been started. Make sure while configuring the my.cnf file.
1. Take the copy of my.cnf file before starting the replication configuration.
2. Make sure skip-networking parameter is not enabled in the my.cnf file.
