MySQL Password Problem

[Ric_]

The scenario... I was updating one of my Linux boxes (Debian Etch) and something broke - Apache/Perl/PHP/something and everything I tried seemed to do nothing or make things worse.

 

Anyway... I hadn't rebuilt this server for a while so I backed up the databases (in a sloppy manner using mysqldump without any switches :oops: ).

 

I rebuilt the server and quickly dumped the sql data back into the freshly installed MySQL forgetting about certain key authentication data held within the database.

 

Anyway, it transpires that there is an account called debian-sys-main that I believe resides in MySQL and I need to change the password for this so that MySQL will start properly (at the moment I have to make it run without authentication :? ).

 

Any ideas? Please help! I've tried alsorts!

[Ric_]

^ Nudgey nudge nudge

[Geoff]

Change the password for the debian-sys-maint mysql user (phpmyadmin is easiest) and then put the new password (in plaintext) in the /etc/mysql/debian.cnf file.

[Ric_]

Change the password for the debian-sys-maint mysql user (phpmyadmin is easiest) and then put the new password (in plaintext) in the /etc/mysql/debian.cnf file.

 

I tried changing the password using the command prompt (i.e. directly updating the DB) but it did not find the user. Adding the user didn't work either... I guess that this is because the DB is started useing the command

 

/usr/bin/mysqld_safe --skip-grant-tables &

 

I know that this is bad practice but I had to get it running.

 

BTW... I don't have phpmyadmin installed.

[Geoff]

Ubuntu 6.06 LTS here (not that it matters).

 

root@goliath:~# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = EO07Dx3bfeigAMMQ
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = EO07Dx3bfeigAMMQ
socket   = /var/run/mysqld/mysqld.sock

and

root@goliath:~# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70336 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select User,Password from mysql.user where User = 'debian-sys-maint';
+------------------+------------------+
| User             | Password         |
+------------------+------------------+
| debian-sys-maint | 5e705119345bc519 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

 

and finally

root@goliath:~# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70354 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants for 'debian-sys-maint'@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for debian-sys-maint@localhost                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD '5e705119345bc519' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> quit
Bye