Search code examples
backupmysqlblocking

Backup MySQL database

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?

Solution
    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.