实战4、MySQL用户管理-sysbench__2018.6.25

一、MySQL 用户管理

遗留问题，授权，3取2

注意：
1、避免使用root,可以创建一个root权限的账户 -with grant option；

=》好处：可以阻止未授权的用户访问超出其权限的数据 -> 更安全

mysql version< 5.7:
需要清除匿名账户：

delete from mysql.user where user!='root' or host!='localhost';

练习1、 mysql 用户连接

顺序：
先验证用户名是否存在
来源是否允许，
密码是否正确
连接注意
创建用户 song:

mysql> create user 'song'@'172.16.100.%' identified by 'song';
Query OK, 0 rows affected (0.00 sec)

查看权限：

mysql> show grants for song@'172.16.100.%';
+-------------------------------------------------+
| Grants for song@172.16.100.%                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'song'@'172.16.100.%' |
+-------------------------------------------------+
1 row in set (0.00 sec)

现在用此账号连接 db，走localhost方式、127.0.0.1方式，都连接失败，走TCP的方式连接成功
1、走localhost

[root@mongo1 ~]# mysql3306  -usong -psong
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'song'@'localhost' (using password: YES)

2、走TCP

[root@mongo1 ~]# mysql3306  -usong -psong  -h172.16.100.245
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

练习2、通过精确匹配限制某个IP连接

当前用户信息如下：

mysql> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| song          | 172.16.100.% |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
+---------------+--------------+
4 rows in set (0.00 sec)

现在我想禁用102 IP的账户登录。
用root用户新增一个账号： song@‘172.16.100.102’

mysql> create user song@'172.16.100.102';
Query OK, 0 rows affected (0.00 sec)

mysql> select  user,host from mysql.user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| song          | 172.16.100.%   |
| song          | 172.16.100.102 |  //这里
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
+---------------+----------------+
5 rows in set (0.00 sec)

在102 IP上登录，登录失败，表示禁用102 IP 成功！截图如下：

[root@server-102 ~]# mysql -h172.16.100.245 -usong -psong
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'song'@'172.16.100.102' (using password: YES)

创建用户：
用户：用户名@主机名
用户名： < 10 个字符
主机名：

• localhost
• mysql1.db.com
• 172.16.100.92
• 172.16.100.0/255.255.255.0
• 172.16.100.% 或 172.16.100.1_

问题1、% 和 _
1、 %和 _区别
2、使用%的危害

练习3、 使用主机名来授权

前提：
1、系统配置：

注意 需要/etc/hostname 和 /etc/hosts里配置的hostname相同：

[root@mongo1 ~]# cat /etc/hostname
mongo1

[root@mongo1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.100.245 mongo1
172.16.100.246 mongo2

2、mysql自身有关 hostname 配置

[mysqld]
skip-name-resolve=off               #解析主机名

3、创建用户

mysql> create user 'Song'@'mongo%' identified by 'Song';
Query OK, 0 rows affected (0.00 sec)

查看创建的 Song@’mongo%’ 用户：

mysql> select user,host from mysql.user;
+---------------+----------------+
| user          | host           |
+---------------+----------------+
| song          | 172.16.100.%   |
| song          | 172.16.100.102 |
| mysql.session | localhost      |
| mysql.sys     | localhost      |
| root          | localhost      |
| Song          | mongo%         |   //这里
+---------------+----------------+
6 rows in set (0.00 sec)

4、测试
在mongo1和mongo2上分别测试，登录成功界面 如下：

[root@mongo1 ~]# mysql3306  -uSong -pSong -h172.16.100.245
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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>

在A上，创建用户 Song@’mongo%’

练习4、误删 root

避免使用root，创建跟root 同样权限的songyt 用户。

mysql> grant all on *.* to  'songyt'@'%' identified by 'songyt' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

创建用户的方法：

create user 用户名@主机名   identified by  '密码'；
 grant   all on  库名.表名  to  用户名@主机名  identified  by  '密码'；

修改用户、密码：

alter  user  '用户名'@'主机名'    identified  by  '密码'； //改密码
set  password  for '用户名'@'主机名'=password('密码')；  //改密码
rename user '旧_用户名'@‘主机名% ’ to  '新_用户名'@‘主机名’；    //更改用户名、主机部分
mysqladmin  password
update  授权表（better  not）

删除用户

drop  user '用户名'@'主机名'；    //直接删除该用户、从授权表中删除该用户的记录

确认密码

• 1、查看没密码的用户

     version=5.6 : select  user,host  from  mysql.user  where password=''；

     version=5.7: select  user,host from mysql.user  where authentication_string='';

• 2、让用户口令失效，登录后必须改密码

 alter user '用户名'@'主机名'  password  expire;

如果不小心删了root用户，在本库内：

版本一样的情况下：
cp user* zst/ ;
修改后再cp回来。

二、权限

查看所有权限

show  privileges;

help show        //看帮助文档

用户连接mysql后，

• 用户是谁

• 用户的权限是啥

  • 全局
  • db
  • 表
  • 列
  • 存储过程
    -权限可以操作啥

• 只读用户

  • 全局、db、表 的权限：select
• 一般开发
  
  • 业务库权限：insert、delete、update、select、execute
• 管理用户
  
  • 全局级别：权限：insert、delete、update、select、create、alter、drop、file、process、shutdown、super

巧用rename table

rename table db1.tb1  to  db2.tb1;

一共有100个表，需要只授权80个，How？

测试：一共有3个表，只需授权2个：
先查出来表名：

mysql> select table_name from information_schema.tables where table_schema='zst';
+------------+
| table_name |
+------------+
| tb1        |
| tb2        |
| tb3        |
+------------+
3 rows in set (0.00 sec)

再拼接：

mysql> select concat("grant select on zst.",table_name," to zst@'mongo%'; ") from information_schema.tables where table_schema='zst';
+----------------------------------------------------------------+
| concat("grant select on zst.",table_name," to zst@'mongo%'; ") |
+----------------------------------------------------------------+
| grant select on zst.tb1 to zst@'mongo%';                       |
| grant select on zst.tb2 to zst@'mongo%';                       |
| grant select on zst.tb3 to zst@'mongo%';                       |
+----------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select concat("grant select on zst.",table_name," to zst@'mongo%'; ") from information_schema.tables where table_schema='zst' and table_name not in ('user');
+----------------------------------------------------------------+
| concat("grant select on zst.",table_name," to zst@'mongo%'; ") |
+----------------------------------------------------------------+
| grant select on zst.tb1 to zst@'mongo%';                       |
| grant select on zst.tb2 to zst@'mongo%';                       |
| grant select on zst.tb3 to zst@'mongo%';                       |
+----------------------------------------------------------------+
3 rows in set (0.01 sec)

密码：
密码最好不要用明文，以下几种方法生成密码：

• md5sum

[root@mongo1 ~]# echo "123456" |md5sum
f447b20a7fcbf53a5d5be013ea0b15af  -

• openssl

[root@mongo1 ~]# openssl rand -base64 12
3AKLA+9xpfYWL1mC

show slave status和show master status；
前者只能读取本地binlog，后者可以远程读取master的binlog到本地。
要是有mysql.user的权限 也可以修改。

注意以下权限：

• file
  ：允许用户指示mysql服务器在服务器主机文件系统中读取和写入文件
  mysql5.7需要配合
• process
  ：允许用户使用show processlist语句
• super
  ：允许用户中止其他客户机连接，或者更改服务器的运行时配置，执行kill，set
• all
  ：授予所有权限，（但不能要其他用户授权）
• grant all …. with grant option;
  ：授予所有权限，（可以向其他用户授权）

最好不要使用外键

权限控制表：

表	用处
mysql.user	每个创建的用户都会有一条记录
mysql.db	限制用户作用于特定的db
mysql.tables_priv	用于表级别的权限控制
mysql.procs_priv	用于存储过程和函数权限限制

mysql启动时从mysql库中把权限读取下 加载到内存中；
如果通过DML更新权限表，需要借助：flush privileges；生效（最好不要对权限表进行DML操作）

查看打开了哪些表：

show  open tables；

查看进程id

[root@mongo1 ~]# pidof mysqld
28154

撤销用户权限

• 1、使用revoke
  revoke 权限名 on 库名.表名 from 用户名@’主机名’；

docker里

docker run  --privileged -d -v /docker/zst/mysql:/opt/mysql -v /docker/zst/zst1:/data -v /mnt:/mnt  -v /etc/hosts:/etc/hosts -p1021:22 --cap-add=NET_ADMIN --name zst1  -h zst1 zst/centos7

三、sysbench

利用sysbench给数据库点压力
官网：https://github.com/akopytov/sysbench

git  clone  https://github.com/akopytov/sysbench.git
cd sysbench
autogen.sh
make install

可以自定义读写比例，比如1：1，也可以读80%，写20%
几个比较好的测试工具：
1、sysbench 产生的表更多
2、雅虎的YCSB，可以调整比例
3、mysql-tpcc

安装过程中可能会遇到PATH问题：
把mysql的“/usr/local/mysql/lib” 放到/etc/ld.so.conf.d/mysql.conf 中：

cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib

看下是否缺少库文件：

ldd  /usr/local/bin/sysbench

看版本：

sysbench  --version

在mysql中创建sysbench使用的db、用户：

create database sysbench_test;
grant all on sysbench_test.* to sysbench@'%'  identified by 'sysbench';

生成基本的表：

#cleanup
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host= 172.17.0.2 --mysql-port=3306 --mysql-user=wubx --mysql-password=wubxwubx --mysql-db=zst --tables=10 --table_size=100000 --mysql_storage_engine=Innodb  cleanup

#prepare
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host= 172.17.0.2 --mysql-port=3306 --mysql-user=wubx --mysql-password=wubxwubx --mysql-db=zst --tables=10 --table_size=100000 --mysql_storage_engine=Innodb  prepare

#run
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host= 172.17.0.2 --mysql-port=3306 --mysql-user=wubx --mysql-password=wubxwubx --mysql-db=zst --tables=10 --table_size=100000 --mysql_storage_engine=Innodb  --threads=10 --time=3600 --warmup-time=100 --report-interval=10 --rand-type=uniform run

运行过程中，可以看下：

show global status like '%thread%';
    Threads_running =9      //说明有9个并发

iostat -m -x 1

这压力测试跑的啥sql？
文件： oltp_common.lua
开启slowlog

show  global variables like '%long%';
    long_query_time=1

预热是把数据加载到buffer_pool，预热完再看slow.log
监控：
1、zabbix监控mysql
2、用cadvisor监控 docker
3、pt-query-digest

sys库：

select  * from  statement_analysis;

proxysql做动态的读写分离