Tuesday, January 16, 2007

Backup a MySQL Database

Backup a MySQL Database

Through Shell:

Backup an entire database

$mysqldump -udatabase_user -p --host="host.com" --opt -f database_name > database_backup.sql

Backup through Windows:

c:\> mysqldump -udatabase_user -p --host="host.com" --skip-lock-tables database_name > database_backup.sql


Backup a table

$mysqldump -udatabase_user -p --host="host.com" --opt -f database_name table_name > database_table_backup.sql

-p prompts for a password after the above command is executed

--force, -f

Continue even if an SQL error occurs during a table dump.

One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the defintion refers to a table that has been dropped. Without --force, mysqldump exits with an error message. With --force, mysqldump prints the error message, but it also writes a SQL comment containing the view definition to the dump output and continues executing.

--opt

This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.

The --opt option is enabled by default. Use --skip-opt to disable it. See the discussion at the beginning of this section for information about selectively enabling or disabling certain of the options affected by --opt.

Backup a MySQL Database

No comments: