Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the summary of the basic knowledge of MySQL database backup". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Shortcut to data backup

Since this method has not been verified by official documents, let's call it an experiment.

Purpose: to back up a MySQL database in the hostA host, back up the TestA, and restore it to the hostB machine

Test environment:

Operating system: WinNT4.0,Mysql3.22.34,MyAdmin 2.1.0

Install MySQL backup and establish TestA database in hostA

Host B machine installs MySQL database backup, no TestA database

Methods and steps:

Start phpMyAdmin to view the list of databases in HostA and HostB. There is no TestA database in HostB.

Locate the installation directory for MySQL database backup in HostA, and find the database directory data

In my experimental environment, this directory is C:data

Find the subdirectory C:mysqldataTestA of the corresponding database name

Paste and copy to the Data directory of HostB. The HostA is the same as the files in the backup data directory of the HostB MySQL database.

Refresh the phpMyAdmin of HostB and take a look at the database list. We can see that TestA has appeared, and the operations such as query modification are normal, and the backup recovery is successful.

Conclusion: the database of MySQL can be saved, backed up and restored in the form of files, as long as the corresponding file directory is restored, and there is no need to use other tools to back up.

Second, formal methods (official recommendations):

To export the mysqldump tool to use for MySQL database backup, the basic usage is:

Mysqldump [OPTIONS] database [tables]

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

By executing mysqldump-help, you can get a 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 results, which may 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. (for faster insertion into MySQL database backups).

-- 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 the table name before the column name.

-c,-- complete-insert

Use the full insert statement (with column names).

-C,-- compress

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

-- delayed

Insert a row with the INSERT DELAYED command.

-e-- extended-insert

Use the new multiline INSERT syntax. (give more compact and faster insert statements)

-#,-- debug [= option_string]

Track 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 choices are used with the-T selection and have the same meaning as the corresponding LOAD DATA INFILE clause.

LOAD DATA INFILE syntax.

-F,-- flush-logs

Wash out the log files in the MySQL database backup server before starting the export.

-f,-- force

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

-h,-- host=..

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

-l,-- lock-tables.

Lock all tables to start the export.

-t,-- no-create-info

Do not write table creation information (CREATE TABLE statement)

-d,-- no-data

No row information is written to the table. This is useful if you only want to get an export of the structure of a table!

-- opt

Same as:

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

Should give you the fastest possible export for reading into a MySQL database backup server.

-pyour_pass,-- password [= your_pass]

The password used to connect to the server. If you don't specify? = your_pass? In part, mysqldump requires a password from the terminal.

-P port_num,-- port=port_num

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

-Q,-- quick

Without buffering the query, export it directly to stdout; and use MySQL_use_result () to do it.

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 SQL CREATE commands, and a table_name.txt file that contains 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 user name used by MySQL when connecting to the server. The default is your Unix login.

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

Sets the value of a variable. Possible variables are listed below.

-v,-- verbose

Lengthy mode. Print out more information about what the program does.

-V-- version

Print version information and exit.

-W,-- where=where-condition

Export only selected records; note that quotation marks are mandatory!

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

The most common mysqldump use may make a backup of 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

Because mysqldump exports complete SQL statements, it is easy to import the data with the MySQL database backup client:

Mysqladmin create target_db_name

MySQL target_db_name backup-file.sql

Although MySQL database backup is not often encountered at ordinary times, it is not a bad thing to be familiar with one more knowledge. MySQL database backup is used to protect the database from data loss due to operational errors for people who are new to it.

This is the end of the summary of the basics of MySQL database backup. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.