MySQL Backup – Gain Precise Position of Live Database During Full Backup

backupMySQLmysqlbinlogmysqldump

I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups.

The production server is on the west coast while business hours are on the east coast.

When I perform the fullback up I query the master for is log and position. I put these values in the filename of the zip that I compress the databases to.

I'm having difficulties using mysqlbinlog to create incremental sql file from a certain position that I know the backup has ended at.

Would running Show Master Status then then running mysqldump --flush-logs --single-transaction... suffice to get accurate information in order to restore.

I know the ideal setup is to backup a slave database since locking won't hurt the production environment but at the moment I haven't made the switch.

Am I not getting the desirable results because I'm not locking all the tables?

Thanks in advance

EDIT

I realized with the help of RolandoMySQLDBA that I wasn't pointing to the appropriate binlog file. I was instead pointing to the whole directory binlog/bin-log.[0-9]* > sql.txt. So with just the position I was hitting logs that started much earlier than desired. So I changed my script to pull the appropriate binlog file as well as the position and that worked like a charm.

Thanks

Best Answer

When you are using mysqldump, you can get the binary logs coordinates of the backup at the point-in-time of the the mysqldump's start.

Just add the --master-data option

mysqldump --master-data=2 --flush-logs --single-transaction...

Using --master-data=2 will record the binary log filename and position in the form of a CHANGE MASTER TO command. It is recorded as a comment.

Using --master-data=1 will also record the binary log filename and position in the form of a CHANGE MASTER TO command. It is not recorded as a comment, but as a command that will be executed when you reload the mysqldump.

In both cases, you can always find that CHANGE MASTER TO command on line 23 of the dump. I mentioned this before in my old post MySQL Exec_Master_Log_Pos value greater than Read_Master_Log_Pos under the heading "BUT WAIT..."

To retrieve that line from a mysqldump file named myfullbackup.sql, simply run

head -23 myfullbackup.sql | tail -1 > MasterLogFilePos.sql

and the CHANGE MASTER TO command will be in it

If you run the mysqldump with

mysqldump --master-data=2 --flush-logs --single-transaction...

the Master will continue allowing reads and writes to all InnoDB tables.

Related Solutions

Mysql – Looking for an efficient way to fix “Could not parse relay log event entry…” error

There is more stable approach you can try

Here is something to remember

Whenever you run CHANGE MASTER TO, it will erase every relay log you have. You do not want to keep relay logs of commands you have not executed any SQL on as of yet

The following is an excerpt taken from a post I made back on Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication :

Please notice that there are two sets of replication coordinates from the Master

(Master_Log_File,Read_Master_Log_Pos)

(Relay_Master_Log_File,Exec_Master_Log_Pos)

There is a major difference between them

(Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs.

(Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs THAT IS NEXT TO BE EXECUTED ON THE SLAVE.

What you want are two things:

Erase Every Binary Log You Have
Start Collecting Binary Log Entries From the Last SQL You Successfully Executed.

In your case, you must use the second set of Replication Coordinates

Relay_Master_Log_File
Exec_Master_Log_Pos

It is easy to distrust a corrupt relay log as shown in the error message. The one that hurts the most is a corrupt Master Log. You will have to jump through hoops if that is the case. On the other hand, if one of the other situations was the reason for the corrupt relay log, the simplest and most concise approach is what I stated.

To make sure, whatever is reported for Relay_Master_Log_File, if that particular binary log still exists on the Master, perform a mysqlbinlog on it. If it dumps in its entirety without corrupt characters, go ahead and use the second set of replication coordinates.

From my same earlier post

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           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: 858190247
            Relay_Log_Space: 873772
            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: 0
1 row in set (0.00 sec)

notice that the Replication Coordinates from SHOW SLAVE STATUS\G for what was last executed are (mysql-bin.000254,858190247). The CHANGE MASTER TO command in this case would be:

CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;

Give it a Try !!!

UPDATE 2012-09-14 16:38 EDT

If you worried about the stockpiling relay logs, just throttle the relay logs. In SHOW SLAVE STATUS\G, there is a field called Relay_Log_Space. That gives you the sum of all relay sizes in bytes. Did you know you could put a cap on that number ?

The option is called relay_log_space_limit.

For example, if you want to cap the total number of bytes to 10G, do the following

STEP 01) Add this to /etc/my.cnf on the Slave

[mysqld]
relay_log_space_limit = 10G

STEP 02) Run service mysql restart on the Slave

and that's it !!!

When the oldest relay has all its entries processed, it is deleted and a new relay log is created. That gets filled until all relay logs add up to 10G. That's the only way to control runaway relay log space issues.

UPDATE 2012-09-14 18:10 EDT

SUGGESTION : If you make mysqldump backups of the data on the Slave every midnight, you could set up the following to restrict having 1TB of binary logs:

STEP 01) Add this to /etc/my.cnf on the Master

[mysqld]
expire_logs_days = 14

STEP 02) Run this query on the Master

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 14 DAY;

STEP 03) service mysql restart on the Master

STEP 04) Add a mysqldump backup script to a crontab on the Slave

This will make the Slave more useful and would control having excess binary logs to worry about

Mysql – Replication master binlog rotation when network is unavailable

Whenever a Slave loses network connectivity, the IO thread simply dies and tries to reconnect every 60 seconds. You did the right thing in pointing the Slave back but let's clarify what to use to point back with.

For the give SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.17.20.102
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002814
          Read_Master_Log_Pos: 823078734
               Relay_Log_File: relay-bin.007364
                Relay_Log_Pos: 823078879
        Relay_Master_Log_File: mysql-bin.002814
             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: 823078734
              Relay_Log_Space: 823079071
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

WHAT YOU SHOULD NOT PICK

Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.
Read_Master_Log_Pos represents the position within Master_Log_File of the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.

WHAT YOU SHOULD PICK

Relay_Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was executed on the Slave.
Exec_Master_Log_Pos represents the position within Relay_Master_Log_File of the last successfully executed SQL statement on the Master that was executed on the Slave.

In most cases, when caught early enough, Master_Log_File and Relay_Master_Log_File are the same. When there is significant replication lag or if the SQL thread breaks and the IO Thread keeps collecting, Master_Log_File and Relay_Master_Log_File will be different.

CONCLUSION

ALWAYS PICK Relay_Master_Log_File and Exec_Master_Log_Pos as a restart point.

Here are my other posts that reemphasize this paradigm:

Dec 05, 2011 : Does `Seconds_Behind_Master` show exact Slave Lag from Master?
Jan 04, 2012 : MySQL Slave replication: Need to determine where replication left off to start slave in correct location
Jan 24, 2012 : Mysql master-master replication auto flush old logs (Under Reason #3)
Apr 04, 2012 : Seconds Behind Master in Cloud Service

CAVEAT #1

Please do not be concerned with binlog rotation on the Master when it comes to Replication's status. The Slave's IO Thread is very sensitive to the Master's change. Here is how you can verify this:

STEP 01 : On the Slave, run SHOW SLAVE STATUS\G

STEP 02 : On the Master

For MySQL 5.5, run FLUSH LOGS; or FLUSH BINARY LOGS;
Prior to MySQL 5.5, run FLUSH LOGS;

STEP 03 : On the Slave, run SHOW SLAVE STATUS\G

You will see the Master_Log_File and Read_Master_Log_Pos change immedaitely.

CAVEAT #2

Since Relay_Master_Log_File has the most recent binary from the Master to be executed on the Slave, that's the log you keep on the Master. From the above SHOW SLAVE STATUS\G, you would run

PURGE BINARY LOGS TO 'mysql-bin.002814';