Backing up your Database with phpMyAdmin

When you first log in to phpMyAdmin, if you have more than one database you will need to choose your database by either the dropdown box in the left panel or clicking Databases in the right panel as shown in the graphic below. I prefer to use the left panel as it is quicker.

However, if you have just one database the left panel will automatically open to your database and you will see a screen similar to the one below. You will need to click on Databases in order to see the list of tables in the right panel.



After clicking on Databases your screen will appear as shown in the graphic at the right. Click on the database you wish to open and you will end up with a list of tables both in your left panel and your right. The difference is that you left panel will list each of the tables with a link to open each table, while the right panel holds much more information. I use the left panel to navigate to the next table after I have finished with the table I was working on. You will want to check the right panel before doing any backup. The screenshot below shows the screen you will see after choosing your database. This screenshot and any that follow will be taken from an account with more than one database present.



Before performing a backup you will want to look for any table that has data recorded in the Overhead column. This will mean that the table needs to be optimized before performing a backup of your data. The database in question here does have two items that needs to be backed up, they appear after scrolling down the list and are shown in the screenshot below. I have circled the two tables that require optimization before the database is backed up. To optimize the table, go to the left panel and click on the first table (phpbb_sessions) is the first in this database that needs optimizing, so I clicked on it in the left panel. The full screen is too large a screenshot to post in this tutorial but click here to see a screenshot of what you will see when you click on the table in the left panel. From that page you will want to find the Space usage area which is under the structure list and beside indexes. Simply click on the Optimize table link and when it is finished optimizing your table you will see the screen as shown in the screenshot below.



Repeat the procedure for all tables that report overhead, then you are ready to back up your database. Once you have optimized all tables that require it, go to the top of your screen and click on the database name as shown in the screenshot below.



When the new screen appears click on Export as shown in the screenshot below.



You should see on your screen a page similar to the screenshot here. The settings I use to back up my databases are:

  • Under Export you will not need to make any changes, by not selecting any tables it will back up all tables in the database and sql is what you will want the extension to be.

  • Under Sql Options and Structure you will want to check off Add DROP TABLE, Add IF NOT EXISTS, and Enclose table and field names with backquotes.

  • And for you will want to check Complete Inserts and Use hexadecimal for binary fields.

  • Export type: Use Replace

  • Check Save as file

  • Name your backup, I usually use the name of my database and the date of the backup (amm-3-8-2005) without the brackets.

  • Click Go

  • You will then get the file download window. Choose where you want to save your backup and click Save. When the download is finished click close and you have your backup.

   Site Map

    Copyright © 2001-2005 Margaret Walker