[MDEV-33188] Enhance mariadb-dump and mariadb-import capabilities similar to MyDumper Created: 2024-01-05  Updated: 2024-01-23

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: 11.5

Type: New Feature Priority: Critical
Reporter: Pandikrishnan Gurusamy Assignee: Vladislav Vaintroub
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-28395 LOAD DATA transfer plugins Open

 Description   

Enhance mariadb-dump features similar to MyDumper

Backup

  • Increase the parallelism, MDEV-32216 using --tab option we can able to backup only one database at a time.
  • In mariadb-dump --tab, the binlog positions are not stored in a separate file. It is displayed in stdout
  • The big tables were split into multiple data files which will help faster restore.
    • this should be done when bulk-load in multiple connection becomes faster

Other features that could be added to mariadb-dump that would make it more useful:

  • Compression of the dump (especially with --tab). This also implies that we would implement de-compression of the files ion the server (MDEV-28395).
  • Dump tables not alphabetically, but in inverse size order (biggest tables first). This will help ensuring that the user does not have to wait for the big tables to finish at the end of the backup (in most cases). See benchmarks in MDEV-32216.

Restore:

  • Using mariadb-import can restore only one database at a time and the user need to restore the table structure and data files separately. We should enhance mariadb-import with an option to re-create the table structure first and then start the data import.
  • In case of customers having 100+ databases mariadb-dump (with --tab) and mariadb-import are not user-friendly. (Not hard to fix)
  • Have mariadb-import in parallel mode start importing the tables in inverse size order (biggest tables first). This will help ensuring that the user does not have to wait for the big tables to finish at the end of the backup (in most cases).

We need to improve the functionality of mariadb-dump and mariadb-import to allow for the backup and restoration of multiple databases using a single command.

Description of the intended design

  • allow multiple databases with a new switch --dir (suggestions for a better name welcome, as I got no good idea at the moment)
    With this set, mariadb-dump creates a directory tree structure in the given path, <path>/dbname/ for each db, and <tablename>.txt (tab-separated data, created using SELECT INTO OUTFILE) and tablename.sql with DDL . for all tables under the corresponding db directory.
    The files are almost exactly the same as before with --tab, except for directory tree
  • mariadb-import also takes new --dir parameter, that points to directory tree created by dump, executes all *.sql files to create tables (and databases if not exist), loads the data using "LOAD DATA INFILE" for the .txt files

Big tables won't be split into small tables now, and I do not think they should be in the future. If required, threading should be handled transparently by LOAD DATA INFILE, i.e by the server, rather than loading single table from 2 different connections. LOAD DATA is our officiaL bulk-loading interface, and we better improve it in the server, rather than building workarounds.

 Comments   
Comment by Sergei Golubchik [ 2024-01-12 ]

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

Comment by Vladislav Vaintroub [ 2024-01-13 ]

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,

Comment by Sergei Golubchik [ 2024-01-13 ]

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) ?

Comment by Pandikrishnan Gurusamy [ 2024-01-15 ]

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.

Generated at Thu Feb 08 10:37:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.