MySQL Replication – Is it Safe to Lock Tables on a MySQL Replica Slave?

MySQLmysql-replication

I have a master mysql database where the application writes and read production data, and then I setup a slave database just in case something goes wrong with the master database server (and I can do a quick switch).

On the slave database, I run reports.

I have to create daily backups for the database, I was thinking on creating them on the slave database, so the production environment can continue working without any delay, and now I have the option to lock or not the slave tables.

Is it safe to lock the tables to perform the backup (which could take like 30 minutes) and once the backup is done, mysql does automatically sync the slave database with the updates performed on the master database when the tables were locked?

Best Answer

Thats fine. The slave tables can be locked, and it will catch up the master later on (as long as the bin log is not expired, normally its set for couple of days)

Related Solutions

Mysql – Sporadic crash of master-slave MySQL replication process

Double check that your character sets on the databases and connections are what you expect them to be - your replication choking on a statement with a non-ASCII character is immediately suspicious.

Look at the master's binary log to see how the statement it logged is different from the statement that the replica is trying to execute - that will offer clues to the problem.

Sadly, you should also make sure that the versions of the server for both the master and slave are the same. I recently encountered a weird instance where we had bad SQL trying to update a non-NULL numeric column to NULL. In the version that the master was running, MySQL silently converted it to 0, but in the version that the replica was running, it was an error.

Also, instead of doing CHANGE MASTER to fix the replica, you should be doing

STOP SLAVE;
[correct and execute whatever statement is the problem]
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

http://dev.mysql.com/doc/refman/5.1/en/set-global-sql-slave-skip-counter.html

MySQL – Prevent Non-Replication Writes to MySQL Slave

Enable the read-only option in my.cnf. It can also be specified as a flag on the command line using --read-only with mysqld.

Best Answer

Related Solutions

Mysql – Sporadic crash of master-slave MySQL replication process

MySQL – Prevent Non-Replication Writes to MySQL Slave

Related Topic