Mysql Permission screwup

[pratikmsinha]

Through the whm, i revoked all the permission of the root user on mysql and now I cannot connect to the database .. how do i get out of this mess now? http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html => I tried that, it works for a normal mysql server on the laptop, but doesn't work on the cpanel server .. any clues?

 

[chirpy]

Have you tried:

/scripts/fixmysql

 

[pratikmsinha]

[email protected] [/scripts]# ./fixmysql
[email protected] [/scripts]#
[email protected] [/scripts]#
[email protected] [/scripts]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[email protected] [/scripts]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I ran the fixmysql script, however the root still doesn't allow me to login. How do i set the mysql server to an empty password - rather no password?

regards,
Pratik

 

[pratikmsinha]

I tried to restart the mysql server through whm and I get the following error:

Restarting MySQL

Main >> Restart Services >> SQL Server (MySQL)
Attempting to restart mysql
Waiting for mysql to restart.... . . . . . . . . . . finished.

mysql status

root 27886 0.1 0.1 2680 1088 ? S 08:00 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid
mysql 27907 1.5 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27908 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27909 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27910 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27911 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27912 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27913 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27914 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27915 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking
mysql 27916 0.0 1.1 32160 11700 ? S 08:00 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/cpanel.aradasystems.com.pid --skip-locking

/usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' mysql has failed, please contact the sysadmin (result was "mysql has failed").

 

[freedog96150]

I am assuming that you have tried the following from the WHM interface:

MySql Root Password
Reset Local MySql Root Password
Additional MySql Access Hosts

These usally let you completely reset the MySql password without knowing the existing password. Make sure that you add your additional hosts. Since you are most likely trying to access databases from behind a dynamic address on your ISP you may have to add the proper wildcard to your login such as [email protected]%

Hope this helps.

 

[pratikmsinha]

Before I saw your post, this is what I tried.

http://faq.cpanel.net/show.cgi?qa=104700616405907

[email protected] [/scripts]# /etc/init.d/mysql stop; /etc/init.d/chkservd stop; mysqld --skip-grant-tables -u mysql &
Shutting down MySQL... [ OK ]
Stopping chkservd: [ OK ]
[1] 31600
[email protected] [/scripts]# 061125 9:08:15 [Warning] Asked for 196608 thread stack, but got 126976
061125 9:08:15 InnoDB: Started; log sequence number 0 43912
mysqld: ready for connections.
Version: '4.1.18-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)

[email protected] [/scripts]# mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.18-standard

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

mysql> UPDATE user SET Password=PASSWORD('') WHERE user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> FLUSH PRIVILEGES;
061125 9:08:48 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[email protected] [/scripts]# killall -9 mysqld
[1]+ Killed mysqld --skip-grant-tables -u mysql
[email protected] [/scripts]# /etc/init.d/mysql start
Starting MySQL [ OK ]
[email protected] [/scripts]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

So there's this warning message when I do FULSH PRIVILEGES,
061125 9:08:48 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run
Query OK, 0 rows affected (0.00 sec)

Will running the mysql_fix_privilege_tables after resetting the password help?

thanks for all the help