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
