Basics of MySQL Database backup

One, the data backup shortcut

Since this method has not been validated by official documents, we are tentatively called experiments.

Objective: To back up a MySQL database backup testa in the Hosta host and restore it to the HOSTB machine

Test environment:

Operating system: Winnt4.0,mysql3.22.34,phpmyadmin 2.1.0

Install MySQL database backup and build Testa database in Hosta

HOSTB Machine installs MySQL database backup, no Testa database

Method steps:

Start phpMyAdmin View the list of databases in Hosta and HostB, and there are no Testa databases in HostB

Locate the MySQL database backup installation directory in Hosta and locate the database directory data

In my experimental environment, this directory is C:/mysql/data

Locate the subdirectory of the corresponding database name C:/mysql/data/testa

Paste copy into HostB's data directory, Hosta is the same as the file in the HOSTB MySQL database backup directory

Refresh HostB phpMyAdmin Look at the list of databases, we see that Testa has already appeared, and make a query to modify the operation is normal, backup restore success

Test conclusion: MySQL database can be saved by file, backup, restore as long as the corresponding file directory restore, no need to use other tools to back up.

Ii. Formal approach (official recommendation):

Export the Mysqldump tool that you want to use for MySQL database backup, basic usage is:

mysqldump [OPTIONS] database [tables]

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

By executing mysqldump--help, you can get the options table supported by your mysqldump version.

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

mysqldump supports the following options:

--add-locks

Add lock tables before each table is exported and then unlock table. (for faster insertion into MySQL database backup).

--add-drop-table

Add a drop table before each create statement.

--allow-keywords

Allow creation of column names that are keywords. This is done by means of a table name in front of the column name.

-C,--complete-insert

Use the full INSERT statement (with the column name).

-C,--compress

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

--delayed

Inserts a row with the insert delayed command.

-E,--extended-insert

Use the new multiple-line insert syntax. (Gives a tighter and faster insert statement)

-#,--debug[=option_string]

Trace the use of the program (for debugging).

--help

Displays a help message and exits.

--fields-terminated-by= ... --fields-enclosed-by= ... --fields-optionally-enclosed-by= ... --fields-escaped-by= ... --fields-terminated-by= ...

These selections are used with the-T selection and have the same meaning as the corresponding load DATA infile clause.

The LOAD DATA infile syntax.

-F,--flush-logs

Before you start exporting, wash the log files in the MySQL database backup server.

-F,--force,