How to Backup/Export Your MySql Database(s)

I will teach you two ways to backup/export your mysql database(s). The first option is using phpMyAdmin; phpMyAdmin is generally installed on most shared hosting and is bundled with cPanel. The second method is SSH; this requires SSH access (often found on VPS/Dedicated servers).

Backing Up Your Database With phpMyAdmin

  1.  Login to your phpMyAdmin. If you are using cPanel, it has its own icon on the dashboard when you login.
  2.  Click on the database you want to backup/export.
  3.  You should see a screen like this:phpmyadmin
  4. Click on the Export tab at the top.

The default options are generally good. It will save both your schema (database design) and data (database content). However, if the database is large, you probably want to choose 'Save as a File' and pick a compression type (zip/gzip). This will let you download the file and have it take less space. If it's small and you just want the code directly, don't change this.

If you want to import the data, just click the import tab (instead of export) and upload the file.

Backing Up Your Database With SSH

We are going to use mysqldump which comes with mysql.

  1.  Login to your server via SSH.
  2. Type in the following command replacing YOURUSER, YOURPASSWORD and YOURDATABASE with your mysql username, mysql password and the database you want to backup. Also change /path/to/output/file.sql to the location you want to save the backup.
mysqldump –quick –user=YOURUSERNAME –password=YOURPASSWORD YOURDATABASE < /path/to/output/file.sql

If you want to backup all your databases instead of a specific database, replace YOURDATABASE with –all-databases

Once the command finishes running, your backup is in the .sql file you specified.

If you want to automate backups of your database, there is a nifty MySql backup to Amazon's S3 file storage script here: https://github.com/woxxy/MySQL-backup-to-Amazon-S3

The following two tabs change content below.
avatar
Kevin Ohashi is the geek-in-charge at Review Signal. He is passionate about making data meaningful for consumers. Kevin is based in Washington, DC.





Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

Loading...

Interested in seeing which web hosting companies people love (and hate!)? Click here and find out how your web host stacks up.