how to complete mysql daily backup dump without timeouts?

Question

what is best way to complete daily Mysql database backup, we are having critical mysql database connection timeouts during backup dump

we use dump and gzip

cron has a line:

 1 1 * * * root nice -n 19 /etc/automysqlbackup.sh

problem occurs during the dump.

Solution 1

logical backup (mysqldump, like used by your script) locks the database. this will disrupt client operation.

my approach for backing up a mission critical database is to use InnoDB on OpenSolaris and to take daily ZFS snapshots of the datadir and the logs dir.

those snapshots are then copied to an offsite server.

since InnoDB is transactional and the snapshot is atomic, there is no need to shutdown the server before taking the snapshot (recovering from it is just like recovering form a sudden power failure: InnoDB supports it).

Solution 2

Are dumping your mysql databases on a network (NFS) share? We had a similar time out problem, so we had to restart mysql daemon with the following two options:

/etc/my.cnf

[mysqld]
net_read_timeout=300
net_write_timeout=300

Please let us know your newest results!

Stivi

Solution 3

I agree with theotherreceive; the locking is probably what's causing the timeouts. If that's the case, you could create a mysql slave and do dumps of that (preferably on separate hardware, but whatever). This will prevent your master db from being locked while the dumps happen, and the slave will catch up with the master once the dumps are finished.

Solution 4

To create a dump that you can actually use mysql needs to lock the database in order to create a consistent dump file. This, I believe, is what's causing the timeouts.

Solution 5

• If zipping is the problem. Run your backupjob from another machine connecting to your mysql server and zip there.
• instead of the dump with mysql5 you could also you mysqlhotcopy
• but than again mybe your disk io is simply too slow. what kind of disks are they, what´s the amount of io you have...?

View more solutions

Comments

[pauska]

You basically said all the things I was going suggest, +1

[Tom]

hotcopy LOCKS table? Its not possible as we are 24h service. About diskIO: is there "slower method" than a zip for compressing the log file as its text.. incompressed log file is 600mb

[Tom]

compressed size: 78mb. would it help to adjust GZIP params?

[lepole]

that´s not really a lot of data. what is your system configuration?

[voretaq7]

For most database systems (and most MySQL back-ends) you really need the database to be shut down when you grab your filesystem backups or the results of a restore can be unpredictable.

[Omry]

as I said, InnoDB is robust to this. (assuming you take an atomic snapshot of the file system - which ZFS snapshot is guaranteed to be). I am not talking out of my ass here, I have verified this time and again: InnoDB recovers fine from a snapshot I take while the server is running.

[Vincent]

So in order to create a backup it needs to do something that causes the backup to fail? That's brilliant.