This chapter is from the book
This chapter is from the book
This chapter is from the book
3.4 Using mysqldump
The mysqldump client program dumps table contents to files. The program is useful for making database backups or for transferring database contents to another server. mysqldump can produce SQL-format dump files that contain CREATE TABLE and INSERT statements for re-creating the dumped files or it can produce tab-delimited datafiles. This section describes how to produce SQL-format dump files. Knowledge of tab-delimited dump files is not needed for the Core exam; that topic is discussed in the "Professional Study Guide."
3.4.1 General mysqldump Operation
mysqldump has three general modes of operation, depending on how it's invoked:
-
By default, mysqldump interprets its first nonoption argument as a database name and dumps all the tables in that database. If any other arguments follow the database name, mysqldump interprets them as table names and dumps just those tables. The following command dumps the contents of all the tables in the world database into a file named world.sql:
shell> mysqldump world > world.sql
The contents of the world.sql file will begin something like this (statements to create and load the other tables in the database would follow the partial display shown here):
-- MySQL dump 9.10 -- -- Host: localhost Database: world --------------------------------------------------------- -- Server version 4.0.18-log -- -- Table structure for table 'City' -- CREATE TABLE City ( ID int(11) NOT NULL auto_increment, Name char(35) NOT NULL default '', CountryCode char(3) NOT NULL default '', District char(20) NOT NULL default '', Population int(11) NOT NULL default '0', PRIMARY KEY (ID) ) TYPE=MyISAM; -- -- Dumping data for table 'City' -- INSERT INTO City VALUES (1,'Kabul','AFG','Kabol',1780000); INSERT INTO City VALUES (2,'Qandahar','AFG','Qandahar',237500); INSERT INTO City VALUES (3,'Herat','AFG','Herat',186800); INSERT INTO City VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800); INSERT INTO City VALUES (5,'Amsterdam','NLD','Noord-Holland',731200); INSERT INTO City VALUES (6,'Rotterdam','NLD','Zuid-Holland',593321); ...
The following command names just the City and Country tables after the database name, so mysqldump dumps just those tables to a file called city_country.sql:
shell> mysqldump world City Country > city_country.sql
With the --databases (or -B) option, mysqldump interprets any nonoption argument as a database name and dumps all the tables in each of the named databases. For example, the following command dumps both the world and test databases into a single file:
shell> mysqldump --databases world test > world_and_test.sql
With the --all-databases (or -A) option, mysqldump dumps all tables in all databases. For example, this command writes a backup for all databases to the file alldb.sql:
shell> mysqldump --all-databases > alldb.sql
If you manage a lot of data, alldb.sql will be very large. Be sure that you have sufficient free disk space before issuing such a command.
mysqldump understands the standard connection parameter options, such as --host and --user. You'll need to supply these options if the default connection parameters aren't appropriate. mysqldump also understands options that provide more specific control over the dump operation. Invoke mysqldump with the --help option to see a list of available options. Those options described here are ones you're likely to find most useful:
-
--add-drop-table
-
--all or -a
-
--extended-insert or -e
-
--no-create-db or -n
-
--no-create-info or -t
-
--no-data or -d
-
--opt
-
--quick
Instructs mysqldump to precede the dump output for each table with a DROP TABLE statement that drops the table. This option ensures that when you reload the dump output, the reload operation removes any existing copy of the table before re-creating it.
Instructs mysqldump to produce CREATE TABLE statements that include all the MySQL-specific options (such as the table type and table comment) with which each table was created. By default, mysqldump does not include all these options, resulting in dump files that might be more portable for loading with a DBMS other than MySQL. With the --all option, tables created during reloading into MySQL will have the same options as the original tables.
By default, mysqldump writes each row as a separate INSERT statement. This option produces multiple-row INSERT statements that add several rows to the table at a time. Multiple-row statements can be reloaded more efficiently, although they're less readable than single-row statements if you examine the dump output. They're also less portable and might not be understood by other database systems.
Normally, when you run mysqldump with the --all-databases or --databases option, the program precedes the dump output for each database with a CREATE DATABASE statement to ensure that the database is created if it doesn't already exist. The --no-create-db option causes CREATE DATABASE statements not to be written. Note that their presence in the file is usually not a problem. They include an IF NOT EXISTS clause, so they're ignored when reloading the dump file for any database that does exist.
This option suppresses the CREATE TABLE statement that normally precedes the INSERT statements containing a table's data. Use this option when you're interested in dumping only a table's data. The option is useful mostly when you plan to reload the data into tables that already exist.
This option suppresses the INSERT statements containing table data. Use this option when you're interested in dumping only the CREATE TABLE statements that describe table structures. The --no-data option provides an easy way to get a dump file that can be processed to create empty tables with the same structure as the original tables.
This option turns on a set of additional options to make the dump and reload operations more efficient. Specifically, it's equivalent to using the --add-drop-table, --add-locks, --all, --quick, --extended-insert, --lock-tables, and --disable-keys options together. Note that this option makes the output less portable and less likely to be understood by other database systems.
This option tells mysqldump to write dump output as it reads each row from the server, which might be useful for large tables. By default, mysqldump reads all rows from a table into memory before writing the output; for large tables, this requires large amounts of memory, possibly causing the dump to fail.
3.4.2 Reloading mysqldump Output
To reload an SQL-format dump file produced by mysqldump, process it with mysql. For example, to make a copy of the Country table from the world database in the test database, you could issue these commands:
shell> mysqldump --opt world Country > dump.sql shell> mysql test < dump.sql
mysql can read from a pipe, so you can combine the use of mysqldump and mysql into a single command. The preceding example can thus be written as one command:
shell> mysqldump --opt world Country | mysql test
This technique also can be used to copy databases or tables over the network to another server. For example, the following command uses a pipe to copy the Country table from the world database on the local host to the world database on the remote host other.host.com:
shell> mysqldump --opt world Country | mysql -h other.host.com world