Detailed explanation of mysqldump data export issues

The following editor will bring you an article that discusses in detail the issue of mysqldump data export. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

1. When using mysqldump, an error message (1064) is reported. This is because the mysqldump version is too low and inconsistent with the current database version.

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)
[root@bastion-IDC ~]# mysqldump --versionmysqldump Ver 10.13 Distrib 5.1.61, for redhat-linux-gnu (x86_64)
[root@bastion-IDC ~]# mysql           //或者登陆mysql，select version();也可查看版本
Server version: 5.6.25-log Source distribution。。。。。

In this case, you must know the absolute path of mysqldump, which is in the mysql installation directory.

2, specify the character set when exporting, and an error is reported

Character set 'utf-8' is not a compiled character set and is not specifie .
--default-character-set=utf-8

This is because the character set is wrong. Yes--default-character-set=utf8

3, a warning is prompted when exporting, A partial dump from a server that has GTIDs

[root@bastion-IDC ~]# mysqldump -uroot -p xqsj_db > xqsj_db20160811.sql
Warning:
 A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that 
changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete 
dump, pass --all-databases --triggers --routines --events.

About GTID, after 5.6, the global transaction ID (GTID) was added to strengthen the primary and secondary consistency, fault recovery, and fault tolerance of the database.
Officially given: A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).
So it may be because it is unique in a database, but when imported Other libraries may be duplicated. There will be a reminder for all.

Can be set by adding the two parameters --set-gtid-purged=off or --gtid-mode=OFF.

It is possible to regenerate the GTID in the import library instead of using the original one.

[root@bastion-IDC ~]# mysqldump -uroot --set-gtid-purged=off -p xqsj_db > xqsj_db20160811.sql    #这样就ok了！

The above is the detailed content of Detailed explanation of mysqldump data export issues. For more information, please follow other related articles on the PHP Chinese website!