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

Thursday, 8 March 2018

Master -Master Replication in Active-Active and Active-Passive mode

In this article I'm explaining how exactly Master-master Replication working in different Mode.

Master-Master replication is working is two mode . 
1: Active-Active Mode 
2: Active-Passive Mode

In both scenario the architecture/Configuration is same but in case of active-active mode we need to more attention on monitoring .

As I already shown the flow diagram of Master -Master replication in Fig1.

Fig1:Architecture of Master-Master Replication
There are two important threads working in replication.

1: Slave_IO Thread 
2: Slave_SQL Thread

Slave_IO thread reads the binlog file written by other Master and write the same in it's Relay log and then Slave_SQL thread read it's own relay log file and execute the transactions .

Active-Passive Mode:-

In this mode one master is Live and another one is standby only . Transactions are performed only on one master.
eg. We are considering Master1 is active(Live) and Master2 is Passive(Standby - not active).

At a time only master1 writing a binlog (MySql-binlog 1) on master1 accordingly this binlog is read by Slave_IO thread of Master2 and written in Relay log(RelayLog 2) of master2 . After that Slave_SQL thread of master2 read the Relay Log2 and execute the transaction on master2.

And so on the data of Master1 has been replicated on Master2.

Active-Active Mode:- 

In this mode both master server are live, it means simultaneous transaction can perform in both master.

In this mode two important parameter is come in knowledge.
auto_increment_increment= 2
auto_increment_offset  = 1 
[ For more details about these parameters work and how it configure, please Click Here ]

This parameter maintains the consistency of primary Key Value and simultaneous transaction must be replicated with same transaction id.

At a time master1 writing a binlog (MySql-binlog 1) on master1 and  master2 writing a binlog (MySql-binlog 2) on master2. 
Slave_IO thread of master2 read "Mysql-binlog 1" and write a RelayLog2 on master2 .After that  Slave_SQL thread of master2 read the Relay Log2 and execute the transaction on master2. 
As same as  Slave_IO thread of master1 read "Mysql-binlog 2" and write a RelayLog1 on master1. After that  Slave_SQL thread of master1 read the Relay Log1 and execute the transaction on master1. 

Monday, 19 February 2018

MySql Replication - Slave Lagging behind master

Issue: second_behind_master is continuously increasing --

Some common causes of replication slave lags include:
  1. CPU load: Slave Server is to busy to process replication events in a timely manner.
  2. Missing indexes: no primary key/Unique key on the table that is being updated .
  3. Lock wait timeouts: lock wait timeouts caused by uncommitted transaction on the slave.
  4. Data consistency: frequent slave stops and starts due to data consistency issues (duplicate keys or row not found).
  5. Slow queries: Some Sql_thread take much more time to execute queries.
  6. Network issues: due to somehow network issue like server bandwidth/data transfer on network speed etc.

We can perform below troubleshooting to minimize the lag...

First of all match the server timestamp of both Master-Slave.

Analyze which thread is reason for behind IO_THREAD or SQL_THREAD
MySQL> show slave status \G;

Keep an eye on the Relay_Master_Log_File and make sure that it is going up and catching up with the Master_Log_File.
If Master_Log_File: "mysql-bin.000990" and Relay_Master_Log_File: "mysql-bin.000990"  is equal it's means IO_THREAD is caching up master log properly.

Stop Slave IO_THREAD;

Monitor Exec_Master_Log_Pos: xxxxxxxxxx - if this is constant it's means SQL_THREAD is facing some issue while executing . If it's also increasing then you can check below other parameters.

           Slave_IO_State: Waiting for master to send event
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000173
      Read_Master_Log_Pos: 15909435
           Relay_Log_File: mysqld-relay-bin.000079
            Relay_Log_Pos: 91173356
    Relay_Master_Log_File: mysql-bin.000093
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
               Last_Errno: 0
             Skip_Counter: 0
      Exec_Master_Log_Pos: 91173210
          Relay_Log_Space: 8179978166
          Until_Condition: None
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
    Seconds_Behind_Master: 486330
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
           Last_SQL_Errno: 0
         Master_Server_Id: 1
1 row in set (0.00 sec)

No query specified
Herein you can check Seconds_Behind_Master that high, Now I Look at the following:
Relay_Log_Space: 8179978166
You have approx 7GB of relay logs to process.
Master_Log_File: mysql-bin.000173
Relay_Master_Log_File: mysql-bin.000093
This tell me that you have read up to mysql-bin.000173, but you are currently processing from the mysql-bin.000093.

This also tell me you have about 80 binary logs on the Master, each about 100 MB.
The Seconds_Behind_Master is simply the NOW() minus the TIMESTAMP set at mysql-bin.000093(Relay_Master_Log_File) position 91173210 (Exec_Master_Log_Pos).

As long as Slave_SQL_Thread is Yes, the relay logs get processed
  • Relay_Log_Space will decrease each time a relay log is done
  • Exec_Master_Log_Pos will increase until current relay log is done, then resets to the beginning of the next relay
  • TIMESTAMP keeps increasing, which make Seconds_Behind_Master decrease (NOW() minus the TIMESTAMP set at Relay_Master_Log_File position Exec_Master_Log_Pos)
After all troubleshooting you can do following some changes on Slave Server and then check..

  • Set innodb_flush_log_at_trx_commit=0
  • innodb_lock_wait_timeout=200 (According to database working performance)
  • transaction-isolation=READ-COMMITTED    (This is global parameter ,so please first check with set global tx_isolation='READ-COMMITTED'; )
  • Enable Slow Query Log and observe/optimize if any slow queries found.
  • Check CPU Load and server performance.
  • Check network latency/speed/bandwidth etc.

Note: Please read the impact of all above mentioned parameter from MYSQL website before doing any changes on your Server. 

For any Clarification please write us Click Here

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...