5

Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast.

The problem is, from research, MyISAM is okay with being moved entirely, but InnoDB is not.

I read about this: http://www.mysqlperformanceblog.com/2009/07/31/copying-innodb-tables-between-servers/

The final question is, is there a way to move the entire database at once and make sure that both the InnoDB and MyISAM tables are good.

Any ideas? Thank a lot!

1
  • How big is the database? Why the mysqldump is slow? Jul 24, 2012 at 19:39

2 Answers 2

4

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.

0
0

Also check out Percona's XtraBackup. Essentially zero downtime for InnoDB tables; some lock time for MyISAM tables.

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.