32.4. Making Text Backups


This section describes methods that you can use to make text-format database or table backups.

32.4.1. Making Text Backups via SQL

The SELECT ... INTO OUTFILE statement writes the contents of an arbitrary result set to a disk file on the server host. For backup purposes, it can be used in the following form to write a text dump of an entire table:

 SELECT * INTO OUTFILE 'file_name' FROM table_name; 

SELECT ... INTO OUTFILE has the following characteristics:

  • The statement can be used with either local or remote servers. The resulting disk file is always created on the server host, however, because the server itself writes the file.

  • The output file must not already exist.

  • The statement works for any storage engine.

  • The statement requires the FILE privilege.

  • The output format can be controlled by using statement options that specify column and line delimiters, quote characters, and escape characters.

For more detail on the SELECT ... INTO OUTFILE statement, see Section 15.2.2, "Exporting Data with SELECT ... INTO OUTFILE."

32.4.2. Making Text Backups with mysqldump

The mysqldump client program dumps table contents to files. It has the following characteristics:

  • It can dump all databases, specific databases, or specific tables.

  • mysqldump can back up local or remote servers, although the destination for the dump files depends on how you invoke it. For tab-delimited data files made using the --tab option, the server writes them on the server host. For SQL-format dump files that contain CREATE TABLE and INSERT statements for re-creating the tables, the server sends table contents to mysqldump, which writes the files on the client host.

  • It works for tables created by any storage engine.

  • Output files are written in text format and are portable, so they can be used for transferring database contents to another server.

This section concentrates on using mysqldump to produce SQL-format dump files. Instructions for using mysqldump to produce tab-delimited data files are given in Section 15.3.2, "Exporting Data with mysqldump."

When you use mysqldump to make SQL-format dump files, it has three general modes of operation, depending on the arguments with which you invoke it:

  • By default, mysqldump interprets its first non-option 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 10.10 -- -- Host: localhost    Database: world -- ------------------------------------------------------ -- Server version       5.0.10-beta-log ... -- -- Table structure for table `City` -- DROP TABLE IF EXISTS `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`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `City` -- /*!40000 ALTER TABLE `City` DISABLE KEYS */; LOCK TABLES `City` WRITE; INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000), ... UNLOCK TABLES; ... 

    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 non-option 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. The options described in the following list are some of those that you're likely to find most useful:

  • --add-drop-table

    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.

  • --add-locks

    Adds statements around INSERT statements that acquire table locks for the dumped tables.

  • --create-options

    Instructs mysqldump to produce CREATE TABLE statements that include all the MySQL-specific options 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 --create-options option, tables created during reloading into MySQL will have the same options as the original tables.

  • --disable-keys

    Includes ALTER TABLE statements in the dump file that disable and enable index updating. For MyISAM tables, this makes reloading faster.

  • --extended-insert or -e

    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 less likely to be understood by other database systems.

  • --flush-logs

    Tells the server to flush the logs before starting the dump. This causes the next binary log to be synchronized (checkpointed) to the time of the dump, which is useful when performing data recovery operations.

  • --lock-tables

    Before dumping tables, mysqldump acquires a READ LOCAL lock for all of them. For MyISAM tables, a READ LOCAL lock allows concurrent inserts to proceed while tables are being dumped. (See Section 28.2, "Explicit Table Locking.")

  • --no-create-db or -n

    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.

  • --no-create-info or -t

    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.

  • --no-data or -d

    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.

  • --opt

    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, --create-options, --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 has been enabled by default since MySQL 4.1. To disable it, use --skip-opt. To leave the option enabled but disable individual options that --opt turns on, use their --skip forms. For example, to disable --quick, use --skip-quick.

  • --quick

    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.

  • --single-transaction

    Dumps tables within a transaction. This is recommended when dumping InnoDB tables. It uses a consistent read to allow a dump to be made that reflects InnoDB state as of the beginning of the dump, regardless of the activity of other clients.

32.4.3. Making Text Backups with MySQL Administrator

The MySQL Administrator GUI program provides backup and restore capabilities. It generates backup files containing SQL statements that can be reloaded into your MySQL server to re-create databases and tables. These files are similar to the SQL-format backup files generated by mysqldump.

MySQL Administrator stores backup configuration options as projects. You can select and execute these projects later to perform a backup operation based on a given set of specifications. The project approach enables you to easily select from among multiple types of backups. You can select backup projects on demand or schedule them for periodic execution.

For more information about these capabilities of MySQL Administrator, see Section 26.5, "Backup and Restore Capabilities."



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net