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?

sav1sav
  • 11
  • 4

2 Answers2

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.
Rick James
  • 2,463
  • 1
  • 6
  • 13
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.

t3ln3t
  • 434
  • 2
  • 8
  • 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 04 '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 04 '21 at 22:37