Detailed discussion on the issue of mysqldump data export

1. An error (1064) is reported when using mysqldump. This is because the mysqldump version is too old 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 --version
mysqldump Ver 10.13 Distrib 5.1.61, for redhat-linux-gnu (x86_64)

[root@bastion-IDC ~]# mysql //Or log in to mysql, select version(); You can also view the 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 will be reported

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

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

3. Warning 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.

Regarding GTID, after 5.6, the global transaction ID (GTID) was added to enhance the master-slave consistency, fault recovery, and fault tolerance of the database.
Official: 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 one database, but it may be duplicated when imported into other libraries. There will be a reminder for all.

This can be set by adding the following two parameters: --set-gtid-purged=off or --gtid-mode=OFF.

It is very likely that the GTID is regenerated in the import library instead of the original one.

[root@bastion-IDC ~]# mysqldump -uroot --set-gtid-purged=off -p xqsj_db > xqsj_db20160811.sql #That's it!

The above article discusses the problem of mysqldump data export in detail. This is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.