0

I have a mysql server running on a Debian server. I have a mysql database wich is 150GB... What is the best way to take a backup of it?

I'll say: screen then mysqldump -u user -p database > database.sql?

Any suggestions?

2

(Approximately in order, least-invasive to most-invasive.

  • Backup the Replica -- IF you already have such. Not fast, but has no impact on the Primary.
  • LVM Snapshot -- This almost instantly makes a "copy" of the entire disk. This, also, requires setup.
  • Other forms of disk copy. (But you need to be careful about what gets copied.)
  • Percona has a better backup.
  • mysqldump from another server and write the output on that extra server.
  • mysqldump, writing to same server, is near the bottom of the list.
1

The absolute best way is to create a replica and then backup the replica. This way won't lock your DB tables when mysqldump cycles through the tables of your DB.

There are many, many tutorials online that can help guide you through the process of setting up a read-only replica of your DB. If you use something like Commvault or NetBackup, they have commercial DB backup modules that can do really neat stuff, but I'm still a big fan of setting up a replica just to handle backups. Make sure you have enough disk space for your DB dump and perhaps one more copy while you compress.

2
  • i agree with both, but moreover i would suggest that you should compress in the same way since ist 100 percent text and it could become great compress ratios
    – djdomi
    Nov 4 '21 at 17:21
  • @djdomi - If there is lots of TEXT, the resulting dump will compress about 3x, hence speeding up the write side of thedump. However, it may slow down the dump due to the extra CPU effort needed.
    – Rick James
    Nov 4 '21 at 22:37

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.