Follow us on Social Media:

MySQL Database Administrator

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

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_Host: 192.168.200.178
              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
          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: 91173210
          Relay_Log_Space: 8179978166
          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: 486330
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: 1
1 row in set (0.00 sec)

ERROR: 
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

Thursday, 8 February 2018

Database Recovery Using binlog file (mysql-bin.xxxxx)

Database Recovery Using binlog file (mysql-bin.xxxxx)

mysqlbinlog Utility for Processing Binary Log Files.

In this  article, I'm discussing about the use of mysqlbinlog Utility. How to read the binary logs , How to use it in case of Disaster recovery , how to export it in other readable format etc. I will start our discussion from basic and then drill in deep step-by-step.


Before starting our discussion, I would like to put a small footprint about MySQL binary log.



The binary log files that the server generates are written in binary format.MySQL records all the transaction in binary log. But we can not able to read this log normally due to it's binary format .To examine these files in text format, use the mysqlbinlog utility. As this file contains all the transaction so we can use this log for any Disaster Recovery / Point-in-Time recovery* .



We can also use mysqlbinlog to read relay log files written by a slave server in a replication* setup. Relay logs have the same format as binary log files.



How to display the content of  binary log file named mysql-bin.xxxxx ?


>> mysqlbinlog  [options] mysql-bin.xxxxx

eg.

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe  mysql-bin.xxxxx 

We can export this file to other format like .sql/.txt etc

eg.
C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe  mysql-bin.xxxxx > D:\GetMySQL.sql


We can also read this log from remote server by using some additional parameters like --read-from-remote-server ,--host, --password, --port, --protocol, --socket, --user etc.


Some of the important parameter's which we use in DB recovery as --database=name, --debug-info , --start-datetime=datetime , --stop-datetime=datetime , --start-position=N , --stop-position=N 


Once again I bring to mind you that Binary log is by-default disabled. So you need to enable* it before starting MySQL services. Binary Log is also use in setup of MySQL Replication*

For example, we have 5 binlog mysql-bin.00001,mysql-bin.00002,mysql-bin.00003,mysql-bin.00004,mysql-bin.00005

We have some limitation in this recovery.

1: We must have to restore any old backup which contains all the database/ table .
2: Need to administrative privileges to perform this activity.
3: Server health must be good in which we will perform this activity.

Now perform below steps in your task...

1: Restore any available old backup .
2: read and export all the binlog file to readable format.
3: Analyze binlog file Start time/Stop Time/Start Position/Stop Position etc.
4: Now we use the below commands for restoring...

For restoring mysql-bin.00001

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe mysql-bin.00001 | mysql -h [host ip] -u root -p ;

If you want to recover by Specifying time stamp..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --stop-time="2018-02-07 23:59:59" d:\mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-time="2018-02-07 00:00:01" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-time="2018-02-07 00:00:01" --stop-time="2018-02-07 23:59:59" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

If you want to recover by Specifying particular position..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --stop-position="255124" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-position="107" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --start-position="107" --stop-position="255124" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

If you want to recover by Specifying the operation perform any particular database..

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqlbinlog.exe --database="getmysql" --start-time="2018-02-07 00:00:01" --stop-time="2018-02-07 23:59:59" mysql-bin.00001 | mysql -h 192.168.200.178 -u root -p ;

Some more parameter also useful in recover which are already mention earlier .


Note: I'm suggesting to perform this activity on standby server to avoid any miss happening after that propagate the same on production server.


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