how to complete mysql daily backup dump without timeouts?

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