Backing Up a Live MySQL Database without Locking it!

Problem Statement

You have a busy MySQL database that you cannot stop to take a snapshot. You have to backup the database without taking it off-line. How can you do this without using any fancy MySQL tool?

We will show you how you can use mysqldump with a set of options that will allow you to take snapshot of the database without taking it off-line or locking the tables.

Solution

The mysqldump utility can be run with the --skip-lock-tables option along with --single-transaction to make a dump file of a database without locking it first.

For example, a database called my_db can be backed up online without locking it as follows:

mysqldump -u username -ppassword -h hostname --opt \
            --skip-lock-tables --single-transaction my_db > db name.sql

Category: Backup | Tags: