• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Tips and Tricks HQ

  • Home
  • Blog
  • Projects
    • All Projects
    • Simple WP Shopping Cart
    • WP Express Checkout Plugin
    • WP Download Monitor
    • WP Security and Firewall Plugin
    • WP eStore Plugin
    • WP Affiliate Platform
    • WP eMember
    • WP Lightbox Ultimate
    • WP Photo Seller
  • Products
    • All Products
    • Checkout
  • Support
    • Support Portal
    • Customer Only Forum
    • WP eStore Documentation
    • WP Affiliate Software Documentation
    • WP eMember Documentation
  • Contact

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

You are here: Home / Web Development / How to import WordPress SQL database backup file without having ‘create new database’ privileges in phpMyAdmin

Last updated: April 27, 2013





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.

create new database no privilege
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:

  1. Login to your cPanel and click on ‘MySQL Databases’.
  2. 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).
  3. Open the sql database file that you are using for importing (e.g. ‘localhost.sql‘) in a text editor such as notepad.
  4. Search for the text “CREATE DATABASE”
  5. Delete this line containing the create database command.
  6. In the next line change the database name (tips_dbΒ in this case) to the newly created database (newDB) in step2 after the word ‘USE’.
  7. 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_db`
—
CREATE DATABASE `
tips_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `
tips_db`;

Example of the sql database file after modifying

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

Note:Β Beth pointed out the following in the comment section below which is good to remember:

You have to make sure that you do a search for "CREATE DATABASE" to delete it and make the change to the "USE tips_db" Line in ALL the places it appears.

What to do if you can’t do the above trick?

You could request your web hosting provider to add database creation privilege to your phpMyAdmin.

Also check out the following two tutorials which should be very helpful:

  • How to import/export database via PHPMyAdmin
  • How to move WordPress to a new host

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

Related Posts

  • How to fix WordPress.com Stats plugin showing zero (0) visitors!
  • Protect your WordPress site/blog from comment spam trick
  • How to Uninstall and Reinstall WordPress
  • Apache Mod Security update, How to Fix ‘Error 406’ or ‘Not Acceptable’ issue

Web Development,  Wordpress Database,  database import,  Troubleshooting,  Web Development,  web masters,  Wordpress

Reader Interactions

Comments (60 responses)

  1. Fazil Ali says:
    July 20, 2013 at 3:37 am

    Thank You So Much for your solution. Actually its was an error which i am facing first time when importing the WP tables to new database. and I have spent my time too by checking for a solution. But you saved my Time. Thank You. πŸ™‚

  2. Robert says:
    June 18, 2013 at 6:26 am

    Thank you so much for this solution – customer support and a bunch of other google searches were not helpful, as no one pointed out the need to delete the “CREATE DATABASE” line. Worked like a charm – thank you!

  3. kelskc says:
    April 27, 2013 at 8:39 pm

    THANK YOU!! I have been searching everywhere for this solution (not knowing it was this) and not only did it work you made it so easy… you are amazing!

  4. Beth olson says:
    April 9, 2013 at 8:11 pm

    @Sourabh
    It’s an easy fix!!

    I also kept getting the error: “#1044 – Access denied for user ‘[email protected]%’ to database ‘b6_8637033_wordpress’

    You have to make sure that you do a search for “CREATE DATABASE” to delete it and make the change to the “USE tips_db” Line in ALL the places it appears. I realized that I was getting that error because I was only changing that information in one place but it was actually occurring in TWO places in the code.
    hope that helps!

  5. Frederico Garza says:
    April 8, 2013 at 1:39 pm

    Thank you!!! It worked. It’s rare I see an article helping so many people for four years straight. Thanks again. -Frederico

  6. Josh says:
    March 17, 2013 at 5:47 am

    Thanks, you saved my day

  7. Arup Ghosh says:
    January 19, 2013 at 2:48 pm

    I have no idea about sql database management any this tutorial helped me to bring back my old wordpress website.

  8. Mike says:
    December 23, 2012 at 8:28 pm

    You! Are! Awesome! Wow that was easy. Thanks 100x

  9. Jinny says:
    December 7, 2012 at 4:50 am

    Thank you for this. I’ve been trying to figure this out for ages and stumbled upon your very helpful article. Managed to import successfully at last!

  10. John says:
    November 23, 2012 at 10:02 am

    Absolutely brilliantly simple and works like a dream.

    Thanks
    John

  11. Ronnie says:
    November 14, 2012 at 9:26 am

    Thanks alot…

  12. david d says:
    October 2, 2012 at 12:35 pm

    saved my ### man thanks!

  13. pulau tidung says:
    October 1, 2012 at 10:14 pm

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

  14. Navneet Singh says:
    August 2, 2012 at 2:57 pm

    Thank you so much, you help me alot to get my website back πŸ™‚

  15. Rabah Rahil says:
    May 22, 2012 at 7:34 pm

    Thank you sooo much this was exactly what I needed!

  16. beachmatt says:
    April 10, 2012 at 11:53 am

    That worked for me, thanks for that.

  17. Laura B. says:
    March 15, 2012 at 10:23 am

    Thank you so much! I had never had this error before and it happened today. All the other answers I found did not work (and didn’t make sense).

    Thank you for an easy to understand and effective solution.

  18. Eric W. says:
    February 17, 2012 at 2:27 am

    OMG Thank you thank you thank you. I was SO stuck on why this didn’t work and you saved me.

  19. admin says:
    November 19, 2011 at 9:47 pm

    @Starfall, as long as it is backing up your SQL database, it is essentially the same thing. When you backup from your cPanel, you just get more control over the backup operation.

  20. starfall says:
    November 19, 2011 at 1:54 am

    What’s the difference between having backup in cpanel database and through backend database? Sorry but im not that expert much about wordpress backend. thanks in advance.

  21. Martin says:
    October 28, 2011 at 12:40 pm

    Hi, I’ve found another solution that works too:

    1. In phpMyAdmin select your database first – you can see listing of all tables
    2. Click the Export tab and there select all tables to export, and export as usual

    Exported file then doesn’t contain any ‘create database’ queries, so you don’t need to remove these lines.

  22. Fritz says:
    October 21, 2011 at 10:21 pm

    omg thank you for this sexy article.

  23. Lowell says:
    October 4, 2011 at 3:17 pm

    Thanks a bunch for this. Awesome site BTY!

  24. admin says:
    September 12, 2011 at 11:35 pm

    @Sourabh, If you do not have ‘root’ access to the server, ask your host to do this for you.

  25. Sourabh says:
    September 12, 2011 at 1:34 pm

    thanx for info but im gettin this on importing..plz help me to fix it.

    SQL query:

    —
    — Database: ` b6_8637033_wordpress`
    —
    USE ` b6_8637033_wordpress` ;

    MySQL said:

    #1044 – Access denied for user ‘b6_8637033’@’%’ to database ‘ b6_8637033_wordpress’

  26. Doc Holiday says:
    August 7, 2011 at 3:48 am

    That’s one of the best and easy to understand tips I have seen in regards to using phpMyAdmin.

    You could be a professor πŸ™‚

  27. Technopolis Web Dsigners says:
    August 4, 2011 at 1:06 pm

    That helped me a lot. thanks man.
    I love the way you explain things, best practical article on the subject.

  28. sebastian says:
    July 16, 2011 at 4:41 am

    thanks a lot!!! tha only solution that I found that works. simple and clear πŸ˜€

  29. Ken says:
    May 23, 2011 at 3:28 pm

    Thank you! Worked like a dream!

  30. Laurie says:
    April 24, 2011 at 9:44 pm

    I have spent most of the afternoon trying to figure this out! Yours was the only solution that helped – thanks!

  31. Michelle says:
    March 22, 2011 at 1:22 am

    Thank you so much! Your a life saver. I was moving the company blog from a sub to the main level and ran into that issue. I looked everywhere and your tutorial was the only one that helped. Thanks again.

  32. Rahul says:
    January 28, 2011 at 12:43 pm

    thanks man great post ,it helped me a lot
    thank you X10000

  33. Fran says:
    January 25, 2011 at 6:38 pm

    Thanks !
    I’ve lost 2 days until I’ve stumbled upon your explanation. Great !

  34. Joel C says:
    January 12, 2011 at 8:31 am

    Freakin life saver! Cheers.

  35. JP Caparas says:
    November 22, 2010 at 12:27 pm

    I coudl give you a hug right now, man! I love you!

  36. Will Quick says:
    September 29, 2010 at 8:56 am

    Oh my God, I LOVE you! Thank you SO SO much! I’ve been struggling with this for hours. I’m trying to move a clients WordPress from one hosting account to another and these error messages were driving me INSANE. You have just saved my life and my sanity.

    Honestly, I can’t thank you enough, I’m absolutely overjoyed, haha!

    Thanks again,

    Will

  37. Casey says:
    September 20, 2010 at 1:43 pm

    Wow. That just SAVED ME. Really appreciate it. I was calling friends, begging the internets to help me.

  38. Sara says:
    August 4, 2010 at 4:35 pm

    You Rock! Thank you!

  39. Gordon says:
    June 10, 2010 at 12:38 am

    Thank you so much for putting this info up,so very grateful.

  40. Nagashiko says:
    May 26, 2010 at 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

  41. search engine optimization seo services says:
    December 15, 2009 at 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!

  42. Nedbenj says:
    October 28, 2009 at 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.

  43. Watashiii says:
    June 3, 2009 at 8:57 am

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

  44. admin says:
    May 31, 2009 at 1:08 pm

    @Roady, what charset are you using?

  45. Roady says:
    May 29, 2009 at 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

  46. admin says:
    April 21, 2009 at 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.

  47. abbie says:
    April 21, 2009 at 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..

  48. Brendon says:
    January 9, 2009 at 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!

  49. admin says:
    January 5, 2009 at 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.

  50. Gina Alessia says:
    January 5, 2009 at 8:23 pm

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

Newer Comments »

Leave a Reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Featured & Popular Articles

Video Answers to Top WordPress QuestionsWordPress Optimization Tips and Tricks for Better Performance and SpeedEssential WordPress Security Tips - Is Your Blog Protected?WordPress Simple PayPal Shopping Cart PluginTop 15 Search Engine Optimization (SEO) Techniques I Forget to DoList of the Best and Must Use WordPress PluginsHow do I Start a Blog and Make Money Online?Good Domain Name Picking Tips for Your Blog SetupFind Out Which WordPress Web Hosting Company Offers the Cheapest and Reliable Web Hosting Solution

Featured WordPress Plugins

wordpress estore plugin
wordpress membership plugin
WP Express Checkout Plugin
WordPress Lightbox Ultimate Plugin
WordPress photo seller plugin
wordpress affiliate plugin

Recent Posts

  • Accept Donations via PayPal from Your WordPress Site Easil [...]
  • Buy Now Button Graphics for eCommerce Websites [...]
  • Subscription Button Graphics for eCommerce Websites [...]
  • Adding PayPal Payment Buttons to Your WordPress Sidebar Ea [...]
  • PayPal QR Codes [...]

Comment & Socialize

  • @Rodrigo Souza, Thank you f ...
    - admin
  • The example for 'slm_add_ed ...
    - Rodrigo Souza
  • @Ron, All the valid transac ...
    - admin
  • Hello, when people have sel ...
    - Ron
  • We have hte following featu ...
    - admin

Check out our solutions

View our WordPress plugin collection and start using them on your site.

Our WordPress Solutions

Footer

Company

  • About
  • Privacy Policy
  • Terms and Conditions
  • Affiliate Login

Top WordPress Plugins

  • Simple Shopping Cart
  • PayPal Donations
  • WP Express Checkout
  • WP eStore
  • WP eMember

Blogging Tips

  • How to Start a Blog
  • Selecting a Good Domain
  • Cheap WP Hosting
  • WP Video Tutorials
  • Simple SEO Tips

Search


Keep In Touch

Copyright © 2023 | Tips and Tricks HQ