mysql

mysql 一些命令

Posted by Sunday on 2019-05-24

授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 创建具有所有表权限的用户及密码
grant all on *.* to sunday@192.168.10.%' identified by 'My_password';

# 创建具有gitlab库所有表select权限的用户
grant select on gitlab.* to sunday@'%'; #sunday用户已存在时附加
grant select on gitlab.* to sunday@'%' identified by 'My_password'; #sunday用户不存在时则自动创建用户
flush privileges;

# 撤消用户权限
revoke all on *.* from sunday@'192.168.10.%';
revoke update,insert on *.* from sunday@'192.168.10.%';
flush privileges;

# 查看用户
select User,Host from mysql.user;

# 查看用户权限
show grants;
show grants for sunday@'192.168.10.%';

# 删除用户
drop user sunday@'192.168.10.%';

修改用户密码

1
2
3
4
5
6
7
8
9
10
# 方式一
alter user 'sunday'@'192.168.10.%' identified by 'My_password'; # 注意 密码需要复杂点，不然会报错
或
update mysql.user set password=password("My_now_password") where User='sunday'and Host='192.168.10.%'; # 5.6
update mysql.user set authentication_string=password("My_now_password") where User='sunday'and Host='192.168.10.%'; # 5.7
flush privileges;

# 方式二
set password for 'sunday'@'192.168.10.%'=password('My_password')
alter user 'sunday'@'192.168.10.%' identified by 'My_password';

关闭密码复杂 5.6

1
2
set global validate_password_policy=0;
set global validate_password_length=1;

查看锁表线程

1
2
3
4
5
6
7
# 查看哪个表被锁
show open tables where in_use>0;

# 查看所有进程
show processlist;
show full processlist;
kill id

查看variables

1
2
show variables like '%connections';
show global variables like '%connections';

查看status

1
2
3
show engine innodb status;
show global status like 'Max_used_connections'
show global status like '%connections%'

忘记root密码

1
2
3
4
5
6
7
8
9
10
11
12
/etc/my.cnf
[mysqld]
skip-grant-tables

mysql> update user set authentication_string=password('sunday') where user='root';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

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

# 然后/etc/my.cnf删除skip-grant-tables,重启mysql.

终端免密码登陆

1
2
3
4
5
vim ~/.my.cnf
[mysql]
user='root'
host='localhost'
password='?T:l.vj3rMCf99JKrK++xxxxx'
CATALOG
    FEATURED TAGS
    mysql
    FRIENDS