Backup and Restore of MySql database with phpMyAdmin
|stuff you never thought you wanted to know.
How to backup your Mysql database with phpMyAdmin
Written by Jon Berg <jon.berg|a|turtlemeat.com>
It is very important to do backup of your MySql database, you will probably realize it when it is too late.
A lot of web applications use MySql for storing the content. This can be blogs, and a lot of other things. When you have all your content as html files on your web server it is very easy to keep them safe from crashes, you just have a copy of them on your own PC and then upload them again after the web server is restored after the crash. All the content in the MySql database must also be backed up. A lot of web service providers say they do backup of all the files, but you should never blindly trust them. If you have spent a lot of time making the content and it is only stored in the Mysql server, you will feel very bad if it gets lost for ever. Backing it up once every month or so makes sure you never loose too much of your work in case of a server crash, and it will make you sleep better at night. It is easy and fast, so there is no reason for not doing it.
Backup of Mysql database
It is assumed that you have phpMyAdmin installed since a lot of web service providers use it.
0. Open phpMyAdmin.
1. Click Export in the Menu to get to where you can backup you MySql database. Image showing the export menu.
2. Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.
Select"SQL"-> for output format, Check "Structure" and "Add AUTO_INCREMENT" value. Check "Enclose table and field name
with backquotes". Check "DATA", check use "hexadecimal for binary field". Export type set to "INSERT".
4. Check "Save as file", do not change the file name, use compression if you want. Then click "GO" to download the backup file.
Image showing step 2-4.
Restoring a backup of a MySql database
1. To restore a database, you click the SQL tab.
2. On the "SQL"-page , unclick the show query here again.
3. Browse to your backup of the database.
4. Click Go.
Image showing step 1-4.
phpMyAdmin has some file size limits so if you have large databases it may no be possible to backup using phpMyAdmin. Then you have to use the command line tools that comes with Mysql. Please note that this method is untested.
Mysql backup without phpMyAdmin
PHPMyAdmin can't handle large databases. In that case straight mysql code will help.
1. Change your directory to the directory you want to dump things to:
user@linux:~> cd files/blog
2. Use mysqldump (man mysqldump is available):
user@linux:~/files/blog> mysqldump --add-drop-table -h mysqlhostserver
-u mysqlusername -p databasename (tablename tablename tablename) | bzip2
-c > blog.bak.sql.bz2
Enter password: (enter your mysql password)
mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | bzip2 -c > blog.bak.sql.bz2
Enter password: my-password
The bzip2 -c after the pipe | means the backup is compressed on the fly.
Mysql restore without phpMyAdmin
The restore process consists of unarchiving your archived database dump, and importing it into your Mysql database.
Assuming your backup is a .bz2 file, creating using instructions similar to those given for Backing up your database using Mysql commands, the following steps will guide you through restoring your database :
1. Unzip your .bz2 file:
user@linux:~/files/blog> bzip2 -d blog.bak.sql.bz2
Note: If your database backup was a .tar.gz called blog.bak.sql.tar.gz file, then,
tar zxvf blog.bak.sql.tar.gz
is the command that should be used instead of the above.
2. Put the backed-up sql back into mysql:
user@linux:~/files/blog> mysql -h mysqlhostserver -u mysqlusername
-p databasename < blog.bak.sql
Enter password: (enter your mysql password)
Setup Software Raid 1 with LVM
Setup Linux with Desktop
Manage your website ads with DFP
Google AdSense for Domains - not so great
Let Google Handle Email for your Domain Name
Page Rank banned by Google
Google's highest ranked web pages
SEO intelligent spam causes irrelevant search results
Google ranking factors
How to not give out page rank in web page links
Amazon Cloudfront versus Shared Hosting
Web Server Programming
Simple Java web server
Simple Python web server
Configuring Apache webserver with .htaccess file
Turn off the loginscreen in XP, after installing .NET .
Turn off xp login screen unread mail count
What is .NET
Introduction to Cascading style sheets (CSS)
The value of Alexa traffic rank
HTML META tag, not a search engine optimization tool
Create a maintainable webpage with modularization
The www prefix in your domain name
What is RSS and cool things RSS can be used for
MySql backup and restore with phpMyAdmin
Mix Computer related text
Doing business (making money) with Information Technology
Business with Computer Science
Research in Computer Science
Current and future possibilities of Medical Informatics
Tasks that make sense to have automated
Programming handheld mobile devices (overview)
Security tips for the web surfer
Price and Capacity in computer hardware
Java RMI Tutorial.
Page numbering in Word
Numbering headers or outlines in Word
Create a List of Figures
Turn off the default collapsing menus in Word
Turtlmeat.com 2004-2011 ©