Considerations when using the Mysql mysqldump command to export data _mysql

There was a problem using the mysqldump command to back up data today.
The first migration Discuz 7 forum MySQL database, the use of mysqldump command when everything goes well, but the introduction of the time encountered
Error 1062 (xxxxx) at line 1262:duplicate entry ' xxx ' for key ' xxx ' errors, and stop in situ.

For a friend who encounters the same error, never try to force the import with the –force parameter (unless your original database has crashed) at the first time. Because of this, even if imported, there will be a large number of data loss!
at this point you can try the following 2 methods:

Use--extended-insert=false parameters when mysqldump backup.

the principle is as follows:
Note When exporting data using the mysqldump command
When using MySQL as the underlying database, you need to export the script using mysqldump because you need to import library B's data into library A and the A,b library contains a lot of the same data.
The usual order would be

Mysqldump-t ' dbname ' > ' scriptname.sql '
mysql-f ' dbname ' < ' scriptname.sql '

The use of the following command to import to a library will not succeed, the phenomenon is reported a few duplicate key error after the completion of the rest of the correct data is not inserted into a library.
For a while, I found that in script commands that are exported using mysqldump, the INSERT statement takes the multiline insert Synax. Instead of a single insert Synax. This is the problem. The multiple-line insertion syntax does not execute subsequent INSERT statements of the corresponding table after the first primary key repeats an error.
And then add the parameter--extended-insert=false, the complete command is

Mysqldump--extended-insert=false dbname > Scriptname.sql

Second, manually delete the table in the username duplicate values are deleted

Using Database Tools like Navicat, execute a query:

Select Username,count (*) from Cdb_members Group by username has Count (*) > 1

The results show that there are duplicate fields in the table, and you will see 2 or more identical duplicate user names. Delete one row of records, save and then export the database.
Here need special attention: When backing up, the table structure and data separation!

Here are some usage parameters for mysqldump

Back up the database:

#mysqldump database name > Database backup name
#mysqldump-A-u user name-p password database name > database backup name
#mysqldump-D-A--add-drop-table-uroot-p ; xxx.sql

1. Export structure does not export data

mysqldump-d database name-uroot-p > Xxx.sql

2. Export data does not export structure

MYSQLDUMP-T database name-uroot-p > Xxx.sql

3. Export Data and table structure

Mysqldump database name-uroot-p > Xxx.sql

4. Export the structure of a specific table

Mysqldump-uroot-p-B database name--table table name > Xxx.sql
#mysqldump [OPTIONS] database [tables]

Mysqldump supports the following options:

--add-locks
Add lock tables before each table is exported and then unlock table. (in order to allow faster insertion into MySQL).

--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 the table name prefix in each 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 server.

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

----H,--host=.
Export data from the MySQL server on the named host. The default host is localhost.

-L,--lock-tables.
Lock all tables for starting export.

-T,--no-create-info
Do not write table creation information (CREATE TABLE statement)

-D,--no-data
Any row information that does not write to the table. This is useful if you want to export only the structure of a table!

--opt
--extended-insert--lock-tables with--quick--add-drop-table--add-locks.
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, the mysqldump requires a password from the terminal.

-P Port_num,--port=port_num
The TCP/IP port number to use when connecting to a 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
The socket file used by the 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 works only when Mysqldump is running on the same machine that the mysqld daemon is running. 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 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 about what the program is doing.

-V,--version
Print 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"

Import data:

since mysqldump is exporting complete SQL statements, it is easy to import data into the MySQL client program:

#mysql database name < filename
#source/tmp/xxx.sql