Score:0

What is the best & safe & fastes way to backup a large mysql database?

it flag

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?

Score:2
ua flag

(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.
Score:1
in flag

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.

djdomi avatar
za flag
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
ua flag
@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.
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.