On 01/13/2011 06:39 PM, Paul J Stevens wrote:
On 01/13/2011 06:34 PM, Larry H. wrote:
So just to clarify I would be rsyncing the contents of the dbmail database
folder (/var/lib/mysql/dbmail) to the slave. Then stopping the master and
rsyncing the innodb log files in (/var/lib/mysql) to the slave. Also, won't
rsync miss some of the new data coming in on the master as it completes
certain tables in the data directory during the rsync or is that what the
second pass is for?
Sucks! Don't do that!
/var/lib/mysql/dbmail will not contain anything!
If you need to resync your slave - assuming you need to do that - you
don't need any downtime.
On the slave:
mysql> slave stop;
# create a consistent snapshot of the master
mysqldump -h $_host -u $_user -p$_pass --quick --single-transaction \
--flush-logs --master-data=2 --all-databases> $_file
# extract the MASTER log filename and file position from the dumpfile:
sql=`head -n30 /var/tmp/slave.sql|grep 'CHANGE MASTER'|cut -b3-|sed
's/;$//'`
# the sql variable will now look like:
# "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005',
# MASTER_LOG_POS=98,"
# create a valid 'change master' query
sql="$sql,master_host='$MASTERIP',master_user='replication',master_password='${REPLI_PW}';"
# load the dump
cat /var/tmp/slave.sql | mysql
rm -f /var/tmp/slave.sql
# make the slave catch-up from the logfile position
echo $sql|mysql
echo "slave start;" | mysql
Of course that will work fine, too, but rsync is way quicker. Database
dump time isn't too bad, but the load time can be slow. MySQL is also
still buggy on occassions with regards to table dependencies - it will
sometimes dump the tables in such an order that loading will fail due to
referential integrity constraints. The rsync approach is quicker and
immune to this issue.
This is also one of the procedures mentioned in the mysql docs:
http://dev.mysql.com/doc/refman/5.5/en/replication-howto-mysqldump.html
except that using single-transaction means you don't need to lock the
master.
You'll find the master will grind to a halt anyway while you're doing
this, as soon as the write-ahead log fills up. With small-ish databases
it's not a big deal, but if the size is in the 100GB+ range the rsync
method will be a lot more workable.
Gordan
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
