How to import WordPress SQL database backup file without having ‘create new database’ privileges in phpMyAdmin
Categories: Web Development, Wordpress
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.
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_db 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_db`
CREATE DATABASE `tips_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Example of the sql database file after modifying
CREATE DATABASE `applewoo_applewood` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
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:
Hope this will help anyone having a similar problem. Feel free to share your thoughts by leaving comments in the comment area below.