eworldproblems
  • Home
  • About
  • Awesome Ideas That Somebody Else Already Thought Of
  • Perl defects
  • Books & Resources
Follow

The easiest way to (re)start MySQL replication



I’ve run mirrored MySQL instances with asynchronous replication from time to time for almost 15 years. The fundamentals haven’t changed much over that time, but one thing that has drastically improved is mysqldump‘s support for properly initializing slaves.

In order for your replication slave to have a complete and uncorrupted database, you need to arrange for it to use exactly the same data as was on the master at some particular instant in time as a starting point. In practice, this means taking out some kind of lock or transaction on the master while a complete copy of the database is made. Then, you need to tell the slave from what point in the master’s binary log to start applying incremental updates.

It used to be that doing all this required a lot of refreshing one’s memory by reading the MySQL manual in order to issue a variety of queries manually. But of course, there’s no reason the steps can’t be scripted, and I was pleased to discover this automation is now nicely packaged as part of mysqldump.

By including –master-data in the following command (to be run on the master), mysqldump will take out locks as necessary to ensure a consistent dump is generated, and automatically append a CHANGE MASTER TO query to the dump file with the associated master binary log coordinates:

$ mysqldump --add-drop-database --master-data -u root -p --databases list your replicated database_names here > /tmp/master-resync.sql

That way, you can simply apply this dump file to a slave server whose replication has broken (for example, due to an extended loss of connectivity) and restart the slave process to be back in business. On the slave:

$ cat /tmp/master-resync.sql | mysql -u root -p
$ mysql -u root -p
mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

Tadaa! Clean restart of MySQL replication without any FLUSH TABLES WITH READ LOCKs or manual copying of binlog coordinates in sight!

Posted in Linux
SHARE THIS Twitter Facebook Delicious StumbleUpon E-mail
← Keeping up on one’s OpenSSL cipher configurations without being a fulltime sysadmin
Running nodes against multiple puppetmasters as an upgrade strategy →

No Comments Yet

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Reset connection rate limit in pfSense
  • Connecting to University of Minnesota VPN with Ubuntu / NetworkManager native client
  • Running nodes against multiple puppetmasters as an upgrade strategy
  • The easiest way to (re)start MySQL replication
  • Keeping up on one’s OpenSSL cipher configurations without being a fulltime sysadmin

Categories

  • Computing tips
    • Big Storage @ Home
    • Linux
  • dev
    • devops
    • Drupal
    • lang
      • HTML
      • JavaScript
      • PHP
    • SignalR
  • Product Reviews
  • Uncategorized

Tags

Apache iframe malware performance Security SignalR YWZmaWQ9MDUyODg=

Archives

  • June 2018
  • January 2018
  • August 2017
  • January 2017
  • December 2016
  • November 2016
  • July 2016
  • February 2016
  • January 2016
  • September 2015
  • March 2015
  • February 2015
  • November 2014
  • August 2014
  • July 2014
  • April 2014
  • February 2014
  • January 2014
  • October 2013
  • August 2013
  • June 2013
  • January 2013
  • December 2012
  • November 2012
  • September 2012
  • August 2012
  • July 2012

Blogroll

  • A Ph.D doing DevOps (and lots else)
  • gavinj.net – interesting dev blog
  • Louwrentius.com – zfs@home with 4x the budget, other goodies
  • Me on github
  • My old edulogon.com blog
  • My old GSOC blog
  • My wife started baking a lot
  • Now it's official, my wife is a foodie

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

EvoLve theme by Theme4Press  •  Powered by WordPress eworldproblems