MySQL

From Blue-IT.org Wiki

Revision as of 22:47, 19 July 2008 by Apos (talk | contribs) (Links)

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