MySQL High Availability, 2nd Edition

Appendix A. Replication Tips and Tricks

This appendix is a collection of useful tips and tricks for running, diagnosing, repairing, and improving MySQL replication. They are supplemental material and, as such, may not contain all of the details needed for a full tutorial. Consult the online MySQL Reference Manual for more details about MySQL replication.

The last few sections of this appendix describe features that will be offered by MySQL soon, but are not officially available at the time of this writing.

Examining the Binary Log with Verbose

If you are using row-based logging, you can use the --verbose option to see a reconstruction of the queries in the event. The following shows what this looks like when run on a binary log with row-based logging:

$ mysqlbinlog --verbose master-bin.000001
BINLOG '
qZnvSRMBAAAAKQAAAAYCAAAAABAAAAAAAAAABHRlc3QAAnQxAAEDAAE=
qZnvSRcBAAAAJwAAAC0CAAAQABAAAAAAAAEAAf/+AwAAAP4EAAAA '/*!*/;
### INSERT INTO test.t1
### SET
### @1=3
### INSERT INTO test.t1
### SET
### @1=4

Notice that the values are given using @n-style names for the columns. This is because row-based replication is transferring the record and applying it by column position, but ignores the names of the columns.

Using Replication to Repopulate a Table

If a table on your slave becomes corrupt either through error or accident (e.g., a user deletes the data), you can use replication to recover the data. Do so by creating a temporary table on the master that is a copy of the original table, dropping ...