Importing Databases and Tables with PhpMyAdmin

Reading Time: 4 minutes

Using PhpMyAdmin to import databases can help us in a few different situations, like when moving a database from one server to another, updating a table that is being developed elsewhere, or even restoring content from a backup. PhpMyAdmin helps make all those processes easy to accomplish through our browser.

This guide assumes that you have already logged in to PhpMyAdmin. We will go over what happens if we try to import into an existing database, importing a single table, and also partially restoring from a full database backup.

First, let’s try to import a database into an existing database that still has data in it.

Click on the database name we want to import to from the left-hand navigation. In this case, the lwtest_wpdb is a copy of a WordPress database we took from a live site, and we will try to restore a backup we saved locally in an SQL format.

06-01-phpMyAdmin_Main_Page

Next, we will go to the Import tab on that page.

06-02-Go_to_Import_Tab

Then, we will click on the Choose File button and locate the saved file we have saved locally. It can be an uncompressed or compressed file with the zip, gzip, or bzip2 extension. The import page has other options, but we will just leave them at the default settings, and then click the Go button at the bottom of the page.

06-03-Choose_Your_File

As we can see, PhpMyAdmin uploads the file, but then gives the error:
#1050 – Table ‘wp_comment_meta’ already exists

06-04-Import_Error

Since this database is still storing data, we can quickly drop all the existing tables so it can be restored to that backup sql file. 

We click on the Structure tab to view the existing tables, and next tick the “Check all” box near the bottom. Then, from the dropdown box, we will choose the “Drop” option to completely remove all tables from this database.

06-05-Select_and_Drop

PhpMyAdmin will have us confirm these tables should all be dropped as all data in them will be lost for good

Note:
If you are not 100% sure that all data should be removed, I would recommend taking a backup of the database first or make sure any recent backups taken are good.

So, let’s proceed and click Yes to drop all tables from the database.

06-06-Confirm_Deletion

This brings us back to the Structure tab and shows that no tables exist. Now, it will no longer have any conflicts with the restore, so we can go back to the Import tab and retry the upload again.

06-07-Empty_Database

We begin by selecting the file and then clicking on the Go button at the bottom of the window.

06-08-Select_File_and_Import_Again

Depending on how large the database being restored is, it could take a few minutes to upload and a few more for all the tables to be populated with data. Once this completes, we should see the “Import has been successfully finished” message as well as see the tables in the navigation menu on the left side.

06-09-Data_Imported

Now, to restore a single table, we can click on the table name from the left navigation to select it and then go to the Import tab there. The Import page looks, feels and functions the same as the database Import, but it says “Importing into the table” at the top instead of “Importing into the database”. When we choose a file, it needs to be a file that only contains the data from a single table, and again, the table should be empty or it will cause another error.

06-10-Table_Level_Import

If we have a copy of a full database but only want to restore a single table or even just a few records, then we can go to the home page in PhpMyAdmin. Next, we will click on the Databases tab, and it will list the current databases, but also give us the option to create a new database. In this case, we will rename it to the same name as our original database, but add “_copy” to the end of it. Adding a date to the name also might be a good idea as that would show when it was created. After the new database is created, we can navigate into it and then import the full database there.

06-11-Create_New_Database

Next, to copy a table into the original database, we will want to empty the existing table. Then, we will go back into the original table and under the Structure tab, we can select Empty for the table we will be copying over. The Empty option will keep the table and its structure, but remove all the records. If the table being copied over has a different structure, then we should use the Drop command on the whole table instead, so the imported table can recreate the structure as well.

06-12-Empty_Table

Next, we will see a popup asking us to confirm that the data should be removed. Click OK.

06-13-Confirm_Truncate

Now, we go back to the imported database, under the Structure tab, check the box for the table we want to copy over and then choose the “Copy table” option from the dropdown list near the bottom.

06-14-Check_Box_Copy_Table

A new box will pop up and ask us what database the table should be copied into. Select our original database (lwtest_wpdb) and then for the Options, select “Data only” if the table was emptied, or “Structure and data” if the table was dropped. Click on the Continue button and it will copy over the table data.

06-15-Data_Only

After that, it will run for a bit, depending on the amount of data there is. It will not provide a confirmation if the import succeeds. We will need to navigate back to the original database and visually confirm that the table data was copied back over.

06-16-The_Records_Are_There

Conclusion

PhpMyAdmin can help with Importing databases and tables through your browser. It makes it easy to deploy a database with a file you have saved locally without having to log in to a terminal or use any commands. 

Series Navigation<< Previous ArticleNext Article >>

Author Bio

About the Author: Alex Gorzen

Alex Gorzen has been helping others with technology his whole life. He played with computers even before he could read and wants to make sure his children share that same love as they grow up. In his free time he enjoys gardening, building things, and learning new skills.

Refer a friend and get a $50 hosting credit!