I need to improve backup of databases that won't load production server. I read that good solution to do that is to set database replication and do mysqldumps on slave server.

But description of "replicate_do_db" says that updates won't work with cross-database queries. And here is a problem - I have a lot of PHP apps (few of them are really old) so I have no guarantee that they not use this kind of queries. And I don't want to have out-of-sync databases...

Is there any alternative? Or just use replication only for well-known apps, and mydumper/mysqldump for other?

I know that Percona XtraBackup exist but I want to have backups in .sql format.

  • This would be better handled at dba.stackexchange.com – Rick James Jan 12 '19 at 2:28

replicate_do_db tells the system for which databases the SQL queries should be replicated (I'm assuming SQL replication, and not row replication).

This works like this, assuming replicate_do_db = DB_1

mysql> use DB_1;
mysql> insert into Table_1 values (1,2,3);

Now this insert statement will be replicated to the slave hosts.

mysql> use DB_2;
mysql> insert into DB_1.Table_1 values (2,3,4);

This insert statement will not be replicated, as you're working in DB_2, which is not replicated, even though you happen to specifiy DB_1 in the query.

mysql> user DB_1;
mysql> insert into DB_2.Table_2 values (9,8,7);

This insert statement will be replicated, so if the slave hosts don't have DB_2.Table_2, the replication will break.

This is in short what the cross-database query issue is. Note that select statements across different databases is not a problem, as only data-modifying statements are replicated, not data querying statements.

Presumably if the master has a number of databases that are used and updated in the application, you will want these updates all to be replicated to the slave hosts, so you then need to specify all those databases with replicate_do_db; I don't expect this to be an issue in your situation as you've described it.

Just make sure that you have some sort of monitoring of the replication status, so that if replication does break you're aware of the problem as soon as possible and can fix it before the replicated data becomes too old. Hint: show slave status; on the slave hosts and check that "Slave_IO_Running" and "Slave_SQL_Running" both are "Yes".

I also use mk-table-checksum every night to compare the master and slaves to see that the database tables are identical.

  • I didn't know that if I have DB_1 and DB_2 replicated it will work fine in case when cross DB_1 <-> DB_2 are executed. So the solution is to replicate all databases used in application. Thanks. – kenryk_hania Jan 16 '19 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.