A solution for forgetting or losing the root password of MySQL
-bash-4.2$ service mysql status
SUCCESS! MySQL running (2670)
-bash-4.2$ mysql-u root-p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Here is the solution to password forgetting:
Modify the parameter settings for MySQL:
# vi / etc/my.cnf
Add a sentence to the last line of the paragraph [mysqld]: skip-grant-tables
For example:
[mysqld]
....
Log-bin=mysql-bin
Innodb_data_file_path = ibdata1:12M;ibdata2:100M;ibdata3:300M:autoextend
Innodb_file_per_table=on
Max_connections=500
Skip-grant-tables-- add new lines
Save and exit vi.
Restart the MySQL service:
[root@chenfeng subsys] # service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
-bash-4.2$ mysql-- login directly without entering a password
Welcome to the MariaDB monitor. Commands end with; or\ g.
Your MySQL connection id is 2
Server version: 5.6.15-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.
MySQL [(none)] > show databases
+-+
| | Database |
+-+
| | information_schema |
| | chenfeng |
| | mysql |
| | performance_schema |
| | test |
+-+
5 rows in set (0.00 sec)
MySQL [(none)] > use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
MySQL [mysql] > UPDATE user SET Password = password ('12345678') WHERE User = 'root';-- changed to the new password 12345678
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MySQL [mysql] >
MySQL [mysql] > flush privileges;-- refresh the permission table
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql] > quit
Bye
Change the parameter settings of MySQL back:
# vi / etc/my.cnf
Delete the skip-grant-tables you just added in the last line of the paragraph in [mysqld], and then save the exit.
Restart the MySQL service:
[root@chenfeng subsys] # service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
Log in again with the new password:
-bash-4.2$ mysql-u root-p12345678-- 12345678 is the new password
Welcome to the MariaDB monitor. Commands end with; or\ g.
Your MySQL connection id is 7
Server version: 5.6.15-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.
MySQL [(none)] > use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
MySQL [mysql] > show databases
+-+
| | Database |
+-+
| | information_schema |
| | chenfeng |
| | mysql |
| | performance_schema |
| | test |
+-+
5 rows in set (0.00 sec)
MySQL [mysql] >
