MySQL database backup basics

Usually when we use the MySQL database, often because of operational errors caused by data loss, MySQL database backup can help us to avoid due to various reasons, data loss or other problems with the database.

First, data backup shortcut

Because this method has not been officially certified official document, we tentatively called the test.

Purpose: Back up a MySQL database backup hostA host TestA, and restored to the hostB machine

Test environment:

Operating System: WinNT4.0, Mysql3.22.34, phpMyAdmin 2.1.0

Install MySQL database backup in hostA and set up TestA database

HostB installed MySQL database backup, there is no TestA database

Method steps:

Start phpMyAdmin View HostA and HostB in the database list, there is no TestA database in HostB

Find HostA MySQL database backup installation directory, and find the database directory data

In my experimental environment, this directory is C: mysqldata

Find the corresponding database name subdirectory C: mysqldataTestA

Paste copy to HostB's Data directory, HostA HostB MySQL database backup data directory with the same file

Refresh HostB phpMyAdmin look at the list of databases, we see that TestA has emerged, and for query modification and other operations are normal, the backup and restore success

Test Conclusion: MySQL database can be saved as a file, backup, recovery as long as the corresponding file directory recovery can be, without using other tools to back up.

Second, the formal approach (official advice):

Export to use MySQL database backup mysqldump tool, the basic usage is:

mysqldump [OPTIONS] database [tables]

If you do not give any table, the entire database will be exported.

By executing mysqldump --help you can get the list of options supported by your version of mysqldump.

Note that if you run mysqldump without the --quick or --opt option, mysqldump will load the entire result set into memory before exporting the result, which can be a problem if you are exporting a large database.

mysqldump supports the following options:

--add-locks

Add LOCK TABLES before each table is exported and then UNLOCK TABLE. (In order to make it faster to insert into a MySQL database backup).

--add-drop-table

Add a drop table before each create statement.

--allow-keywords

Allows you to create column names that are keywords. This is done by adding a table name before the column name.

-c, --complete-insert

Use the complete insert statement (using the column name).

-C, --compress

If both client and server support compression, compress all information between the two.

--delayed

Insert a row with the INSERT DELAYED command.

-e, --extended-insert

Use the new multi-line INSERT syntax. (Gives tighter and faster insert statements)

- #, --debug [= option_string]

Track the use of the program (for debugging).

--help

Show a help message and exit.

--fields-terminated-by = ...

--fields-enclosed-by = ...

--fields-optionally-enclosed-by = ...

--fields-escaped-by = ...

--fields-terminated-by = ...

These selections are used with the -T option and have the same meaning as the corresponding LOAD DATA INFILE clause.

LOAD DATA INFILE syntax.

-F, --flush-logs

Before starting to export, wash away the log files in the MySQL database backup server.

-f, --force,

Even if we get an SQL error during a table export, continue.

-h, --host = ..

Export data from a MySQL database backup server on a named host. The default host is localhost.

-l, --lock-tables.

Lock all tables for export start.

-t, --no-create-info

Do not write table creation information (CREATE TABLE statement)

-d, --no-data

Any row information not written to the table. This is useful if you only want to export the structure of a table!

--opt

with:

--quick --add-drop-table --add-locks --extended-insert --lock-tables

You should give your fastest possible export for reading into a MySQL database backup server.

-pyour_pass, --password [= your_pass]

The password used when connecting to the server. If you do not specify the "= your_pass" section, mysqldump needs a password from the terminal.

-P port_num, --port = port_num

The TCP / IP port number to use when connecting to a host. (This is used to connect to a host other than localhost because it uses Unix sockets.)

-q, --quick

Do not buffer the query, export directly to stdout; do it using MySQL_use_result ().

-S / path / to / socket, --socket = / path / to / socket

The socket file used when connecting to localhost, which is the default host.

-T, --tab = path-to-some-directory

For each given table, create a table_name.sql file that contains the SQL CREATE command, and a table_name.txt file that contains the data. Note: This only works if mysqldump is running on the same machine as the mysqld daemon. The format of the .txt file is based on the --fields-xxx and --lines - xxx options.

-u user_name, --user = user_name

The username MySQL uses when connecting to the server. The default is your Unix login name.

-O var = option, --set-variable var = option

Set the value of a variable. The possible variables are listed below.

-v, --verbose

Long pattern. Print out the program to do more information.

-V, --version

Print version information and exit.

-w, --where = 'where-condition'

Export only the selected record; note the quotation marks are mandatory!

"--where = user = 'jimf'" "-wuserid> 1" "-wuserid1"

The most common mysqldump uses a backup that might make the entire database:

mysqldump --opt database> backup-file.sql

But it is also useful for enriching another MySQL database backup with information from one database:

mysqldump --opt database

MySQL - host = remote-host -C database