====== Backing up and Restoring a MySQL database ======

===== Backing up a MySQL database =====

To backup a MySQL database, do the following.

==== Using Virtualmin ====

  - Sign in to Virtualmin (see [[Logins]]).
  - Select a domain from the drop-down in the left-hand navigation (your main domain should be already selected when you first login to Virtualmin).
  - Click **Edit Databases**. You should now see a list of your current databases on the //Edit Databases// page.
  - Select the MySQL database you want to back up from the left-hand column under //Database name// and click **Manage..**.
  - Click **Backup Database**. You should now see the //Backup Database// page.
  - For **Backup to file**, enter a location on your account where you want the data base to be saved and a suitable filename (e.g., /users/home/yourusername/dbbackup). Clicking **...** will allow you to browse to a suitable location on your account.
  - Select whether you want all tables to be saved, or just a few specific ones. If you want to back up everything, then just leave **All tables** selected
  - For **Add drop table statements to backup?**, leave it as **No** unless you need a drop table statment
  - Leave **Character set for backup** as the default
  - Leave **Backup compatability format** as the default
  - And for **Compression format** decide whether you want to compress your backup to save space.
  - Now, click **Backup Now**. You should now see a page confirming a successful backup and your database should be saved in your desired location

And that's it, you've just made a backup of your MySQL database!


==== Using the Shell ====

  - cd to the directory where you wish to store the data.
  - Enter: <code>mysqldump --compress --user=USERNAME -p --opt --lock-tables=false --skip-add-locks --skip-extended-insert DATABASE > DATABASE.sql</code>
  <note tip>Replace the values in CAPS with the specific values for your account.  You can also change the character set, if you wish.  The file can be compressed for faster transfer with: <code>gzip DATABASE.sql</code></note>

===== Restoring up a MySQL database =====

To backup a MySQL database, do the following.

==== Using Virtualmin ====

  - Sign in to Virtualmin (see [[Logins]]).
  - Select a domain from the drop-down in the left-hand navigation (your main domain should be already selected when you first login to Virtualmin).
  - Click **Edit Databases**. You should now see a list of your current databases on the //Edit Databases// page.
  - Select the MySQL database you want to back up from the left-hand column under //Database name// and click **Manage..**.
  - Click **Execute SQL**. You should now see the //Execute SQL// page.
  - Near the bottom of the page either enter a location on your account where you want the data base to be restored from for **From local file** (clicking **...** will allow you to browse and select the file) //or// select **From uploaded file** and click **Choose File** to select a file from your local machine.
  - For **File format**, leave it as **Tab separated** 
  - Now, click **Execute**. You should now see the //Import Data// page confirming a successful restoration and your MySQL database.

And that's it, you've just restored your MySQL database!


==== Using the Shell ====

  - cd to the directory where your sql data is stored.
  - Enter: <code>mysql --user=USERNAME --password=PASSWORD DATABASE < DATABASE.sql</code>
  <note tip>If you have the file gzip'ed, gunzip it first, or change the command above to:  <code>zcat DATABASE.sql.gz | mysql --user=USERNAME --password=PASSWORD DATABASE</code></note>