exporting / importing database error: Access denied for user 'user_name'@'localhost' to database 'database_name'

[Godfrey]

I'm trying to follow some steps from other threads about migrating a site from a local server to a live server.

I've uploaded my site files from my local directory to my live server's directory, and I've created an .sql export file via phpmyadmin on my local server.

However, when I try to import this into my live server's DB (also phpmyadmin), I get this error:

 

Error

SQL query:

--
-- Database: `sitename_pw`
--
CREATE DATABASE IF NOT EXISTS `sitename_pw` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
 

MySQL said: 

#1044 - Access denied for user 'my_user_name'@'localhost' to database 'sitename_pw'

I've tried editing the credential settings in the config.php file in the site/ folder, but a few things are confusing me:

1. does editing this file have anything to do with this error?
2. if I edit this file, what should I put as the database name, etc., since the db hasn't even been created yet?
3. (When I first opened the file, I couldn't find the db credentials at the bottom. It just said "Installer config data appears below", then nothing. That's weird that it just disappeared... I ended up copy and pasting the lines of code for credentials from another config file )

Other than the config.php file, what else can I do to remedy this error? Another catch with my setup is that with my host's cPanel/ phpmyadmin setup, I have to create mysql users and databases through a wizard. All the user and databases are prefixed with myname_, and I have to manually add users to databases through the wizard.... 

[arjen]

I always export the database tables and not the entire database. So in phpmyadmin click on the database on the left to select the database. Then click on export, select all the tables and download the file. Go to your live server, create a user and a database. Select the database and import the tables. 

If that's succesful you edit the config.php with the credentials of the live server.

[Godfrey]

Ah, thanks for the tip arjen! In the end this technique worked smoothly!

At first it this technique of exporting and importing the tables didn't work either, and gave me the exact same error. I think that the compiling of the tables during export still somehow stores the local name of the database, so during import it tries to create a new database with that name. But how my phpmyadmin/mysql is setup won't allow users to access tables they aren't "added" to,  so there's no way for the user to add in tables to a non-existent table. 

However... what I had to do was to: 

1. Move files from the local server folder to the live server 
2. Create a new database on the server.
3. Rename the local database exactly the same as the newly created database on the live server, then
4. add the user, which I log into phpmyadmin with, to that new live database.
5. Export from the local then import to the live.
6. Change the config.php settings only on the live server. 

This way the table import technique worked! Hope this might help others also using this funky cPanel / phpMyAdmin / MySql setup of mine. 

Now I just have to setup my dual config php files and I think fix the broken page links, as mentioned in some other posts...

[arjen]

Glad you got it working. Make sure you don't upload the config-dev.php to live. 

[sambadave]

Hi guys

I had the exact same problem and as a complete novice to MySQL and phpMyAdmin I was struggling to figure out what had gone wrong.

Gnuey I followed your steps first as I seemed to understand the process a little easier and you had the same problem as me. It fixed everything, big thanks for the explanation.

Arjen, in future would you recommend your way of doing things? It was a bit annoying having to rename my local database settings just to get it to work and this seems like a good way to tackle the issue... only now that I've got mine working I'm too scared to mess with it again and try your suggeston haha!

[arjen]

I always use the config-dev.php option. This way you can set-up your config once and just import the tables.

[mikeuk]

I realise an old thread, but just had the issue then realised i'd made a basic mistake. Hadn't granted all user permissions for new db. After that was fine. I didn't need to do anything else. This might help some others.

[FrancisChung]

I have some automated scripts that dump the whole database into a file and uploads that to a dbserver and db of choice.
I use this to do my deployments for testing and going live.

I normally don't touch config.php unless there's been a version upgrade of PW.

Let me know if you're interested and I can post it here.

[pwired]

Hi Francis, yes I am interested in your automated scripts, please post. Thanks in advance.

[szabesz]

5 hours ago, FrancisChung said:

Let me know if you're interested and I can post it here.

Hi, We are still interested  Thank you in advance!

 

[FrancisChung]

@szabesz @pwired : Sorry for the late response. Been very busy with a deadline that inevitably dragged on and on ...

For those who are interested, I've got the following for automating my deployment.
By all means, it's not perfect nor is it complete but it does the job with a minimal investment of my time. 

I have structured my DB scripts into 2 categories.  The dump_<xxx> scripts and the restore_<yyy2zzz> scripts.
The dump scripts are to dump the DB contents as a whole where <xxx> signifies an identifier to the DB you're dumping (e.g. Dev, UAT, Live etc).

So my dump_local.sh script looks like
 

#!/bin/bash
_now=$(date +"%Y%m%d")
_file="../dumps/Dump$_now-LOCAL.sql"
echo "Starting Dump to $_file..."
/Applications/MAMP/Library/bin/mysqldump -h localhost -P 8889 -u root -p'PasswordHereWithTheQuotes'   --single-transaction [DBNAME] "$_file"
echo "Dump to $_file finished"

Key things to note here is :

1) 1st line of code generates a datestamp

2) It then creates a path of the dump file in the dump folder

3) Then it calls MySQLDump using the predefined parameters. Obviously this is for my local installation, so for a server dump file you'll want to change the dump suffix (from LOCAL to DEV,UAT,LIVE or whatever), the hostname, user, password, port (which will be different to 8889 most likely. Mine is 3306 on our service provider) and DBName.

If you;re wondering what the single transaction flag is ...
 

Quote

--single-transaction

This is the least invasive way to do a dump of your data. However it only works if all your tables are InnoDB. Keep in mind it won't error out if you have MyISAM tables, but your backup will be inconsistent. The plus side is that from your applications perspective there is really no blocking locks happening. Very cool indeed!

2

 

The restore files are similar except for my scripts, I've chosen to do a specific one for each XXX2YYY variation. Not the most efficient, but I'm not a bash script ninja either. XXX being Source, YYY being destination in case your'e wondering.

So my restore_local2dev.sh looks like :

 

#!/bin/bash
_now=$(date +"%Y%m%d")
_file="../Dumps/Dump$_now-LOCAL.sql"
echo "Starting restoring from $_file to dev ..."
/Applications/MAMP/Library/bin/mysql -h [HostDBServerAddress] -P [HostDBServerPort] -u [HostDBUserLogin] -p'[HostDBUserPwdInSingleQuotes]' [HostDBName] < "$_file"
echo "Restoring from $_file to dev finished"

 

Notes :

1) I'm using mysql instead of mysqldump here

2) I'm assuming you already created a dump today of the DB file you're uploading (line 2 & 3)

3) There's a bit more involved here with the parameters but I hope the long descriptive labels are self explanatory. Feel free to contact me on this forum if you're unsure.

By all means these should cover the basics that you need. Things to improve upon are obviously in the security side of things. There's -ssl options and SSL certificate options that I haven't looked in at detail. If anyone is using them and willing to give us a quick primer ....
 

[FrancisChung]

Quote

--single-transaction

This is the least invasive way to do a dump of your data. However it only works if all your tables are InnoDB. Keep in mind it won't error out if you have MyISAM tables, but your backup will be inconsistent. The plus side is that from your applications perspective there is really no blocking locks happening. Very cool indeed!

2

Just realised most of PW tables are MyISAM so the -single-transaction flag is kinda useless  absolutely futureproof.

[pwired]

Thanks for coming back on this Francis. Code like this is really necessary. Backing up websites and databases takes time. Yes I know this can be done with services provided from your hoster and this works all fine. But I dont want to put my bets on just the hoster. I dont know about you guys but considering the countless hours of work I want second backups on my portable hdd. At night I want my computer to make those second backups with code or scripts.