MySQL改密

一、未登录mysql：

 1、用初始默认密码

grep password /var/log/mysqld.log  

2、更改密码

mysqladmin -uroot -p'd-tlbwIgP3e2' password  "QianFeng@123"
                   默认密码/老密码                新密码

二、已登录数据库里：

方法一

set password  for  root@localhost = password('你要改的密码');

方法二

mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *1288B346EB8ABFFC6F6F1B79C437AF124FFE05C7 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
rows in set (0.00 sec)

mysql> update mysql.user set
    -> authentication_string=password('TianYun@52')
    -> where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;（刷新）
Query OK, 0 rows affected (0.00 sec)

方法三

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Zjz@5740';

三、取消密码复杂度

vim  /etc/my.cnf   配置文件里设置

[mysqld]
plugin-load=validate_password.so
validate-password=OFF 

或者在数据库内执行

set global validate_password.policy=0

作用将mysql检查密码强度降为最低，只检查密码的位数。如果你的密码强度很高，就没必要输入该条指令。

四、取消密码登录

 # vim /etc/my.cnf

 [mysqld]
 skip-grant-tables

注意：当skip-grant-tables和skip-network没有注销时，3306端口是不会打开的，虽然msyql running，导致navicat连接失败

#netstat -an | grep 3306

# service mysqld restart
# mysql -uroot
 然后敲MySQL登录改密

mysql> UPDATE mysql.user SET authentication_string=password('Zjz@5740')
    WHERE user='root' AND host='localhost';
(效果最强)

mysql> FLUSH PRIVILEGES;（刷新

五、数据库起不来

#chown -R  mysql.mysql   /var/lib/mysql  (赋权)
#systemctl   restart  mysqld

六、mysql8版本修改密码

1、修改/etc/my.cnf配置文件

vim /etc/my.cnf
添加skip-grant-tables配置

2、重启mysql服务

sudo systemctl restart mysqld

3、在mysql控制台依次输入

use mysql;

set global validate_password.policy=0;

alter user  'root'@'localhost' identified by  '12345678'; 

其中set global validate_password.policy=0作用将mysql检查密码强度降为最低，只检查密码的位数。如果你的密码强度很高，就没必要输入该条指令。

上面会报错

mysql> alter user 'root'@'localhost' identified by '12345678'; 
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

解决：刷新一下，重新执行改密sql

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

sudo systemctl restart mysqld
mysql -uroot -p12345678