I have a large database (almost 1GB) and it has a mixture of innodb and myisam tables. Does anyone have any general tips when backing it up or more specifically the commands i should send to mysqldump. I see that i should lock myisam tables, and that single transactions for innodb, but what if i have both. Also, what is actually happening when i lock an entire (very big) table on a production database.
Tell me more
×
Server Fault is a question and answer site for
professional system and network administrators. It's 100% free, no registration required.
|
What is interesting is that MySQL client tools cannot handle both storage engines simultaneously. However, do not despair. There is really one successful way to snapshot data via a mysqldump: Step 1) mysql -h... -u... -p... -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" & Within this MySQL session, this locks all tables, MyISAM, InnoDB, and others. The whole database is in a read-only state Step 2) About 20-30 sec later, Capture the Process ID doing the SLEEP command like this: SLEEPID=`mysql -h... -u... -p... -A -e"SHOW PROCESSLIST" | grep "SLEEP(86400)" |awk '{print $1}'` Step 3) Perform mysqldump mysqldump -h... -u... -p... --single-transaction --routines --triggers --all-databases > MySQLData.sql Step 4) Kill the process doing the SLEEP command: mysql -h... -u... -p... -A -e"KILL ${SLEEPID}" Believe me, perfect snapshot every time. Give it a Try !!! P.S. To be fair, there are other tools you may want to look at Tool 1) CDP R1Soft (using the MySQL Module) Tool 2) XtraBackup from Percona |
|||
|
|
