MySQL

Backup

Basic usage:

mysqldump [--opt] -u USERNAME -p 'PASSWORD' \
    ( --all-databases | --databases DB_NAME_1 DB_NAME2 DBNAME_3 ... )

Secaurity issue: instead of using -p'PASSWORD' one should use the .my.cnf file and a [mysqldump] header.

-opt stands for:

--add-drop-table --add-locks --all --extended-insert --quick --lock-tables

pipe it to

| ssh user@server "dd of=/tmp/$(date +'%d-%m-%y').mysql.dump"

Replay:

mysql < mysql.dump

Attention:

If you don't use any params mysqldump will first load everything into ram. This can lead into problems when backing up big databases and/or less ram!

Links

• mySqlDump Database Backup is a PHP script that is executed either on a scheduled basis or manually via your browser.
• mysqldump - MySQL Referenzhandbuch (GER)