CentOS MySQL Master-Slave Replication - Step by Step
MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves).
I would assume that you have MySQL installed on both servers, so we would get straight into the Master-Slave setup.
I would use the following two servers:
192.168.1.1 - Master
192.168.1.2 - Slave
1. We are going to start with the master:
- SSH to the master server
- Edit the /etc/my.cnf file
Add the following entries under [mysqld] section and don’t forget to replace the database name with database name that you would like to replicate on Slave.
server-id = 1 binlog-do-db=my_database relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/mysql-relay-bin.index log-error = /var/lib/mysql/my_database.err master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/mysql-relay-log.info log-bin = /var/lib/mysql/mysql-bin
- Restart mysql
service mysql restart
- Create the replication user (change the Pass-Goes-Here part with your new pass):
- Note this goes on the slave, so that the master could then connect to the slave server
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'Pass-Goes-Here'; FLUSH PRIVILEGES;
New we have to export the database that we would like to replicate.
- Open a second SSH session
In the first session run:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
You would see something like:
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 11128001 | my_database | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Take a note of the the File (mysql-bin.000003) and Position (11128001) numbers
While the tables have been locked with the read lock - go ahead and export the database in question via the Second SSH session without closing the first one!
mysqldump my_database > /root/my_database.sql
Once that has been done unlock the tables via the first ssh session
UNLOCK TABLES; quit;
Now we are ready to start with the slave setup. Go ahead and copy the dump from the master to the slave.
scp /root/my_database.sql email@example.com:/root/
Then SSH to the slave server.
- Edit the my.cnf:
Under the [mysqld] section add the following and update the details accordingly:
server-id = 2 replicate-do-db=my_database relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/mysql-relay-bin.index log-error = /var/lib/mysql/my_database.err master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/mysql-relay-log.info log-bin = /var/lib/mysql/mysql-bin
Now import the dump file that we exported in earlier command and restart the MySQL service.
mysql -u my_database -p < /root/my_database.sql
Note you might need to create the database before importing the data:
mysql CREATE DATABASE my_database;
Login into MySQL as root user and stop the slave:
Then in order to start the replication run the following and make sure th update the details accordingly:
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave_user', MASTER_PASSWORD='Pass-Goes-Here', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=11128001; start slave; show slave status\G
Then when you run the show slave status command you should see something like this:
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.1 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 441654154 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: my_database Master_Server_Id: 1 Master_Info_File: /var/lib/mysql/mysql-master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Auto_Position: 0 1 row in set (0.00 sec)
To fully test the replication you can try creating a table on the master and then check if it was successfully replicated over to the slave.
This is pretty much it. :)