MySQL DBA - Tips and Techniques Magazine

13 Nov 2014

Taking consistent mysql dumps

/usr/local/bin/mysqldump -uroot --p*** --databases prequal --lock-all-tables | gzip) > /home/backup/rebuild_prequal-`hostname -s`.sql.gz 2> /home/backup/rebuild_prequal-`hostname -s`.sql.err.log


I kept an eye on the database, and it queued all of the inserts until the backup finished, but allowed the selects to go through, an example of processlist is attached. Maybe we should consider this for some of the smaller databases and slaves


1 comment:

  1. The --master-data=1 or --master-data=2 should also help here. And you might want to read about --single-transaction.
    Creating a (almost) non-blocking backup can also be done with MySQL Enterprise Backup or Percona Xtrabackup.

    Other alternatives are using LVM snapshots and/or a slave for backups.