MySQL User Guide Part IV Advanced MySQL Administration
[edit] Part IV. Advanced MySQL Administration
[edit] Configuring MySQL
[edit] MySQL Option Files
[edit] Upgrading MySQL
[edit] Upgrading on <operating system>
[edit] MySQL Security
[edit] Security Basics
[edit] Grants
[edit] Securing Default User Accounts
[edit] Advanced Utilities
- myisamchk -- checking and repairing MyISAM tables
[edit] Advanced Features of Previously Mentioned Utilities
[edit] Best Practices
[edit] Backing Up Data
[edit] Introduction
Say something about disaster recovery.
[edit] Using mysqldump *Complete*
MySQL Contributor. This section was contributed by MySQL staff. For more information see http://mysql.com.
The mysqldump utility is a database back-up program capable of copying everything on a specific MySQL server -- both the database objects and the data. It can also be used to copy a number of databases, one particular database, one or more tables from a specific database, or just specific records from one table. Any kind of data can be saved using this utility -- even images stored as binary data.
The mysqldump utility creates a script file of SQL statements that recreate the database objects selected and it also creates INSERT statements to restore data. There are various other ways to back up MySQL databases or tables; using the mysql client program and SQL statements, copying the MySQL data directory, using binary logs, using MySQL Administrator, and also the Unix-specific utility, mysqlhotcopy. However, mysqldump is the most versatile and accessible tool for backing up tables and databases and it is available for all operating systems.
The reasons for creating back-up files vary:
- As replacements for existing files in the event of database. corruption
- To transfer files from a development server to a production. server
- To migrate to another file format.
- For reverse engineering. For using a file created by mysqldump to see reverse engineering a database.
This chapter shows how to use mysqldump for each of these tasks. This is not meant as a definite treatment of mysqldump; for complete coverage of this utility see http://dev.mysql.com/doc/refman5.0/en/mysqldump.html.
[edit] Options
This section identifies the most commonly used options and briefly describes each one. Examples of using these options are given in subsequent sections.
Since the mysqldump utility gives access to a specific MySQL server, you must have credentials on that server; you must explicitly or implicitly provide a --user and --password. Likewise you must provide --host and --port options. In this respect, mysqldump does not differ from the MySQL client program, mysql, or from other utilities such as mysqladmin.
Other common options are:
-
--all-databases, -A-- Dump all tables in all databases. -
--databases, -B-- Specify this option and mysqldump regards all name arguments as database names -
--fields-terminated-by-- Used in conjunction with the--taboption to specify a field terminator. -
--no-create-db-- Used in conjunction with the--all-databasesor the--databasesoption to suppress theCREATE DATABASEstatement -
--no-data-- Save database objects but not data. -
--opt-- This option is shorthand for a group of options. See ["The--optGroup of Options"]. -
--skip-opt-- Turn off the--optgroup of options. -
--tab=path, -T path-- Create tab-separated data files in the named directory. -
--tables-- Override the--databasesoption. mysqldump regards all name arguments following this option as table names. -
--where=where_condition, -w where_condition-- Only dump rows selected by the where condition. -
--xml-- Dump output as XML.
The --all-databases option is used when you want to dump the entire contents of a server. On the other hand, the --databases option lets you specify particular databases to copy. Both of these options add a CREATE DATABASE statement to the dump file. To turn off this feature use the --no-create-db option. You can also choose not to save any data by using the --no-data option.
The --tables option makes it possible to use the --databases option and also specify which tables you would like to dump.
Use the --tab and --fields-terminated-by options, to dump a database in a variety of text formats. For XML format, use the --xml option. To select only specific rows from a table use the --where option.
For a complete list of all the available options see http://dev.mysql.com/doc/refman5.0/en/mysqldump.html.
[edit] The --opt Group of Options
The --opt option is on by default so you don't have to specify it. However, you do need to know what it does. Using --opt is shorthand for specifying --add-locks, --add-drop-table, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. Find a brief description of these options in what follows.
-
--add-locks-- Lock tables before inserting data. -
--add-drop-table-- Remove tables before recreating them. -
--create-options-- Include all MySQL-specific table options in theCREATE TABLEstatements. -
--disable-keys-- Improve speed by disabling indexes before inserting data. (Applies only to MyISAM tables and only to non-unique indexes.) -
--extended-insert-- Use multiple-rowINSERTsyntax that includes aVALUESlist for each row. -
--lock-tables-- Lock tables before dumping them. -
--quick-- Retrieve rows from a table one row at a time, reducing demands on memory. -
--set-charset-- AddSET NAMESdefault_character_setto the output.
Each of these options can be turned off individually by using the --skip-option-name syntax. For example, if you want to ensure that you recreate tables as the server default table type, you can turn off --create-options by specifying --skip-create-options. No engine or character set will be specified in the CREATE TABLE statement. Turn off --extended-insert by specifying the --skip-extended-inserts option. Doing this creates a separate INSERT statement for each row, making it much easier to remove individual INSERT statements.
[edit] Backing Up Data and Database Objects
To back up the contents of a server and create replacements for all existing databases invoke the mysqldump utility specifying your credentials and the --all-databases option. Using the option short forms, you can back up a server and redirect output to a file in the following way:
shell> mysqldump -u user_name -p -A > dump.sql
Using the short forms shown in the preceding listing is equivalent to using the --user, --password, and --all-databases options. Output is sent to a file using the redirection operator, ">". Since the --host option is not specified, it defaults to localhost. Likewise, --port will default to 3306. Since no password is given at the command line, you will be prompted for one.
Specifying your password at the command line is allowed but note that you cannot leave a space between the option and your password; it must appear as -ppassword. If a space was allowed, the -A option in the preceding listing would be interpreted as the password.
If you wish to copy only specific databases, replace -A with the --databases option (or its short form, -B) followed by the names of the databases that you wish to back up. The file created by this command will contain only the databases specified.
Using mysqldump to back up specific databases or all the databases on a server is an easy way to create replacements in the event of lost data or database corruption.
[edit] Further Refining the Objects and Data Selected
If you wish to copy only one database, you do not need to use the --databases option. Simply specify your credentials and the database name in the following fashion:
shell> mysqldump -u user_name -p db_name > dump.sql
A specific database is selected by using the database name -- no option is necessary. Output is again redirected to the file using the redirection operator.
Remember that the --opt group of options is on by default. (For a complete list of this group of options see [userguide-creating-backups.html#opt-group-options Section 24.2.1.1, "The --opt Group of Options"].) To turn off any one of these options you can use the --skip-option-name< option.
On the other hand, if you want to turn off most of the --opt options, it may be easier to specify --skip-opt and then list the options you wish to use.
Note: If you choose to do things this way, make sure that you specify--skip-optfirst. If it is the last option specified, it will turn off any of the--optgroup of options that precede it.
In some cases you may want to copy only selected tables from a database. This is done by naming the desired tables immediately following the database name. For example:
shell> mysqldump [options] db_name table1 table2
When dumping a specific table, the data selection can be further refined by adding a --where option in the following way:
shell> mysqldump [options] db_name table1 --where=field_name>1000
When using the --where option only one table may be specified. The script file created will contain a CREATE TABLE statement for reconstructing the table and any data that meets the condition specified using the --where option.
Note: If the --where option contains spaces or characters special to your command interpreter, then you must enclose everything in the where condition in quotation marks.
Using a database name at the command line creates a copy of the tables and the data from the specified database. However, no database is created when this syntax is used.
To dump only one database and add a CREATE DATABASE statement, you must use the --databases option. An example using the short form of the --databases option follows:
shell> mysqldump -u user_name -p -B db_name > dump.sql
If you wish your dump file to contain a CREATE DATABASE statement and you only wish to dump selected tables use the --tables option as shown in the following:
shell> mysqldump -u user_name -p -B db_name --tables table1 > dump.sql
If you don't specify the --tables option, the -B option interprets each name as a database.
The next section examines how to restore databases from the script files created by mysqldump.
[edit] Restoring Database Dumps
Databases are restored by redirecting the script file to the mysql client program. If the script file was created using either the -A or -B options, restore the dumped files in the following way:
shell>mysql -u <code>user_name-p <dump.sql</code>
[edit] Warning
Using the -A or -B option with mysqldump creates a script that drops and recreates databases. Any data in existing databases will be lost. Furthermore, if you backed up all databases then the mysql database will be overwritten. Be sure that this is what you intend. For more information see ...
If you created your dump file without using the -A or -B options, then the database that you copy the tables to must already exist. Name that database when invoking mysql:
shell>mysql -u <code>user_name-pdb_name<dump.sql</code>
In this case, you need not worry about overwriting an existing database, but you will overwrite any tables in the existing database that have the same names as tables in the back-up file if the file contains DROP TABLE and CREATE TABLE statements. To remove these statements from a dump file, create it using the --skip-add-drop-table and --no-create-info options.
If you are uploading a database dump file to a remote database then you will have to specify the --host option. If you don't have access to your MySQL server from a remote location, copy your script file to the server, log in using ssh, and then run mysql. If neither of these options is available to you, you may be able to upload and execute the script file using a program such as phpMyAdmin.
[edit] Exporting From MySQL
To use the data from a MySQL database in another application -- a word processor or a spreadsheet, for example -- you might want to export data in text format. The most common way of exporting a file in text format is by using the --tab or -T option and specifying the full pathname to the target directory:
shell>mysqldump -u <code>user_name-pdb_name-T/tmp</code>
Dumping a database specifying this option creates a script file of each table's structure using the table name and the extension sql as the file name and a tab-separated file of each table's data using the table name and the extension txt as the file name. These files are created in the directory specified with the -T option. This directory must be writable and the user indicated by user_name must have the FILE privilege. For more information about the FILE privilege see ...
File permissions are not usually a problem on Windows systems but the file separator and spaces in file names can present difficulties. Use a forward slash to separate directories and, if a directory contains spaces, enclose the path in quotation marks, for example, "C:/Documents and Settings/peter/Desktop/". Failure to include quotation marks results in the following error:
mysqldump: Got error: 1049; Unknown database 'and' when selecting the database
Despite its name, the --tab option can be used to created files with a field terminator other than the tab character. The field terminator is changed by using the --fields-terminated-by option. For example you can specify a "," as the terminator in order to use a table in a spreadsheet program.
The --tab option is designed to extract data from one database only and cannot be used with the --databases option, or with the --all-databases option. Whenever it is used a database name must be one of the arguments to mysqldump.
To further refine the data selected, the --tab option can also be used to select data from one table only. This is done by naming the desired table after selecting the database. The --tab option can also be used with the --where option as shown in [userguide-creating-backups.html#refining-objects-selected Section 24.2.2.1, "Further Refining the Objects and Data Selected"].
Often, when creating text files there is no need for the script file that creates the table structure -- you're simply interested in exporting the data. In cases like this it would be nice to have an option to copy only data. No such option exists but we will see how to do this when we discuss SELECT ... INTO OUTFILE. For more information see ...
For an XML representation of the data and the database objects use the --xml option. This option creates an XML document in the following format:
<?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="sakila"> <table_structure name="actor"> <field Field="actor_id" Type="smallint(5) unsigned" Null="NO" Key="PRI" Extra="auto_increment" /> <field Field="first_name" Type="varchar(45)" Null="NO" Key="" Default="" Extra="" /> <field Field="last_name" Type="varchar(45)" Null="NO" Key="MUL" Default="" Extra="" /> <field Field="last_update" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="" /> <key Table="actor" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="actor_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" /> <key Table="actor" Non_unique="1" Key_name="idx_actor_last_name" Seq_in_index="1" Column_name="last_name" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" /> <options Name="actor" Engine="InnoDB" Version="10" Row_format="Compact" Rows="0" Avg_row_length="0" Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="0" Auto_increment="1" Create_time="2007-04-11 19:35:58" Collation="utf8_general_ci" Create_options="" Comment="InnoDB free: 10240 kB" /> </table_structure> <table_data name="actor"> [table data] ... </table_data> [more table definitions and data] ... </database> </mysqldump>
Given the ease with which a database can be converted to XML you might wonder whether conversion to HTML is also possible. Unfortunately, there is no mysqldump option for creating HTML output. However, this can be done by starting mysql using the --html and --tee options. For instructions on doing this see [mysql-client-programs.html#mysql-client-other-options Section 4.2.2, "Other Options"].
[edit] Replication
[edit] Other Options
[edit] MySQL Log Files
[edit] What They Can Tell You
[edit] Error Log
[edit] The Slow Query Log
If you need to identify slow queries on a production MySQL server you may benefit from using the MySQL Slow Query Log. When the MySQL server is started with the --log-slow-queries option, it writes all queries that take longer than a configurable number of seconds to a log file. The queries in the Slow Query Log can be further examined and optimized.
The Slow Query Log can also be activated by adding the log-slow-queries directive to the [mysqld] section of your server option file, or through the MySQL Administrator:
The Slow Queries Log option is found in the Log Files tab of the Startup Variables screen. Click the clipboard icon to the left of the option to activate the Slow Query Log and click the button. Once the Slow Query Log is activated, restart the MySQL server using the Service Control screen.
The default name of the log file is <code>server-name-slow.log</code>. If your server is named doomhammer.myserver.org, the log file will be named doomhammer.myserver.org-slow.log.
The Slow Query Log is a plain-text file that contains three lines for every query logged:
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 3
SELECT last_name, first_name, actor_id FROM actor WHERE last_name = 'Brando';
The first line lists the username and hostname of the user who executed the query. The second line lists the time taken to execute the query, the time required to acquire the necessary locks, the number of rows returned by the query, and the number of rows the MySQL optimizer needed to examine. The final line of the entry shows the query that was executed.
The Slow Query Log can also be read using the MySQL Administrator using the Slow Log tab of the Server Logs screen:
The Slow Queries Log determines whether a query is slow by how long the query takes to execute in seconds, not counting the time required to acquire table locks. The default time is two seconds and can be adjusted by setting the long_query_time option in the [mysqld] section of the server configuration file. The long_query_time option can also be set using the Log Files tab of the Startup Variables screen of MySQL Administrator.
It should be noted that queries can appear in the Slow Query Log even if they are properly optimized if the server load is high enough to cause the query to take longer than the long_query_time.
If you wish to log all queries that do not use indexes, regardless of how long the queries take to execute, add the log-queries-not-using-indexes option to the [mysqld] section of your MySQL server configuration file, or check the Log queries that don't use indexes option of the Log Files tab of the Startup Variables screen of MySQL Administrator.