mysqldump -h127.0.0.1 -u user -p'password' -t db mail --where 'status=3' > /var/www/project/storage/web/dump2.sql

我在 ubuntu 18.04、mysql 5.7.29 上試了一下。

該文件始終為 141.8MB。數據總是在同一點中止。

它是 innodb 表。還有長文本欄位。18 行，表大小為 132MiB。

也嘗試過--opt- 相同的結果（

沒有錯誤，只有輸出是：

mysqldump: [Warning] Using a password on the command line interface can be insecure.

my.cnf 中的 max_allowed_pa​​cket：

max_allowed_packet         = 2000MB

對於mysqldump也設置了2000MB

[mysqldump]

max_allowed_packet         = 2000MB

--where沒有給出相同結果的簡單變體

mysqldump -h127.0.0.1 -u user -p'password' -t db mail > /var/www/project/storage/web/dump2.sql

詳細輸出：

-- Connecting to 127.0.0.1...
-- Retrieving table structure for table mail...
-- Sending SELECT query...
-- Retrieving rows...
-- Disconnecting from 127.0.0.1...

error.log 的最後 50 行

2020-02-20T19:52:06.417731Z 0 [Note] InnoDB: FTS optimize thread exiting.
2020-02-20T19:52:06.417992Z 0 [Note] InnoDB: Starting shutdown...
2020-02-20T19:52:06.518175Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2020-02-20T19:52:06.519446Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 200220 21:52:06
2020-02-20T19:52:07.759476Z 0 [Note] InnoDB: Shutdown completed; log sequence number 9662111222
2020-02-20T19:52:07.762068Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-02-20T19:52:07.762093Z 0 [Note] Shutting down plugin 'MEMORY'
2020-02-20T19:52:07.762103Z 0 [Note] Shutting down plugin 'CSV'
2020-02-20T19:52:07.762112Z 0 [Note] Shutting down plugin 'sha256_password'
2020-02-20T19:52:07.762118Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-02-20T19:52:07.762364Z 0 [Note] Shutting down plugin 'binlog'
2020-02-20T19:52:07.763198Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2020-02-20T19:52:08.167044Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-02-20T19:52:08.169129Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1-log) starting as process 3238 ...
2020-02-20T19:52:08.176454Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-02-20T19:52:08.176559Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-02-20T19:52:08.176573Z 0 [Note] InnoDB: Uses event mutexes
2020-02-20T19:52:08.176582Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-02-20T19:52:08.176590Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-02-20T19:52:08.176597Z 0 [Note] InnoDB: Using Linux native AIO
2020-02-20T19:52:08.176913Z 0 [Note] InnoDB: Number of pools: 1
2020-02-20T19:52:08.177071Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2020-02-20T19:52:08.180767Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-02-20T19:52:08.195140Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-02-20T19:52:08.198558Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-02-20T19:52:08.211568Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-02-20T19:52:08.262668Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-02-20T19:52:08.262820Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-02-20T19:52:08.348749Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-02-20T19:52:08.350191Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-02-20T19:52:08.350221Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-02-20T19:52:08.350935Z 0 [Note] InnoDB: 5.7.29 started; log sequence number 9662111222
2020-02-20T19:52:08.351327Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-02-20T19:52:08.351529Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-02-20T19:52:08.365316Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-02-20T19:52:08.365361Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-02-20T19:52:08.366280Z 0 [Warning] CA certificate ca.pem is self signed.
2020-02-20T19:52:08.366334Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-02-20T19:52:08.366445Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2020-02-20T19:52:08.366486Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2020-02-20T19:52:08.366542Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2020-02-20T19:52:08.387612Z 0 [Note] Event Scheduler: Loaded 0 events
2020-02-20T19:52:08.387899Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.29-0ubuntu0.18.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2020-02-20T19:52:09.426103Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200220 21:52:09

您的 my.cnf 變數有兩個問題：

• 首先，您可以在https://dev.mysql.com/doc/refman/5.7/en/program-variables.html中閱讀

對於採用數值的變數，該值可以用後綴 K、M 或 G（大寫或小寫）來表示乘數 1024、10242 或 10243。（例如，當用於設置 max_allowed_pa​​cket 時，後綴表示單位為千字節、兆字節或千兆字節。）

所以試試 M 而不是 MB。

• 第二：您正在嘗試為 max_allowed_pa​​cket 設置 2000 MB，最大有效值為 1 GygaByte：https ://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html

可以傳輸到 MySQL 5.7 伺服器或客戶端或從 MySQL 5.7 伺服器或客戶端傳輸的最大可能數據包為 1GB。

因此，不要嘗試 2000M，而是嘗試 1024M 或 1G。

當然，您必須在應用這些更改後重新啟動 MySQL 服務。

鑑於問題中的評論，我認為有一個字元序列導致 mysqldump 失敗。

我建議使用--hex-blob. MySQL 文件是這樣說的：

使用十六進製表示法轉儲二進制列（例如，‘abc’ 變為 0x616263）。當與二進製字元集一起使用時，受影響的數據類型是 BINARY、VARBINARY、BLOB 類型、BIT、所有空間數據類型和其他非二進制數據類型。

只需--hex-blob像這樣添加：

mysqldump -h127.0.0.1 -u user -p'password' -t db mail --hex-blob > /var/www/project/storage/web/dump2.sql

試試看 ！！！

更新 2020-02-20 15:20 EST

該表可能已損壞。您可能必須執行以下操作：

mysql> USE db
mysql> CHECK TABLE mail;

並查看 MySQL 是否報告了某些內容。

引用自：https://dba.stackexchange.com/questions/260182