I have 12 GB MySQL InnoDB Database with around 800 queries per second. No sooner I start 'mysqldump' over it to make backup I start getting '503' on my website.
How do I make backup of my live database without affecting the site performance?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
try the following command since the database size is not small:
mysqldump -u USER -p --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz
You need to use Percona XtraBackup tool. It works like a charm for huge datasets and doesn't interrupt MySQL operations. http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/creating_a_backup_ibk.html There are some tricks but it's worth it.
You should use Percona Toolkit to backup database to prevent from blocking database:
sudo innobackupex --user=root --password=rootPASSWORD --host localhost /tmp/ sudo innobackupex --apply-log --use-memory=2G /tmp/$TIMESTAMP/
You need enough disk space in
/tmp. Once you finish it, you can copy entire directory to another server.
xtrabackup_binlog_info, there is replication information. (it is useful if you need to setup slave replication)
You must use --single-transaction option
mysqldump -uroot -p --single-transaction databasename > dump.sql
It allows for point-in-time snapshot of data. Once mysqldump starts, all the InnoDB tables will be frozen in time. Suppose you start the mysqldump at 2:30 PM and it finishes at 3:00 PM. All the InnoDB tables dumped will be from 2:30 PM. All other changes (INSERTs, UPDATEs, DELETEs) will continue with interruption and will not be included in the dump.
MyISAM would interfere with the point-in-time backup if the MyISAM tables were being updated by INSERTs, UPDATEs, or DELETEs. If the MyISAM tables were just for reads, --single-transaction would still be fine.
If you have MySQL Replication set up, you could go the Slave and run this
mysql -uroot -p... -ANe"STOP SLAVE" mysqldump -uroot -p --single-transaction databasename > dump.sql mysql -uroot -p... -ANe"START SLAVE"
The Master wouldn't know of any backups going on.