Linux – MySQL slave server restart

Hey I know a crazy method to create a slave without augmenting any operation of master (ServerA) or slave (ServerB)

Step 1) Setup a New Server (ServerC)

Step 2) On ServerC, Install MySQL (same version as ServerB)

Step 3) On ServerC, service mysql stop

Step 4) Copy /etc/my.cnf from ServerB to ServerC

Step 5) On ServerC, change server_id to a value different from ServerA and ServerB

Step 6) rsync /var/lib/mysql on ServerB to ServerC

Step 7) When rsync is completed, run "STOP SLAVE;" on ServerB

Step 8) rsync /var/lib/mysql on ServerB to ServerC

Step 9) On ServerB, run "START SLAVE;"

Step 10) On ServerC, service mysql start

Step 11) On ServerC, run "START SLAVE;" (Do this if skip-slave-start is in /etc/my.cnf)

Give it a Try !!!

BTW I have the utmost confidence this will work because I just did this for client over the last 2 days. Client had 2.7TB of data on a slave. I rsyncd to another server while the slave was still active. rsync took like 11 hours. I then ran STOP SLAVE; on the first slave and ran rsync again. That took another hour. I then performed the above step and everything is done.

There are two alternatives

ALTERNATIVE #1 : Use XtraBackup

It is capable of copying MyISAM as well as InnoDB on a running master.

ALTERNATIVE #2 : Run rsync multiple times

You could run rsync against /var/lib/mysql on a master and copy it to /var/lib/mysql on a slave. Of course, I would run rsync several times. Until the final rsync you should run the FLUSH TABLES WITH READ LOCK. Before copying make sure you hose all binary logs and start from scratch.

Before running anything, please make sure binary logs are written in /var/lib/mysql on both master and slave by having something like the following in /etc/my.cnf:

[mysqld]
log-bin=mysql-bin

Please try running this script in the event you do not want to shutdown MySQL on the master:

mysql -u... -p... -e"SET GLOBAL innodb_max_dirty_pages_pct = 0; RESET MASTER;"
RSYNCSTOTRY=7
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
mysql -u... -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);"
sleep 60
SLEEPID=`mysql -u... -p... -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
rsync -r * slaveserver:/var/lib/mysql/.
mysql -u... -p... -e"KILL ${SLEEPID};"

I am a little more conservative in terms of data and index pages being cached while doing this. Personally, I prefer to shutdown mysql after several rsyncs instead of the FLUSH TABLES WITH READ LOCK. Another alternative to this script would be the following script which shuts down mysql for the final rsync:

mysql -u... -p... -e"RESET MASTER;"
RSYNCSTOTRY=7
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
service mysql stop
rsync -r * slaveserver:/var/lib/mysql/.
service mysql start

That's all for the rsync portion from the master. What about the Slave ???

Before you start mysql on the slave, you need to have the log file and log position from the master. The binary logs you copied have you need, particularly the last binary log. Here is how you get it on the slave:

cd /var/lib/mysql
for X in `ls -l mysql-bin.0* | awk '{print $9}'`
do
    LOGFIL=${X}
done
LOGPOS=`ls -l ${LOGFIL} | awk '{print $5}'`
echo "Master Log File ${LOGFIL} Position ${LOGPOS}"

You can trust these numbers because you copied them personally from the master. Now that you have the master log and position, you can start up mysql on the slave and setup replication using the log file and log position that was just reported.

Give it a try !!!

CAVEAT

If you have any InnoDB data, you should set this about 1 hour before attempt to rsync:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This will cause InnoDB to page out uncommitted data from the InnoDB Buffer Pool faster.