Looking for a MySQL Guru

[Volt]

Anyone out there?

[mmblz]

wouldn't call myself a "guru" by far, but might be able to help depending on what it is...
PM me.

[Volt]

Originally Posted by mmblz

wouldn't call myself a "guru" by far, but might be able to help depending on what it is...
PM me.

Thanks for the offer. Here is where I am at:

To start, I am about as novice as can be with MySQL and PHP. As I try to learn, my imediate needs are in migration. I am moving from a internal housed based web app and MySQL (v4.0.17) with IIS. I want to move to a WAMP installation. The WAMP install contains the following:

WampServer 2.0i [07/11/09]
Includes : Apache 2.2.11, MySQL 5.1.36, PHP 5.3.0

WAMP is a stand alone package that installs all the above and does the basic configuring, great for a beginner.

I moved the web and the database over with the web start page working fine. I am getting a message on it though saying "access not authorized". Some research has lead me to believe I need to migrate the database instead of a drag and drop. I have read a bit on migrations but most help seems to be written for people with serious MySQL backgrounds.

Any help or recommendations in a basic way would be appreciated. Thanks in advance for any help.

[mmblz]

wow.



it sounds like the web server is trying to log in with a name/password that isn't right.
probably the new web server just uses a different name than the old one did.
can you determine what name it is trying to use?

The other possibility is that the login name is the same but the database permissions restrict where that user can connect from, and because you've moved to a different server you're coming in from a different address.

you can see the names and where they can login from, by querying the mysql database. use the database named "mysql", then "select Host,User from user" - this will show all users and where they connect from.

[mmblz]

another thought is that somehow you only moved the database with your data in it over - and the permissions, stored in the "mysql" database, just didn't make it over...

[Volt]

will do. I did read enough to know that the user tables and permissions have to be migrated as they are handled differently in the newer MySQL version. I'm pretty sure the access denied is from the mysql database as the web site stirs to hit it. I do know the root password from both versions match. I read somewhere I need to upgrade to 4.1 and then to 5.1. What a pain if true.

[mmblz]

huh - don't know much about the double upgrade possibility.

if your main database made it intact, you may just be able to re-create the permissions for the account that the web server uses...

that is, for a web application, you might not have a complicated set of permissions - you might just have a single account that has permissions on the whole database. so if you know the name/password for that account you might be able to set up that permission without bothering to migrate the whole permissions table...

[T.G]

"Looking for a MySQL Guru"



Likes/dislikes?
Your hobbies?
Location?

[Volt]

Originally Posted by mmblz

huh - don't know much about the double upgrade possibility.

if your main database made it intact, you may just be able to re-create the permissions for the account that the web server uses...

that is, for a web application, you might not have a complicated set of permissions - you might just have a single account that has permissions on the whole database. so if you know the name/password for that account you might be able to set up that permission without bothering to migrate the whole permissions table...

Got it, let me look into that.

Definitely not a guru but I had the same problem.
I have done something similar and found that drag and drop didn't work. I had to export from one and import to the other. All the databases. My system move was BSD-->Linux (LAMP) and all the apps were the same. I use PhpMyAdmin to manage the MySQL.

[mithrilG60]

You need to determine the username and password that your app is using to access the database, you'll like find that in a config file in your PHP source. Once you know the username/password that needs to be granted access run the following:

CREATE USER '<apps_db_usernname>'@'localhost' IDENTIFIED BY 'some-password';
GRANT INSERT, SELECT, DELETE ON <app_db_name>.* TO '<apps_db_usernname>'@'localhost';
FLUSH PRIVILEGES;

The user will now have basic db operational capability. If this account also needs to be able to modify the tables and/or database you'll need to grant it additional permissions like ALTER, DROP, INDEX etc. If want to give it every operational privilege on the DB (not a good choice from a security perspective!!) replace the grant line with this one"

GRANT ALL PRIVILEGES ON <app_db_name>.* TO '<apps_db_usernname>'@'localhost';

[JE3146]

Originally Posted by Volt

Thanks for the offer. Here is where I am at:

To start, I am about as novice as can be with MySQL and PHP. As I try to learn, my imediate needs are in migration. I am moving from a internal housed based web app and MySQL (v4.0.17) with IIS. I want to move to a WAMP installation. The WAMP install contains the following:

WampServer 2.0i [07/11/09]
Includes : Apache 2.2.11, MySQL 5.1.36, PHP 5.3.0

WAMP is a stand alone package that installs all the above and does the basic configuring, great for a beginner.

I moved the web and the database over with the web start page working fine. I am getting a message on it though saying "access not authorized". Some research has lead me to believe I need to migrate the database instead of a drag and drop. I have read a bit on migrations but most help seems to be written for people with serious MySQL backgrounds.

Any help or recommendations in a basic way would be appreciated. Thanks in advance for any help.

you locked yourself out of localhost...

re-install WAMP

And configure the user's properly when you get into the database.

[Volt]

Originally Posted by BC-Axeman

Definitely not a guru but I had the same problem.
I have done something similar and found that drag and drop didn't work. I had to export from one and import to the other. All the databases. My system move was BSD-->Linux (LAMP) and all the apps were the same. I use PhpMyAdmin to manage the MySQL.

Export - Import - it's on the list to try.

Originally Posted by mithrilG60

You need to determine the username and password that your app is using to access the database, you'll like find that in a config file in your PHP source. Once you know the username/password that needs to be granted access run the following:

I'll hunt around

CREATE USER '<apps_db_usernname>'@'localhost' IDENTIFIED BY 'some-password';
GRANT INSERT, SELECT, DELETE ON <app_db_name>.* TO '<apps_db_usernname>'@'localhost';
FLUSH PRIVILEGES;

The user will now have basic db operational capability. If this account also needs to be able to modify the tables and/or database you'll need to grant it additional permissions like ALTER, DROP, INDEX etc. If want to give it every operational privilege on the DB (not a good choice from a security perspective!!) replace the grant line with this one"

GRANT ALL PRIVILEGES ON <app_db_name>.* TO '<apps_db_usernname>'@'localhost';

Originally Posted by JE3146

you locked yourself out of localhost...

re-install WAMP

And configure the user's properly when you get into the database.

A bit more info. On the WAMP I have 2 other apps running. A MOODLE load and a Wordpress blog. Local host seems to be fine. As to the App in question I did a copy past from the old server to the new one. The web started right up but with the error message as noted. Both the root passwords for MySQL are the same for both systems. That's why I thought the drag and drop might work.

It is possible the App name and password (not even sure if the app has a name and password to talk with MySQL, I assume so) are different from the old to what I set up for the WAMP. I did not write the stuff I am trying to move over. As mithrilG60 noted I may have to hunt down some PHP password or export - inport as BC-Axeman has indicated.

It's either the due as several of ya'll have noted a password mismatch or as I read a version formatting issue. I appreciate all the input and will post if I get it figured out.

I'm not a windows guru either but in unix the users have a user and group ID number. Some apps use the numbers instead of the names. They could be different on the systems. This has happened to me. Windows users know if this could be the case?
Wordpress works on the database, so the database seems to be working.

Edit: I just thought, error logs! On unix systems the error logs can be very detailed and helpful. Is this so for windows?

[Volt]

Ok inport crashed with table issues. Error logs, I'll go hunting. Too much damn PHP, mysql, etc for theis server guy. Crash course learning - I love it.

Make sure you got the entire db. Sometimes there is like a 2Meg limit so you can't export or import the whole thing. This, for me I would find in the php.ini file, again I don't know what it would be in 'doze. If you used the command line (I never do) this won't matter.