Site Moved

This site has been moved to a new location - Bin-Blog. All new post will appear at the new location.

Bin-Blog

phpMyAdmin Tips and Tricks - Database Backup and Restore

phpMyAdmin is a big software - there are features that I have not used yet. But there are some features that are not very obvious - but could save you a lot of time.

Quick Table Browse

The normal way to seeing the contents of a table is to click on the table name in the left pane and then click on the 'Browse' tab in the top. But there is a one click way to do this - click on the small icon next to the table name in the left pane.

Click icon to browse table

Default Table Selection

When you access phpMyAdmin, you get the main page - after that you have to chose from the drop down which database should be used. Wouldn't it be useful if the database you use most would be automatically selected on loading? You can do this by accessing phpMyAdmin using the following URL.

http://localhost/phpMyAdmin/?db=my_fav_db

You will have to change the database name and the location to the one in your system - but you got the idea right?

Database Backup

There are many ways to take a database backup using phpMyAdmin - but my favorite way is to take an SQL Dump.

Select the database you want to backup and click 'Export' tab in the main frame. Now select all the tables in the select box. You can select the format of the backup - like SQL, CSV, XML etc. We will select the SQL option. It is recommended that you check the 'Add DROP TABLE' and 'Add IF NOT EXISTS' checkboxs. This will make sure that the existing tables will be removed when restoring the backup. Now check the 'Save as file' checkbox. Then click the 'Go' button. Now you should see a download option for the database backup. You can save this file to you harddisk and run it later if you want to restore the backup. If you did not check the 'Save as file' option, the SQL dump will be shown in a textarea.

This can also be done using the 'mysqldump' command in linux using the following command.

mysqldump --add-drop-table --user=root DATABASE> backup_file.sql

Table Backup

If you just need the backup of a single table, select that table from the table list in the left pane and click the 'Export' tab. Now do the steps described in the database backup section. Here only the selected table will be backuped.

This can be done using the following command.

mysqldump --add-drop-table --user=root DATABASE TABLE> backup_file.sql

Restoring a backup

Click the 'SQL' icon(SQL Icon) in the left pane - this will open a popup. Paste the SQL code you when you backuped the database in the textarea in this popup. Alternatively, you can chose the 'Import Files' tab in this popup and chose the location of the file you saved when backing up the data.

This is not a recommended method to restore large backup - if the backup file is more than 1 MB, don't try this. A better method is to upload the backup file to you host and restore the backup using the shell.

0 Comments: