Some notes on MySQL replication configuration

server version

MySQL supports replication from one major version to the next higher major version, but not the reverse. That’s to say, version of slave should newer than or equal to that of master.
Also, do not use 3 different versions in your setup, even minor version differ. That’s not supported and may cause you trouble.
One day one of our MySQL slave suddenly stopped replication, and the error log said:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'character set utf8... ... load data infile '/tmp/xxx.xx' into table xxx character set utf8 ...

After a quick search on MySQL reference manual I found that the “character set” option in LOAD DATA statement was added in mysql 5.0.38. Unfortunately, that MySQL slave is of version 5.0.22 so it doesn’t know that syntax, whereas the master version is 5.0.96 and it’s happy to execute the statement.

server-id

Do remember to set unique server-id for different mysql instance.

log-bin relay-log

set this two option explicitly, or you may encounter problem when upgrade/migration.
some sample error message that’s due to lack of these option when server environment change:

130124 22:41:08 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts
as a slave and has his hostname changed!! Please use '--relay-log=MyHost-relay-bin' to avoid this problem.
130124 22:41:08 [ERROR] /usr/local/mysql-5.0.22/libexec/mysqld: File './MyHost-relay-bin.001098' not found (Errcode: 2)
130124 22:41:08 [ERROR] Failed to open log (file './MyHost-relay-bin.001098', errno 2)
130124 22:41:08 [ERROR] Failed to open the relay log './MyHost-relay-bin.001098' (relay_log_pos 235)
130124 22:41:08 [ERROR] Could not open log file
130124 22:41:08 [ERROR] Failed to initialize the master info structure

slave_skip_error

some error may occur on master, such as table crash due to disk/filesystem problem. But the tables are good on slave, and it’s safe to ignore these errors, we can use slave_skip_error for that.

#when encounter these error, continue to replicate
#1062: Duplicate entry '%s' for key %d 
#1053: Server shutdown in progress
#1049: Unknown database '%s' 
#1146: Table '%s.%s' doesn't exist 
#1194: Table '%s' is marked as crashed and should be repaired 
#1050: Table '%s' already exists 
slave-skip-errors=1062,1053,1049,1194,1146,1050

Reference: MySQL Reference Manual  16.1 Replication Configuration

This entry was posted in Database, System Administration. Bookmark the permalink.

Leave a Reply