MySQL8.0新特性之ROLE的学习笔记
Role功能可以说是一个期待已有的功能，这从它的Worklog号(WL#988)就可以看出来，这是个相当早并且呼声很高的需求了。
所谓Role，可以认为是一个权限的集合，这个集合有一个统一的名字，就是Role名，你可以为多个账户赋予统一的某个Role的权限，而权限的修改可以直接通过修改Role来实现，而无需每个账户逐一GRANT权限，大大方便了运维和管理。
Role可以被创建，修改和删除，并作用到其所属于的账户上。
举个简单的例子。创建如下测试表
mysql>createdatabasetestdb;
QueryOK,1rowaffected(0.00sec)
mysql>usetestdb;createtablet1(aint,bint,primarykey(a));
Databasechanged
QueryOK,0rowsaffected(0.00sec)
mysql>insertintot1values(1,2);
QueryOK,1rowaffected(0.00sec)
创建Role，拥有t1表的查询权限:
mysql>createrolepriv_t1;
QueryOK,0rowsaffected(0.00sec)
mysql>grantselectontestdb.t1to''priv_t1'';
QueryOK,0rowsaffected(0.00sec)
创建一个账户，并将role的权限赋给它
mysql>createuser''rw_user1''@''%''identifiedby''xxx'';
QueryOK,0rowsaffected(0.00sec)
mysql>grant''priv_t1''to''rw_user1''@''%'';
QueryOK,0rowsaffected(0.00sec)
以rw_user1登录
----查看权限
mysql>showgrants;
+---------------------------------------+
|Grantsforrw_user1@%|
+---------------------------------------+
|GRANTUSAGEON.TO`rw_user1`@`%`|
|GRANT`priv_t1`@`%`TO`rw_user1`@`%`|
+---------------------------------------+
2rowsinset(0.00sec)
##需要加using"role名"才会展开权限
mysql>showgrantsfor''rw_user1''@''%''usingpriv_t1;
+-------------------------------------------------+
|Grantsforrw_user1@%|
+-------------------------------------------------+
|GRANTUSAGEON.TO`rw_user1`@`%`|
|GRANTSELECTON`testdb`.`t1`TO`rw_user1`@`%`|
|GRANT`priv_t1`@`%`TO`rw_user1`@`%`|
+-------------------------------------------------+
3rowsinset(0.00sec)
然而此时并不能直接获得t1表的查询权限，你需要手动进行选择哪些role在账户连接上来时被激活，如下:
mysql>selectfromtestdb.t1;
ERROR1142(42000):SELECTcommanddeniedtouser''rw_user1''@''localhost''fortable''t1''
mysql>SETDEFAULTROLEALLTO''rw_user1''@''%'';
QueryOK,0rowsaffected(0.00sec)
---重新登录生效
mysql>selectuser();
+--------------------+
|user()|
+--------------------+
|rw_user1@localhost|
+--------------------+
1rowinset(0.00sec)
mysql>selectfromtestdb.t1;
+---+------+
|a|b|
+---+------+
|1|2|
+---+------+
1rowinset(0.00sec)
--SETROLE语法参阅官方文档：
--http://dev.mysql.com/doc/refman/8.0/en/set-default-role.html
修改role的权限，会直接作用到对应的账户上:
---增加insert权限
---loginasroot
mysql>grantinsertontestdb.t1to''priv_t1'';
QueryOK,0rowsaffected(0.00sec)
---loginasrw_user1
mysql>insertintotestdb.t1values(2,3);
QueryOK,1rowaffected(0.00sec)
---删除insert权限
---loginasroot
mysql>revokeinsertontestdb.t1from''priv_t1'';
QueryOK,0rowsaffected(0.00sec)
---loginasrw_user1
mysql>insertintotestdb.t1values(3,4);
ERROR1142(42000):INSERTcommanddeniedtouser''rw_user1''@''localhost''fortable''t1''
增加了两个系统表来维护Role信息，一个是mysql.default_roles表，用于展示账户使用的默认role信息，一个是role_edges，用于展示已创建的role信息
mysql>selectfromdefault_roles;
+------+----------+-------------------+-------------------+
|HOST|USER|DEFAULT_ROLE_HOST|DEFAULT_ROLE_USER|
+------+----------+-------------------+-------------------+
|%|rw_user1|%|priv_t1|
+------+----------+-------------------+-------------------+
1rowinset(0.00sec)
mysql>selectfromrole_edges;
+-----------+-----------+---------+----------+-------------------+
|FROM_HOST|FROM_USER|TO_HOST|TO_USER|WITH_ADMIN_OPTION|
+-----------+-----------+---------+----------+-------------------+
|%|priv_t1|%|rw_user1|N|
+-----------+-----------+---------+----------+-------------------+
1rowinset(0.00sec)
新增函数用于显示当前账户使用的role：
mysql>selectcurrent_role();
+----------------+
|current_role()|
+----------------+
|`priv_t1`@`%`|
+----------------+
1rowinset(0.00sec)
相信很多朋友都有这样的体验，在实例启动的时候动态修改了某些配置，但是忘了修改配置文件，结果实例一重启修改就丢失了。或者对于诸如部署在云上的业务（例如RDS），通常用户是没有修改配置文件的权限的，这导致配置的设定不够灵活。从MySQL8.0开始，大家不用担心这个问题了，用户可以选择性的将配置进行持久化。
对应的Worklog:WL#8688
启用这个功能，使用特定的语法SETPERSIST来设定任意可动态修改的全局变量，例如：
mysql>SETPERSISTinnodb_flush_log_at_trx_commit=1;
QueryOK,0rowsaffected(0.00sec)
mysql>SETPERSISTsync_binlog=1;
QueryOK,0rowsaffected(0.00sec)
此时检查默认的my.cnf文件并没有发生任何修改，但是数据目录下，多了个和之前MySQL版本不一样的文件：
$ls-lhmysqld-auto.cnf
-rw-r-----1zhaiwxusers81Sep1718:09mysqld-auto.cnf
文件的格式是JSON存储的，因此可以直接用内置的解析器进行解析
$catmysqld-auto.cnf
{"mysql_server":{"innodb_flush_log_at_trx_commit":"1","sync_binlog":"1"}}
mysqld-auto.cnf文件中的配置相比my.cnf文件具有高优先级，如果相同配置出现在两个文件中，就以mysqld-auto.cnf文件中的记录为主。
注意，即使你通过SETPERSIST修改配置的值并没有任何变化，也会写入到mysqld-auto.cnf文件中。但你可以通过设置成default值的方式将其从mysqld-auto.cnf文件中移走：
mysql>SETPERSISTinnodb_flush_log_at_trx_commit=DEFAULT;
QueryOK,0rowsaffected(0.00sec)
=====
$catmysqld-auto.cnf
{"mysql_server":{"sync_binlog":"1"}}
你可以通过新的PS表performance_schema.variables_info来确定配置项的值的来源，共有几类：
enumenum_variable_source
{
COMPILED=1,
GLOBAL,
SERVER,
EXPLICIT,
EXTRA,
MYSQL_USER,
LOGIN,
COMMAND_LINE,
PERSISTED,
DYNAMIC
};
系统表相关代码：storage/perfschema/table_variables_info.cc
==================
mysql>selectfromperformance_schema.variables_infowherevariable_source=''PERSISTED''\G
1.row
VARIABLE_NAME:sync_binlog
VARIABLE_SOURCE:PERSISTED
VARIABLE_PATH:/u01/my80/data/mysqld-auto.cnf
MIN_VALUE:0
MAX_VALUE:4294967295
1rowinset(0.00sec)
如果你不想让mysqld-auto.cnf中的配置生效，可以在启动时关闭只读参数persisted_globals_load，这样在启动时就不会载入mysqld-auto.cnf中的配置项了。
主要代码：
Commitf2bc0f89b7f94cc8fe963d08157413a01d14d994
主要入口函数(8.0.0)：
接口函数大多定义在sql/persisted_variable.cc文件中：
启动时载入mysqld-auto.cnf的内容:Persisted_variables_cache::load_persist_file()；通过json解析合法性，并存入内存
将文件中读取的配置进行设置：Persisted_variables_cache::set_persist_options
运行SETPERSIST命令时，调用Persisted_variables_cache::set_variable更新内存中存储的值
写入mysqld-auto.cnf文件中：Persisted_variables_cache::flush_to_file
MySQL主主同步和主从同步的原理一样，只是双方都是主从角色。
环境
操作系统版本：CentOS764位
MySQL版本：mysql5.6.33
节点1IP：192.168.1.205主机名：edu-mysql-01
节点2IP：192.168.1.206主机名：edu-mysql-02
MySQL主从复制官方文档：http://www.edu800.cn/fandede9988/login.php
注意：
1>主从服务器操作系统版本和位数要保持一致
2>Master和Slave数据库的版本要一致
3>Master和Slave数据库中的数据要一致
配置
配置之前先参考《MySQL5.7安装与配置（YUM）》安装好MySQL（注意本文演示的是5.6版本，需要修改文章中的yum源为5.6）
1、安全配置
1>防火墙
添加mysql通信端口（默认为3306）
shell>vim/etc/sysconfig/iptables
-AINPUT-mstate--stateNEW-mtcp-ptcp--dport3306-jACCEPT
shell>serviceiptablesrestart
或关闭防火墙
shell>serviceiptablesstop
2>关闭selinux
shell>vi/etc/selinux/config
SELINUX=disabled
将SELINUX的值修改为disabled
2.节点1配置（192.168.1.205）
2.1添加数据同步配置
shell>vim/etc/my.cnf
在[mysqld]中增加以下配置项：
#服务器的ID,必须唯一，一般设置自己的IP
server_id=205
#复制过滤：不需要备份的数据库（MySQL库一般不同步）
binlog-ignore-db=mysql
#开启二进制日志功能，名字可以随便取，最好有含义（比如项目名）
log-bin=edu-mysql-bin
#为每个session分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
#主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
##跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
##如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
#作为从服务器时的中继日志
relay_log=edu-mysql-relay-bin
#log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
#主键自增规则，避免主从同步ID重复的问题
auto_increment_increment=2#自增因子（每次加2）
auto_increment_offset=1#自增偏移（从1开始），单数
2.2Master配置
#先重启一下服务
shell>servicemysqldrestart
#登录到mysql
shell>mysql-uroot-p
#创建数据库同步用户，并授予相应的权限
mysql>grantreplicationslave,replicationclienton.to''repl''@''192.168.1.206''identifiedby''root123456'';
#刷新授权表信息
mysql>flushprivileges;
#查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
mysql>showmasterstatus;
+----------------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+----------------------+----------+--------------+------------------+-------------------+
|edu-mysql-bin.000001|120||mysql||
+----------------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)
2.3Slave配置
#master_user和master_password：在206上执行grantreplicationslave...创建的用户和密码
#master_log_file和master_log_pos：在206上运行showmasterstatus;命令执行结果对应File和Position字段的值
mysql>changemastertomaster_host=''192.168.1.206'',master_user=''repl'',master_password=''root123456'',master_port=3306,master_log_file=''edu-mysql-bin.000001'',master_log_pos=439,master_connect_retry=30;
#查看作为从节点的状态信息
mysql>showslavestatus\G;
1.row
Slave_IO_State:
Master_Host:192.168.1.206
Master_User:repl
Master_Port:3306
Connect_Retry:30
Master_Log_File:edu-mysql-bin.000001
Read_Master_Log_Pos:439
Relay_Log_File:edu-mysql-relay-bin.000001
Relay_Log_Pos:4
Relay_Master_Log_File:edu-mysql-bin.000001
Slave_IO_Running:No
Slave_SQL_Running:No
#省略其它配置。。。
由于此时从节点还没有启动，Slave_IO_State的值为空，Slave_IO_Running和Slave_SQL_Running线程为No表示也没有运行。
2.4启动Slave
注意：要在节点2上创建同步帐户后再启动，否则会报连不上master错误
#启动从节点，开始工作接收主节点发送事件（www.zycaihui.com数据库数据变更的所有事件）
mysql>startslave;
#此时再查看slave节点的状态
mysql>showslavestatus\G;
1.row
Slave_IO_State:Waitingformastertosendevent
Master_Host:192.168.1.206
Master_User:repl
Master_Port:3306
Connect_Retry:30
Master_Log_File:edu-mysql-bin.000001
Read_Master_Log_Pos:439
Relay_Log_File:edu-mysql-relay-bin.000002
Relay_Log_Pos:287
Relay_Master_Log_File:edu-mysql-bin.000001
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:
#...省略其它配置
3.节点2配置（192.168.1.206）
3.1添加数据同步配置
shell>vim/etc/my.cnf
在[mysqld]中增加以下配置项：
server_id=206
binlog-ignore-db=mysql
log-bin=edu-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=edu-mysql-relay-bin
log_slave_updates=1
#ID自增从2开始，双数
auto_increment_increment=2
auto_increment_offset=2
3.2Master配置
#先重启一下服务
shell>servicemysqldrestart
#登录到mysql
shell>mysql-uroot-p
#创建数据库同步用户，并授予相应的权限（只允许repl用户从192.168.1.205上登录）
mysql>grantreplicationslave,replicationclienton.to''repl''@''192.168.1.205''identifiedby''root123456'';
#刷新授权表信息
mysql>flushprivileges;
#查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
mysql>showmasterstatus;
+----------------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+----------------------+----------+--------------+------------------+-------------------+
|edu-mysql-bin.000001|439||mysql||
+----------------------+----------+--------------+------------------+-------------------+
1rowinset(0.00sec)
这时可以启动节点1(205)的slave服务
3.3Slave配置
#master_log_file和master_log_pos：205节点上执行showmasterstatus;对应File和position的值
mysql>changemastertomaster_host=''192.168.1.205'',master_user=''repl'',master_password=''root123456'',master_port=3306,master_log_file=''edu-mysql-bin.000001'',master_log_pos=120,master_connect_retry=30;
QueryOK,0rowsaffected,2warnings(0.02sec)
mysql>showslavestatus\G;
1.row
Slave_IO_State:
Master_Host:192.168.1.205
Master_User:repl
Master_Port:3306
Connect_Retry:30
Master_Log_File:edu-mysql-bin.000001
Read_Master_Log_Pos:120
Relay_Log_File:edu-mysql-relay-bin.000001
Relay_Log_Pos:4
Relay_Master_Log_File:edu-mysql-bin.000001
Slave_IO_Running:No
Slave_SQL_Running:No
Replicate_Do_DB:
#...省略其它配置
3.4、启动Slave
shell>startslave;
QueryOK,0rowsaffected(0.01sec)
mysql>showslavestatus\G;
1.row
Slave_IO_State:Waitingformastertosendevent
Master_Host:192.168.1.205
Master_User:repl
Master_Port:3306
Connect_Retry:30
Master_Log_File:edu-mysql-bin.000001
Read_Master_Log_Pos:439
Relay_Log_File:edu-mysql-relay-bin.000002
Relay_Log_Pos:287
Relay_Master_Log_File:edu-mysql-bin.000001
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:
...省略其它配置
4、验证
#登录205创建一个数据库
shell>mysql-uroot-p
mysql>createdatabaseifnotexistsmydbdefaultcharactersetutf8collateutf8_general_ci;
mysql>createtableuser(idint,usernamevarchar(30),passwordvarchar(30));
mysql>insertintouservalues(1,''yangxin'',''123456'');
#下面是在206节点上的操作
#1、登录206查询所有库，是否包含mydb数据库
#2、切换到mydb库，是否包含user表，并有一条数据
#3、在206的mydb.user表插入一条数据，查看205是否同步过去
mysql>insertintouservalues(2,''yangxin2'',''123456'')
|
|
