Mysql – MariaDb thesqldump configurations

mariadbmy.cnfMySQL

We have extremely large database (mariaDB 10.3) and mysqldump taking 2-3 hours to take the full database dump and also causing downgrade in application performance during the dump process.

After some research, we found out that [mysqldump] section can be added in my.cnf file. also options like 'quick' , 'max_allowed_packet = 512M' can be added for it.

If we can add 'single-transaction' also along with above settings then DB will not be locked during the dump and it should speedup the application.

So are below entries are fine for my.cnf (mariaDB 19.3) ??

[mysqldump]
single-transaction
quick
max_allowed_packet = 512M

Best Answer

If your database structure is relatively static then dump it separately (once), and dump only tables data using SELECT .. INTO OUTFILE - this is more fast.

Of course you need:

  • create a script (the best way - an event procedure) which dumps all (or only changeable) tables data
  • create a script (maybe as stored procedure in structure dump) which restores the data from this dump taking into account that relational data must be restored in "one then many" order strictly
Related Question