MyDumper uses database.table.sql(.gz|.zst) (according to https://github.com/mydumper/mydumper/blob/master/docs/files.rst#table-data). Let's use what users might already know? I mean, database.table.txt, not .sql(.gz|.zst) part

serg, could you be more precise to "use what users already know". Is this about naming? Is this about 100% compatible output?

1. yes, mydumper uses database.table.sql to store the data, and database.table-schema.sql for DDL.
mariadb-dump --tab uses table.sql to store the DDL, and table.txt to store the data, in tab-separated form

To me it means, we can't possibly make the names 100% compatible, unless we want to change the logic and give a tab-separated values file the extension .sql.

2. If the idea is to store files with "database." prefix, and everything else as proposed, I have no strong objections, although I think directory tree will be clearer, better than potentially thousands files in a flat directory.

3. If the idea is not just to use the same naming schema, AND the very same logic as mydumper/myloader (i.e generate INSERTs rather than "SELECT INTO OUTFILE"), it is quite a different thing and a large change,

No, not 100% compatible. I mentioned that I don't think we should use .sql(.gz|.zst) filename suffix, I'd prefer we use .txt.

Also, mydumper uses database.table-schema.sql(.gz|.zst) for what they call "table schema", I suspect it's a very bad name for table definition and we definitely should not use that. "Schema" is something very different. Your db/table.sql or db.table.sql naming is much better.

#3 might be useful, I thought about it, we might want it some day, but it's a different task, definitely not part of this one.

So, above I only meant #2, that is, just the naming. I don't have a strong preference either way, so I checked what other tools do.

pandi.gurusamy, do you have an opinion? you've requested this feature, after all. If dumping many databases at once, should files named db/table.txt (that is, one directory per database) or db.table.txt (potentially thousands files in a flat directory) ?

it's a good idea to have db/table.txt and db/table.sql, while restoring mariadb-import should automatically restore the tables into the correct databases when there are hundreds of database.

Subtask MDEV-33627 is in progress

Thanks for improving the classic export/import utility. Would it be possible to add support for --hex-blob with --tab/--parallel/--dir to these enhancements? Would be great to be able to do speed up binary exports/imports as well. Right now it's silently ignored. We mainly use InnoDB so would be great if it could be used together with --single-transaction.

--single-transaction can be emulated with a global read lock, I suppose.

--hex-blob is only defined for SQL dumps. And --tab uses SELECT ... INTO OUTFILE. This needs to be implemented in the server. And for LOAD DATA too.

--single-transaction works out of the box with --parallel

Thanks for the updated information. I have no idea how complex it would be, to add support for --hex-blob. I knew it wasn't just one line of code!
I have just looked at performance charts for mydumper and other alternatives, and it's quite massive compared to plain old SQL-dumps.

MDEV-34832 points out that AUTO_INCREMENT attributes had better be added after loading data.

I'm unsure that AUTO_INCREMENT is better to add after loading data. Why so? When we load data, we define all fields. We do not calculate autoincrement. If there is still overhead, even when autoinc field is defined, it needs to be solved by storage engines.