d8888888b    888 .d8888b.888888888888888888888Y88b   d88P  .d8888b. 8888b
       d888888888b   888d88P  Y88b   888    888        Y88b d88P  d88P  Y88b88888b
      d88P88888888b  888Y88b.        888    888         Y88o88P   888    888888?88b
     d88P 888888Y88b 888 "Y888b.     888    8888888      Y888P    888       888 ?88b
    d88P  888888 Y88b888    "Y88b.   888    888           888     888       888  ?8bb
   d88P   888888  Y88888      "888   888    888           888     888    888888   ?8bb
  d8888888888888   Y8888Y88b  d88P   888    888           888  d8bY88b  d88P8888888888b
 d88P     888888    Y888 "Y8888P"    888    8888888888    888  Y8P "Y8888P" 888     ?88b
Articles

Cheat Sheet: Dump or Restore a MySQL or MariaDB Database

Posted Nov 8th, 2013

There's many ways to backup a MySQL database.

My preferred method is the tried-and-true mysqldump tool, piping the output through some form of archiver. Originally I used gzip and sometimes bzip2, and finally now I just use xz for everything.

Here's some quick one-liners suitable for copy-paste to make things quick and painless.

mysqldump -cqQ --hex-blob -u[user] -p [database]

With gzip

mysqldump {options} | gz > dump.sql.gz
gunzip < dump.sql.gz | mysql {options}

With bzip2

mysqldump {options} | bzip2 > dump.sql.bz2
bunzip2 < dump.sql.bz2 | mysql {options}

With xz

mysqldump {options} | xz > dump.sql.xz
xzcat < dump.sql.xz | mysql {options}

You can of course pass options to the archiver, like -9 for maximum compression (just be careful if you do this with xz as it can use a lot of memory!)


Home - Geocaching - PHP Scripts - Articles - Music - Programming - Hot Sauce Review
About This Site - Site Search - About Me - Link Directory - Contact Me

This site was constructed entirely by hand { more info }
Modified Monday, January 5th 2015 UTC

(C) Copyright 2000-2017 Marty Anstey ~~ I didn't rip you off, so don't rip me off.