I tried to change MySQL username and password:
mysql> UPDATE mysql.user SET user='myuser', password=PASSWORD('mypassword') WHERE user='root';
Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
but now mysql won't let me in and can't try anything else. :/
Messing with MySQL's system tables in the mysql schema is unwise unless you have serious experience with MySQL internals. For information, privileges are stored in several other tables in the mysql schema, which must be updated in cascade should you decide to manually edit the mysql.user table.
This is not recommended at all.
If you want to "rename" a user, you should create a new user and grant the same privileges as the old one. You can leverage the SHOW GRANTS
command. It outputs valid GRANT statements, which you can easily adapt for your new user.
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'.
GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'localhost';
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
UPDATE mysql.user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='myuser';
First of all you should create a new user if you required, instead of renaming to root user.
Further if you have any other admin user with grant privileges then you can connect by that user.
If you don't have any other admin user and not able to connect mysql by any way then you need to follow below steps to unlock root user.
Step1: add below 2 line in your my.cnf or my.ini file. skip-grant-tables skip-networking
Step2: restart mysql service.
Step2: connect mysql without password (just pass mysql command without username and password) and change user name again to root by below command.
UPDATE mysql.user SET user='root' WHERE USER='myuser'; flush privileges;
Step3: Remove newly added 2 lines from my.cnf file and restart mysql service.
Step4: Now try with below command-
mysql -uroot -p
pass password whatever you changed for myuser as we just changed username but not password at this time.
