Before you give up on mysqldump, have you ever considered performing parallel mysqldumps?
I wrote a script on how to do this: How can I optimize a mysqldump of a large database?
Going back to your question, you could resort to doing rsync of a live server as long as the target DB server will have the exact same version of MySQL that the source DB server has.
Just run rsync against /var/lib/mysql on a master and copy it to /var/lib/mysql on the target server. 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.
If you want the target server to have binary logging, please make you have 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:
RSYNCSTOTRY=10
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=10
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
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. In MySQL 5.5, this is no longer necessary.
mysqldump
is slow?