Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I'm having a master/slave mysql setup. This night I had some problems with the master. I needed to recreate all databases again because they were corrupt.

Now I stopped my mysql slave but want to start it again and rebuild everything from scratch. Is there a way to do this? Without locking the master...

I also think the binlog file should be reset. So everything is clean again.

Any ideas?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

if you use innodb engine on the master - just use mysqldump with --skip-lock-tables, --single-transaction and --master-data=2 switches.

if your slave is only logically corrupted - try pt-table-sync with lock=0 option

share|improve this answer
If I set the master-data to 2 the change master query is put in comment. But how about the pointer in the binlog? And should I reset the binlog? –  Ward Loockx Sep 1 '12 at 9:37
you can set it to 1 to get it uncommented. i prefer to get have --master-data=2 and later - when i'm ready copy & paste the CHANGE MASTER TO to the mysql command line on a slave. dont reset binlog on master; i assume your slave will be a clean slate - so there's no need of resetting anything there. just reload the dump, issue CHANGE MASTER TO and include address and credentials of master host. –  pQd Sep 1 '12 at 9:42
Currently the slave is already configured, I just did STOP SLAVE. So I just need to import the new dump and that's it ? –  Ward Loockx Sep 1 '12 at 9:43
ok - so import new dump on slave; it'll drop all existing databases anyway. then issue CHANGE MASTER TO.. and SLAVE START. then check SHOW SLAVE STATUS\G. for a good measure run pt-table-checksum to make sure all is in sync. –  pQd Sep 1 '12 at 9:44

Here are step by step instruction how to rebuild MySQL slave from the master using Linux LVM snapshots. This is ideal method when you cannot afford much downtime to copy binary files to the slave. There are other ways of doing this but I always question the consistency of the slave. I take my backups from the slave so I absolutely need to make sure that my snapshots are consistent at all times.

Set innodb max dirty pages percent to zero. This will force MySQL to write all the pages to the disk which will significantly speed up the restart.

set global innodb_max_dirty_pages_pct = 0;

To monitor the number of dirty pages run the command

mysqladmin ext -i10 | grep dirty

Once the number stop decreasing you have reach the point to continue. Next reset the master to clear the old bin logs / relay logs:


Execute lvdisplay to get LV Path


Output will look like this

--- Logical volume ---
LV Path                /dev/vg_mysql/lv_data
LV Name                lv_data
VG Name                vg_mysql

Shutdown the master database with command

service mysql stop

Next take a snaphot, mysql_snapshot will be the new logical volume name. If binlogs are place on the OS drive those need to be snapshot as well.

lvcreate --size 10G --snapshot --name mysql_snapshot /dev/vg_mysql/lv_data

Start master again with command

service mysql start

Restore dirty pages setting to the default

set global innodb_max_dirty_pages_pct = 75;

Run lvdisplay again to make sure the snapshot is there and visible



--- Logical volume ---
LV Path                /dev/vg_mysql/mysql_snapshot
LV Name                mysql_snapshot
VG Name                vg_mysql

Mount the snapshot

mkdir /mnt/mysql_snapshot
mount /dev/vg_mysql/mysql_snapshot /mnt/mysql_snapshot

If you have an existing MySQL slave running you need to stop it

service mysql stop

Next you need to clear MySQL data folder

cd /var/lib/mysql
rm -fr *

Back to master. Now rsync the snapshot to the MySQL slave

rsync --progress -harz /mnt/mysql_snapshot/ targethostname:/var/lib/mysql/

Once rsync has completed you may unmount and remove the snapshot

umount /mnt/mysql_snapshot
lvremove -f /dev/vg_mysql/mysql_snapshot

Create replication user on the master if the old replication user doesn't exist or password is unknown

GRANT REPLICATION SLAVE on *.* to 'replication'@'[SLAVE IP]' identified by 'YourPass';

Verify that /var/lib/mysql data files are owned by the mysql user, if so you can omit the following command:

chown -R mysql:mysql /var/lib/mysql

Next record the binlog position

ls -laF | grep mysql-bin

You will see something like

-rw-rw----     1 mysql mysql  1073750329 Aug 28 03:33 mysql-bin.000017
-rw-rw----     1 mysql mysql  1073741932 Aug 28 08:32 mysql-bin.000018
-rw-rw----     1 mysql mysql   963333441 Aug 28 15:37 mysql-bin.000019
-rw-rw----     1 mysql mysql    65657162 Aug 28 16:44 mysql-bin.000020

Here the master log file is the highest file number in sequence and bin log position is the file size. Record these values:


Next start the slave MySQL

service mysql start

Execute change master command on the slave by executing the following:


Finally start the slave


Check slave status:


Make sure Slave IO is running and there are no connection errors. Good luck!

My full blog post is found at for more information:

share|improve this answer

Follow this:

  • Disconnect slave and master
  • Just drop all databases from slave
  • dump data from master with single-transaction
  • use dump on slave
  • reconnect master and slave, and wait for sync-ing
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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