The mysqldump command can be used to backup a MySQL database. The mysqldump command will write its output, which consists of SQL statements that can be used to recreate the database, to standard output by default; you can use
> filename to redirect its output to a file.
The format of the command to backup one or more databases is
mysqldump --databases db1 db2 db3 > outputfile.sql where
db1, db2, etc. are the names of the databases
you wish to back up and outputfile.sql is the name of the output
file to which the dababase will be dumped.. The --databases
option causes all names on the command line to be treated as database
names. Without this option, mysqldump treats the first name as a database
name and those following as table names.
# mysqldump -p --databases partsdb > partsdb-20111016.sql Enter password:
In the above example, the -p option causes mysql to prompt
for the mysql account password with the username assumed to be the same as
the login name for the account from which you are running the command.
If you wish to specify the username, use --user=user_name or
-u user_name.
If you want to back up all databases you can use the command
mysqldump --all-databases > dump.sql to back up all databases
to which the account has access on the system to a file named dump.sql
in this case.
A dump file can be used in several ways:
-
As a backup to enable data recovery in case of data loss.
-
As a source of data for setting up replication slaves.
-
As a source of data for experimentation:
-
To make a copy of a database that you can use without changing the original data.
-
To test potential upgrade incompatibilities.
-
An example script to dump mysql databases to a gzip file which will be transferred to another server via SSH for safekeeping can be found at How to have a rock solid Linux backup without pro budget. Placing your dump file on another system ensures you don't lose your data should the system on which your databases reside suffer a hard drive crash or some other catastrophe.
References:
