Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

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

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.

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.

Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

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.