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.

Options	Description
--opt	Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--add-drop-table	Add a DROP TABLE statement before each CREATE TABLE statement
--add-locks	Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--create-options	Include all MySQL-specific table options in CREATE TABLE statements
--disable-keys	For each table, surround the INSERT statements with statements to disable and enable keys
--extended-insert	Use multiple-row INSERT syntax that include several VALUES lists
--lock-tables	Lock all tables before dumping them
--log-error=file_name	Append warnings and errors to the named file
--quick	Retrieve rows for a table from the server a row at a time
--set-charset	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/backup.sh (chmod 700) to backup the database into a folder.

#!/bin/bash
database="ngblog"
user="backupuser"
password="backuppassword"
dest="/backup"
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/backup.sh

Reference

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html