Backing Up with mysqldump
|
|
The previous two methods directly copy the files—and only work with MyISAM tables. (InnoDB tables are not stored as files, so they cannot make use of the earlier methods.)
Another method, mysqldump, makes a dump of the SQL statements needed to create the tables being backed up. This also potentially allows the database to be ported to other database systems (bearing in mind that not all MySQL features are standard to other databases). If you do have to do a move to another DBMS, expect a long, laborious task ahead.
To back up the customer table, run the following from the Unix shell:
% mysqldump firstdb customer > /db_backups/customer_2002_11_12.sql
Or run the following from the command line on Windows:
C:\MySQL\bin>mysqldump firstdb customer > c:\db_backups\customer_2002_11_12.sql C:\MySQL\bin>
Remember to specify the path, username, and password if you need to. This creates a file in the db_backups directory containing the SQL statements needed to re-create the customer table. You can view this file in any text editor, such as Notepad or vi. The first part of the file contains the following:
# MySQL dump 8.14 # # Host: localhost Database: firstdb #-------------------------------------------------------- # Server version 4.0.1-alpha-max-log
The hashes (#) are just comments, inefficacious information about versions and so on. Later in the file are the important SQL statements needed to re-create the various tables. This snippet is what can re-create the customer table:
# # Table structure for table 'customer' # CREATE TABLE customer ( id int(11) NOT NULL auto_increment, first_name varchar(30) default NULL, surname varchar(40) default NULL, initial varchar(5) default NULL, PRIMARY KEY (id), KEY surname (surname,initial,first_name) ) TYPE=MyISAM; # # Dumping data for table 'customer' # INSERT INTO customer VALUES (1,'Yvonne','Clegg','X'); INSERT INTO customer VALUES (2,'Johnny','Chaka-Chaka','B'); INSERT INTO customer VALUES (3,'Winston','Powers','M'); INSERT INTO customer VALUES (4,'Patricia','Mankunku','C'); INSERT INTO customer VALUES (5,'Francois','Papo','P'); INSERT INTO customer VALUES (7,'Winnie','Dlamini',NULL); INSERT INTO customer VALUES (6,'Neil','Beneke',NULL); INSERT INTO customer VALUES (10,'Breyton','Tshabalala','B');
| Warning | Using the results of a default mysqldump to restore a database can be time consuming. Because the index buffer is flushed after each INSERT statement, large tables can take a long time to restore. See the mysqldump options later in the next section to see how to speed this up. |
Restoring a Database Backed Up with mysqldump
You can test your backup by dropping and re-creating the data:
mysql> DROP TABLE customer; Query OK, 0 rows affected (0.31 sec) mysql> exit Bye
| Warning | Once again, don't do this to test the backup of a live database. This is just a way to simulate the loss of your database. |
To restore the table on a Unix machine, run:
% mysql firstdb < /db_backups/customer_2002_11_12.sql
Or to restore from Windows:
C:\MySQL\bin>mysql firstdb < c:\db_backups\customer_2002_11_12.sql
The table has now been restored.
Table 11.1 describes the various options available to mysqldump.
| Option | Description |
|---|---|
| --add-locks | Places a LOCK TABLES statement before, and an UNLOCK TABLE statement after, each table dump. This causes the INSERT statements to be processed much more quickly (as the key buffer is only flushed after the UNLOCK TABLE statement). |
| --add-drop-table | Adds a DROP TABLE before each CREATE TABLE statement. If the table already exists, it may interfere with the restoration, so this ensures a clean slate for the restored table. |
| -A, --all-databases | Dumps all existing databases. It is equivalent to the -B or --databases option with all the databases listed. |
| -a, --all | Includes all MySQL-specific CREATE options. |
| --allow-keywords | Usually column names cannot be the same as a keyword. This allows the creation of column names that are keywords by beginning each column name with the table name. |
| -c, --complete-insert | Uses complete insert statementsin other words, INSERT INTO tablename(x,y,z) VALUES (a,b,c) rather than INSERT INTOtablename VALUES (a,b,c). |
| -C, --compress | Compresses the data transferred between the client and server if both support compression. |
| -B, --databases | Dumps several databases. No table names can be specified if this option is chosenall tables in a database are dumped. The output places a USE databasename statement before each new database. |
| --delayed | Rows are inserted with the INSERT DELAYED command rather than just INSERT. |
| -e, --extended-insert | Uses the multiline INSERT syntax. The output is more compact and also runs faster because the index buffer is flushed only after each INSERT statement. |
| -#, --debug[=option_string] | Traces program usage for debugging purposes. |
| --help | Displays a help message and exits. |
| --fields-terminated-by=... | Same as the LOAD DATA INFILE options. See the sections on SELECT INTO and LOAD DATA INFILE. |
| --fields-enclosed-by=... | Same as the LOAD DATA INFILE options. See the sections on SELECT INTOand LOAD DATA INFILE. |
| --fields-optionally-enclosed-by=... | Same as the LOAD DATA INFILE options. See the sections on SELECT INTO and LOAD DATA INFILE. |
| --fields-escaped-by=... | Same as the LOAD DATA INFILE options. See the sections on SELECT INTOand LOAD DATA INFILE. |
| --lines-terminated-by=... | Same as the LOAD DATA INFILE options. See the sections on SELECT INTO and LOAD DATA INFILE. |
| -F, --flush-logs | Flushes the log file before starting the dump. |
| -f, --force | Continues even if there are MySQL errors during the dump. |
| -h, --host=... | Dumps data from the MySQL server found on the named host. The default host is localhost. |
| -l, --lock-tables | Locks all tables before starting the dump. The tables are locked with READ LOCAL, which allows concurrent inserts for MyISAM tables. |
| -K, --disable-keys | Indexes are disabled before the INSERT statements, and enabled afterward, which makes the insertions much quicker. |
| -n, --no-create-db | A CREATE DATABASE statement will not be placed in the output. It usually is if the --databases or --all-databases option is used. |
| -t, --no-create-info | Does not include the CREATE TABLE statement, so the tables are assumed to exist already. |
| -d, --no-data | Only dumps the table structure and does not include any INSERT statements for the table. |
| --opt | Equivalent to --quick --add-drop-table --add-locks --extended-insert --lock-tables. This results in the fastest restore. |
| -ppassphrase, --password[=passphrase] | Specifies the password to use when connecting to the server. As usual, if you don't specify the password, you will be prompted for it, which is a safer option. |
| -P portnumber, --port=portnumber | Specifies the TCP/IP port number to use when connecting to the host. This does not apply in the case of connections to localhost. See the -S option. |
| -q, --quick | Does not buffer the query, but dumps directly to stdout. Uses mysql_use_ result() to do this. You may have to do this with large tables. |
| -Q, --quote-names | Places table and column names within single quotes. |
| -r, --result-file=... | Outputs directly to a given file. This is useful in DOS because it prevents Unix newline \n characters from being converted to a newline and carriage return \n\r. |
| -S /path/to/socket, --socket=/path/to/socket | Specifies the socket file to use when connecting to localhost (the default). |
| --tables | Overrides the -B or --databases option. |
| -T, --tab=path-to-some-directory | Creates two files for each table: tablename.sql, containing the CREATE statements, and tablename.txt, containing the data. This option only works when mysqldump is run on the server. |
| -u username, --user= username | Specifies the MySQL username to use when connecting to the server. The default value is your Unix login name. |
| -O var=option, --set-variable var=option | Sets the value of a variable. |
| -v, --verbose | Makes MySQL more talkative by forcing it to display more information on the mysqldump process. |
| -V, --version | Displays version information and exits. |
| Continued on next page | |
| -w, --where='where-condition' | Dumps only the records that satisfy the where condition. The condition must appear in quotes. |
| -X, --xml | Dumps the database as well-formed XML. |
| -x, --first-slave | Locks all tables across all databases. |
| -O net_buffer_length=n | Creates rows of up to n size when it creates multirow-insert statements (the -e or --opt options. n must be less than 16MB, and the mysqld variable max_ allowed_packet must be larger than n. |
You can use mysqldump in three main ways.
% mysqldump [OPTIONS] database [tables]
or:
% mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
or:
% mysqldump [OPTIONS] --all-databases [OPTIONS]
The following examples demonstrate some of the available options. Specifically, the next example dumps all tables in the firstdb database:
% mysqldump firstdb > /db_backups/firstdb_2002_11-12.sql
The -v (verbose) option gives more information as it goes through the process, which could be useful for debugging if there are problems:
% mysqldump -v firstdb customer > /db_backups/customer_2002_11-12.sql # Connecting to localhost... # Retrieving table structure for table customer... # Sending SELECT query... # Retrieving rows... # Disconnecting from localhost... %
The following example uses where to limit the backup to only those records where the id>5:
% mysqldump --where='id>5' firstdb customer > /db_backups/customer_2002_11-12.sql
This leaves the output looking like this:
# # Dumping data for table 'customer' # WHERE: id>5 # INSERT INTO customer VALUES (7,'Winnie','Dlamini',NULL); INSERT INTO customer VALUES (6,'Neil','Beneke',NULL); INSERT INTO customer VALUES (10,'Breyton','Tshabalala','B');
The -e (extended) option allows for faster inserts:
% mysqldump -e firstdb customer > /db_backups/customer_2002_11-12.sql
This makes use of the multi-line INSERT statement, as you can see from viewing the text file:
# # Dumping data for table 'customer' # INSERT INTO customer VALUES (1,'Yvonne','Clegg','X'), (2,'Johnny','Chaka-Chaka','B'), (3,'Winston','Powers','M'), (4,'Patricia','Mankunku','C'), (5,'Francois','Papo','P'), (7,'Winnie','Dlamini',NULL), (6,'Neil','Beneke',NULL),(10,'Breyton','Tshabalala','B');
As there is only one INSERT statement, the index buffer is only flushed once, which is faster than doing it after every insert.
|
|
