MGR单主模式集群方案搭建部署

﹏ヽ暗。殇╰゛Y 2022-12-17 01:19 263阅读 0赞

# MGR集群方案 #

## MGR工作原理概述 ##

### 工作原理 ###

![5e4ca146e9d451ad4e8e3d753d3f49ad.png][]

如图所示，DB1 、DB2 、DB3 构成的 MGR 集群， 集群中每个 DB 都有 MGR 层，MGR 层功能也可简单理解为由 Paxos 模块和冲突检测 Certify 模块实现。

Paxos 模块是基于 Paxos 算法确保所有节点收到相同广播消息，transaction message 就是广播消息的内容结构;冲突检测 Certify 模块进行冲突检测确保数据最终一致性，其中 certification info 是冲突检测中内存结构。

当 DB1 上有事务 T1 要执行时，T1 对 DB1 是来说本地事务，对于 DB2、DB3 来说是远端事务;DB1 上在事务 T1 在被执行后，会把执行事务 T1 信息广播给集群各个节点，包括 DB1 本身，通过 Paxos 模块广播给 MGR 集群各个节点，半数以上的节点同意并且达成共识，之后共识信息进入各个节点的冲突检测 certify 模块，各个节点各自进行冲突检测验证，最终保证事务在集群中最终一致性。

在冲突检测通过之后，本地事务 T1 在 DB1 直接提交即可，否则直接回滚。远端事务 T1 在 DB2 和 DB3 分别先更新到 relay log，然后应用到 binlog，完成数据的同步，否则直接放弃该事务。

### 特点 ###

MGR包括自动主备切换能力,自动化组员身份管理以及基于分布式恢复技术的provisioning能力(可以在运行其间增加备机节点)

主备切换:集群所有节点通过定期运行paxos协议交换节点状态,每个节点都能发现其他节点的变化状态.如果主节点消失,就会在其他节点中选出新的主节点.如果发现若干节点消失导致失去quorum(n=2\*f+1),集群自动转为只读防止脑裂(数据损坏).MGR把paxos和主备复制结合,提升了集群的容灾能力.

### 优点 ###

1. 避免脑裂：MGR中不会出现脑裂的现象（但要注意可能出现网络分区的情况，MGR根据参数group\_replication\_unreachable\_majority\_timeout=0/N设置的不同，行为稍有不同）。

2. 数据一致性保障：MGR的冗余能力很好，能够保证Binlog Event至少被复制到超过一半的成员上，只要同时宕机的成员不超过半数便不会导致数据丢失。MGR还保证只要Binlog Event没有被传输到半数以上的成员，本地成员不会将事务的Binlog Event写入Binlog文件和提交事务，从而保证宕机的服务器上不会有组内在线成员上不存在的数据。因此，宕机的服务器重启后，不再需要特殊的处理就可以加入组。

3. 多节点写入支持：多写模式下支持集群中的所有节点都可以写入。

4. 单主多主模式可以进行切换

### MGR集群限制 ###

1.  必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set

2.  仅支持InnoDB表;并且每张表一定要有一个主键，用于做write set的冲突检测

3．不支持二进制日志检查，必须binlog-checksum=NONE

4．不支持gap锁，建议使用Read-Commit（这点集成默认推荐使用RC模式不冲突）

6．不支持 Savepoints特性，无法做全局间的约束检测与部分部分回滚COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景

7．不支持事务级别SERIALIZABLE（可串行化）

8．不支持同一对象上的DDL和DML操作（Multi-Master情况下）

9．不支持外键级联约束限制（Multi-Master情况下）

5．MGR没有考虑到表锁情况，个人目前感觉这点主要体现在多主情况，或者单主模式下Primary lock table后，主从切换表锁失效问题。

10.  目前一个MGR集群最多支持9个节点

### MGR适用场景 ###

MGR是金融级分布式数据库之一,天生就是为金融场景设计的，例如：支付，证券交易，保险，银行等等。因为这些场景要求数据必须做到零丢失，数据库可用性在4个9，甚至5个9（年度停机时间不超过5分钟）。

4个9：(1-99.99%)\*365\*24=0.876小时=52.6分钟，表示该系统在连续运行1年时间里最多可能的业务中断时间是52.6分钟。

5个9：(1-99.999%)\*365\*24\*60=5.26分钟，表示该系统在连续运行1年时间里最多可能的业务中断时间是5.26分钟。

### 数据备份以及添加节点 ###

通过分布式恢复（distributed recovery）我们可以用一条SQL语句就轻松加入一个新的备机节点，或者把一个老备机节点带到最新状态。这个功能意味着两个复杂而重要的事情被自动化和简化了。

首先，我们可以使用它来做数据全量备份，代替Percona Xtrabackup — 只要新做一个DB实例，作为备机加入MGR集群，执行一条语句‘start group\_replication’，完成distributed recovery后停机后，这个实例就具有了主节点当前的全部数据。然后归档存储其数据目录即可当作全量备份。

### 容灾能力 ###

1.  MGR基于paxos协议的主备复制机制,实现了事务binlog原子广播,从而提高容灾能力
2.  MGR需要大多数节点处于活跃状态以达到仲裁成员数(即上文提到quorum),从而做出决定.这对系统可以容忍的不影响自身及其整体功能的故障数量有直接影响,容忍f个故障所需要的节点数量n=2\*f+1

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> &nbsp; 组大小 </td> 
 <td colspan="1"> &nbsp; 多数 </td> 
 <td colspan="1"> &nbsp; 允许的即时故障数 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; 1 </td> 
 <td colspan="1"> &nbsp; 1 </td> 
 <td colspan="1"> &nbsp; 0 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; 2 </td> 
 <td colspan="1"> &nbsp; 2 </td> 
 <td colspan="1"> &nbsp; 0 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; 3 </td> 
 <td colspan="1"> &nbsp; 3 </td> 
 <td colspan="1"> &nbsp; 1 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; 4 </td> 
 <td colspan="1"> &nbsp; 4 </td> 
 <td colspan="1"> &nbsp; 1 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; 5 </td> 
 <td colspan="1"> &nbsp; 5 </td> 
 <td colspan="1"> &nbsp; 2 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; 6 </td> 
 <td colspan="1"> &nbsp; 6 </td> 
 <td colspan="1"> &nbsp; 2 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; 7 </td> 
 <td colspan="1"> &nbsp; 7 </td> 
 <td colspan="1"> &nbsp; 3 </td> 
 </tr> 
 </tbody> 
</table>

1.  MGR对binlog数据量有要求,因为如果事务binlog传输的时耗超过了paxos协议的超时，那么其他节点就会发现自己超时没有受到主节点的任何消息，误以为主节点已经宕机了,于是会误触发主备切换。因此必须限制事务binlog的规模。

## 公司目前ORACLE环境 ##

oracle\*2+rac+共享

## MGR集群规划(MGR+MySqlRouter) ##

### 集群规划 ###

<table> 
 <tbody> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 用户 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 主机名 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql版本 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 插件 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 备注 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; master </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; master </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql8+ </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql-server、mysql-shell、mysql-router、keepalived、mysql-client </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 读、写 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve1 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql8+ </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql-server、mysql-shell、mysql-router、keepalived、mysql-client </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 读 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve2 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql8+ </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql-server、mysql-shell、mysql-router、keepalived、mysql-client </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 读 </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve3 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql8+ </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; mysql-server、mysql-shell、mysql-router、keepalived、mysql-client </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 读 </td> 
 </tr> 
 </tbody> 
</table>

### 硬件需求 ###

<table> 
 <tbody> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 服务器 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 处理器 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 内存 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 存储（ssd） </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; master </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 32 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 128(待定) </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 3T(待定) </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve1 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 32 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 128(待定) </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 3T(待定) </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve2 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 32 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 128(待定) </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 3T(待定) </td> 
 </tr> 
 <tr> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; salve3 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 32 </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 128(待定) </td> 
 <td colspan="1" style="vertical-align:middle;"> &nbsp; 3T(待定) </td> 
 </tr> 
 </tbody> 
</table>

### 实施过程(复制粘贴的) ###

1. Host文件配置 (所有节点操作)

vi /etc/hosts

0.0.0.0  master

0.0.0.0  slave1

0.0.0.0  slave2

0.0.0.0  slave3

2. 创建mysql用户和组（所有节点操作）

Groupadd mysql

useradd -r -s /sbin/nologin -g mysql mysql -d /usr/local/mysql

3. 创建安装目录及权限配置（所有节点操作）

mkdir –p  /data/mysql

chown -R mysql /data/mysql

4. 优化服务器配置

\[root@localhost ~\]\# cat>>/etc/sysctl.conf <<EOF

fs.aio-max-nr = 1048576

fs.file-max = 681574400

kernel.shmmax = 137438953472

kernel.shmmni = 4096

kernel.sem = 250 32000 100 200

net.ipv4.ip\_local\_port\_range = 9000 65000

net.core.rmem\_default = 262144

net.core.rmem\_max = 4194304

net.core.wmem\_default = 262144

net.core.wmem\_max = 1048586

EOF

\[root@localhost  ~\]\# sysctl-p

\[root@localhost  ~\]\#cat>>/etc/security/limits.conf <<EOF

mysql soft nproc 65536

mysql hard nproc 65536

mysql soft nofile 65536

mysql hard nofile 65536

EOF

\[root@localhost  ~\]\#cat>>/etc/pam.d/login <<EOF

session required /lib/security/pam\_limits.so

session required pam\_limits.so

EOF

\[root@localhost  ~\]\#cat>>/etc/profile<<EOF

if \[ $USER = "mysql" \]; then

ulimit -u 16384 -n 65536

EOF

5. 主节点安装Mysql和mysql-shell

tar –zxvf mysql-8.0.19-linux-glibc2.12-x86\_64.tar

mv mysql-8.0.19-linux-glibc2.12-x86\_64 /usr/local/mysql

cd /usr/local

chown –R mysql.mysql mysql

tar –zxvf mysql-shell-8.0.19-linux-glibc2.12-x86-64bit.tar

mv mysql-shell-8.0.19-linux-glibc2.12-x86-64 /usr/local/mysql-shell

6 .修改环境变量

vi/etc/profile

export PATH=$PATH:/usr/local/mysql-shell/bin/:/usr/local/mysql/bin/

source /etc/profile

7. 配置集群配置文件（所有节点操作）

\[root@master1 percon-cluster\]\# cat /etc/my.cnf

\[client\]

port    = 3306

socket  = /data/mysql/mysql.sock

\[mysql\]

prompt="\\u@amfe-srv-1 \\R:\\m:\\s \[\\d\]> "

no-auto-rehash

\[mysqld\]

user    = mysql

port    = 3306

basedir = /usr/local/mysql

datadir = /data/mysql

socket  = /data/mysql/mysql.sock

pid-file = amfe-srv-1.pid

character-set-server = utf8mb4

open\_files\_limit    = 65535

back\_log = 1024

max\_connections = 512

max\_connect\_errors = 1000000

table\_open\_cache = 1024

table\_definition\_cache = 1024

table\_open\_cache\_instances = 64

thread\_stack = 512K

external-locking = FALSE

max\_allowed\_packet = 32M

sort\_buffer\_size = 4M

join\_buffer\_size = 4M

thread\_cache\_size = 768

interactive\_timeout = 600

wait\_timeout = 600

tmp\_table\_size = 32M

max\_heap\_table\_size = 32M

slow\_query\_log = 1

log\_timestamps = SYSTEM

slow\_query\_log\_file = /data/mysql/slow.log

log-error = /data/mysql/error.log

long\_query\_time = 2

log\_queries\_not\_using\_indexes =1

log\_throttle\_queries\_not\_using\_indexes = 60

min\_examined\_row\_limit = 100

log\_slow\_admin\_statements = 1

log\_slow\_slave\_statements = 1

server-id = 3306

log-bin = /data/mysql/mybinlog

sync\_binlog = 1

binlog\_cache\_size = 4M

max\_binlog\_cache\_size = 200M

max\_binlog\_size = 1G

expire\_logs\_days = 21

master\_info\_repository = TABLE

relay\_log\_info\_repository = TABLE

gtid\_mode = on

enforce\_gtid\_consistency = 1

lower\_case\_table\_names = 1

log\_slave\_updates

slave-rows-search-algorithms = 'INDEX\_SCAN,HASH\_SCAN'

binlog\_format = row

binlog\_checksum = 1

relay\_log\_recovery = 1

relay-log-purge = 1

key\_buffer\_size = 15M

read\_buffer\_size = 8M

read\_rnd\_buffer\_size = 4M

bulk\_insert\_buffer\_size = 64M

myisam\_sort\_buffer\_size = 128M

myisam\_max\_sort\_file\_size = 10G

myisam\_repair\_threads = 1

lock\_wait\_timeout = 3600

explicit\_defaults\_for\_timestamp = 1

innodb\_thread\_concurrency = 0

innodb\_sync\_spin\_loops = 100

innodb\_spin\_wait\_delay = 30

transaction\_isolation = REPEATABLE-READ

\#innodb\_additional\_mem\_pool\_size = 16M

innodb\_buffer\_pool\_size = 1024M

innodb\_buffer\_pool\_instances = 1

innodb\_buffer\_pool\_load\_at\_startup = 1

innodb\_buffer\_pool\_dump\_at\_shutdown = 1

innodb\_data\_file\_path = ibdata1:100M:autoextend

innodb\_flush\_log\_at\_trx\_commit = 1

innodb\_log\_buffer\_size = 32M

innodb\_log\_file\_size = 100M

innodb\_log\_files\_in\_group = 4

innodb\_max\_undo\_log\_size = 100M

innodb\_undo\_directory = /data/undo

innodb\_undo\_tablespaces = 20

\# 根据您的服务器IOPS能力适当调整

\# 一般配普通SSD盘的话，可以调整到 10000 - 20000

\# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000

innodb\_io\_capacity = 4000

innodb\_io\_capacity\_max = 8000

innodb\_flush\_sync = 0

innodb\_flush\_neighbors = 0

innodb\_write\_io\_threads = 8

innodb\_read\_io\_threads = 8

innodb\_purge\_threads = 4

innodb\_page\_cleaners = 4

innodb\_open\_files = 65535

innodb\_max\_dirty\_pages\_pct = 50

innodb\_flush\_method = O\_DIRECT

innodb\_lru\_scan\_depth = 4000

innodb\_checksum\_algorithm = crc32

innodb\_lock\_wait\_timeout = 10

innodb\_rollback\_on\_timeout = 1

innodb\_print\_all\_deadlocks = 1

innodb\_file\_per\_table = 1

innodb\_online\_alter\_log\_max\_size = 4G

innodb\_stats\_on\_metadata = 0

innodb\_undo\_log\_truncate = 1

innodb\_status\_file = 1

\#注意: 开启 innodb\_status\_output & innodb\_status\_output\_locks 后, 可能会导致log-error文件增长较快

innodb\_status\_output = 0

innodb\_status\_output\_locks = 0

\#performance\_schema

performance\_schema = 1

performance\_schema\_instrument = '%memory%=on'

performance\_schema\_instrument = '%lock%=on'

\#innodb monitor

innodb\_monitor\_enable="module\_innodb"

innodb\_monitor\_enable="module\_server"

innodb\_monitor\_enable="module\_dml"

innodb\_monitor\_enable="module\_ddl"

innodb\_monitor\_enable="module\_trx"

innodb\_monitor\_enable="module\_os"

innodb\_monitor\_enable="module\_purge"

innodb\_monitor\_enable="module\_log"

innodb\_monitor\_enable="module\_lock"

innodb\_monitor\_enable="module\_buffer"

innodb\_monitor\_enable="module\_index"

innodb\_monitor\_enable="module\_ibuf\_system"

innodb\_monitor\_enable="module\_buffer\_page"

innodb\_monitor\_enable="module\_adaptive\_hash"

\#组复制设置

\#server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列 transaction\_write\_set\_extraction=XXHASH64

\#告知插件加入或创建组命名UUID

loose-group\_replication\_group\_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

\#server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。

loose-group\_replication\_start\_on\_boot=off

\#告诉插件使用IP地址，端口33061用于接收组中其他成员转入连接

loose-group\_replication\_local\_address="master:33061"

\#启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意

loose-group\_replication\_group\_seeds="master:33061,slave1:33061,slave2:33061" loose-group\_replication\_ip\_whitelist="master,slave1,slave2,manager"

loose-group\_replication\_bootstrap\_group=off

\# 使用MGR的单主模式

loose-group\_replication\_single\_primary\_mode=on

loose-group\_replication\_enforce\_update\_everywhere\_checks=off

disabled\_storage\_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

\[mysqldump\]

quick

max\_allowed\_packet = 32M

8. 初始化安装数据库软件（所有节点操作）

bin/mysqld --initialize  --basedir=/usr/local/mysql--datadir=/data/mysql --user=mysql

注意:以上目录根据具体环境进行修改

9. 配置数据库环境（所有节点操作）

cp /usr/local/mysql/support-files/mysql.server/etc/init.d/mysql

chmod a+x /etc/init.d/mysql

\#加入到开机自启动列表

chkconfig --add mysqld

10.修改root密码（所有节点操作）

alter user root@’localhost’identified by ‘mysql’;

11.创建cluster集群（所有节点操作）

使用mysql-shell登录数据库

bin/mysqlsh

mysql-js>shell.connect('root@localhost:3306');

\#连接成功后

\#配置实例

dba.configureLocalInstance();

\#此时会让选择创建管理cluster的用户，我选1，使用root管理，并且允许远程登陆“%”

\#接着查看实例状态

dba.checkInstanceConfiguration("root@localhost:3306");

如果出现：

You can now use it in an InnoDB Cluster.

"status": "ok"

说明配置成功

12.master创建集群

Tar –zxvf mysql-router-8.0.19-linux-glibc2.12-x86\_64.tar

Tar –zxvf mysql-shell-8.0.19-linux-glibc2.12-x86-64bit.tar

Mv mysql-router-8.0.19-linux-glibc2.12-x86\_64/usr/local/mysql-router

Mv mysql-shell-8.0.19-linux-glibc2.12-x86-64bit/usr/local/mysql-shell

\#登陆master节点的shell，创建cluster

bin/mysqlsh

\# 连接01

mysql-js> shell.connect('root@master:3306');

\# 创建一个 cluster，命名为 'myCluster'

mysql-js> var cluster =dba.createCluster('myCluster');

\# 创建成功后，查看cluster状态

mysql-js> cluster.status();

\#创建后，可以看到master已经添加进cluster，并且状态是读写

mysql-js> cluster.status();

"clusterName": "myCluster",

"defaultReplicaSet": \{

"name": "default",

"primary": "master:3306",

"status": "OK\_NO\_TOLERANCE",

"statusText": "Cluster is NOT tolerant to anyfailures.",

"topology": \{

"master:3306": \{

"address": "master:3306",

"mode": "R/W",

"readReplicas": \{\},

"role": "HA",

"status": "ONLINE"

13.配置slave

先安装好mysql和mysql-shell，注意配置文件中server\_id需要改成

server\_id=2

loose-group\_replication\_local\_address="slave1:33061"

\#登陆shell，执行配置

bin/mysqlsh

mysql-js>shell.connect('root@localhost:3306');

mysql-js> dba.configureLocalInstance();

\#停掉mysql服务，在配置文件my.cnf末尾添加配置：

loose-group\_replication\_allow\_local\_disjoint\_gtids\_join=ON

\#重启mysql后，通过master节点的shell，将slave1添加到cluster：

\# 添加实例

cluster.addInstance('root@slave1:3306');

\# 创建成功后，查看cluster状态

mysql-js> cluster.status();

cluster.status();查不到添加的实例,使用cluster.rescan();

Instance 'manager:3306' is part of the Group Replication group but is not in the metadata. Please use <Cluster>.rescan() to update the metadata.

注意: 和slave1一样，添加slave2节点，注意server\_id和loose-group\_replication\_local\_address需要修改成3和slave2:33061

14.安装mysql-router （所有节点操作）

\#安装router

/usr/local/mysql-route/bin/mysqlrouter--bootstrap root@master:3306 -d myrouter --user=root

\#会在当前目录下产生mysql-router 目录, 并生成router配置文件，默认通过route连接mysql后, 6446端口连接后可以进行读写操作. 6447端口连接后只能进行只读操作

\#启动mysql-route

myrouter/start.sh

15.连接验证Router

a) 管理节点本机mysql-shell连接:

mysqlsh --uri root@localhost:6446

b) 管理节点本机mysql连接:

mysql -u root -h 127.0.0.1 -P 6446 -p

c) 远程客户机通过route连接mysql

mysql -u root -h master\_ip -P 6446 -p

mysql -u root -h savle\_ip -P 6446 -p

### 集群验证 ###

1.登陆后，新建一个表，往里面写进数据，查看从节点数据会不会同步；

2.关闭master的mysql服务，route将主节点自动切换到slave1，slave1从只读变为可读写，重新启动master mysql后，master变为只读模式。

### 集群监控 ###

可用性监控

本节点是不是online:

select member\_state from replication\_group\_members wheremember\_id=@@server\_uuid;

当前节点是不是可以写：

select \* from performance\_schema.global\_variables where variable\_name in('read\_only', 'super\_read\_only');

节点是Online表示属于集群中，正常工作。 节点不可写，表示是Single-master中的非Master节点。

性能监控

复制是不是存在延迟：

对比获得到的GTID和本节点执行的GTID是不是一致：

获取的GTID：

SELECT Received\_transaction\_set FROMperformance\_schema.replication\_connection\_status WHERE Channel\_name ='group\_replication\_applier';

本节点执行的GTID：

select @@gtid\_executed;

远程获取的GTID - 本节点执行的GTID = 延迟的GTID数

本节点执行队列是不是有堆积(大于0表示有延迟）：

select count\_transactions\_in\_queue from replication\_group\_member\_stats wheremember\_id=@@server\_uuid;

流控

在MGR中如果节点落后集群中其它成员太多，就会发起让其它节点等他完成在做的控制，这个叫流控。

当启用： group\_replication\_flow\_control\_mode=QUOTA 是表示启用流控。

流控默认通过两个参数控制：

group\_replication\_flow\_control\_applier\_threshold (默认:25000)

group\_replication\_flow\_control\_certifier\_threshold (默认:25000)

也就说默认延迟在25000个GTID时，会对整个集群Block住写操作。

当然，也可以允许节点延迟，就如同我们主从结构，从节点延迟，不往上面发请求就可以。

关闭Flow control：

set global group\_replication\_flow\_control\_mode='DISABLED';

提示： 关闭流控制，注意查看是不是存在延迟，如果延迟，自已控制阀值不向上面发请求即可。多IDC结构的MGR，建议关闭流控。

参数调优

因为基本复制结构，所有的数据复制，还是逻辑的重放，所以优化也是复制优化点。

并行复制配置：

slave\_parallel\_type -> LOGICAL\_CLOCK

增强sql\_thread个数：

slave\_parallel\_workers -> 2-8

如果CPU瓶颈，网络没问题，减少CPU压缩：

group\_replication\_compression\_threshold = 1000000 -> 2000000

由原来的1M变成2M，再进行压缩（主要针对大事务传述优化）

### mysql-router优化 ###

mysql-router路由是部署在一台机器上，如果该机器宕机，那么mysql-router服务就中断了。

为了解决上述问题，所以要用额外的一台集群来单独部署mysql-router功能。

此时，还将面临mysql-router单点的问题。

为了保证mysql-router的高可用，可部署多一台mysql-router角色。

然后在所有的mysql-router角色上部署keepalived，生成一个vip对外提供服务，实现业务ip的高可用。

## MYSQL日志 ##

### 错误日志 error log ###

错误日志记录着mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。

指定日志路径两种方法:

编辑my.cnf 写入 log-error=\[path\]

通过命令参数错误日志 mysqld\_safe –user=mysql –log-error=\[path\] &

log\_warnings = 1|0 是否记录warnings信息到错误日志中

### 查询日志 general log ###

show variables like '%general\_log%';查询日志是否开启及存储位置

show variables like 'log\_output';参数log\_output控制着查询日志的存储方式

### 慢查询日志 slow\_query\_log ###

慢日志记录执行时间过长和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。

### ★重做日志 redo log ###

作用:

确保事务持久性.redo日志记录事务执行后的状态,用来恢复未写入datafile的已成功事务更新的数据.防止在发生故障的时间点,尚有脏页未写入磁盘,在重启mysql服务的时候根据redo log进行重做,从而达到事务的持久性这一特性.

内容:

物理格式的日志,记录的是物理数据页面修改的信息,其redo log是顺序写入redo log file 的物理文件中去的.

产生时间:

事务开始时产生redo log,落盘并不是随着事务提交才写入的,而是在事务的执行过程中,便开始写入redo log文件中.

释放时间:

当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

物理文件:

默认情况下，对应的物理文件位于数据库的data目录下的ib\_logfile1&ib\_logfile2

innodb\_log\_group\_home\_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。

innodb\_log\_files\_in\_group 指定重做日志文件组中文件的数量，默认2

其他:

之所以说重做日志是在事务开始之后逐步写入重做日志文件，而不一定是事务提交才写入重做日志缓存，原因就是，重做日志有一个缓存区Innodb\_log\_buffer，Innodb\_log\_buffer的默认大小为8M(这里设置的16M),Innodb存储引擎先将重做日志写入innodb\_log\_buffer中。

然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘

·Master Thread 每秒一次执行刷新Innodb\_log\_buffer到重做日志文件。

·每个事务提交时会将重做日志刷新到重做日志文件。

·当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb\_log\_buffer到重做日志文件是Master Thread线程的定时任务。

因此重做日志的写盘，并不一定是随着事务的提交才写入重做日志文件的，而是随着事务的开始，逐步开始的。

### ★回滚日志 undo log ###

作用：

保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

产生时间：

事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

释放时间：

当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

物理文件：

undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

其他：

undo是在事务开始之前保存的被修改数据的一个版本，产生undo日志的时候，同样会伴随类似于保护事务持久化机制的redolog的产生。

### ★二进制日志 bin log ###

作用：

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。

用于数据库的基于时间点的还原

内容：

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都会真相大白。

因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

产生时间：

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin\_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

释放时间：

binlog的默认是保持时间由参数expire\_logs\_days配置，也就是说对于非活动的日志文件，在生成时间超过expire\_logs\_days配置的天数之后，会被自动删除。

物理文件：

配置文件的路径为log\_bin\_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

其他：

二进制日志的作用之一是还原数据库的，这与redo log很类似，很多人混淆过，但是两者有本质的不同

作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。

内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句

另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。

恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

关于事务提交时，redo log和binlog的写入顺序，为了保证主从复制时候的主从一致（当然也包括使用binlog进行基于时间点还原的情况），是要严格一致的，MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

### 中继日志 relay log ###

从其他节点读取到的bin log,最终转成本节点bin log

[5e4ca146e9d451ad4e8e3d753d3f49ad.png]: /images/20221123/65034fb91d2d49d895799c3e6260dbf2.png