0

I have a 12GB database. I want to take a compressed backup using the export option via phpmyadmin.

Does this cause any performance impact or locking on the database?

2
  • 1
    This sounds like an excessive size to do manually on a web interface. Are all your tables (that are being modified) innodb?
    – danblack
    Commented Nov 9, 2021 at 10:11
  • What do you mean by innodb?
    – variable
    Commented Nov 9, 2021 at 10:31

2 Answers 2

0

This is not advisable.

Since phpMyAdmin is a web application, it is affected by resource limits that are imposed by the webserver (things like memory use or execution time, things that could bog down a server and cause it to stop responding, are limited so that the web server can recover from a long-running or memory-intensive process). The phpMyAdmin application does make some efforts to work around those limits, but for a 12GB database I would not recommend even trying it. Further, when you compress an export in phpMyAdmin, it usually consumes extra memory (while the exported data is queued to be compressed), and again phpMyAdmin tries to work around that but there are better solutions in place. For that matter, some web browsers can behave unpredictably when downloading such a large file; I'm sorry to say it but the entire toolchain for doing this would be fragile at best.

The best way to handle this is to use the mysqldump utility that's included with MySQL, you can usually pipe the output directly to xz, gzip, zip, or your favorite other compression utility.

To answer your question, phpMyAdmin has a checkbox to select whether to lock the tables. The mysqldump utility does lock tables during export, but if your tables are of the InnoDB type, you can follow this advice to use the --single-transaction and --quick flags to improve that process.

Actually, my understanding is that --quick is always enabled by default (as part of --opt, but quoting directly from the MySQL manual: "To dump large tables, combine the --single-transaction option with the --quick option."

0

Turn it around -- First review backup options, then see if phpmyadmin can invoke the desired technique (compression, non-locking, etc). (It probably cannot.)

Probably the two least invasive backups are:

  • (no downtime on Primary) Have replication set up; do the backup on the Replica (with compression, etc).
  • (very brief downtown) Have LVM set up on the server; take a snapshot (probably under a minute of downtime, regardless of size); backup the snapshot (optionally with compression, etc).
4
  • Using LVM to do a file-level backup of a MySQL database is probably not ideal because of consistency issues. It should work most of the time, but is not recommended by MySQL and can (potentially) lead to data loss. LVM is great, but I don't think it's appropriate as a database backup solution. Also, phpMyAdmin can handle compressing and locking an export, I'm not sure why you don't think it can? They're both options on the export page.
    – ibennetch
    Commented Nov 9, 2021 at 15:08
  • @ibennetch - By stopping mysqld for the brief time to take the snapshot, consistency is guaranteed.
    – Rick James
    Commented Nov 9, 2021 at 16:52
  • Compression costs CPU time, hence performance. Any "export" form of backup has locking issues; Xtrabackup perhaps has the least locking.
    – Rick James
    Commented Nov 9, 2021 at 16:54
  • Ah, yes, in that case I quite agree that stopping mysqld is a way to assure consistency for the snapshot; can I suggest that you edit your answer to explicitly include that mysqld should be stopped to create the snapshot (which I also agree should be very quick), but in my opinion isn't very clearly stated.
    – ibennetch
    Commented Nov 10, 2021 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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