MySQL Backup – How to Speed Up CPU Limited Database Backup

backupMySQLmysqldump

I have a database that has a structure that unfortunately requires having a lot of tables (10k+). It's not possible to change the structure now.

The database itself is not very large (<1GB), but the backups are taking a very very long time, something like 40 minutes. It is a CPU limited process as it runs one core on the server at 100% for the entire duration.

The command used to do the backup is:

mysqldump -u root -password --single-transcation --skip-lock-tables --all-databases > dump.sql

Are there any tricks to speed up this backup?

Best Answer

Have a look at Percona xtrabackup.