I have very large Magento databases with of almost 400 of tables some of which have up to 10GB of data in. I’ve always had headaches when backing up these databases. Using default mysqldump it quickly spirals the server load out of control and locks up everything… affecting my users. Trying to stop the process can lead to crashed tables and lots of downtime during recovery of those tables. I now use
mysqldump -u USER -p --single-transaction --quick --lock-tables=false DATABASE | gzip > OUTPUT.gz
mysqldump reference at dev.mysql.com even says
To dump large tables, you should combine the –single-transaction option with –quick.
Says nothing about that being dependent on the database being InnoDB, mine are myISAM and this worked beautifully for me. Server load was almost completely unaffected and my service ran like a Rolex during the entire process. If you have large databases and backing them up is effecting your end user… this IS the solution. 😉
from the internet
