/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
The --master-data=1 or --master-data=2 should also help here. And you might want to read about --single-transaction.
ReplyDeleteCreating 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.