Mysql 5.1 master-slave replication

Finally did this, at least a simple Master -> Slave arrangement, but still pretty cool and great for any case that your production server is (mostly) read-only.

dev.mysql.com/5.1/replication

No real issues to add. The mysql site (see link above) has pretty clear instructions.

Still would like to try a full replication cluster someday.


quick fix if replication stops:
mysql -u root --password="xxx" -Bse "stop slave"
mysql -u root --password="xxx" -Bse "reset slave"
mysql -u root --password="xxx" -Bse "slave start"

Script to check and log/email replication errors:
mysql_replication_check_script.txt
will also try the above quick fix procedure. Enter mysql user details and email as well as log location prior to use.


Fixing replication via full reset:

If the above quick-fix doesn’t work, step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
And copy the values of the result of the last command somewhere.

Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -uroot -p –all-databases > /a/path/mysqldump.sql
Now you can release the lock, even if the dump hasn’t end. To do it perform the following command in the mysql client:

UNLOCK TABLES;
Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;
Load master’s data dump with this console command:

mysql -uroot -p < mysqldump.sql
Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;
And to check that everything is working again, if you type

SHOW SLAVE STATUS;
you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

(thanks david-espart @SO)

Leave a Reply