Tutorial: MySQL Replication

Setting up MySQL Replication (Master/Slave)

Master

Slave

1. Install MySQL
yum install mysql-server  
2. Edit the /etc/my.cnf and add the following values.
log-bin             on  
expire-logs-days    5  
max-binlog-size     512M  
server-id           1  
Example /etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql

symbolic-links=0  
log-bin=on  
expire-logs-days=5  
max-binlog-size=512M  
server-id=1

[mysqld_safe]
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
3. Start mysql on the master.
service mysqld start  
4. Create a replication user.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<SlaveIP>' IDENTIFIED BY 'my_secure_password';  
5. Create a database dump:
mysqldump --all-databases --add-drop-database --master-data --routines --events > sqldump.sql  
6. Copy database dump to the slave:
scp sqldump.sql root@<SlaveIP>:/root/sqldump.sql  
1. Install MySQL
yum install mysql-server  
2. Edit the /etc/my.cnf and add the following values.
log-bin             on  
expire-logs-days    5  
max-binlog-size     512M  
server-id           2  
Example /etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql

symbolic-links=0  
log-bin=on  
expire-logs-days=5  
max-binlog-size=512M  
server-id=2

[mysqld_safe]
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
3. Start mysql on the master.
service mysqld start  
7. Import the database dump on the slave.
mysql < /root/sqldump.sql  
8. Add the replication credentials to the slave.
mysql> CHANGE MASTER TO MASTER_HOST='<MasterIP>', MASTER_USER='replication', MASTER_PASSWORD='my_secure_password'  
9. Start the slave.
mysql> START SLAVE;  
10. Check the slave has correctly synced with the data.
mysql> SHOW SLAVE STATUS\G  
NOTE: Important values to check for
                  Master_Host: <MasterIP>
                  Master_User: replication
                           ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                           ...
                   Last_Errno: 0
                   Last_Error:
                           ...
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error: