1) Make sure whether the binary logging is enabled on the master database server and to make InnoDB aware of the current binary log position, you must run at least one transaction while binary logging is enabled. For example I'm executing following command,
mysql> insert into first values(1,'siva','dasari'); Query OK, 1 row affected (0.10 sec)
2) Verify the binlog position on master database server.
mysql> show master status \G +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 432 | repl01 | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
3) Take full backup as image file.
mysqlbackup --backup-dir=/usr/siva/mysql_full_backup --backup-image=full.bki backup-to-image
4) Copy the backup folder to target server. (make sure the destination directory has right permissions)
scp -r /usr/siva/mysql_full_backup/ siva@192.168.159.143:/usr/siva/
5) Stop the mysql service on destination.
systemctl stop mysqld
6) Remove all the files in mysql data directory.
rm -rf /var/lib/mysql/
7) Grant owner permissions to the account used to access the backup image file location.
chown -R siva:siva /usr/siva/
8) Use following command to restore full backup by applying the logs.
mysqlbackup --datadir=/var/lib/mysql --backup-image=/usr/siva/mysql_full_backup/full.bki --backup-dir=/usr/siva/mysql_full_backup copy-back-and-apply-log
9) Edit the my.cnf file on the new slave and add skip-slave-start and event_scheduler=off under the [mysqld] section.
10) Bring the mysql service online. (Initially it failed to bring the service online, fixes are provided below)
systemctl start mysqld
Issues encountered while bringing the service online.
i) Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied)
Fix: chown -R mysql:mysql /var/lib/mysqld (add permissiona to mysql user)
ii) SELinux is preventing /usr/sbin/mysqld from getattr access on the file /var/lib/mysql/ibdata1.
Fix: restorecon -R -v /var/lib
11) Once the slave instance is up and running. Issue following command from mysql client on slave,
mysql> RESET MASTER;12) From the restored directory find the file named "backup_gtid_executed.sql" which contains following commands. (update the file as required.)
# Stop the binary logging on slave mysql> SET sql_log_bin=0
# On a new slave, issue the following command if GTIDs are enabled: SET @@GLOBAL.GTID_PURGED='e65fa8h2-0a1e-11f5-b980-100205633290:1-3';
# To use GTID handshake protocol uncomment below and modify it as required. CHANGE MASTER TO MASTER_HOST='192.168.159.141',MASTER_USER='slave_user', MASTER_PASSWORD='****', MASTER_AUTO_POSITION = 1;
13) After updating the file execute the script file as below,
mysql>source /usr/siva/mysql_full_backup/backup_gtid_executed.sql
14) Execute following commands once you connect on mysql instance,
mysql> UPDATE mysql.event SET status = 'SLAVESIDE_DISABLED';
15) Remove the edits from my.cnf which are added as part of step -9.
16) Reatart the slave mysql service.
systemctl stop mysqld
systemctl start mysqld
17) Once the service is successfully started check the slave status as below,
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event Master_Host: 192.168.159.141 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 432 Relay_Log_File: mysqlslave-relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave is up & running succefully.
========================================================
