MySQL – Applying Same Binary Log

MySQLreplication

Quick question – if I apply same binary log multiple times via:

mysqlbinlog --start-position=xxx mysql-bin.xxxx | mysql

will the server understand and omit previously applied changes or further corrupt the database?

Also, in my backup I have the log position stated like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=155;

However, when I do 

SHOW MASTER STATUS

this is what I get:

+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
| File      | Position      | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set      |
+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
| mysql-bin.000002 | 497824459     |                   |                       |                        |
+ --------- + ------------- + ----------------- + --------------------- + ---------------------- +
1 rows

I understand that the number from the backup should be more accurate because the backup was made in transaction, but my confusion is how comes the numbers are so different? (in a matter of 30 minutes) Or these are different numbers and I should use the 155 number for the
–start-position option?

Best Answer

It it evident from your question, that 30 min worth of transactions came into the database while the mysqldump was in progress.

In order for the binlog position to move and the mysqldump to work, the mysqldump parameter --single-transaction had to have been issued. What does it do ???

According to MySQL Documentation on mysqldump --single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

To dump large tables, combine the --single-transaction option with the --quick option.

The log file and position recorded in the mysqldump (usually between lines 23-25) simply give you the place to start when setting up a MySQL Replication Slave.

EXAMPLE

Suppose you ran mysqldump --single-transaction from IP 10.20.30.40 (call it Server M1) and loaded that mysqldump into another server (the one to become the MySQL Replication Slave, call it Server S1).

You run the following commands on S1

CHANGE MASTER TO
    master_host='10.20.30.40',
    master_post=3306,
    master_user='replicationusername',
    master_password='replicationpassword',
    master_log_file='mysql-bin.000002',
    master_log_pos=155;
START SLAVE;

What this will do is activate two DB threads

  • I/O Thread : This will copy all binlog events from the initial position you gave it or the last position it successfully executed from the master (M1). When it gets to the end of the binlog it was reading, it will move to the next binlog (mysql-bin.000003) and keep going from there.
  • SQL Thread : This will execute all binlog collected from the I/O until there are no more. Then, the SQL thread will wait for new events from the I/O Thread to the collected.

What are binlog events ?? The recorded SQL commands that a Master Server records when the Master has executed them.

Would you like to know what the binlog events look like ???

You would go to M1 and run the following

mysqlbinlog --start-position=155 --stop-position=497824459  mysql-bin.000002 > binlog_events.sql
less binlog_events.sql

The file binlog_events.sql will contain all the binlog events that executed on the Master while the mysqldump was in progress.

If you want those events on another server without setting up MySQL Replication, you could load the mysqldump and then load binlog_events.sql into a new server and have the dump just as up-to-date as possible.

YOUR ACTUAL QUESTION

Yes, you will further corrupt the DB.

Related Question