{%Pragmatic Coding%} Tips and tricks for software developers.


How To BackUp and Restore MySql Database

Hello dear reader!
I will give you simple ways how to restore and backup you MySql database with Command line and PHPMyAdmin.

1. Command Line. Backup Database.

Why we may want to back up or restore database from command line?
It is because PHPMyAdmin can't handle large databases. In that case straight mysql code and command line will help.

From a command line, you can backup your entire database using next steps:

Step 1. Change your directory to the directory you want to dump your DB to:

Step 2. Use mysqldump comand:

Backup with compression

The bzip2 -c after the pipe | means that the backup is compressed on the fly.
Backup without compression

2. Command Line. Restore Database.

Let's look on simple restore from sql file.

This will only work if the database does not already exist.

If you want to restore from archive, first you need unarchive your database dump, and second, import it into your Mysql database.
Let's tool how we can unarchive dump. Assuming your backup is a .bz2 file, creating using instructions similar to those given for Backing up your database using Mysql commands, the following step will guide you through restoring your database.

First of all unzip your .bz2 file:

Note: If your database backup was a .tar.gz called database_name.bak.sql.tar.gz file, then,

is the command that should be used instead of the above.
Than import it into your Mysql database.

3. PHPMyAdmin. Backup Database.

It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.

Do next simple steps to afford database backup

  • Login to phpMyAdmin
  • Click on your database name
  • Click Export in the Menu to get to where you can backup you MySql database. Image showing the export menu.
  • Select all tables you want to backup (usually all)
  • Default settings usually work, just make sure SQL is checked
  • Check the "Save as file", do not change the file name, use compression if you want.
  • Then click "GO" to download the backup file.

phpMyAdmin Export

phpMyAdmin Export database

4. PHPMyAdmin. Restore Database.

To restore database make next steps

  • Login to phpMyAdmin
  • Click on the tab labeled IMPORT
  • Check "Partial import" to prevent time limit error and set number of records to skip from last import.
  • Choose your backup file
  • Press GO to start importing.

Or you can import via SQL tab by inserting SQL Stript into window.

That's it!

Hope this article was helpful for you my friend!

Share on social network

Share to Facebook
Share to Google Plus
Share to LiveJournal
Share to MyWorld
Share to Odnoklassniki
Share to Yandex
Tagged as: mysql Leave a comment
Comments (0) Trackbacks (0)

No comments yet.

Leave a comment

No trackbacks yet.