Considerations when using the MySQLMySqldump command to export data

A problem occurred when I used the mysqldump command to back up data today. At the beginning of the migration to the mysql database of the Discuz7 Forum, everything went smoothly when using the mysqldump command, but the error ERROR1062 (xxxxx) atline1262: DuplicateentryXXXforkeyXXX was encountered during the import, and it stopped in the original.

A problem occurred when I used the mysqldump command to back up data today. At the beginning of the migration to the mysql database of the Discuz 7 Forum, everything went smoothly when using the mysqldump command, but encountered ERROR 1062 (xxxxx) at line 1262 during import: the Duplicate entry 'xxx' for key 'xxx' is incorrect and stops at the original location.

A problem occurred when I used the mysqldump command to back up data today.
At the beginning of the migration to the mysql database in the Discuz 7 Forum, everything went smoothly when using the mysqldump command, but we encountered
ERROR 1062 (xxxxx) at line 1262: Duplicate entry 'xxx' for key 'xxx'Error.

For friends who encounter the same errorNever try to use the-force parameter to force the import immediately (unless your original database has crashed ). In this case, even if the data is imported, a large amount of data will be lost!
At this time, you can try the following two methods:

1. Use the -- extended-insert = false parameter for mysqldump backup.

The principle is as follows:
Note when using the MySqldump command to export data
When using Mysql as the basic database, because database B's data needs to be imported into database A, and database A and database B contains A large amount of the same data, you need to use mysqldump to export the script.
The common command is

mysqldump -t 'dbName' > 'scriptName.sql'mysql -f 'dbName' < 'scriptName.sql'

The following command fails to be used to import data to database A. The result is that several Duplicate key errors are reported and the correct data is not inserted into database.
After a while, we found that in the script command exported using mysqldump, the insert statement adopts multiline insert synax. instead of using single insert synax. this is the problem. after the first primary key duplicate error occurs, the insert statement of the corresponding table will not be executed.
Then add the parameter -- extended-insert = false. The complete command is

mysqldump --extended-insert=false dbname > scriptname.sql

2. manually delete duplicate values in the username table

Execute a query using database tools such as Navicat:

Select username,Count(*) From cdb_members Group By username Having Count(*) > 1

The results show that duplicate fields exist in the table. You can see two or more identical duplicate usernames. Delete the record of one row, save the record, and then export the database.
Note the following:During backup, the table structure and data are separated!

The following are some usage parameters of mysqldump:

Back up the database:

# Mysqldump Database Name> Database Backup name # mysqldump-A-u username-p Password Database Name> Database Backup name # mysqldump-d-A -- add-drop-table-uroot-p> xxx. SQL

1. the exported structure does not export data

Mysqldump-d database name-uroot-p> xxx. SQL

2. Export data without exporting the 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 Name> xxx. SQL # mysqldump [OPTIONS] database [tables]

Mysqldump supports the following options:

-- Add-locks
Add lock tables and unlock table before each TABLE is exported. (To make it faster to insert data to MySQL ).

-- Add-drop-table
Add a drop table before each create statement.

-- Allow-keywords
Names of columns allowed to be created as keywords. This is done by the table name prefix on each column name.

-C, -- complete-insert
Use the complete insert Statement (with the column name ).

-C, -- compress
If both the client and server support compression, all information is compressed between the two.

-- Delayed
Use the insert delayed command to INSERT rows.

-E, -- extended-insert
Use the new multiline INSERT syntax. (A more compact and faster insert statement is provided)

-#, -- Debug [= option_string]
Tracking Program usage (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 options are used with-T options and have the same meaning as the load data infile clause.
Load data infile syntax.

-F, -- flush-logs
Wash Out the log files on the MySQL server before starting the export.

-F, -- force,
Even if we get an SQL error while exporting 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 start export.

-T, -- no-create-info
Create table statement)

-D, -- no-data
No row information is written to the table. If you only want to export the structure of a table, this is very useful!

-- Opt
Same as -- quick -- add-drop-table -- add-locks -- extended-insert -- lock-tables.
You should be given the fastest export possible for reading a MySQL server.

-Pyour_pass, -- password [= your_pass]
The password used to connect to the server. If you do not specify "= your_pass", mysqldump requires a password from the terminal.

-P port_num, -- port = port_num
The TCP/IP Port used to connect to a host. (This is used to connect to a host other than localhost because it uses Unix sockets .)

-Q, -- quick
Directly export the data to stdout without buffering the query; 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 the SQL CREATE command and a table_name.txt file that contains data. Note: This only works when mysqldump runs on the same machine where the mysqld daemon is running .. The format of the txt file is determined by the options -- fields-xxx and -- lines -- xxx.

-U user_name, -- user = user_name
The username used by MySQL to connect to the server. The default value is your Unix login name.

-O var = option, -- set-variable var = option to set the value of a variable. Possible variables are listed below.

-V, -- verbose
Lengthy mode. Print out more information about the program.

-V, -- version
Print the version information and exit.

-W, -- where = 'where-condition'
Only selected records are exported. Note that the quotation marks are mandatory!
"-- Where = user = 'jimf '"-wuserid> 1 ""-wuserid <1"

Import data:

Mysqldump exports complete SQL statements, so it is easy to import data into mysql client programs:

# Mysql database name <file name # source/tmp/xxx. SQL