Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Sunday, 20 May 2018

How to configure MySQL Replication using Percona XtraBackup without any downtime?

Configure Replication in Consistent Mode
-No Downtime
Percona XtraBackup

Before starting the replication with Percona XtraBackup, You will need to check some points and key notes as mentioned below.

Summary: All the things you will need
ü Installing Percona XtraBackup from Percona yum repository (Skip if already installed)
ü Make a backup on Master and prepare it using XtraBackup
ü Move existing Data on Slave to other Folder
ü Copy backed up data to Slave.
ü Configure the Master MySQL server.
ü Configure the Slave MySQL server.
ü Start the replication

All the things you will need:

Setting up a slave for replication with Percona XtraBackup is really a very straightforward procedure. In order to keep it simple, here is a list of the things you need to follow the steps without hassles:
The Master db01 system with a MySQL-based server installed, configured and running. This system will be called The Master, as it is where your data is stored and the one to be replicated. We will assume the following about this system:
  • The MySQL server is able to communicate with others by the standard TCP/IP port;
  • The SSH server is installed and configured;
  • You have a user account in the system with the appropriate permissions;
  • You have a MySQL’s user account with appropriate privileges.
  • Server has binlogs enabled and server-id set up to 1.

The Slave db02 another system, with a MySQL-based server installed on it. We will refer to this machine as The Slave and we will assume the same things we did about The Master, except that the server-id on the Slave is 2.
Percona XtraBackup The backup tool we will use. It should be installed in both computers for convenience.

Note: It is not recommended to mix MySQL variants (Percona Server, MySQL, MariaDB) in your replication setup. This may produce incorrect xtrabackup_slave_info file when adding a new slave.

Install Percona XtraBackup

Testing the repository
Make sure packages are now available from the repository, by executing the following command:

yum list | grep percona

You should see output similar to the following:

percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64


db01 - Master MySQL Server
db02 - Slave MySQL Server

On db01 only, install Percona XtraBackup using Percona’s repos:

[alok@db01 ~]# yum install
[alok@db01 ~]# yum install percona-xtrabackup-24
On db01 only, confirm it installed XtraBackup version 2.3 or newer:
[alok@db01 ~]# xtrabackup –version
Result like...
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
Take XtraBackup and setup MySQL Replication using Percona XtraBackup

On db02 only, rename the existing MySQL datadir, and create a fresh folder:
[alok@db02 ~]# service mysqld stop
[alok@db02 ~]# mv /var/lib/mysql /var/lib/mysql.old
[alok@db02 ~]# mkdir /var/lib/mysql
On db01 only, create the backup, make the snapshot consistent by applying the log, and copy it over to db02 using either scp or rsync:

[alok@db01 ~]# sudo mkdir /home/alok/perconabackup
[alok@db01 ~]# sudo innobackupex --user=root --password=mysql /home/alok/xtra_Bkup_18may/ 
Will get result like…
180518 10:56:37 [00]        ...done
180518 10:56:37 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2008941'
xtrabackup: Stopping log copying thread.
.180518 10:56:37 >> log scanned up to (2008941)

180518 10:56:37 Executing UNLOCK TABLES
180518 10:56:37 All tables unlocked
180518 10:56:37 Backup created in directory '/home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/'
MySQL binlog position: filename 'mysql-bin.000001', position '118445'
180518 10:56:37 [00] Writing /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/backup-my.cnf
180518 10:56:37 [00]        ...done
180518 10:56:37 [00] Writing /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/xtrabackup_info
180518 10:56:37 [00]        ...done
xtrabackup: Transaction log of lsn (2008941) to (2008941) was copied.
180518 10:56:37 completed OK!

[alok@db01 ~]# sudo innobackupex --user=root --password=mysql --apply-log /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/ 

This will get result like..

InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 118445, file name mysql-bin.000001
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 2010645
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2011550
180518 11:00:09 completed OK!

Now Copy Backup folder to slave server…
[alok@db01 ~]# sudo scp -r /home/alok/perconabackup/2018-05-17_14-58-31/ alok@
[alok@db01 ~]# sudo rsync -r /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/ alok@
On db02, fix the ownership of the datadir, startup MySQL, and grab the binlog name and position:
[alok@db02 ~]# sudo chown -R mysql:mysql /var/lib/mysql
[alok@db02 ~]# sudo service mysqld start
NOTE: Troubleshooting
Once you will start mysqld service this may happen to fail in start service. Kindly follow the below troubleshooting one-by-one
1. Check /var/lib/mysql folder having chown mysql:mysql if not kindly set to mysql as owner.
2. Check /var/lib/mysql/AAAA.err file having some error like ibdata access permission denied . So provide chmod –R 777 /var/lib/mysql . If still issue follows the next…
3. You will need to disable Selinux at once.
[alok@localhost ~]$ sudo vi /etc/selinux/config
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX= disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
4. After that reboot server and check mysql service started. Once checked working fine then again you can revert SELINUX setting as back. 

When MySQL service start successfully then…

[alok@db02 ~]# cat /var/lib/mysql/xtrabackup_binlog_info
db01-bin-log.000001     120
Now on db02, startup slave replication:
[alok@db02 ~]# mysql –u root –p
mysql> CHANGE MASTER TO MASTER_HOST='10.x.x.x', MASTER_USER='repl', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='db01-bin-log.000001', MASTER_LOG_POS=120;
mysql> start slave;
mysql> show slave status\G
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

1 comment:

Unknown said...

Where did you create a user for replication purpose...add that topic also

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...