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

I try it on ubuntu 18.04, mysql 5.7.29.

And the file is always 141.8MB. Data just aborted always at the same point.

It is innodb table. There is also longtext field. 18 rows, table size is 132MiB.

Tried also with --opt - same result(

No errors, only output is:

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

max_allowed_packet in my.cnf:

max_allowed_packet         = 2000MB

For mysqldump is also set 2000MB

[mysqldump]

max_allowed_packet         = 2000MB

Simple variant without --where gives the same result

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

Verbose output:

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

Last 50 lines from error.log

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
  • Are you getting any errors? How is the export not as you expected? Note that you are using a where clause on the export, so it won't dump everything – Philᵀᴹ Feb 20 at 18:54
  • @Philᵀᴹ, but it must and it does export, but I have data aborted in sql file somewhere in middle. So it produces not valid, broken sql – Mikael Feb 20 at 18:56
  • 1
    Tried this on Windows, with my local db, and the --where is only working when putting backquotes arround field name like: --where "```status```=3", the doublequotes here are probably just a Windows-thing... (hmmz: single backquoot around status 😁, how to put a backquoot in a comment on SE ?) – Luuk Feb 20 at 19:15
  • @Luuk, just tried, same thing, broken sql with same size – Mikael Feb 20 at 19:18
  • try adding --skip-extended-insert – Luuk Feb 20 at 19:19
1

You have two problems on your my.cnf variables:

For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

So try M instead of MB.

The largest possible packet that can be transmitted to or from a MySQL 5.7 server or client is 1GB.

so instead of 2000M try 1024M or 1G.

Of course, you must restart the MySQL service after applying these changes.

| improve this answer | |
  • Thank u very much for useful info! I was hoping but did not help(( now max_allowed_packet = 1G, mysql I have restarted. The same, file is broken and same 141.8MB(( – Mikael Feb 20 at 19:53
  • Can you confirm what you see when SHOW VARIABLES LIKE 'max_allowed_packet'; – Jesus Uzcanga Feb 20 at 19:56
  • max_allowed_packet is 1073741824 – Mikael Feb 20 at 19:57
  • Can you post the last 50 lines of your error log? To locate: SHOW VARIABLES LIKE 'log_error'; – Jesus Uzcanga Feb 20 at 20:00
1

Given the comments in the question, I think there is a character sequence causing mysqldump to fail.

I would recommend using --hex-blob. MySQL Documentation says this:

Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263). The affected data types are BINARY, VARBINARY, BLOB types, BIT, all spatial data types, and other non-binary data types when used with the binary character set.

Just add --hex-blob like this:

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

GIVE IT A TRY !!!

UPDATE 2020-02-20 15:20 EST

The table might be corrupt. You may have to execute this:

mysql> USE db
mysql> CHECK TABLE mail;

and see if MySQL reports something back.

| improve this answer | |
  • I am very thankful for your help, but I am honest, same result, same file size(( tried: mysqldump -h 127.0.0.1 -u user -p'password' db mail --max-allowed-packet=1G --verbose --hex-blob --result-file='/var/www/project/storage/web/dump2.sql' and the last characters where it is broken is: i9QQYr6wUOYwNYnsQk/TmF8i8nJxf31R2YIA/m1kJmPN it is base64 in long text – Mikael Feb 20 at 20:14
  • It is not a character problem. Because now I left only one big row and it imported well. Before I had 10 clones of same big row and then it broken with file of same size – Mikael Feb 20 at 20:23
  • Please execute CHECK TABLE mail; What does it say ??? – RolandoMySQLDBA Feb 20 at 20:25
  • it says OK. But now I added few more clones of big row, now file is 40MB and it is aborted.. – Mikael Feb 20 at 20:29
  • I just deleted 2 big rows (1 row size is about 10MB) and now in works properly.... – Mikael Feb 20 at 20:32
0

I was totally wrong (( I opened files in phpstorm, and phpstorm shows file not completely, it looks like broken! I just tried another editor and the file is ok! Forgive me everybody please! ((

| improve this answer | |
  • 1
    Well, at least you fixed your max_allowed_packet variable – Jesus Uzcanga Feb 20 at 21:28
  • Lower max_allowed_packet and try again. 2GB could choke may editors. – Rick James Feb 26 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.