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) Execute following commands once you connect on mysql instance,
mysql>CHANGE MASTER TO MASTER_HOST='192.168.159.141',MASTER_USER='slave_user', MASTER_PASSWORD='****', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=432;
mysql> UPDATE mysql.event SET status = 'SLAVESIDE_DISABLED';
12) Remove the edits from my.cnf which are added as part of step -9.
13) Reatart the slave mysql service.
systemctl stop mysqld
systemctl start mysqld
14) 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.
========================================================
No comments:
Post a Comment