4

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.

1
  • People can probably work it out - but you should include a link to the script that you are using.
    – Dan Carley
    Sep 11, 2009 at 10:28

10 Answers 10

2

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).

2
  • 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.
    – voretaq7
    Feb 3, 2010 at 4:54
  • 1
    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.
    – Omry
    Feb 3, 2010 at 15:10
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

1
  • 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...?
4
  • You basically said all the things I was going suggest, +1
    – pauska
    Sep 11, 2009 at 8:55
  • 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
    Sep 11, 2009 at 9:19
  • compressed size: 78mb. would it help to adjust GZIP params?
    – Tom
    Sep 11, 2009 at 9:21
  • that´s not really a lot of data. what is your system configuration?
    – lepole
    Sep 11, 2009 at 9:56
1

If you are running your MySQL data files on a LVM volume, you could just create a snapshot. Take a look at mylvmbackup for a proven solution.

1

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.

1
  • So in order to create a backup it needs to do something that causes the backup to fail? That's brilliant.
    – Vincent
    Apr 29, 2018 at 16:01
1

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.

1

The method I use (for Postgres on FreeBSD) is:

Set up a slave server (You do have one, just in case your main DB is hit by lightening, right?)
Make sure the data directory on the slave is on its own filesystem (it makes life easier later!)

  1. At backup time, stop the slave server (Replication Stops)
  2. Make a filesystem snapshot of the data FS (takes 5-10 seconds)
  3. Start the slave again (replication resumes)
  4. Mount the snapshot and back up its contents however you want
    • Because it's a snapshot it's not changing, and because it was made when your DB was stopped it's guaranteed to be in a good, quiescent state.
  5. Unmount and delete the snapshot.

This would be identical for MySQL.

If your server/FS can't do snapshots move step 3 to the end and omit steps 2 & 5 (Replication is stopped for the duration of the backup, but you're guaranteed a consistent backup, and since it's a slave your clients don't even know it's happening).

0

If all of your tables are InnoDB, then you can mysqldump with --single-transaction. That will dump the database in a consistent state while still allowing other updates to your tables to occur inside other transactions. I've migrated a number of systems to InnoDB just so I could have this feature.

0

If all your tables are InnoDB then you can use --single-transaction. This will lock the database only briefly, relying on the internal consistent snapshot available in a transaction to complete the backup.

If they're not, then you will have to do something else; if you're on Linux then you can use an LVM snapshot provided you're using LVM AND you have space for one (You'll still need a flush tables while you take the snapshot, but the snapshot is quick).

If your underlying storage device (e.g. RAID controller) supports snapshots, you could use one of those too.

0

Are you using InnoDB? If so, take a look at Percona's xtrabackup tool. Or more specifically, use their innobackupex script which wraps xtrabackup and adds support for also dumping any MyISAM tables among other things.

It can do online backups (zero locking for InnoDB) and the output is a valid mysql data directory you can copy into place to restore generally making restores much faster than restoring a dump.

It also supports streaming the backups to other machines, incremental backups, and is just a very robust and useful tool.

I use it to backup extremely busy databases in the 500GB+ range without any issues at all and very little replication delay even when load is especially fierce.

Here's a sample usage that takes a backup and then applies the logs in the backup to make it viable for restore:

innobackupex /var/backups/db
innobackupex --apply-log --use-memory=1G /var/backups/db

Restoring that backup on a box would then look like this (path of the mysql data directory may vary by distro):

cp -r /var/backups/db /var/lib/mysql/data
chown -R mysql:mysql /var/lib/mysql/data

The Percona documentation for this tool is quite good so you can read more about it there:

http://www.percona.com/doc/percona-xtrabackup/innobackupex/innobackupex_script.html

Whatever you go with be sure to test it, including the restore part!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .