4

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?

  • What options did you use for mysqldump ? – RolandoMySQLDBA Dec 3 '14 at 19:26
  • none, 'mysqldump databasename > dump.sql' – dragosrsupercool Dec 3 '14 at 19:28
5

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
  • 1
    --single-transaction disables --lock-tables, so no need for --lock-tables=false (See dba.stackexchange.com/a/60145/877) – RolandoMySQLDBA Dec 3 '14 at 21:43
  • And what does --quick do? – dragosrsupercool Dec 4 '14 at 10:24
  • @dragosrsupercool this is a quot from MySQL website : --quick:This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out. – Ahmad Abuhasna Dec 4 '14 at 13:16
  • @RolandoMySQLDBA In-fact I referred to the following website t that describe why use (--lock-tables=false) or (--skip-lock-tables) aaronbonner.io/post/5662779872/optimising-backups-with-mysql please advise thanks in advance – Ahmad Abuhasna Dec 4 '14 at 13:20
3

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.

  • All should keep in mind that mysqldump can catch a snapshot from the start of the backup whereas Percona XtraBackup catches snapshot from the completion of the backup. – RolandoMySQLDBA Dec 5 '14 at 22:28
3

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.

In xtrabackup_binlog_info, there is replication information. (it is useful if you need to setup slave replication)

  • +1 for the extra info. All should keep in mind that mysqldump can catch a snapshot from the start of the backup whereas Percona XtraBackup catches snapshot from the completion of the backup. – RolandoMySQLDBA Dec 5 '14 at 22:29
  • Ran out of votes. I'll upvote after 7PM EST. – RolandoMySQLDBA Dec 5 '14 at 22:29
2

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.

SUGGESTION

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.