MySQL Master-Slave Replication on CentOS / RHEL
Posted on 19 March 2010 by Jason Grimme
Today I set up some MySQL replication for a server that I set up a few weeks ago. This was my first time doing replication and it was quite a learning experience. This was using MySQL 5.0 and CentOS 5.3, but this should work for most semi-recent versions of both.
In this setup, transactions are mirrored to the slave server as they happen on the master.
In this tutorial I’ll use the following setup:
Master Server: 10.1.100.1 Slave Server: 10.2.200.2 MySQL Data path: /var/lib/mysql MySQL slave user named slave_user
[Master]
First, edit the master server MySQL config file. Add/Replace the following lines
1 | vim /etc/my.cnf |
# [mysqld] section # Start Modification # First line is probably already there datadir = /var/lib/mysql server-id = 1 relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/var/mysql-relay-bin.index log-error = /var/lib/mysql/mysql.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 # Stop Modification
Restart MySQL to load the changes
1 | service mysqld restart |
[Slave]
Now we’ll do about the same thing on the slave server
1 | vim /etc/my.cnf |
# [mysqld] section # Start Modification # First line is probably already there datadir = /var/lib/mysql server-id = 2 relay-log = /var/lib/mysql/mysql-relay-bin relay-log-index = /var/lib/mysql/mysql-relay-bin.index log-error = /var/lib/mysql/var/mysql.err master-info-file = /var/lib/mysql/mysql-master.info relay-log-info-file = /var/lib/mysql/mysql-relay-log.info # Stop Modification
Restart MySQL to load the changes
1 | service mysqld restart |
[Master]
Now we need to tell MySQL where we are replicating to and what user we will do it with.
1 2 3 4 | mysql -u root -p mysql> STOP SLAVE; mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password'; mysql> FLUSH PRIVILEGES; |
Now we will test that this side of the replication is working and get the location that we will start the replication from.
1 2 3 4 5 6 7 8 9 | mysql> USE Any_database_name; mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 451228 | | | +------------------+----------+--------------+------------------+ 1 ROW IN SET (0.00 sec) |
Write down the File, Position number, as this is where we will start the replication from.
Here it is a good idea to do a dump of your master database(s) and pipe it into your slave server.
1 | mysqldump -u root --all-databases --single-transaction --master-data=1 > /home/MasterSnapshot.sql |
[Slave]
First grab your SQL dump file from the master server. You can use whatever method you would like to transfer the file. SCP example:
1 | scp root@10.1.100.1:/path/to/MasterSnapshot.sql root@10.2.200.2:/home/MasterSnapshot.sql |
Import the SQL file into MySQL
1 | mysql -u root -p < /home/MasterSnapshot.sql |
Now we’ll set the slave to read from the master server, starting at the record position we wrote down earlier. Make sure you use the MASTER_LOG_FILE and MASTER_LOG_POS from a few steps back.
1 2 3 | mysql> CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G; |
Make sure that from the resulting output you have the following:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If you don’t, check the MySQL error log to find out what is causing the problem 
Tags | CentOS, Linux, MySQL, Replication
hello,
i follow your tut & at step SHOW MASTER STATUS; it return Empty set (0.00 sec).
Replication dont work & i dont know why
i use centos 5.4, mysql 5.0.77
huu2uan. Did you try restarting the slave?
ok, i dont put config in [mysqld] section
It works Man. Great Quick article. I followed the same and it works perfectly.
Worked perfectly for me. Thanks for the straight forward write up.