May 26, 2010

Basic MySQL backup by mysqldump

I m testing a scheduled backup of particular MySQL database in the environment (Ubuntu 10.04 / MySQL 5.1.41-3ubuntu12). mysqldump client dumps the databases / tables into SQL statements, which are suitable for my need.

After reading MySQL 5.1 Reference Manual - mysqldump, the following options I will use.




Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.


Add a DROP TABLE statement before each CREATE TABLE statement


Surround each table dump with LOCK TABLES and UNLOCK TABLES statements


Include all MySQL-specific table options in CREATE TABLE statements


For each table, surround the INSERT statements with statements to disable and enable keys


Use multiple-row INSERT syntax that include several VALUES lists


Lock all tables before dumping them


Append warnings and errors to the named file


Retrieve rows for a table from the server a row at a time


Add SET NAMES default_character_set to the output

First create a dedicated MySQL user backupuser to do the backup job, grant the user necessary privileges, LOCK TABLES and SELECT, on the database ngblog.
shell> mysql --user=root --password=rootpassword --host=localhost mysql

mysql> CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'backuppassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT LOCK TABLES,SELECT ON ngblog.* TO 'backupuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Execute mysqldump to see if the backup works fine.

shell> mysqldump --user=backupuser --password=backuppassword --opt --log-error=ngblog.log ngblog > ngblog.sql
Write a simple script /usr/local/sbin/ (chmod 700) to backup the database into a folder.
now=`/bin/date +%Y%m%d%H%M%S`

/usr/bin/mysqldump --user=${user} --password=${password} --opt \
    --log-error=$dest/${database}_${now}.log \
    ${database} > $dest/${database}_${now}.sql
To run the script at 03:00 everyday, add an entry into /etc/crontab

* 3 * * * root /usr/local/sbin/


No comments:

Post a Comment