How to create a replication (Master/Slave) in MySQL

Overview

This post is based on my work on Master/Slave environments creation.
I Discover 2 way of creating a Slave/Replica from a Master.
the first way is by using a cold backup of the/var/lib/mysql folder from the Master which means that you will have to have a down time on your application if you want a consistent backup.
The second solution that I found out not long ago during a Slave/Replica crash that I couldn't resync with the master since they were out of sync since about 60 days. That second option for creating a Slave/Replica from a Master is by using a mysqldump backup using to proper options.
So here are the 2 solutions to share.

These 2 procedures assume that both Master and Slave/Replica hosts have already been install with MySQL.

If not you can follow this link to install MySQL How to Install MySQL on linux using RPM

NOTE:

Assuming
master host = 192.168.10.1
slave/replica host = 192.168.10.2 

Create a Slave/Replica using a cold backup (requires applications down time)

1. Create a replication account on the Master

create user repl@'192.168.10.1' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE, REPLICATION CLIENT 
        ON *.*
        TO repl@'192.168.10.1' [ IDENTIFIED BY 'yourpassword' ];

GRANT REPLICATION client, REPLICATION CLIENT 
        ON *.*
        TO repl@'192.168.10.1' [ IDENTIFIED BY 'yourpassword' ]; 

2. Modify the /etc/my.cnf file on the Master

under [mysqld] 

expire_logs_days=2 #  purge bin logs older than 2 days
log-bin=/var/lib/mysql/log-bin # bin location (use full path)
server-id=100 # unique id for the master (default is 1)
sync_binlog=1 # to sync the binlogs 

if you are using InnoDB it is recommended to use

innodb-flush-log-at-trx-commit=1 # This will flush the log write
innodb-support-xa=1 # mysql 5.5 and later 

3. Take note of the Master current bin log and position

This information will be needed when setting the Slave/Replica later one.

show master status\G

*************************** 1. row ***************************
            File: log-bin.000056
        Position: 425307404
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec) 

4. Restart your Master to enable the new parameters

You need to restart the Master in order to take the new parameters from themy.cnf.

service mysql restart

5. Do a cold backup of the Master

This backup is a cold backup of the /var/lib/mysql folder using the cp command.

service mysql stop
cp -Rp /var/lib/mysql  /tmp/.
cd /tmp
tar -zcvf mysql.tar mysql
rm -Rf /tmp/mysql

6. Restart the master

service mysql start

7. Transfer the backup to the Slave/Replica host using SCP

cd /tmp
scp mysql.tar root@192.168.10.2:/tpm/.

8. Stop MySQL on the slave host (192.168.10.2)

service mysql stop

9. Remove the content of /var/lib/mysql on the slave

I highly suggest that you take a backup of /var/lib/mysql before you erase it.

cd /var/lib/mysql
rm -Rf *

10. Untar the mysql.tar into /var/lib on the Slave

cp /tmp/mysql.tar /var/lib/.
cd /var/lib
tar -xvf mysql.tar

NOTE: if you are using 5.6 and later remove the file auto.cnf in /var/lib/mysql/ to avoid errno 1593

Errno: 1593
Fatal error: The slave I/O thread stops because master and slave
have equal MySQL server UUIDs; these UUIDs must be different for
replication to work.

11. Change the /etc/my.cnf on the slave

    under  [mysqld]

slave-skip-errors=1062 # skip duplicate transaction errors
log-bin=/var/lib/mysql/log-bin # log bin location
relay_log=/var/lib/mysql/mysql_relay-bin # Relay log location
log_slave_updates=1
server-id=101 # we usually put the last digits of the slave IP
skip-slave-start # Does not restart replication on instance restart

I recommend that once the Slave/Replica will be created and sync with the Master that you remove the slave-skip-errors=1062 from the my.cnf file then that you restart the Slave/Replica and you restart the replication.

12. Start MySQL on the Slave

service mysql start

13. Configure the replication on the slave

Note: Remember the bin log file name and position from step 3

CHANGE MASTER TO
    MASTER_HOST='192.168.10.1', 
    MASTER_USER='repl',
    MASTER_PASSWORD='yourpassword',
    MASTER_LOG_FILE='binlognamefrompoint3',
    MASTER_LOG_POS=positionfrompoint3;

14. Start the replication on the slave

start slave;

15. Validate the replication

In the result of this command the most important line is Seconds_Behind_Master.
If the value in the line is NULL, this means that your replication with the Master is not working.
If the value is greater than 0, this means that your replication is catching on. if you run this command few time, the value should go down.
If the value is equal to 0, that means that your replication is running and up to date.

show slave status\G 

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000056
          Read_Master_Log_Pos: 982311504
               Relay_Log_File: mysql_relay-bin.000130
                Relay_Log_Pos: 60222517
        Relay_Master_Log_File: log-bin.000056
             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: 982311504
              Relay_Log_Space: 60406973
              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: 123

Create a Slave/Replica using a mysqldump backup (No down time)

This option is almost identical to the first one except this time you are using a mysqldump backup and there will be no down time on the master.

1. Create a replication account on the Master

create user repl@'192.168.10.2' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE, REPLICATION CLIENT 
        ON *.*
        TO repl@'192.168.10.2' [ IDENTIFIED BY 'yourpassword' ];

2. Modify the /etc/my.cnf file on the Master

under [mysqld] 

expire_logs_days=2 #  purge bin logs older than 2 days
log-bin=/var/lib/mysql/log-bin # bin location (use full path)
server-id=100 # unique id for the master (default is 1)
sync_binlog=1 # to sync the binlogs 

if you are using InnoDB it is recommended to use

innodb-flush-log-at-trx-commit=1 # This will flush the log write
innodn-support-xa=1 # mysql 5.5 and later 

3. Take note of the Master current bin log and position

This information will be needed when setting the Slave/Replica later one.

show master status\G

*************************** 1. row ***************************
            File: log-bin.000056
        Position: 425307404
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec) 

4. Restart your Master to enable the new parameters

You need to restart the Master in order to take the new parameters from themy.cnf.

service mysql restart

5. Take a backup of the Master using mysqldump

mysqldump -uroot --password=your_password --all-databases  --add-drop-database --flush-logs --master-data=2  > /tmp/mysql_backup.sql

gzip mysql_backup.sql

6. Restart the master

service mysql start

7. Transfer the backup to the Slave/Replica host using SCP

cd /tmp
scp mysql_backup.gz root@192.168.10.2:/tpm/.

8. Unzip the mysql_backup.sql.gz

cd /tmp 

gunzip mysql_backup.sql.gz 

9. Change the /etc/my.cnf on the slave

    under  [mysqld]

slave-skip-errors=1062 # skip duplicate transaction errors
log-bin=/var/lib/mysql/log-bin # log bin location
relay_log=/var/lib/mysql/mysql_relay-bin # Relay log location
log_slave_updates=1
server-id=101 # we usually put the last digits of the slave IP
skip-slave-start # Does not restart replication on instance restart

I recommend that once the Slave/Replica will be created and sync with the Master that you remove the slave-skip-errors=1062 from the my.cnf file then that you restart the Slave/Replica and you restart the replication.

10. Start MySQL on the Slave

service mysql start

11. Restore the mysqldump backup into the Slave/Replica environment

this restore will overwrite all the databases.

cd /tmp

mysql -uroot -pyour_password < mysql_backup.sql

12. restart the Slave/Replica instance

service mysql restart

13. Configure the replication on the slave

Note: Remember the bin log file name and position from step 3

CHANGE MASTER TO
    MASTER_HOST='192.168.10.1', 
    MASTER_USER='repl',
    MASTER_PASSWORD='yourpassword',
    MASTER_LOG_FILE='binlognamefrompoint3',
    MASTER_LOG_POS=positionfrompoint3;

14. Start the replication on the slave

start slave;

15. Validate the replication

In the result of this command the most important line is Seconds_Behind_Master.
If the value in the line is NULL, this means that your replication with the Master is not working.
If the value is greater than 0, this means that your replication is catching on. if you run this command few time, the value should go down.
If the value is equal to 0, that means that your replication is running and up to date.

show slave status\G 

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000056
          Read_Master_Log_Pos: 982311504
               Relay_Log_File: mysql_relay-bin.000130
                Relay_Log_Pos: 60222517
        Relay_Master_Log_File: log-bin.000056
             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: 982311504
              Relay_Log_Space: 60406973
              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: 123