3

I have a MySQL master server and slave became corrupt because of space issues on the master. Binary logs are potentially corrupt and I don't trust them. The databases are using MyISAM tables.

I want to create a new slave, but I can't afford to take the master down or lock the tables for a mysqldump.

Is there a way I can get a known place on the master to seed the slave without creating an outage? If not, what is the way to do this with the briefest outage?

1
  • before answer anything, You are use MyISAM - so no ways dump without locks, what is Your environment for what You so afraid do this? You have same loading 24x7?
    – a_vlad
    Feb 27, 2017 at 9:15

2 Answers 2

0

Can you shutdown the master server long enough to copy the data folder to another location?

You don't have to perform a mysqldump. You could just shutdown MySQL on the master server and copy the 'data' folder (folder having your database files) to another location (or server). Next, start both servers with replication disabled (skip-slave-start). Next, issue a RESET MASTER and RESET SLAVE on both servers. Then do a CHANGE MASTER on the slave. Finally, start the slave. If you are using GTIDs, then remove the auto.cnf file on the SLAVE so that it generates a new UUID (auto.conf is usually stored in the data folder).

See this for step-by-step instructions: http://mysql-vsjr.blogspot.com/2015/09/how-to-clone-gtid-mysql-56-server-using.html

2
  • No, no downtime is tolerable.
    – flickerfly
    Apr 4, 2017 at 12:27
  • Then you are hosed.
    – Van
    Apr 5, 2017 at 16:22
0

To answer @a_vlad, the environment hosts customer dbs for a SaaS.

Here is how we dealt with it:

Load occurs on some customer DBs around the clock. We ended up writing a script to detect usage, if no usage occurred during our lighter times, we did a mysql dump and import for that particular organization on the master, forcing a full sync to the slave for that database. We then ran this overnight for a month and were able to catch most of the organizations on that particular database master in a usage lull.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.