Help migrating mysql database

[daveed]

Hi,

I have been running mysql and perl on w2k3 server to manage our projects. Recently we decided to convert to xampplite so that we could develop in internal web page with Word Press.

I've been fighting with this for weeks. It seems that when I can access the project database, I am locked out of phpmyadmin and I cannot access the database I have created for Word Press.

When I can access phpmyadmin and the Word Press database, I cannot access the project database and receive the following error:

Catalyst/TT Error
An error has occurred. We're terribly sorry about that, but it's one of those things that happens from time to time.

Here's the error message, on the off-chance that it means something to you:
Cannot connect: Access denied for user 'dots_user'@'localhost' (using password: YES)

Thanks in advance for you help!

[ifokkema]

Moving databases is all nice, but the MySQL accounts are also in that database. You'll need to recreate the accounts used by PhpMyAdmin and your Wordpress installation in each other's database.

So apparently, you used to use 'dots_user'@'localhost' but this account has not been created in the database you're using now.

[daveed]

Moving databases is all nice, but the MySQL accounts are also in that database. You'll need to recreate the accounts used by PhpMyAdmin and your Wordpress installation in each other's database.

So apparently, you used to use 'dots_user'@'localhost' but this account has not been created in the database you're using now.

I have created this account and granted permissions.

In order to get the dots-eser to work, i have to copy over from the initial install the files db.myi and db.myd, but once these files are copied into the new install directory I can no longer get into php

[ifokkema]

I have created this account and granted permissions.

Did you also flush the privileges?

In order to get the dots-eser to work, i have to copy over from the initial install the files db.myi and db.myd, but once these files are copied into the new install directory I can no longer get into php

No, you need to manually create this user. The user data is in the database called mysql, and the settings are spread over multiple tables. The actual username and password are in the user table in this database. Copying the db table does not work. But if you just manually recreate the account, and just move over the databases that you need and don't mess with the 'mysql' database or it's tables, it'll be fine.

[daveed]

Did you also flush the privileges?

No, I have not and I'm not sure I know how to...

[ifokkema]

If you manually created the account by using the grant statement, you'll need to flush the privileges to be able to use the account. Do so by entering:

flush privileges;

in the mysql console.

[daveed]

OK, I have just gone through these steps:


Start servers and phpMyAdmin:

Start Apache Server by double clicking on Server_Start.bat
From apanel start MySQL server click on Run Mysql link
From apanel start phpMyAdmin click on phpMyAdmin link
Create databases:

Enter new database name into Create new database field and click on Create
Click on Home takes you back to the home page.
Repeat steps 4 and 5 for each new database that requires creating

Create user accounts:

Click on Home (Start page)
Click on Privileges link this opens User overview page.
Click on Add a new User link this opens Add a new User page


Work down this page and fill in the details as follows:
Enter user name (make sure use text field is selected in drop down list)
Host: from the drop down list select Any Host
Password: Enter password (make sure use text field is selected in drop down list)
Re-type: Enter the password again
Make sure all global privileges are UnChecked
Click on Go this creates the user account and displays the user page


Scroll down the user page to Database-specific privileges
Click on the drop down list and select a database to assign to the user.
(Alternatively enter the database name and click on go)
In either case opens the user edit privileges page for that database


In the edit privileges check the following:
Data: SELECT, INSERT, UPDATE and DELETE
Structure: CREATE, ALTER, INDEX and DROP
Administration: Leave all UnChecked
Click Go a confirmation page is displayed
Repeat steps 7 to 13 for each new user.
Refresh the MySQL grant tables:

At the bottom of the User overview page (home > privileges) you will see the following statement:

"phpMyAdmin gets the users' privileges directly from MySQL's privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should reload the privileges before you continue."

Click on reload the privileges link contained in the above statement this executes the query

SQL query:
FLUSH PRIVILEGES ;

Instructing the MySQL server to take another look at the user tables and hence puts all of your new users and privileges into operation.

I then restarted Apache and mysql and I still get this error:

Catalyst/TT Error
An error has occurred. We're terribly sorry about that, but it's one of those things that happens from time to time.

Here's the error message, on the off-chance that it means something to you:
Cannot connect: Access denied for user 'dots_user'@'localhost' (using password: YES)

[ifokkema]

Aargh... what happened to the plain old dirty console work?
Just try through the console:

mysql -u dots_user -p

Then enter the password. Didn't work? The above instructions suck or you missed something there. Did work?

Use databasename;
(Whatever database you need to have access to)

Doesn't work? Then make sure the user has privileges on the database you created!

[daveed]

Thanks for explaining it to me like I'm a 5-year old : )

OK, from the command prompt I navigated to the mysql\bin directory. Once there, I typed

mysql -u dots_user -p

then an enter (I don't need a password right now)

then I typed

use dots; (dots is the name of the databse)

It said:

Database changed

Then i went to to a web browser and typed

http://localhost/dots/projects (this is a table in the database)

And I still get

Cannot connect: Access denied for user 'dots_user'@'localhost' (using password: YES)

[Wiedmann]

mysql -u dots_user -p
then an enter (I don't need a password right now)

Cannot connect: Access denied for user 'dots_user'@'localhost' (using password: YES)

You see the difference?
--> Your script is configured to use a password. But there is no password for that user.

[daveed]

You see the difference?
--> Your script is configured to use a password. But there is no password for that user.

I do now. Thanks for aiming the spotlight on the problem for me.

I just got in. I wound up deleting the dots_user, then recreating it with the required password (sorry to say I did this through phpmyadmin rather than teh mysql console : (). Works like a champ now.

Thanks everyone for you help and, more importantly, your patience!