用户密码管理
修改用户密码
1)修改root密码
A. mysqladmin
mysqladmin -u root -h localhost -p password 'rootroot'
B.修改 mysql.user表
use mysql;
update mysql.user set authentication_string=PASSWORD('root') where user='root';
flush privileges;
mysql root@localhost:(none)> set password=PASSWORD('root')
D.使用 alter user
alter user 'root'@'localhost' identified by 'root'
alter user 'root'@'%' identified by 'root'
2)修改普通用户的码
A.修改 mysql.user 表
use mysql;
update mysql.user set authentication_string=PASSWORD('itpux') where user='itpux' and host='localhost';
flush privileges;
B.使用grant语句
grant usage on *.* to 'itpux'@'%' identified by 'root';
grant usage on *.* to 'itpux'@'localhost' identified by 'root';
flush privileges;
C.当前用户登录(比如:itpux)
set password=PASSWORD('itpux')
D.使用 alter user(推荐)
alter user 'itpux'@'localhost' identified by 'root';
alter user 'itpux'@'%' identified by 'root';
3)密码过期问题
mysql 5.7.11之前有一个360天密码过期的问题,5.7.12之后又改为密码不过期。
show variables like 'default_password_lifetime';
mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
A.永久:如果密码要设置过期或者不过期
my.cnf 参数
[mysqld]
default_password_lifetime=0 或 360
B.水久:alter user
alter user 'itpux'@'localhost' password expire interval 90 day;
select * from mysql.user;
alter user 'itpux'@'localhost' password expire never;
alter user 'itpux'@'localhost' password expire DEFAULT;
4)用户锁定与解锁的问题
alter user 'itpux'@'localhost' account lock;
alter user 'itpux'@'localhost' account unlock;
root用户密码丢失的解决办法
win
加入参数到my.ini参数文件中,然后重启,登录时不用密码,进入后再改密码,改完后,去掉参数,重启生效
skip-grant-tables
or:
mysqld --skip-grant-tables
use mysql;
update mysql.user set authentication_string=PASSWORD('root') where user='root';
flush privileges;
linux
1. service mysql stop
2.加入忽略授权表参数到my.cnf,改参数前备份原参数
[mysqld]
skip-grant-tables
3. service mysql start
4. mysql -uroot-p
不用密码
5.改密码
update mysql.user set authentication_string=PASSWORD('root') where user='root';
flush privileges;
6.从my.cnf里面取消;
skip-grant-tables
7. service mysql restart
8.登录测试
常用的登录方式5种
A mysql -uroot -p
B mysqL -p
C mysqL -S /mysql/data/3306/mysql.sock -uroot -p
D mysql -h ip -u root -p
E mysql -hlocalhost -uroot -proot
F mysql -uroot -p --defaults-file=/mysql/data/3306/my.cnf
免密码登录的方式5种
A skip-grant-tables 参数方法
B 统一方法:直接修改my.cnf
[client]
user = "root"
password = "root"
登录方法:
mysql --defaults-file=/mysql/data/3306/my.cnf
C.不同客户端方法:
[mysql]
user = "root"
password = "root"
[mysqladmin]
user = "root"
password = "root"
D.当前环境变量
vi vim ~/.my.cnf
[client]
user = "root"
password = "root"
E.使用环境变量 MYSQL_PWD
export MYSQL_PWD=root
登录方法:
mysql -uroot
F.最安全的方法,使用 login-path:
[root@elasticsearch ~]# mysql_config_editor set --login-path=itpuxpw --user=root --password
Enter password:
[root@elasticsearch ~]# mysql_config_editor print --all
[itpuxpw]
user = root
password = *****
[root@elasticsearch ~]# mysql --login-path=itpuxpw
清除
[root@elasticsearch ~]# mysql_config_editor remove --login-path=itpuxpw