Backup MySQL database

Question

Hi,

I have a MySQL Database of about 1.7GB. I usually back it up using mysqldump and this takes about 2 minutes. However, I would like to know the answers to the following questions:

1) Does mysqldump block read and/or write operations to the database? Because in a live scenario, I would not want to block users from using the database while it is being backed up.

2) It would be ideal for me to only backup the WHOLE database once in, say, a week, but in the intermediate days only one table needs to be backed up as the others won't change. Is there a way to achieve this?

3) Is mysqlhotcopy a better alternative for these purposes?

Thanks in advance, TM

Answer 1

Hi,

1. No, you can specify tables to be locked using --lock-tables but they aren't by default

2. If you don't specify any tables then the whole DB is backed up, or you can specify a list of tables :

   mysqldump [options] db_name [tables]

3. Not used it sorry, however I run a number of MySQL DBs, some bigger some smaller than 1.7gb and I use mysqldump for all my backups.

Answer 2

Hi

mysqlhotcopy does not work in certain cases where the readlock is lost, and does not work with INNODB tables.

mysqldump is more used because it can back up all kinds of tables.

From MySQL documentation

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

Bye.

Answer 3

1) mysqldump only blocks when you ask it to (one of the --lock-tables, --lock-all-tables, --single-transaction). but if you want your backup to be consistent then mysqldump should block (using --single-transaction or --lock-all-tables) or you might get an inconsistent database snapshot. Note: --single-transaction works only for InnoDB.

2) sure, just enumerate the tables you want to be backed up after the database name:

mysqldump OPTIONS DATABASE TABLE1 TABLE2 ...

Alternatively you can exclude the tables you don't want:

mysqldump ... --ignore-table=TABLE1 --ignore-table=TABLE2 .. DATABASE

So you can do a whole database dump once a week and backup only the changing tables once a day.

3) mysqlhotcopy inly works on MyISAM tables and in most applications you are better off with InnoDB. There are commercial tools (quite expensive) for hotbackup of innodb tables. Lately there is also the new opensource one for this purpose - Xtrabackup

Also, to automate the process you can use astrails-safe. It supports database backup with mysqldump and filesystem with tar. +encryption +upload to S3, +many other goodies. There is no xtrabackup support yet, but it should be easy to add if this is what you need.

Answer 4

Adding a mysql slave to your setup would allow you to take consistant backups without locking the production database.

Adding a slave also gives you a binary log of changes. A dump is a snapshot of the database at the time you took the dump. The binary log contains all statements that modified the data along with a timestamp.

If you have a failure in the middle of the day and your only taking backups once a day, you've lost a half a days worth of work. With binary logs and mysqldump, you could restore from the previous day and 'play' the logs forward to the point of failure.

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

If your running MySQL on a linux server with LVM disks or a windows server with VSS, you should check out Zamanda. It takes binary diffs of the data on disk, which is much faster to read and restore than a text dump of the database.

Answer 5

Maatkit dump might be useful.

http://www.maatkit.org/doc/mk-parallel-dump.htmlhttp%3A//www.maatkit.org/doc/mk-parallel-dump.html