Forum Moderators: open
My system:
======================================
MySQL 4.1.20
phpMyAdmin 2.7.0-pl2
Linux 2.6.11-1.1369_FC4smp
======================================
I have tried to restart mysqld with skip-grant-tables but alas, you cannot GRANT ALL PRIVILIGES for 'root'@'localhost' when this is done.
I have also tried running mysql_fix_privilege_tables (with and without skip-grant-tables) and that didn't fix it either.
I did have root account accessible to the FQDN but it still does not let me restore priviliges back to root@localhost.
So my questions:
1. Is there any scripts or other mysql commands I can use in order to restore rights?
2. If a reinstall is needed:
a. How do I save my databases from being wiped
b. What db/table do I need to delete in order for the reinstall to give me root@localhost rights again.
How to Reset the Root Password [dev.mysql.com]
======================================
# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 4.1.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
======================================
... and without --skip-grant-tables:
======================================
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
======================================
Now obviously, with skip-grant-tables, you cannot change privileges for root:
======================================
# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql>
======================================
so flush privileges and try again;
======================================
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret';
Query OK, 0 rows affected (0.01 sec)
mysql>
======================================
But when I do that, anything that uses the root (including phpMyAdmin) does not connect.
My init file read like:
======================================
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'shinynewpasswd';
=====================================
My commands in shell:
======================================
shell> kill `cat /var/run/mysqld/mysqld.pid`
shell> mysqld_safe --skip-kill-mysqld --init-file=/root/mysql-init &
======================================
I kept getting the following error in my ye old phpMyAdmin:
Client does not support authentication protocol requested by server; consider upgrading MySQL client
When I looked that up, it refered me to setting OLD_PASSWORD:
======================================
shell> kill `cat /var/run/mysqld/mysqld.pid`
shell> mysqld_safe --skip-kill-mysqld --init-file=/root/mysql-init &
======================================
So I set it:
======================================
# mysql -u root -pshinynewpasswd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.1.20
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SET PASSWORD FOR
-> 'root'@'localhost' = OLD_PASSWORD('shinynewpasswd');
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
======================================
... and I can now do root stuffs in mysql.
Thanks.
