MySQL in a Nutshell

Name

mysqldump

Synopsis

mysqldump [options] database [table]

Use this to export data and table structures from MySQL. Typically, you use this utility to make backups of databases or to move databases from one server to another. You can run it on an active server. For consistency of data between tables, the tables should be locked (see the --lock-tables option) or the daemon should be shutdown. To make a backup of a database, enter something like the following from the command line:

mysqldump -u russell -p -l db1 > /tmp/db1.sql

In this example, the username is given with the -u option. The -p option tells the utility to prompt the user for a password. The -l option is the same as the --lock-tables option. It has the server lock the tables, making the backup, and then unlock them when it’s finished. Next the database to back up (db1) is specified. Finally, using the redirect (the greater-than sign), the output is saved to the path and filename given.

The dump file created will be in the text file format. It will contain a CREATE TABLE statement for each table in the database, along with a separate INSERT statement for each row of data. To restore the data from a dump file created by mysqldump, you can use the mysql client. To restore the file created by the preceding statement, you can enter the following from the command line:

mysql -u russell -p < /tmp/db1.sql

This example redirects the stdin through the use of the less-than sign. This instructs the mysql client to take input from the file ...