联系：QQ(5163721)
标题：MYSQL小白的FAQ系列—-2—-几种常见情况下需要修改mysql的root口令的处理
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
一般常见的，两种情况下必须修改mysql的root口令。
一种是刚安装完，系统提示：ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
修改方法就如提示所说，使用set password命令，例如：set password=password(“lunar”);
[root@lunar mysql5.6]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-enterprise-commercial-advanced
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password("lunar");
Query OK, 0 rows affected (0.19 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.05 sec)
mysql>
另一种是，忘记了mysql的root口令，需要启动到单用户模式来修改：
停止mysql服务：
[root@lunar ~]# /etc/init.d/mysql stop Shutting down MySQL....[ OK ] 或者 Bye [root@lunar mysql5.6]# service mysql stop Shutting down MySQL..[ OK ] [root@lunar mysql5.6]#
安全模式启动mysql：
[root@lunar ~]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & [1] 4153 [root@lunar ~]# 140803 15:12:22 mysqld_safe Logging to '/var/lib/mysql/lunar.err'. 140803 15:12:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 这里 --skip-grant-tables会跳过mysql的授权 mysql的启动日志记录在/var/lib/mysql/lunar.err 也可以简单的使用：mysqld_safe --skip-grant-tables & [root@lunar mysql5.6]# mysqld_safe --skip-grant-tables & [1] 5200 [root@lunar mysql5.6]# 140803 16:24:16 mysqld_safe Logging to '/var/lib/mysql/lunar.err'. 140803 16:24:16 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql [root@lunar mysql5.6]#
然后，进入mysql数据库：
[root@lunar ~]# 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 Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 或者简单的使用：mysql -p [root@lunar mysql5.6]# mysql -p Enter password: --注意，这里不用输入任何口令，直接回车就可以了 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
现在，可以使用update修改root口令为:lunarbaobao
mysql> UPDATE user SET Password=PASSWORD('lunarbaobao') where USER='root' and host='root' or host='localhost';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)
mysql> exit
Bye
如果要修改所有数据库的root口令，那么只需要where user='root'就可以了，不用指定host，即：
mysql> update mysql.user set password=password("lunarbaobao") where user='root';
Query OK, 4 rows affected (0.05 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from mysql.user;
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+
| localhost | root | *8D4165021FB50C23292ACA2C4478CB9688E08607 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | N |
| lunar | root | *8D4165021FB50C23292ACA2C4478CB9688E08607 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | Y |
| 127.0.0.1 | root | *8D4165021FB50C23292ACA2C4478CB9688E08607 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | Y |
| ::1 | root | *8D4165021FB50C23292ACA2C4478CB9688E08607 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | | Y |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+------------------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)
mysql>
mysql缺省的本地主机是localhost，对应的IP是127.0.0.1。
可以看到，当前的mysql认到的4个主机标识都修改了。
接下来，重启mysql服务：
[root@lunar ~]# /etc/init.d/mysql restart Shutting down MySQL..140803 15:13:35 mysqld_safe mysqld from pid file /var/lib/mysql/lunar.pid ended [ OK ] Starting MySQL.[ OK ] [1]+ Done mysqld_safe --user=mysql --skip-grant-tables --skip-networking [root@lunar ~]# 或者 [root@lunar mysql5.6]# service mysql stop Shutting down MySQL..140803 16:33:00 mysqld_safe mysqld from pid file /var/lib/mysql/lunar.pid ended [ OK ] [1]+ Done mysqld_safe --skip-grant-tables [root@lunar mysql5.6]# service mysql start Starting MySQL.[ OK ] [root@lunar mysql5.6]# 也可以尝试kill 所有mysql的进程： ps -ef |grep mysql|xargs kill -9 再次登录试试看： [root@lunar mysql5.6]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>
平时如果需要，也可以使用update user命令来修改用户口令，例如：
update user set password=password(“lunar”) where user=’root’;
flush privileges;
