Can I just play relay logs as sql file?

Question

Can I just play relay logs as sql file? I have a server that is lagging for 3 months from master now. I was thinking can I just record the log position from relay logs and make a SQL dump and play the transactions on the slave server.

Answer 1

Replaying relay logs as SQL is exactly what replication is doing. Or, trying to do.

The problem of lagging happens when the replica can't play the SQL changes as fast as the master created those changes. This is not uncommon, because the master created the in many threads, while the replica has to replay them in a single thread (until MySQL 5.6 improved the capability to set slave_parallel_workers).

Eventually, the lag might get so bad, and be so difficult to catch up, that you might consider just reinitializing the replica using a current backup of its master.

But even if you do that, you might see the lag creep up again.

You have a few other strategies you can use to address this problem:

• Are the queries optimized? If the queries the replica needs to replay are slow, this could make everything harder to replay. A typical example is if you use row-based replication but your tables have no primary key constraint defined. In that case, every update or delete will cause a table-scan for every row. The remedy is to make sure every table has a primary key. Even if you don't use RBR, make sure updates and deletes are optimized with an index.

• Do you need a more powerful server for your replica? It might do better if it has more RAM, or faster disks, or a faster CPU. Upgrading to a CPU with more cores probably won't help.

• Do you need to make fewer writes to your database on the master? Consider if you're using the database in lieu of a cache or a queue. Those are probably uses of the database that require a lot of rapid changes, and it could be generating too much volume in the binlogs.

• Is it time to split this MySQL instance into more than one instance, so each instance takes a subset of writes?