13 Sep 2008, 12:46

MySQL: Replication hints


When you have to handle more queries than your DB is able to handle, if you want to improve reliability or if you don’t want to shut your db down for backups, then you’ll probably look at Replication. For MySQL there is a quite good howto in the official documentation but unfortunately some questions are left open.

First of all you should read the MySQL documentation related to replication. Its well written and contains much important information. If your are setting up exclusive slave nodes, i.e. nodes which only serve as slave and don’t contain data different from the master, you make sure that your slave won’t get corrupted if you set the whole DBMS into read only mode. Just add the line “read_only = 1” to your my.cnf. Put it below the server_id setting.

The MySQL documentation will tell you that you can use your DB dump to set up further slave nodes. That is right, but remember that you need to make a new DB dump as soon as the latest binlog, which was used during the dump, is expired, i.e. deleted/compressed by logrotate. You will get “file binlog.xxx not found” errors in the syslog of the slave if you don’t remember this. How long it takes for the binlog to expire depends on the load on your DB.

When you make a copy/dump of your DB you have basicly two options: Backup raw-table files or use mysqldump. It suggest you use mysqldump with the options lock-table and master data: “mysqldump –lock-tables –master-data –all-databases”. This will add the master-information (only binlog position and offset) to the dump. Don’t forget to set the other master information (host, port, user, pass, …) before you start the slave.