How to import WordPress SQL database backup file without having 'create new database' privileges in phpMyAdmin

How to import WordPress SQL database backup file without having ‘create new database’ privileges in phpMyAdmin

Categories: Troubleshooting

In this article I have explained how you can import your WordPress SQL database backup file even when you do not have the ‘create new database’ privilege in phpMyAdmin. This is particularly useful when you are trying to import the SQL database as part of transferring your site from one host (old-host.com) to another host (new-host.com) and your new host doesn’t have the ‘Create new database’ privilege in phpMyAdmin.

In your phpMyAdmin page check for the ‘create new database’ privilege. If the privilege settings looks like the example shown below then your database import following the normal method is going to fail as it will try to create a database and you don’t have permission to do that.

No privilege to create new database

You will get the following error when trying to import the database file:

#1044 – Access denied for user ‘username’@'localhost’ to database ‘database_name’

To get around this problem and to complete importing your database to your new host do the following:

Login to your cPanel and click on ‘MySQL Databases’.
Create a new database with your preferred name (I am going to use the name ‘newDB‘ for easy reference. You should have privilege to create a new database through this MySQL Database panel even though you don’t have privilege to create new database through phpMyAdmin).
Open the sql database file that you are using for importing (e.g. ‘localhost.sql‘) in a text editor such as notepad.
Search for the text “CREATE DATABASE”
Delete this line containing the create database command.
In the next line change the database name (tips_tricks in this case) to the newly created database (newDB) in step2 after the word ‘USE’.
Now use the import wizard from phpMyAdmin to import the database and it will use the already created database to import all the tables without any problem.

Example of the sql database file before modifying

–
– Database: `tips_tricks`
–
CREATE DATABASE `tips_tricks` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `tips_tricks`;

Example of the sql database file after modifying

CREATE DATABASE `applewoo_applewood` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `newDB`;

Alternatively, you could request your web hosting provider to add database creation privilege to your phpMyAdmin.

Hope this will help anyone having a similar problem. Feel free to share your thoughts by leaving comments in the comment area below.

Tags: database import, Troubleshooting, Web development, web masters, Wordpress

Similar posts that you may like

#1 by Gina Alessia on January 5, 2009 - 8:23 pm

Thank you, thank you, thank you. Just what i needed, best explanation possible.

#2 by admin on January 5, 2009 - 8:48 pm

@Gina I am glad that the explanation solved your database import issue. I was terrified when I was getting errors trying to import my wordpress database from my old hosting to the new hosting using phpMyAdmin.

#3 by Brendon on January 9, 2009 - 5:40 am

Exactly what I was looking for. I was transferring my blog and I got the error when importing the SQL database. Glad to have found this post!

#4 by abbie on April 21, 2009 - 9:42 am

Hi Admin,
Thanks for sharing this info.

So.. we have to make a new database. What if I want to import a downloaded sql from an old blog to an existing new blog that has had posts and comments already, so that the (different) posts and comments in the old blog inserted into the new blog?

Any suggest would be highly appreciated. Thx..

#5 by admin on April 21, 2009 - 10:10 pm

@abbie, this should be easy.. just use the database of the existing blog and run the SQL commands using phpMyAdmin to add the data of the old blog to this new one.

So…
1. Back up the SQL file of your old blog using phpMyAdmin.
2. open the recently downloaded backup SQL file and change the line where it says create database to use the database name of the new blog.
3. Execute the content of the SQL file in the phpMyAdmin of your new blog so it inserts all the entries to this database.

#6 by Roady on May 29, 2009 - 5:59 pm

I took the backup of my database of wordpress though the cpanel -> backup > mysql . When I open it, The text is not in english and in some other weird language. Any thing I can do with this file to get my database back up?

Thanks

#7 by admin on May 31, 2009 - 1:08 pm

@Roady, what charset are you using?

#8 by Watashiii on June 3, 2009 - 8:57 am

Thank you thank you! I love you and I want to have your babies

#9 by Nedbenj on October 28, 2009 - 4:38 pm

You’re a genius .

Thanks a lot for this handy tip, I spent hours trying to get this to work before seeing your post.
Nedbenj´s last blog ..Ban the Bag!

#10 by search engine optimization seo services on December 15, 2009 - 5:41 pm

This is just what I needed to know. So glad I found your blog because I was ready to pull my hair out!
search engine optimization seo services´s last blog ..October Was Breast Cancer Awareness Month: Raising Breast Cancer Awareness in a Hospital

#11 by best wordpress plugin on May 15, 2010 - 1:52 pm

Good post. But, im still confusing about this! Maybe i must try this again. Thanks!
best wordpress plugin´s last blog ..mike brown fired

#12 by Nagashiko on May 26, 2010 - 5:32 pm

This post is very helpful for people like me who doesn’t know that much about IT stuff. The instructions were easy to follow! Thanks so much for the info! =D
Nagashiko´s last blog ..Try to guess what my excuse would be…

#13 by Gordon on June 10, 2010 - 12:38 am