Backup and recovery of mysqldum database in MySQL

1, mysqldump
1.1 Backup
Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files and is relatively appropriate for upgrades between different versions of MySQL, which is also the most common method of backup. Now let's talk about some of the main parameters of mysqldump:

--compatible=name
It tells Mysqldump which database or older version of the MySQL server the exported data will be compatible with. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. , you use a few values to separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.

--complete-insert,-c
The exported data takes a complete INSERT with the field name, which means that all values are written on one line. This can increase the efficiency of the insertion, but may be affected by the Max_allowed_packet parameter and cause the insertion to fail. Therefore, you need to use this parameter with caution, at least I do not recommend it.

--default-character-set=charset
Specifies what character set to use when exporting data, and if the datasheet is not the default Latin1 character set, you must specify this option when exporting, otherwise the garbled problem will occur after you import the data again.

--disable-keys
Tell mysqldump to add/*!40000 ALTER table Table DISABLE KEYS at the beginning and end of the INSERT statement. and/*!40000 ALTER table table to ENABLE the KEYS * *; statement, which greatly increases the speed of the INSERT statement because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.

--extended-insert = True|false
By default, Mysqldump opens the--complete-insert mode, so if you don't want to use it, use this option to set its value to false.

--hex-blob
Exports a binary string field using hexadecimal format. You must use this option if you have binary data. The field types that are affected are BINARY, VARBINARY, and blobs.

--lock-all-tables,-x
Commit the request to lock all tables in all databases before starting the export to ensure data consistency. This is a global read lock and automatically turns off the--single-transaction and--lock-tables options.

--lock-tables
Similar to--lock-all-tables, it locks the currently exported datasheet instead of locking the table under all the libraries at once. This option applies only to MyISAM tables, and if the Innodb table can use the--single-transaction option.

--no-create-info,-t
Exports only data without adding a CREATE TABLE statement.

--no-data,-d
Does not export any data, only the database table structure is exported.

--opt
This is just a shortcut option, equivalent to adding--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables- Quick--set-charset option. This option allows mysqldump to quickly export data, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with--skip-opt. Note that if you run mysqldump without specifying the--quick or--opt option, the entire result set is placed in memory. Problems may occur if you export a large database.

--quick,-q
This option is useful when exporting large tables, forcing mysqldump to obtain records directly from a server query instead of having all the records cached in memory.

--routines,-r
Export stored procedures and custom functions.

--single-transaction
This option submits a BEGIN SQL statement before the data is exported, and the begin does not block any applications and guarantees the consistency state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB. This option and the--lock-tables option are mutually exclusive, because lock tables will cause any pending transactions to be implicitly committed. To export a large table, you should use the--quick option in combination.

--triggers
The trigger is also exported. This option is enabled by default, and it is disabled with--skip-triggers.

For additional parameter details please refer to the manual, I usually use the following SQL to back up the MyISAM table:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false --triggers-r--hex-blob-x db_name > Db_name.sql

Use the following SQL to back up the Innodb table:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false --triggers-r--hex-blob--single-transaction db_name > Db_name.sql

Open start-> run-> input cmd enter command line mode

The code is as follows	Copy Code
C:>mysqldump-h localhost-u root-p mydb >e:mysqlmydb.sql

Then enter the password and wait for the export to succeed and check for success in the target file.

2. Export the mytable in the database mydb to the E:mysqlmytable.sql file:

The code is as follows	Copy Code
C:>mysqldump-h localhost-u root-p mydb mytable >e:mysqlmytable.sql

3. Export the structure of the database mydb to the E:mysqlmydb_stru.sql file:

The code is as follows	Copy Code
C:>mysqldump-h localhost-u root-p mydb--add-drop-table >e:mysqlmydb_stru.sql

-h localhost can be omitted, it is generally used on the virtual host

1.2 Restore

A file backed up with Mysqldump is a SQL script that can be poured directly, and there are two ways to import the data.

To import data from an external file to a database:
For example:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysql-uyejr-pyejr Db_name < Db_name.sql

To import the SQL statements from the file into the database from E:mysqlmydb2.sql:

The code is as follows	Copy Code
C:>mysql-h localhost-u root-p mydb2 <e:mysqlmydb2.sql

• Use SOURCE syntax

In fact, this is not a standard SQL syntax, but the functionality provided by the MySQL client, for example:

The code is as follows	Copy Code
Source/tmp/db_name.sql;

You need to specify the absolute path of the file, and it must be a file that the Mysqld run user (for example, nobody) has permission to read.

Access to the MySQL database console: such as Mysql-u root-p

The code is as follows	Copy Code
Mysql>use DatabaseName

Then use the source command, followed by the script file (here is the. sql)

The code is as follows	Copy Code
Mysql>source D:mydb.sql

And then enter the password and it's OK.

Two. command line access to MySQL method:

The code is as follows	Copy Code
C:>mysql-h Hostname-u Username-p

Press ENTER to wait and then enter the password, where hostname is the name of the server, such as the user name Localhost,username for MySQL, such as root. You can go to the command line to operate MySQL directly.

about the resolution of the import file size limit problem:

By default: MySQL for the import of the file size limit, the maximum 2M, so when the file is very large, can not import directly, the resolution listed as follows:

1. Modify the relevant parameters in PHP.ini:

There are three parameters that affect the size of the MySQL import file:

The code is as follows	Copy Code
memory_limit=128m, upload_max_filesize=2m, post_max_size=8m

Modify upload_max_filesize=200m here to modify the size you need, you can modify the other two memory_limit=250m, post_max_size=200m. This allows you to import the. sql files under 200M.

The above is the MySQL placed in the system path, in fact, can also be. If my MySQL installation directory is D:mysql Server 5.0, first open the DOS window and enter D: (no ') carriage return, and then you should have a flag like d:>, then enter D:mysql server 5.0bin carriage return; D: MySQL Server 5.0bin> then enter mysqldump-u root-p database name > database name. SQL (You can also enter a path); (refer to the above) import file Also, just change the ' > ' for ' < ' on it, or directly with source can also: