DISCLAIMER: Unless you know exactly what you are doing, please back up your database in multiple ways before attempting this. I am not responsible for any lost data if you remove or otherwise change your database without it being backed up properly. See also: What to do if your WordPress site does go down.
If you're like me, you have been very frustrated trying to import and export Wordpress Databases using the phpmyadmin panel supplied by Godaddy. The method I use to accomplish this would also apply to any other hosting method utilizing phpmyadmin.
We've all been there. You thought, "Hmmmm.... I'll just use this nifty little 'export' button. No problem!" But then when you went to import it, you are faced with this error:
Would you like some help with your WordPress Website? Fill out the form and we'll take a look.
Error
SQL query: -- -- Database: `information_schema` -- CREATE DATABASE `information_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; MySQL said: #1044 - Access denied for user 'dbname'@'%' to database 'information_schema'
Or possibly something like "#1050 - Table 'wp_commentmeta' already exists" Or even that the database already exists.
I'm not sure the technical details of these errors or their causes. I'm no expert with mysql or phpmyadmin. It would seem that when you export the database, it puts commands in there that make it create new tables and the database itself when you import it. Which would be fine if you were importing into a blank database, but you aren't usually! You are importing into an existing Wordpress database.
But over the years I have learned methods that work every time for backing up and restoring databases.
Now you may have tried a couple other things in the past that didn't work so well. Here are a couple of them:
Using Godaddy'd built-in database backup and restore system. This is actually a legitimate way to do this, but it has one huge drawback: time. When you are viewing your list of all your databases in your hosting control panel, next to the database you want to backup, you can click 'actions' -> 'Back Up'. A backup of the whole database with all the correct formatting and everything will be created. And for no reason at all, this will take several hours. Once that's ready, you use the same method to restore the database. Just click 'actions' -> 'Restore' and then select the backup you just created. Then, again, for no reason at all, wait several hours for it to do its thing. If you plan on transferring the Wordpress database to another host, it will be a little more involved. You have to locate the file in a backups folder located in the root of the hosting account. And it's still going to take forever.
So, what is the best way to do it, you ask?
The Solution - Importing and Exporting a WordPress Database
There are actually two main ways to first export the database properly: using PHPMyAdmin, or a WordPress plugin. Either way, the import will require PHPMyAdmin so I recommend you use that method so you're familiar with it.
To backup the database so that it can be restored later easily, using PHPMyAdmin:
(Note: instructions could vary with different versions of hosting, or if you are using a different host. They should still work though!)
- Log in to your phpmyadmin console for the database you want to back up.
- Find where to export the database. In Godaddy's phpmyadmin, it's a tab at the top labeled "Export." Click that.
- Near the top left of the export area, select only the name of your database, unselect "information_schema."
- Just stick with the default settings. Now, this is important. Make sure "save as a file" is checked.
- Click "Go" in the bottom right to complete the export.
- The database export file will now download. Congrats, you did it!
To backup the WordPress database using a plugin:
You can use any one of a huge variety of plugins for this, but we'll be using BackupWordPress.
BackupWordPress has a very easy-to-use interface.
- Install the BackupWordPress plugin on your WordPress website.
- Navigate to tools -> Backups.
- Under "Database Daily", click "Run Now" and wait for that to complete.
- Click the "Download" link to the right of the most recent database backup.
That's it! You have a good backup of your database. But now, what if you want to RESTORE that database at some point?
Restoring the Wordpress Database from the file we created:
- Log in to your phpmyadmin console for the database you want to restore. It doesn't matter if there is an existing database there.
- Go to where you are viewing the tables for your database. In Godaddy, just look on the left, and click the name of your database. It should be right below or above "information_schema."
- Check all the checkboxes for the tables. If there is a "Check All" button just use that. If this a brand new database with nothing in it, there won't be anything to check. Skip to step 6.
- Click the drop-down menu that is defaulted to "With selected:". Select "Drop." Click "Yes" when it asks you if you really want to do that. Wait for it to complete.
- Click the "Import" tab.
- Click "Choose File" and navigate to your backup file. Then click "Go" on the lower right.
That should be it! It should import successfully, resulting in a database that is the same as it used to be! Easy stuff. Let me know if you have any trouble!
I also have a solution available to you if you are looking to simply redirect some pages in Godaddy hosting instead.
4 Comments on “How to Import and Export a WordPress Database with Godaddy Properly: Tutorial”
Good one Brian, maybe you can review our plugin with feedback too?
https://wordpress.org/plugins/export-database-littlebizzy/
Thanks for considering…
Oh, how I wish I saw this before I tried other tutorials earlier today. After updating my locally-hosted WordPress site and plugins (in preparation to move to GoDaddy), my site went down. I backed up using two methods: a .zip export in myphpadmin and a file export through WordPress. I’ve tinkered too much in myphpadmin with the databases and crashed the site. Whenever I try uploading the .sql file, I get errors. I even tried moving forward by importing the .sql backup to the new GoDaddy site but get a 1044 user denied error. I’m stuck with a crashed wordpress website! Any insight?
BACK UP THE CURRENT NONWORKING DATABASE BEFORE YOU TRY THIS
In phpmyadmin, select your wordpress database (not information schema). Then click “Check All” and do with selected: DROP. You want to drop everything. Then import your .sql file, and you should be good to go!
Pingback: How to create MySQL cPanel on Phpmyadmin - Page 2