Currently my database is almost 20 GB big and still growing. I'm taking a daily backup with mysqldump and it's getting really slow. So slow that in the meanwhile new connections stack up and eventually cause this error:
SQLSTATE[HY000] [1040] Too many connections
(I could improve the amount of connections that's accepted but that won't do anything because the connections are still just frozen, waiting for the backup to complete, which will lead to timeout)
I've been reading up on some options to improve the speed and this is what I've found:
- option --quick (Will probably help)
- option --single-transaction (Will prevent tables from being locked, but may cause database to become incorrect)
- Master-Slave replication (Probably the best thing I could do, one problem, I have only one server available)
The master-slave replication really sounds like it's the best option since I can stop the slave from updating, take the backup, and let it resume syncing. The problem is I only have one fysical machine to work with.
I know that I can set up multiple mysql instances on this one server. The question is: Is it wise to do so? The slave is really only used to generate that backup file (which will be copied to a different disk on the network) so that the master can stay live.
if you use just innodb - try xtrabackup.
if you use both myisam and innodb - flush + lvm snapshot + file-level copy might work for you.
indeed replication slave for backups is good idea as well. just remember to periodically check data consistency between the master and the slave.