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.