What are the methods of backing up MySQL database

This article mainly explains "what are the methods of backing up MySQL database". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the methods of backing up MySQL database"?

What are the simple ways to back up a MySQL database

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 TestA in the hostA host and restore it to the hostB machine

Test environment:

Operating system: WinNT4.0,MySQL3.22.34,phpMyAdmin2.1.0

Install MySQL database and establish TestA database in hostA

Host B machine installs MySQL database, 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 of MySQL in HostA and find the database directory data

In my experimental environment, this catalog is

C:MySQLdata

Find the subdirectory of the corresponding database name

C:MySQLdataTestA

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

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.

What are the simple ways to back up a MySQL database

Second, formal methods (official recommendations):

To export the MySQLdump tool to be used with MySQL, 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 LOCKTABLES before each table is exported and then UNLOCKTABLE. (for faster insertion into the MySQL).

-- add-drop-table

Add a droptable 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 _ mam _ r _ r

Use the full insert statement (with column names).

-Cremlin Mustang compress

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

-- delayed

Insert a row with the INSERTDELAYED command.

-emam, music, talk, extend, 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=...

Thank you for your reading, these are the contents of "what are the methods of backing up MySQL database?" after the study of this article, I believe you have a deeper understanding of the method of backing up MySQL database, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!