[Solved] Getting Clean mysql uninstall or fix password file

[Anon_E_Mouse]

"Plan" is to use Base a front end for mysql database for use by my people. Was connecting and working but mysql password file got messed up after use with tool mogwaiERdesignerNG (be care of that tool) and now can't access using command line client utility. Also, Base gets same error. However, I can access using mysql desktop??? Have spent hours trying uninstalling using win uninstall, mysql uninstall that came with install package, deleting directories etc. etc. and then trying reinstall. Somehow, mysql is putting crap all over I can't get to or get cleaned/deleted so I can do a completely clean reinstall. so....
1) any suggestions on how to completely clean any trace of mysql for completely clean reinstall, or,
2) where (name of) is password file and if I delete that and reinstall will that fix it, or
3) any suggestions on fixing problem directly with password file???
Thanks.

[eremmel]

Please consider to look for assistance in a MySQL forum.

[rudolfo]

The common recommendation for this problem is to start the mysql server with the --skip-grant-tables option. If mysql is running as a windows service (xammp may be different) you need to stop that service first (see http://dev.mysql.com/doc/refman/5.1/en/ ... rvice.html on how to do this). Then open a command console and run in the installation directory of mysql:

Code: Select all

 bin\mysqld --skip-grant-tables --defaults-file=C:\my.ini

The configuration file is in most cases in the installation directory of mysql and not simply in the root of drive C:. So you have to adjust that path. You can copy it from the parameter settings of the service ... inspect this in the management console of the Windows OS (just in case: Management Console is the tool that you use to start and stop the service).

With the above console command the mysql server will run in the foreground. But that doesn't matter, just don't be irritated by this. Open another command console and connect with the mysql client terminal to the server. Use root and an empty password.

Code: Select all

 use mysql
update user set password=password('your-new-password') where user = 'root';

Disconnect from the mysql terminal and use "mysqladmin shutdown" to terminate the current database process that's still running in the other command console.
Start the mysql service from the OS service manager again ... you can now login as root with your new password.

[Anon_E_Mouse]

Rudolpho:
Many thanks your reply. Have printed out for study & will carefully follow (am tied up next few days). Thanks again. The OOo forums are really great. Appreciate it. Will follow up when checked out per your suggestions.

[Anon_E_Mouse]

Rudolfo,
many thanks - finally had time to test out your suggestions. Results? well can access now and it took me in on my old password (which it wouldn't do before). however, 'root' can't change its own password. So it's sorta fixed. My suspicion is still there's something wrong somewhere in the system but don't know all different places MySQL is putting info in various .ini files and so forth. Frustrating but is sorta working, thanks to your response. Very much appreciated.

Finally received a number of the OOo printed manuals today - same content as PDF, but much easier to read. Need to get one for the MySQL ref manual - all 3,600 pages!!! weeding through PDFs not very convenient. Am sure glad I found OOo - actually want to do the old "free, free, free at last" (from Microsoft I mean). What you all have been doing is a wonderful thing. With Sun behind it (and Oracle continuing the support of it) folks have a real choice. I was so sick of Microsoft treating me like I was a thief - never illegally stole software ever in 30 years. Anyway, many thanks.

Anon_E_Mouse

[rudolfo]

Hm, sounds a bit strange ... what you say about MySQL, not what you say about Microsoft -- that's only too familiar.
The --skip-grant-tables option tells the mysql server to by pass the privilege system in the internal mysql database. Not sure, if this means that all added users are unknown now, or if anyone can log in in this state.
The idea is to login with the mysql superuser account (root and empty password) one time and fix the mysql.user and optionally the mysql.db table manually. The following shows how the mysql.user table looks on my Debian system (lots of not so relevant columns have been left out):

Code: Select all

mysql> use mysql
mysql> select host, user, password, super_priv, select_priv from user;
+---------------------------+------------------+------------------+------------+-------------+
| host                      | user             | password         | super_priv | select_priv |
+---------------------------+------------------+------------------+------------+-------------+
| localhost                 | root             | *38E110CDBB4BC11 | Y          | Y           |
| 127.0.0.1                 | root             | *38E110CDBB4BC11 | Y          | Y           |
| localhost                 | debian-sys-maint | *BC1138E110CDBB  | Y          | Y           |
| 192.168.1.0/255.255.255.0 | music            | xxxxxxxxxxxx     | N          | N           |
| %                         | rolf             | xxxxxxxxxxx      | N          | N           |
| localhost                 | webuser          |                  | N          | N           |
| localhost                 | rolf             | xxxxxxxxxxx      | N          | N           |
| localhost                 | music            | xxxxxxxxxxxx     | N          | N           |
+---------------------------+------------------+------------------+------------+-------------+

Normally a change in this table (like the update user set password= from the above post) requires an additional flush privileges; to activate the changes. But as long as the --skip-grant-tables is active these changes will be skiped anyway. So instead you shutdown the current server process and restart it normally which will pick up the changes that you have made manually.
You can surely change the password of root this way. The only catch is without the restart of the server you won'T see it. And just to make sure that my previous post is understood in the right way: The --skip-grant-tables is not the cure for your problem, but the key to the backdoor. Once you are in, you have to clean the database and create new keys, get out again and come in normally through the frontdoor with the new set of keys.
If you do anything meaningful with your database don't run it permanantly with the --skip-grant-tables option.