The easiest way to backup MySQL database

Http://www.kankanews.com/ICkengine/archives/194.shtml

Using MySQL for database backup, but also a very formal database backup method, and other database server has the same concept, but have not thought, MySQL will have a more concise use of file directory backup method, but also fast and good.

First, data backup shortcuts

Since this method has not been formally validated by official documentation, we call it a trial.

Purpose: Back up a MySQL database testa in the Hosta host and revert to the HOSTB machine

Install the MySQL database in hosta and build the Testa database

HOSTB machine installed MySQL database, no testa database

Method steps:

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

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

In my test environment, this directory is

C:\mysqldata

Locate the subdirectory of the corresponding database name

C:\mysqldataTestA

Paste Copy to HostB data directory, hosta same as hostb mysql file directory

Refresh HostB phpMyAdmin Look at the list of databases, we see Testa has appeared, and make query changes, and so on, the backup recovery is successful

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

Ii. Formal Methods (official recommendations):

To export the Mysqldump tool to use with MySQL, the 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 a list of options supported by your mysqldump version.

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 would probably 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. (To make it faster to insert into MySQL).

–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 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

Insert a row with the insert delayed command.

-e,–extended-insert

Use the new multi-line insert syntax. (Give a more condensed and faster INSERT statement)

-#,–debug[=option_string]

The use of the tracker (for debugging purposes).

Help

–fields-terminated-by= ...

–fields-enclosed-by= ...

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

–fields-escaped-by= ...

–fields-terminated-by= ...

These options 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

Before starting the export, wash down the log files in the MySQL server.

-f,–force,

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

-h,–host=

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

-l,–lock-tables.

Locks all tables for starting 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 export the structure of a table!

–opt

With –quick–add-drop-table–add-locks–extended-insert–lock-tables.

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

-pyour_pass,–password[=your_pass]

The password to use when connecting to the server. If you do not specify the "=your_pass" section, Mysqldump requires a password from the terminal.

-P Port_num,–port=port_num

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

-q,–quick

Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.

-s/path/to/socket,–socket=/path/to/socket

A socket file that is used with localhost when it is connected (it 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 when the mysqldump is running on the same machine as the mysqld daemon. The format of the TXT file depends on the –fields-xxx and –LINES–XXX options.

-U user_name,–user=user_name

The user name that MySQL uses when connecting to the server. The default value is your UNIX login name.

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

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

-v,–verbose

Verbose mode. Print out more information that the program has done.

-v,–version

Print the version information and exit.

-w,–where= ' Where-condition '

Only the selected records are exported; Note that the quotation marks are mandatory!

"–where=user= ' JIMF '" "-wuserid>1″"-wuserid<1″

The most common use of mysqldump may be to make a backup of the entire database:

Mysqldump–opt Database > Backup-file.sql

But it is also useful to enrich another MySQL database with information from one database:

Mysqldump–opt Database | Mysql–host=remote-host-c Database

Since mysqldump exports a complete SQL statement, it is easy to import the data using the MySQL client program:

Mysqladmin Create Target_db_name

MySQL Target_db_name < Backup-file.sql

No tags for this post.

Unless indicated, this site article is original or compiles, reprint please specify: Article from Kengine | Kankanews.com

The easiest way to backup MySQL database