0

I know that you can use mysqldump. I am currently dumping the following way:

${MYSQLDUMP} --single-transaction -u ${MUSER} -h ${MHOST} -p${MPASS} $db | ${GZIP} -9 > $FILE

From my understanding this locks the database and prevents any type of use of the database and can even lock up websites. Is there a better way to maybe do daily/hourly backups of the MySQL database should the database be in the 100mbs and even 1gbs in size?

Improve this question
1
  • 2
    Tiffany, have you looked at the ton of related questions on this site as well as a simple google search? There are lots of resources that will point you in the right direction. I would venture this one will be closed unless you can specify a certain requirement that isn't being met by one of the other SF questions on this subject. Commented Nov 24, 2012 at 19:20

2 Answers 2

Reset to default
2

You probably want to:

  • take (ideally) nonblocking full backups every now and then [depends on your data change rate and requirements for speed of the recovery process] - eg once per day or per week
  • take backups quite often of MySQL binary logs that can be used to do point-in-time recovery. You can think about those logs as incrementals - list of operations that have happened after you've taken full backup

For your backups to be non-blocking, you'll need to have InnoDB engine used for all the tables.

For the alternatives - solutions that don't come bundled with MySQL - take a look at:

  • xtrabackup - that's an alternative way of taking full / incremental backup of mysql.
  • lvm [or other storage level] snapshots - eg this script
Improve this answer
1
  • 1
    +1 for xtrabackup, the best tool for the incremental job. Do make sure you understand the use cases and caveats, and understand all of the implications of -- and have tested -- restores using it before putting it into production. Commented Nov 24, 2012 at 19:54
0

This would only lock MyISAM tables. --single-transaction works only on prevents table locking by running this into a transaction.

100MB to 1GB is actually a small size, so, if you can live with a small chance on inconsistency, you could add --skip-lock-tables and also avoid locking MyISAM tables. This way your websites will not be affected.

Inconsistency would happen in case there is an application that updates two tables. If this happens during the time between backing up the two tables, the backup might contain the update to the second table and not the first one.

This is a rare condition, especially if you have a small database. If you can't live with that, you might need to use more complex solutions like lvm snapshots or using replication.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.