热门标签
热门文章
- 1毕设选题 基于机器学习的情感分类与分析算法设计与实现（源码+论文）
- 2uniapp使用uview搭建小程序脚手架_uniapp uview 脚手架
- 3蓝桥杯算法基础（15）：十大排序算法（堆排序）c语言版
- 4苹果笔记本android studio安装教程,MAC下如何安装AndroidStudio_android studio mac
- 5几分钟打造自己的简历生成器，收割offer！_python生成pdf简历
- 6模拟电子技术基础 第二章 常用半导体器件原理_扩散电流和漂移电流
- 7Origin画图技巧之回归预测（拟合）_orign 拟合图
- 8大学考试分数越高学分越多吗_大学之路 | 伯克利学姐讲学分，选课和转专业那些事...
- 9《最新出炉》系列入门篇-Python+Playwright自动化测试-9-页面（page）_playwright page对象能关闭浏览器不
- 10【论文笔记】FastPillars: A Deployment-friendly Pillar-based 3D Detector
当前位置: article > 正文
MySQL数据库（九） 集群 Cluster 和性能优化_cn.af101.wiki
作者：盐析白兔 | 2024-03-16 21:27:00
赞
踩
cn.af101.wiki
6 MySQL 集群 Cluster
6.1 MySQL主从复制
6.1.1 主从复制架构和原理
6.1.1.1 服务性能扩展方式
- Scale Up，向上扩展，垂直扩展 （给予更好的配置）
- Scale Out，向外扩展，横向扩展 （更多的服务器）
6.1.1.2 MySQL的扩展
- 读写分离
- 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
6.1.1.3 复制的功用
- 数据分布
- 负载均衡读
- 备份
- 高可用和故障切换
- MySQL升级测试
6.1.1.4 复制架构
一主一从复制架构
一主多从复制架构
6.1.1.5 主从复制原理（面试）
主从复制相关线程
主节点：必须开启二进制日志功能
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：建议开启二进制日志功能，可以预防主服务器挂掉，上位
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
- master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
- relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
范例: 中继日志
[root@slave ~]#file /var/lib/mysql/mariadb-relay-bin.000001
/var/lib/mysql/mariadb-relay-bin.000001: MySQL replication log, server id 18 MySQL V5+, server version 10.3.17-MariaDB-log
[root@slave ~]#mysqlbinlog /var/lib/mysql/mariadb-relay-bin.000001|head
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200615 17:58:48 server id 18 end_log_pos 256 CRC32 0x7bd00c79 Start:
binlog v 4, server v 10.3.17-MariaDB-log created 200615 17:58:48
BINLOG '
WEbnXg8cAAAA/AAAAAABAAAAAAQAMTAuMy4xNy1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
6.1.1.6 主从复制特点
- 异步复制
- 主从数据不一致比较常见
6.1.1.7 各种复制架构
- 一Master/一Slave
- 一主多从
- 从服务器还可以再有从服务器（级联，复制延迟大）
- Master/Master
- 一从多主:适用于多个不同数据库
- 环状复制
复制需要考虑二进制日志事件记录格式
- STATEMENT（5.0之前）会造成主从数据不一致
- ROW（5.1之后，推荐）
- MIXED
6.1.2 实现主从复制配置
参考官网
https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
主节点配置：
(1) 启用二进制日志
[mysqld]
log_bin
- 1
- 2
(2) 为当前节点设置一个全局惟一的ID号
[mysqld]
server-id=#
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名
- 1
- 2
- 3
说明：
server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接
(3) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
- 1
(4) 查看从二进制日志的文件和位置开始进行复制
SHOW MASTER LOG;
- 1
从节点配置：
(1) 启动中继日志
[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
- 1
- 2
- 3
- 4
- 5
- 6
(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TO MASTER_HOST='masterhost',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
范例：新建主从复制
#主节点 [root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin [root@master ~]#systemctl restart mariadb [root@master ~]#mysql MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'magedu'; #查看二进制文件和位置 MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28052 | | mariadb-bin.000002 | 545 | +--------------------+-----------+ 2 rows in set (0.001 sec) #从节点 [root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 [root@slave ~]#systemctl restart mariadb [root@slave1 ~]#mysql MariaDB [(none)]> help change master to MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.8.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=545; MariaDB [(none)]> start slave; Query OK, 0 rows affected, 1 warning (0.000 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 26987890 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 26987902 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 26987890 Relay_Log_Space: 26988213 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 #复制的延迟时间 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 8 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 34 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 100006 1 row in set (0.000 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
范例：主服务器非新建时,主服务器运行一段时间后，新增从节点服务器
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
- 通过备份恢复数据至从服务器
- 复制起始位置为备份时，二进制日志文件及其POS
主服务器： [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin [root@centos8 ~]#systemctl restart mariadb [root@centos8 ~]#ll /var/lib/mysql/ #多出两个文件 -rw-rw---- 1 mysql mysql 330 Jun 14 09:50 mariadb-bin.000001 -rw-rw---- 1 mysql mysql 21 Jun 14 09:50 mariadb-bin.index #查看日志节点 MariaDB [(none)]> show master logs; #先查看节点，再创建账户 +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 330 | +--------------------+-----------+ 1 row in set (0.000 sec) #主节点上要建立一个参与复制的账户 MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu'; #repluser是用户名，10.0.0.%代表可以从这个网段中任何一个主机连接，identified by 指定密码 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) [root@centos8 ~]#mkdir /backup/ [root@centos8 ~]#mysqldump -A --single-transaction --master-data=1 -F > /backup/all.sql [root@centos8 ~]#ll /backup/all.sql -rw-r--r-- 1 root root 487711 Jun 14 09:55 /backup/all.sql [root@centos8 ~]#vim /backup/all.sql CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375; #复制数据库 [root@centos8 ~]#scp /backup/all.sql 10.0.0.18:/data 从服务器： [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 [root@centos8 ~]#systemctl start mariadb #主从复制的配置 方法一： [root@centos8 ~]#vim /data/all.sql CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */; 方法二： MariaDB [(none)]> help change master to MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375; #还原数据库和复制信息 [root@centos8 ~]#mysql < /data/all.sql #此时复制信息已经准备好了 MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 375 Relay_Log_File: mariadb-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: No #I/O线程未启用 Slave_SQL_Running: No #SQL线程未启用 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 375 Relay_Log_Space: 256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL #复制延期，主节点和从节点复制差了多长时间，注意观察，最好是0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) #查看新增加的文件 [root@centos8 ~]#ll /var/lib/mysql/ -t total 122952 -rw-rw---- 1 mysql mysql 50331648 Jun 14 10:31 ib_logfile0 -rw-rw---- 1 mysql mysql 12582912 Jun 14 10:31 ibdata1 drwx------ 2 mysql mysql 4096 Jun 14 10:31 mysql drwx------ 2 mysql mysql 272 Jun 14 10:31 hellodb -rw-rw---- 1 mysql mysql 56 Jun 14 10:31 relay-log.info #二进制日志和中继日志的对应关系 -rw-rw---- 1 mysql mysql 256 Jun 14 10:31 mariadb-relay-bin.000001 -rw-rw---- 1 mysql mysql 27 Jun 14 10:31 mariadb-relay-bin.index #复制下来的中继日志 -rw-rw---- 1 mysql mysql 154 Jun 14 10:31 master.info #主节点的信息 #启用两个线程 MariaDB [(none)]> show processlist; #这是从节点启用前的进程 +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 9 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+ 6 rows in set (0.000 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 572 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 754 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes #线程已开启 Slave_SQL_Running: Yes #线程已开启 Seconds_Behind_Master: 0 #复制延迟为0；说明已经复制成功 ...省略... MariaDB [(none)]> show processlist; #这是从节点的进程 +----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+ | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 13 | system user | | NULL | Slave_IO | 143 | Waiting for master to send event | NULL | 0.000 | | 14 | system user | | NULL | Slave_SQL | 143 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 | | 15 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | +----+-------------+-----------+------+-----------+------+-----------------------------------------------------------------------------+------------------+----------+ 8 rows in set (0.000 sec) MariaDB [(none)]> show processlist; #从节点启用后，主节点上的Dump线程同时启用 +----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 11 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | | 12 | repluser | 10.0.0.18:55922 | NULL | Binlog Dump | 323 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | 0.000 | +----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+----------+ 7 rows in set (0.000 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
简洁步骤：
#在主服务器完全备份 [root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql [root@master ~]#ll /backup/ total 2988 -rw-r--r-- 1 root root 3055918 Nov 27 17:41 fullbackup_2019-11-27_17:41:17.sql [root@master ~]#scp /backup/fullbackup_2019-11-27_17\:41\:17.sql 192.168.8.11:/data/ #建议优化主和从节点服务器的性能 #global innodb_flush_log_at_trx_commit=2 #sync_binlog=0 MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2; Query OK, 0 rows affected (0.001 sec) MariaDB [hellodb]> show variables like 'sync_binlog'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | +---------------+-------+ 1 row in set (0.001 sec) #将完全备份还原到新的从节点 [root@slave ~]#dnf -y install mariadb-server [root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=11 read-only [root@slave ~]#systemctl restart mariadb #配置从节点，从完全备份的位置之后开始复制 [root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup_2019-11-27_17\:41\:17.sql CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389; [root@slave ~]#vim /data/fullbackup_2019-11-27_17\:41\:17.sql CHANGE MASTER TO MASTER_HOST='192.168.8.10', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389; [root@slave ~]#mysql < /data/fullbackup_2019-11-27_17\:41\:17.sql [root@slave ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.11-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.8.10 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000003 Read_Master_Log_Pos: 389 Relay_Log_File: mariadb-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mariadb-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 389 Relay_Log_Space: 256 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) MariaDB [(none)]> start slave;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
6.1.3 主从复制相关
-
限制从服务器为只读
read_only=ON #注意：此限制对拥有SUPER权限的用户均无效- 1
- 2
注意：以下命令会阻止所有用户, 包括主服务器复制的更新
FLUSH TABLES WITH READ LOCK;- 1
-
在从节点清除信息
注意：以下都需要先 STOP SLAVE
RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和PASSWORD 等- 1
- 2
-
复制错误解决方法
可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID
#系统变量，指定跳过复制事件的个数 SET GLOBAL sql_slave_skip_counter = N #服务器选项，只读系统变量，指定跳过事件的ID [mysqld] slave_skip_errors=1007|ALL- 1
- 2
- 3
- 4
- 5
- 6
-
START SLAVE 语句，指定执到特定的点
START SLAVE [thread_types] START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos thread_types: [thread_type [, thread_type] ... ] thread_type: IO_THREAD | SQL_THREAD- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
范例：复制冲突的解决
#方法1
MariaDB [(none)]> stop slave;
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> start slave;
#方法2
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL
[root@slave1 ~]#systemctl restart mariadb
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
保证主从复制的事务安全
参看https://mariadb.com/kb/en/library/server-system-variables/
在master节点启用参数：sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差 #如果用到的为InnoDB存储引擎： innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘 innodb_support_xa=ON #分布式事务MariaDB10.3.0废除 sync_master_info=# #次事件后master.info同步到磁盘- 1
- 2
- 3
- 4
- 5
在slave节点启用服务器选项：
skip-slave-start=ON #不自动启动slave- 1
在slave节点启用参数：
sync_relay_log=# #次写后同步relay log到磁盘 sync_relay_log_info=# #次事务后同步relay-log.info到磁盘- 1
- 2
范例：当master服务器宕机，提升一个slave成为新的master
#找到哪个从节点的数据库是最新，让它成为新master [root@centos8 ~]#cat /var/lib/mysql/relay-log.info 5 ./mariadb-relay-bin.000002 1180 mysql-bin.000002 996 0 #新master修改配置文件，关闭read-only配置 [root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 read-only=OFF log-bin=/data/mysql/logbin/mysql-bin #清除旧的master复制信息 MariaDB [hellodb]>set global read_only=off; MariaDB [hellodb]>stop slave; MariaDB [hellodb]>reset slave all; #在新master上完全备份 [root@slave1 ~]#mysqldump -A --single-transaction --master-data=1 -F > backup.sql [root@slave1 ~]#scp backup.sql 10.0.0.28: #分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据 #其它所有 slave 重新还原数据库，指向新的master [root@slave2 ~]#vim backup.sql CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=371; MariaDB [hellodb]>stop slave; MariaDB [hellodb]>reset slave all; MariaDB [hellodb]>set sql_log_bin=off; MariaDB [hellodb]>source backup.sql; MariaDB [hellodb]>set sql_log_bin=on; MariaDB [hellodb]>start slave;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
6.1.4 实现级联复制
需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制
[mysqld]
server-id=18
log_bin
log_slave_updates
read-only
- 1
- 2
- 3
- 4
- 5
案例：三台主机实现级联复制
#在10.0.0.8充当master #在10.0.0.18充当级联slave #在10.0.0.28充当slave #在master实现 [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin [root@centos8 ~]#systemctl restart mariadb [root@centos8 ~]#mysql MariaDB [(none)]> show master logs; #先查看节点，再创建账户，这样复制过去的日志里就有创建账号的记录 +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28198 | | mariadb-bin.000002 | 541 | +--------------------+-----------+ MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu'; [root@centos8 ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql [root@centos8 ~]#scp /data/all.sql 10.0.0.18:/data [root@centos8 ~]#scp /data/all.sql 10.0.0.28:/data #在中间级联slave实现 [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 log-bin read-only log_slave_updates #级联复制中间节点的必选项 [root@centos8 ~]#systemctl restart mariadb #还原数据库 [root@centos8 ~]#vim /data/all.sql CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=541; [root@centos8 ~]#mysql MariaDB [(none)]> set sql_log_bin=0; MariaDB [(none)]> source /data/all.sql MariaDB [(none)]> show master logs; #记录二进制位置，给第三个节点使用 +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28200 | | mariadb-bin.000002 | 471 | +--------------------+-----------+ MariaDB [(none)]> set sql_log_bin=0; MariaDB [(none)]> start slave; #在第三个节点slave上实现 [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=28 read-only [root@centos8 ~]#systemctl restart mariadb [root@centos8 ~]#vim /data/all.sql CHANGE MASTER TO MASTER_HOST='10.0.0.18', #中间节点的IP MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=471; [root@centos8 ~]#mysql < /data/all.sql [root@centos8 ~]#mysql -e 'start slave;'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
6.1.5 主主复制
注意：统一版本，配置上时主主复制，实际当成一主一从，即可以考虑设置为一读一写，这样在主服务器宕掉的情况下，从服务器可以迅速上位，两边配置基本一样，只有读写不同
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
- 1
- 2
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2
- 1
- 2
主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号（在复制数据之后，二进制日志里就记录了创建过程）
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程
范例：实现两个节点的主主复制模型
检查两个数据库是否都是新装，若有一个有数据，那就先备份
备份建议加时间
help change master to
再导入数据前关闭二进制日志功能
开启线程
二进制日志没增长，数据却复制过来了，原因是：
主服务器：dump线程
从服务器：iothread线程、sqlthread线程
本机的二进制日志只记录本机收到的二进制修改，别的机器传来的不影响本机的二进制日志
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
#在第一个master节点上实现 [root@master1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 #指定id log-bin #开启二进制日志 auto_increment_offset=1 #开始点 auto_increment_increment=2 #增长幅度 [root@master1 ~]#systemctl start mariadb [root@master1 ~]#mysql MariaDB [(none)]>show master logs; #记录位置 +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 330 | +--------------------+-----------+ 1 row in set (0.000 sec) #建立复制账号，此操作是在记录位置之后发生的，所以账号就复制过去了，另一个服务器就不需要再创建，两边用同一个账号进行彼此复制 MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu'; #在第二个master节点上实现 [rootmaster2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 log-bin auto_increment_offset=2 #开始点 auto_increment_increment=2 #增长幅度 [root@master2 ~]#systemctl start mariadb [root@master2 ~]#mysql MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=330; Query OK, 0 rows affected (0.019 sec) MariaDB [(none)]> start slave; #开启进程，实现了单向复制 Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> show master logs; #查看二进制位置 MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 330 | +--------------------+-----------+ 1 row in set (0.000 sec) #在第一个master节点上实现 MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=330; Query OK, 0 rows affected (0.007 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> create database db1; MariaDB [(none)]> use db1 MariaDB [db1]> create table t1(id int auto_increment primary key,name char(10)); #两个节点分别插入数据 #在第一个节点上执行，奇数增长， MariaDB [db1]> create database db1; MariaDB [db1]> insert t1 (name) values('user1'); #在第二个节点上执行，偶数增长 MariaDB [db1]> insert t1 (name) values('user2'); #两个节点同时插入数据 MariaDB [db1]> insert t1 (name) values('userX'); MariaDB [db1]> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | user1 | | 2 | user2 | | 3 | userX | | 4 | userX | +----+-------+ 4 rows in set (0.001 sec) #两个节点同时创建数据库，发生复制冲突 MariaDB [db1]> create database db2; MariaDB [db1]> show slave status\G
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
6.1.6 半同步复制
默认情况下，MySQL的复制功能是异步的（客户端更新数据，主服务器收到后立刻返回成功结果，但此时数据还并没有复制到从服务器上），异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失
半同步复制实现：
客户端发请求写操作发送给主服务器，主服务器在自己服务器上更新，找一个从节点复制且必须复制成功（也就是所有从节点中至少有一个成功），最后主服务器把成功结果返还给客户端
说明：半同步机制里，任何一个从节点超过10秒（默认）没复制成功，主服务器就告诉客户数据已经更新成功
官方文档： https://mariadb.com/kb/en/library/semisynchronous-replication/
范例：CentOS 8 在Mariadb-10.3.11上实现 实现半同步复制
#在master实现，启用半同步功能 [root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin plugin-load-add = semisync_master rpl_semi_sync_master_enabled=ON rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端 [root@centos8 ~]#systemctl restart mariadb MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; +---------------------------------------+--------------+ | Variable_name | Value | +---------------------------------------+--------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 3000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------------+--------------+ 9 rows in set (0.002 sec) MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_get_ack | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_request_ack | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_send_ack | 1 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 18 rows in set (0.001 sec) MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28198 | | mariadb-bin.000002 | 344 | +--------------------+-----------+ 2 rows in set (0.000 sec) MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu'; Query OK, 0 rows affected (3.001 sec) #在其它所有slave节点上都实现，启用半同步功能 [root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 plugin_load_add = semisync_slave rpl_semi_sync_slave_enabled=ON [root@slave ~]#systemctl restart mariadb [root@slave ~]#mysql MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------------+--------------+ | Variable_name | Value | +---------------------------------------+--------------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------------+--------------+ 9 rows in set (0.001 sec) MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_get_ack | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_request_ack | 0 | | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_send_ack | 1 | | Rpl_semi_sync_slave_status | ON | +--------------------------------------------+-------+ 18 rows in set (0.001 sec) MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='magedu', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000002', -> MASTER_LOG_POS=344; Query OK, 0 rows affected (0.008 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 541 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 754 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 541 Relay_Log_Space: 1065 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 8 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 1 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec) #在master上实现 MariaDB [db1]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | | Rpl_semi_sync_master_get_ack | 3 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 3 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 1 | | Rpl_semi_sync_master_request_ack | 2 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 3495 | | Rpl_semi_sync_master_tx_wait_time | 3495 | | Rpl_semi_sync_master_tx_waits | 1 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 1 | | Rpl_semi_sync_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 18 rows in set (0.002 sec) #测试 #在master实现，创建数据库，立即成功 MariaDB [db1]> create database db2; Query OK, 1 row affected (0.004 sec) #在所有slave节点实现，停止复制线程 MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.011 sec) #在master实现，创建数据库，等待3s才能成功 MariaDB [db1]> create database db3; Query OK, 1 row affected (3.003 sec) #在任意一个slave节点实现，恢复复制线程 MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.006 sec) #在master实现，创建数据库，立即成功 MariaDB [db1]> create database db4; Query OK, 1 row affected (0.002 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
范例：CentOS 7 实现半同步复制
#主服务器配置: INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; UNINSTALL PLUGIN rpl_semi_sync_master ; SHOW PLUGINS; #查看插件 SET GLOBAL rpl_semi_sync_master_enabled=1; SET GLOBAL rpl_semi_sync_master_timeout = 1000; #超时长1s,默认值为10s SHOW GLOBAL VARIABLES LIKE '%semi%'; SHOW GLOBAL STATUS LIKE '%semi%'; #从服务器配置: INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; SET GLOBAL rpl_semi_sync_slave_enabled=1; #mariadb-10.3版以后 #主服务器配置: [mysqld] plugin_load_add = semisync_master #从服务器配置: [mysqld] plugin_load_add = semisync_slave
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
6.1.7 复制过滤器
让从节点仅复制指定的数据库，或指定数据库的指定表
复制过滤器两种实现方式：
(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
此方法存在的问题：基于二进制还原将无法实现；不建议使用
注意：此项和binlog_format相关
参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
vim /etc/my.cnf
binlog-do-db = #数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-ignore-db = #数据库黑名单列表，不记录二进制
#以上两个都是服务器选项，只能改配置文件
- 1
- 2
- 3
- 4
范例：
#主服务器修改配置文件，修改为只有hellodb可以记录二进制日志 MariaDB [(none)]> show master status; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000003 | 344 | hellodb | | +--------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) #主服务器上查看hellodb库中teachers表并插入新的一行 MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.001 sec) MariaDB [hellodb]> insert teachers (name,age,gender)values('mage',50,'M'); Query OK, 1 row affected (0.003 sec) #从服务器上能查看到新的一行 MariaDB [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | mage | 50 | M | +-----+---------------+-----+--------+ 5 rows in set (0.000 sec) #主服务器上创建一个新表 MariaDB [hellodb]> create database db5; Query OK, 1 row affected (0.000 sec) #从服务器上查看不到 MariaDB [hellodb]> show databases; +--------------------+ | Database | +--------------------+ | db1 | | db2 | | db3 | | db4 | | hellodb | | information_schema | | mysql | | performance_schema | +--------------------+ 8 rows in set (0.001 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
注意：
This option will not work with cross-database updates with statement-based
logging. See the Statement-Based Logging section for more information.
This option can not be set dynamically.
When setting it on the command-line or in a server option group in an option file, the option does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the option multiple times.
- 1
- 2
- 3
- 4
- 5
(2) 从服务器SQL_THREAD在relay log（中继日志）中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
此方法存在的问题：会造成网络及磁盘IO浪费
从服务器上的复制过滤器相关变量
replicate_do_db=db1,db2,db3 #指定复制库的白名单，变量可以指定逗号分隔的多个值，选项不支持多值,只能分别写多行实现
replicate_ignore_db= #指定复制库黑名单，既是服务器选项又是变量
replicate_do_table= #指定复制表的白名单
replicate_ignore_table= #指定复制表的黑名单
replicate_wild_do_table= foo%.bar% #支持通配符
replicate_wild_ignore_tablemysql=
- 1
- 2
- 3
- 4
- 5
- 6
When setting it dynamically with SET GLOBAL, the system variable accepts a
comma-separated list of filters.
When setting it on the command-line or in a server option group in an option file, the system variable does not accept a comma-separated list. If you would like to specify multiple filters, then you need to specify the system variable multiple times.
- 1
- 2
- 3
注意：跨库的更新将无法同步
范例:
[mysqld]
replicate_do_db=db1
replicate_do_db=db2
replicate_do_db=db3
- 1
- 2
- 3
- 4
MariaDB [db1]> create table db2.t1(id int);
Query OK, 0 rows affected (0.010 sec)
- 1
- 2
6.1.8 主从复制加密
基于SSL复制：在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性
官网文档：https://mariadb.com/kb/en/library/replication-with-secure-connections/
实现MySQL复制加密
- 生成 CA 及 master 和 slave 的证书
[root@master ~]#mkdir /etc/my.cnf.d/ssl/ [root@master ~]#cat ca.sh #!/bin/bash . /etc/init.d/functions CERT_INFO=([00]="/O=magedu/CN=ca.magedu.org" \ [01]="cakey.pem" \ [02]="cacert.pem" \ [03]=2048 \ [04]=3650 \ [05]=0 \ [10]="/C=CN/ST=hubei/L=wuhan/O=magedu/CN=master.magedu.org" \ [11]="master.key" \ [12]="master.crt" \ [13]=2048 \ [14]=365 [15]=1 \ [16]="master.csr" \ [20]="/C=CN/ST=hubei/L=wuhan/O=magedu/CN=slave.cuiqinghe.org" \ [21]="slave.key" \ [22]="slave.crt" \ [23]=2048 \ [24]=365 \ [25]=2 \ [26]="slave.csr" ) COLOR="echo -e \\E[1;32m" END="\\E[0m" DIR=/data cd $DIR for i in {0..2};do if [ $i -eq 0 ] ;then openssl req -x509 -newkey rsa:${CERT_INFO[${i}3]} -subj ${CERT_INFO[${i}0]} \ -set_serial ${CERT_INFO[${i}5]} -keyout ${CERT_INFO[${i}1]} -nodes -days ${CERT_INFO[${i}4]} \ -out ${CERT_INFO[${i}2]} &>/dev/null else openssl req -newkey rsa:${CERT_INFO[${i}3]} -nodes -subj ${CERT_INFO[${i}0]} \ -keyout ${CERT_INFO[${i}1]} -out ${CERT_INFO[${i}6]} &>/dev/null openssl x509 -req -in ${CERT_INFO[${i}6]} -CA ${CERT_INFO[02]} -CAkey ${CERT_INFO[01]} \ -set_serial ${CERT_INFO[${i}5]} -days ${CERT_INFO[${i}4]} -out ${CERT_INFO[${i}2]} &>/dev/null fi $COLOR"**************************************生成证书信息**************************************"$END openssl x509 -in ${CERT_INFO[${i}2]} -noout -subject -dates -serial echo done chmod 600 *.key action "证书生成完成" #执行脚本生成证书 [root@master ~]#bash ca.sh **************************************生成证书信息************************************** subject=O = magedu, CN = ca.magedu.org notBefore=Jun 16 07:27:05 2020 GMT notAfter=Jun 14 07:27:05 2030 GMT serial=00 **************************************生成证书信息************************************** subject=C = CN, ST = hubei, L = wuhan, O = magedu, CN = master.magedu.org notBefore=Jun 16 07:27:05 2020 GMT notAfter=Jun 16 07:27:05 2021 GMT serial=01 **************************************生成证书信息************************************** subject=C = CN, ST = hubei, L = wuhan, O = magedu, CN = slave.cuiqinghe.org notBefore=Jun 16 07:27:05 2020 GMT notAfter=Jun 16 07:27:05 2021 GMT serial=02 证书生成完成 [ OK ] [root@master ~]#tree /data/ /data/ ├── cacert.pem ├── cakey.pem ├── master.crt ├── master.csr ├── master.key ├── slave.crt ├── slave.csr └── slave.key 0 directories, 8 files [root@master ~]#cp /data/* /etc/my.cnf.d/ssl/ [root@master ~]#cd /etc/my.cnf.d/ssl/ [root@master ssl]#ll total 32 -rw-r--r-- 1 root root 1143 Jun 16 15:28 cacert.pem -rw------- 1 root root 1704 Jun 16 15:28 cakey.pem -rw-r--r-- 1 root root 1090 Jun 16 15:28 master.crt -rw-r--r-- 1 root root 985 Jun 16 15:28 master.csr -rw------- 1 root root 1704 Jun 16 15:28 master.key -rw-r--r-- 1 root root 1090 Jun 16 15:28 slave.crt -rw-r--r-- 1 root root 989 Jun 16 15:28 slave.csr -rw------- 1 root root 1704 Jun 16 15:28 slave.key [root@master ssl]#chown -R mysql.mysql /etc/my.cnf.d/ssl/ [root@master ssl]#ll total 32 -rw-r--r-- 1 mysql mysql 1143 Jun 16 15:28 cacert.pem -rw------- 1 mysql mysql 1704 Jun 16 15:28 cakey.pem -rw-r--r-- 1 mysql mysql 1090 Jun 16 15:28 master.crt -rw-r--r-- 1 mysql mysql 985 Jun 16 15:28 master.csr -rw------- 1 mysql mysql 1704 Jun 16 15:28 master.key -rw-r--r-- 1 mysql mysql 1090 Jun 16 15:28 slave.crt -rw-r--r-- 1 mysql mysql 989 Jun 16 15:28 slave.csr -rw------- 1 mysql mysql 1704 Jun 16 15:28 slave.key
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 主服务器开启 SSL，配置证书和私钥路径
[mysqld] log-bin server_id=1 ssl ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ssl-cert=/etc/my.cnf.d/ssl/master.crt ssl-key=/etc/my.cnf.d/ssl/master.key MariaDB [(none)]> show variables like '%ssl%'; +---------------------+----------------------------------+ | Variable_name | Value | +---------------------+----------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/my.cnf.d/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /etc/my.cnf.d/ssl/master.crt | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /etc/my.cnf.d/ssl/master.key | | version_ssl_library | OpenSSL 1.1.1c FIPS 28 May 2019 | +---------------------+----------------------------------+ 10 rows in set (0.001 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 创建一个要求必须使用 SSL 连接的复制账号
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.%' IDENTIFIED BY 'magedu' REQUIRE SSL;
- 1
- 从服务器slave上使用CHANGER MASTER TO 命令时指明ssl相关选项
[root@slave1 ~]#mysql -urepluser -pmagedu -h10.0.0.8 ERROR 1045 (28000): Access denied for user 'repluser'@'10.0.0.18' (using password: YES) [root@centos8 ~]#mysql -urepluser -pmagedu -h10.0.0.8 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key MariaDB [(none)]> MariaDB [(none)]> \s -------------- mysql Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 53 Current database: Current user: repluser@10.0.0.18 SSL: Cipher in use is TLS_AES_256_GCM_SHA384 #已加密 Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.3.17-MariaDB-log MariaDB Server Protocol version: 10 Connection: 10.0.0.8 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 23 min 4 sec Threads: 10 Questions: 11 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.007 -------------- #主服务器上查看进程 MariaDB [(none)]> show processlist; +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 9 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | | 53 | repluser | 10.0.0.18:56006 | NULL | Sleep | 35 | | NULL | 0.000 | +----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+ 7 rows in set (0.001 sec) [root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=2 #可选方式1 ssl ssl-ca=/etc/my.cnf.d/ssl/cacert.pem ssl-cert=/etc/my.cnf.d/ssl/slave.crt ssl-key=/etc/my.cnf.d/ssl/slave.key MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344, MASTER_SSL=1; #可选方式2 MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344, MASTER_SSL=1, MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key'; MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 682 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 895 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 682 Relay_Log_Space: 1206 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes 1 row in set (0.000 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
6.1.9 GTID复制
GTID复制：（Global Transaction ID 全局事务标识符） MySQL 5.6 版本开始支持，GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog文件名和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找到相应的位置开始同步
GTID 架构
GTID = server_uuid:transaction_id，在一组复制中，全局唯一
server_uuid 来源于 /var/lib/mysql/auto.cnf
GTID服务器相关选项
gtid_mode #gtid模式
enforce_gtid_consistency #保证GTID安全的参数
- 1
- 2
GTID配置范例
- 主服务器
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin #可选
gtid_mode=ON
enforce_gtid_consistency
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by 'magedu';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 从服务器
vim /etc/my.cnf server-id=2 gtid_mode=ON enforce_gtid_consistency mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.100', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_AUTO_POSITION=1; mysql>start slave; #主服务器上的日志节点 mysql> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | centos8-bin.000001 | 177 | | centos8-bin.000002 | 437 | | centos8-bin.000003 | 448 | +--------------------+-----------+ 3 rows in set (0.00 sec) #从服务器 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.8 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: centos8-bin.000003 Read_Master_Log_Pos: 448 Relay_Log_File: centos8-relay-bin.000002 Relay_Log_Pos: 665 Relay_Master_Log_File: centos8-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 448 Relay_Log_Space: 874 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: f61f784f-afbe-11ea-8710-000c2930800a Master_Info_File: /data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: f61f784f-afbe-11ea-8710-000c2930800a:1 Executed_Gtid_Set: f61f784f-afbe-11ea-8710-000c2930800a:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
6.1.10 复制的监控和维护
(1) 清理日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL] #清理二进制日志在从节点上生成的信息
- 1
- 2
- 3
(2) 复制监控
SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST
- 1
- 2
- 3
- 4
- 5
(3) 从服务器是否落后于主服务
Seconds_Behind_Master：0 #0代表主节点和从节点完全同步
- 1
(4) 如何确定主从节点数据是否一致
percona-toolkit
(5) 数据不一致如何修复
删除从数据库，重新复制
6.1.11 复制的问题和解决方案
6.1.11.1 数据损坏或丢失
- Master：MHA + semisync replication
- Slave： 重新复制
6.1.11.2 不惟一的server id
重新复制
6.1.11.3 复制延迟
- 需要额外的监控工具的辅助
- 一从多主：mariadb10 版后支持
- 多线程复制：对多个数据库复制
6.1.11.4 MySQL主从数据不一致
-
造成主从不一致的原因
- 主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。
- 主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。
- 从节点未设置只读，误操作写入数据
- 主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致
- 主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能
- MySQL自身bug导致
-
主从不一致修复方法
-
将从库重新实现
虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。 -
使用percona-toolkit工具辅助
PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用
关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html
-
-
手动重建不一致的表
在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的
范例：A,B,C这三张表主从数据不一致
1、从库停止Slave复制 mysql>stop slave; 2、在主库上dump这三张表，并记录下同步的binlog和POS点 mysqldump -uroot -pmagedu -q --single-transaction --master-data=2 testdb A B C >/backup/A_B_C.sql 3、查看A_B_C.sql文件，找出记录的binlog和POS点 head A_B_C.sql 例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666; 4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置 mysql>start slave until MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666; #以上指令是为了保障其他表的数据不丢失，一直同步，直到同步完那个点结束，A,B,C表的数据在之前的备份已经生成了一份快照，只需要导入进入，然后开启同步即可 5、在Slave机器上导入A_B_C.sql mysql -uroot -pmagedu testdb mysql>set sql_log_bin=0; mysql>source /backup/A_B_C.sql mysql>set sql_log_bin=1; 6、导入完毕后，从库开启同步即可。 mysql>start slave;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 如何避免主从不一致
- 主库binlog采用ROW格式
- 主从实例数据库版本保持一致
- 主库做好账号权限把控，不可以执行set sql_log_bin=0
- 从库开启只读，不允许人为写入
- 定期进行主从一致性检验
6.2 MySQL 中间件代理服务器
6.2.1 关系型数据库和 NoSQL 数据库
数据库主要分为两大类：关系型数据库与 NoSQL 数据库。
-
关系型数据库，是建立在关系模型基础上的数据库，其借助于集合代数等数学概念和方法来处理数据库中的数据。主流的 MySQL、Oracle、MS SQL Server 和 DB2 都属于这类传统数据库。
-
NoSQL 数据库，全称为 Not Only SQL，意思就是适用关系型数据库的时候就使用关系型数据库，不适用的时候也没有必要非使用关系型数据库不可，可以考虑使用更加合适的数据存储。主要分为临时性键值存储 （memcached、Redis）、永久性键值存储（ROMA、Redis）、面向文档的数据库（MongoDB、CouchDB）、面向列的数据库（Cassandra、HBase），每种 NoSQL 都有其特有的使用场景及优点。
Oracle，mysql 等传统的关系数据库非常成熟并且已大规模商用，为什么还要用 NoSQL 数据库呢？主要是由于随着互联网发展，数据量越来越大，对性能要求越来越高，传统数据库存在着先天性的缺陷，即单机（单库）性能瓶颈，并且扩展困难。这样既有单机单库瓶颈，却又扩展困难，自然无法满足日益增长的海量数据存储及其性能要求，所以才会出现了各种不同的 NoSQL 产品，NoSQL 根本性的优势在于在云计算时代，简单、易于大规模分布式扩展，并且读写性能非常高
RDBMS和NOSQL的特点及优缺点：
6.2.2 数据切分
简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机） 上面，以达到分散单台设备负载的效果。
数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。
一种是按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据 表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。
垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小， 业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。 根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。
水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中， 对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些。
6.2.2.1 垂直切分
表之间不能有连接关系
一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：
系统被切分成了，用户，订单交易，支付几个模块。 一个架构设计较好的应用系统，其总体功能肯定是由很多个功能模块所组成的，而每一个功能模块所需要的数据对应到数据库中就是一个或者多个表。而在架构设计中，各个功能模块相互之间的交互点越统一越少，系统的耦合度就越低，系统各个模块的维护性以及扩展性也就越好。这样的系统，实现数据的垂直切分也就越容易。
但是往往系统之有些表难以做到完全独立，存在着跨库 join 的情况，对于这类表，就需要去做平衡，是数据库让步业务，共用一个数据源，还是分成多个库，业务之间通过接口来做调用。在系统初期数据量比较 少，或者资源有限的情况下，会选择共用数据源，但是当数据发展到了一定的规模，负载很大的情况，就需要必须去做分割。
一般来讲业务存在着复杂 join 的场景是难以切分的，往往业务独立的易于切分。如何切分，切分到何种程度是考验技术架构的一个难题。
垂直切分的优缺点：
优点：
- 拆分后业务清晰，拆分规则明确
- 系统之间整合或扩展容易
- 数据维护简
缺点：
- 部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度；
- 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高
- 事务处理复杂
由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶颈，所以就需要水平拆分来做解决。
6.2.2.2 水平切分
对应shard中查询相关数据
相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分 到一个数据库，而另外的某些行又切分到其他的数据库中，如图：
拆分数据就需要定义分片规则。关系型数据库是行列的二维模型，拆分的第一原则是找到拆分维度。比如： 从会员的角度来分析，商户订单交易类系统中查询会员某天某月某个订单，那么就需要按照会员结合日期来拆分， 不同的数据按照会员 ID 做分组，这样所有的数据查询 join 都会在单库内解决；如果从商户的角度来讲，要查询某 个商家某天所有的订单数，就需要按照商户 ID 做拆分；但是如果系统既想按会员拆分，又想按商家数据，则会有 一定的困难。如何找到合适的分片规则需要综合考虑衡量。
几种典型的分片规则包括：
- 按照用户 ID 求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中
- 按照日期，将不同月甚至日的数据分散到不同的库中
- 按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中
如图，切分原则都是根据业务找到适合的切分规则分散到不同的库，下面用用户 ID 求模举例：
既然数据做了拆分有优点也就优缺点。
优点：
- 拆分规则抽象好，join 操作基本可以数据库做
- 不存在单库大数据，高并发的性能瓶颈
- 应用端改造较少
- 提高了系统的稳定性跟负载能力
缺点：
- 拆分规则难以抽象
- 分片事务一致性难以解决
- 数据多次扩展难度跟维护量极大
- 跨库 join 性能较差
前面讲了垂直切分跟水平切分的不同跟优缺点，会发现每种切分方式都有缺点，但共同特点缺点有：
- 引入分布式事务的问题
- 跨节点 Join 的问题
- 跨节点合并排序分页问题
- 多数据源管理问题
针对数据源管理，目前主要有两种思路：
A. 客户端模式，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个数据库， 在模块内完成数据的整合
B. 通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明； 可能 90%以上的人在面对上面这两种解决思路的时候都会倾向于选择第二种，尤其是系统不断变得庞大复杂 的时候。确实，这是一个非常正确的选择，虽然短期内需要付出的成本可能会相对更大一些，但是对整个系统的 扩展性来说，是非常有帮助的。
MySQL中间件服务器可以通过将数据切分解决传统数据库的缺陷，又有了 NoSQL 易于扩展的优点。通过中间代理层规避了多数 据源的处理问题，对应用完全透明，同时对数据切分后存在的问题，也做了解决方案。
由于数据切分后数据 Join 的难度在此也分享一下数据切分的经验：
第一原则：能不切分尽量不要切分
第二原则：如果要切分一定要选择合适的切分规则，提前规划好。
第三原则：数据切分尽量通过数据冗余或表分组（Table Group）来降低跨库 Join 的可能
第四原则：由于数据库中间件对数据 Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量 少使用多表 Join。
6.2.3 MySQL中间件各种应用
mysql-proxy：Oracle，https://downloads.mysql.com/archives/proxy/
Atlas：Qihoo，https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
dbproxy：美团，https://github.com/Meituan-Dianping/DBProxy
Cetus：网易乐得，https://github.com/Lede-Inc/cetus
Amoeba：https://sourceforge.net/projects/amoeba/
Cobar：阿里巴巴，Amoeba的升级版， https://github.com/alibaba/cobar
Mycat：基于Cobar http://www.mycat.io/ (原网站)
http://www.mycat.org.cn/
https://github.com/MyCATApache/Mycat-Server
ProxySQL：https://proxysql.com/
6.2.4 Mycat
6.2.4.1 Mycat介绍
在整个IT系统架构中，数据库是非常重要，通常又是访问压力较大的一个服务，除了在程序开发的本身做优化，如：SQL语句优化、代码优化，数据库的处理本身优化也是非常重要的。主从、热备、分表分库等都是系统发展迟早会遇到的技术问题问题。Mycat是一个广受好评的数据库中间件，已经在很多产品上进行使用了。
Mycat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理（类似于Mysql Proxy），用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。
Mycat发展到目前的版本，已经不是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。而在最终用户看来，无论是那种存储方式，在MyCat里，都是一个传统的数据库表，支持标准的SQL语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发难度，提升开发速度
Mycat可以简单概括为
- 一个彻底开源的，面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
Mycat 官网：http://www.mycat.org.cn/
Mycat关键特性
- 支持SQL92标准
- 遵守MySQL 原生协议，跨语言，跨平台，跨数据库的通用中间件代理
- 基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群
- 支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster
- 基于Nio实现，有效管理线程，高并发问题
- 支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数,支持跨库分页
- 支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join
- 支持通过全局表，ER关系的分片策略，实现了高效的多表join查询
- 支持多租户方案
- 支持分布式事务（弱xa）
- 支持全局序列号，解决分布式下的主键生成问题
- 分片规则丰富，插件化开发，易于扩展
- 强大的web，命令行监控
- 支持前端作为mysq通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉
- 支持密码加密
- 支持服务降级
- 支持IP白名单
- 支持SQL黑名单、sql注入攻击拦截
- 支持分表（1.6）
- 集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）
为什么要用MyCat
这里要先搞清楚Mycat和MySQL的区别（Mycat的核心作用）。我们可以把上层看作是对下层的抽象，例如操作系统是对各类计算机硬件的抽象。那么我们什么时候需要抽象？假如只有一种硬件的时候，我们需要开发一个操作系统吗？再比如一个项目只需要一个人完成的时候不需要leader，但是当需要几十人完成时，就应该有一个管理者，发挥沟通协调等作用，而这个管理者对于他的上层来说就是对项目组的抽象
同样的，当我们的应用只需要一台数据库服务器的时候我们并不需要Mycat，而如果你需要分库甚至分表，这时候应用要面对很多个数据库的时候，这个时候就需要对数据库层做一个抽象，来管理这些数据库，而最上面的应用只需要面对一个数据库层的抽象或者说数据库中间件就好了，这就是Mycat的核心作用。所以可以这样理解：数据库是对底层存储文件的抽象，而Mycat是对数据库的抽象
Mycat工作原理
Mycat外还有一层代理，是为了解决Mycat的单点失败问题，keeplaive可以解决两个服务器共享一个IP的功能，对外只有一个VIP（虚拟）
Mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户
Mycat应用场景
Mycat适用的场景很丰富，以下是几个典型的应用场景
- 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
- 分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片
- 多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化
- 报表系统，借助于Mycat的分表能力，处理大规模报表的统计
- 替代Hbase，分析大数据
- 作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择
- Mycat长期路线图
- 强化分布式数据库中间件的方面的功能，使之具备丰富的插件、强大的数据库智能优化功能、全面的系统监控能力、以及方便的数据运维工具，实现在线数据扩容、迁移等高级功能
- 进一步挺进大数据计算领域，深度结合Spark Stream和Storm等分布式实时流引擎，能够完成快速的巨表关联、排序、分组聚合等 OLAP方向的能力，并集成一些热门常用的实时分析算法，让工程师以及DBA们更容易用Mycat实现一些高级数据分析处理功能
- 不断强化Mycat开源社区的技术水平，吸引更多的IT技术专家，使得Mycat社区成为中国的Apache，并将Mycat推到Apache基金会，成为国内顶尖开源项目，最终能够让一部分志愿者成为专职的Mycat开发者，荣耀跟实力一起提升
Mycat不适合的应用场景
-
设计使用Mycat时有非分片字段查询，请慎重使用Mycat，可以考虑放弃！
-
设计使用Mycat时有分页排序，请慎重使用Mycat，可以考虑放弃！
-
设计使用Mycat时如果要进行表JOIN操作，要确保两个表的关联字段具有相同的数据分布，否则请慎重使用Mycat，可以考虑放弃！
-
设计使用Mycat时如果有分布式事务，得先看是否得保证事务得强一致性，否则请慎重使用Mycat，可以考虑放弃！
MyCat的高可用性：
需要注意: 在生产环境中, Mycat节点最好使用双节点, 即双机热备环境, 防止Mycat这一层出现单点故障。可以使用的高可用集群方式有:
- Keepalived+Mycat+Mysql
- Keepalived+LVS+Mycat+Mysql
- Keepalived+Haproxy+Mycat+Mysql
6.2.4.2 Mycat安装
下载安装JDK
yum -y install java
#确认安装成功
java -version
openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)
- 1
- 2
- 3
- 4
- 5
- 6
下载安装mycat
wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
mkdir /app
tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /app
ls /app/mycat/
bin catlet conf lib logs version.txt
- 1
- 2
- 3
- 4
- 5
- 6
- 7
mycat安装目录结构：
- bin mycat命令，启动、重启、停止等
- catlet catlet为Mycat的一个扩展功能
- conf Mycat 配置信息,重点关注
- lib Mycat引用的jar包，Mycat是java开发的
- logs 日志文件，包括Mycat启动的日志和运行的日志
- version.txt mycat版本说明
logs目录:
- wrapper.log mycat启动日志
- mycat.log mycat详细工作日志
Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：
- server.xml Mycat软件本身相关的配置文件，设置账号、参数等
- schema.xml Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、节点控制
- rule.xml Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等
启动和连接
#配置环境变量
vim /etc/profile.d/mycat.sh
PATH=/app/mycat/bin:$PATH
source /etc/profile.d/mycat.sh
#启动
mycat start
#查看日志，确定成功
cat /app/mycat/logs/wrapper.log
...省略...
INFO | jvm 1 | 2019/11/01 21:41:02 | MyCAT Server startup successfully. see logs in logs/mycat.log
#连接mycat：
mysql -uroot -p123456 -h 127.0.0.1 -P8066
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
6.2.4.3 Mycat 主要配置文件说明
server.xml
存放Mycat软件本身相关的配置文件，比如：连接Mycat的用户，密码，数据库名称等
server.xml文件中配置的参数解释说明：
参数 说明
user 用户配置节点
name 客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。
password 客户端登录MyCAT的密码
schemas 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2
privileges 配置用户针对表的增删改查的权限
readOnly mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false
- 1
- 2
- 3
- 4
- 5
- 6
- 7
注意：
- server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息
- 逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！
- 这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！
schema.xml
是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的
schema.xml文件中配置的参数解释说明：
参数 说明
schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
dataNode 分片信息，也就是分库相关配置
dataHost 物理数据库，真正存储数据的数据库
- 1
- 2
- 3
- 4
配置说明
name属性唯一标识dataHost标签，供上层的标签使用。
maxCon属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数
minCon属性指定每个读写实例连接池的最小连接，初始化连接池的大小
每个节点的属性逐一说明
schema: 属性 说明 name 逻辑数据库名，与server.xml中的schema对应 checkSQLschema 数据库前缀相关设置，这里为false sqlMaxLimit select 时默认的limit，避免查询全表 table 属性 说明 name 表名，物理数据库中表名 dataNode 表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name primaryKey 主键字段名，自动生成主键时需要设置 autoIncrement 是否自增 rule 分片规则名，具体规则下文rule详细介绍 dataNode 属性 说明 name 节点名，与table中dataNode对应 datahost 物理数据库名，与datahost中name对应 database 物理数据库中数据库名 dataHost 属性 说明 name 物理数据库名，与dataNode中dataHost对应 balance 均衡负载的方式 writeType 写入方式 dbType 数据库类型 heartbeat 心跳检测语句，注意语句结尾的分号要加
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
schema.xml文件中有三点需要注意：balance=“1”，writeType=“0” ,switchType=“1”
schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。balance 属性负载均衡类型，目前的取值有 4 种：
- balance=“0”：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上,即读请求仅发送到writeHost上
- balance=“1”：一般用此模式，读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。即全部的readHost与stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1, S2 都参与 select语句的负载均衡
- balance=“2”：读请求随机分发到当前dataHost内所有的writeHost和readHost上。即所有读操作都随机的在writeHost、 readhost 上分发
- balance=“3”：读请求随机分发到当前writeHost对应的readHost上。即所有读请求随机的分发到wiriterHost 对应的 readhost 执行, writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有
writeHost和readHost 标签
这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。
唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。
在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去
注意：
Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的主从复制将数据复制到readhost
6.2.4.4 实战案例：利用Mycat实现MySQL的读写分离
系统环境：
cat /etc/centos-release
CentOS Linux release 8.0.1905 (Core)
- 1
- 2
服务器共三台
mycat-server 10.0.0.8 内存建议2G以上
mysql-master 10.0.0.18
mysql-slave 10.0.0.12
- 1
- 2
- 3
关闭SELinux和防火墙
systemctl stop firewalld
setenforce 0
时间同步
- 1
- 2
- 3
1、创建 MySQL 主从数据库
[root@centos8 ~]#yum -y install mariadb-server
- 1
1) 修改master和slave上的配置文件
#master上的my.cnf
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id = 1
log-bin
#slave上的my.cnf
[mysqld]
server-id = 2
[root@centos8 ~]#systemctl start mariadb
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
2) Master上创建复制用户
[root@centos8 ~]#mysql -uroot -pmagedu
MariaDB [(none)]>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.28'
IDENTIFIED BY 'magedu';
MariaDB [(none)]> FLUSH PRIVILEGES; #保证权限能生效
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 330 |
+--------------------+-----------+
1 row in set (0.000 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
3) Slave上执行
[root@centos8 ~]#mysql -uroot -p MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.18', -> MASTER_USER='repluser', -> MASTER_PASSWORD='magedu', -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=330; MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.18 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 527 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 754 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...省略...
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
2、在10.0.0.8安装mycat并启动
[root@centos8 ~]#yum -y install java mariadb #确认安装成功 [root@centos8 ~]#java -version openjdk version "1.8.0_252" OpenJDK Runtime Environment (build 1.8.0_252-b09) OpenJDK 64-Bit Server VM (build 25.252-b09, mixed mode) #下载并安装 [root@centos8 ~]#wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz [root@centos8 ~]#mkdir /app [root@centos8 ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /app #配置环境变量 [root@centos8 ~]#echo 'PATH=/app/mycat/bin:$PATH' > /etc/profile.d/mycat.sh [root@centos8 ~]#source /etc/profile.d/mycat.sh #查看端口 [root@centos8 ~]#ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:111 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 128 0.0.0.0:5355 0.0.0.0:* LISTEN 0 128 [::]:111 [::]:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:5355 [::]:* #启动mycat [root@centos8 ~]#mycat start Starting Mycat-server... [root@centos8 ~]#ss -ntlp State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:111 0.0.0.0:* users:(("rpcbind",pid=738,fd=4),("systemd",pid=1,fd=71)) LISTEN 0 128 0.0.0.0:22 0.0.0.0:* users:(("sshd",pid=810,fd=4)) LISTEN 0 1 127.0.0.1:32000 0.0.0.0:* users:(("java",pid=4932,fd=4)) LISTEN 0 128 0.0.0.0:5355 0.0.0.0:* users:(("systemd-resolve",pid=950,fd=13)) LISTEN 0 128 [::]:111 [::]:* users:(("rpcbind",pid=738,fd=6),("systemd",pid=1,fd=73)) LISTEN 0 128 [::]:22 [::]:* users:(("sshd",pid=810,fd=6)) LISTEN 0 50 *:36505 *:* users:(("java",pid=4932,fd=65)) LISTEN 0 50 *:1984 *:* users:(("java",pid=4932,fd=64)) LISTEN 0 100 *:8066 *:* users:(("java",pid=4932,fd=85)) LISTEN 0 50 *:46051 *:* users:(("java",pid=4932,fd=63)) LISTEN 0 100 *:9066 *:* users:(("java",pid=4932,fd=81)) LISTEN 0 80 *:3306 *:* users:(("mysqld",pid=4254,fd=21)) LISTEN 0 128 [::]:5355 [::]:* users:(("systemd-resolve",pid=950,fd=15)) #查看日志，确定成功，可能需要等一会儿才能看到成功的提示 [root@centos8 ~]#tail /app/mycat/logs/wrapper.log STATUS | wrapper | 2020/06/16 22:45:41 | Launching a JVM... ERROR | wrapper | 2020/06/16 22:46:10 | Startup failed: Timed out waiting for a signal from the JVM. ERROR | wrapper | 2020/06/16 22:46:10 | JVM did not exit on request, terminated INFO | wrapper | 2020/06/16 22:46:10 | JVM exited on its own while waiting to kill the application. STATUS | wrapper | 2020/06/16 22:46:10 | JVM exited in response to signal SIGKILL (9). STATUS | wrapper | 2020/06/16 22:46:15 | Launching a JVM... INFO | jvm 2 | 2020/06/16 22:46:15 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org INFO | jvm 2 | 2020/06/16 22:46:15 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved. INFO | jvm 2 | 2020/06/16 22:46:15 | INFO | jvm 2 | 2020/06/16 22:46:18 | MyCAT Server startup successfully. see logs in logs/mycat.log #用默认密码123456来连接mycat [root@centos8 ~]#mysql -uroot -p123456 -h 10.0.0.8 -P8066 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.001 sec) MySQL [(none)]> USE TESTDB Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MySQL [TESTDB]> show tables; +------------------+ | Tables in TESTDB | +------------------+ | address | | travelrecord | +------------------+ 2 rows in set (0.012 sec) MySQL [TESTDB]> select * from travelrecord ; ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
4、在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@centos8 ~]#vim /app/mycat/conf/server.xml
...省略...
<user name="root"> #连接Mycat的用户名
<property name="password">magedu</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相对应
</user>
</mycat:server>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
这里使用的是root，密码为magedu,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。
5、修改schema.xml实现读写分离策略
[root@centos8 ~]#vim /app/mycat/conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema> <dataNode name="dn1" dataHost="localhost1" database="mycat" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="host1" url="10.0.0.18:3306" user="root" password="123456"> <readHost host="host2" url="10.0.0.28:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema> #重新启动mycat [root@centos8 ~]#mycat restart
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.18为主库，10.0.0.28为从库
注意：要保证10.0.0.18和10.0.0.28机器能使用root/123456权限成功登录mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！
范例：schema.xml
6、在后端主服务器创建用户并对mycat授权
[root@centos8 ~]#mysql -uroot -p
mysql> create database mycat;
mysql>GRANT ALL ON *.* TO 'root'@'10.0.0.%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql> privileges;
- 1
- 2
- 3
- 4
7、在Mycat服务器上连接并测试
[root@centos8 ~]#mysql -uroot -pmagedu -h127.0.0.1 -P8066 -DTESTDB
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB | //只能看一个虚拟数据库
+----------+
MySQL [(none)]> use TESTDB;
MySQL [TESTDB]> create table t1(id int);
MySQL [TESTDB]> select @@server_id;
MySQL [TESTDB]> select @@hostname;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
8、通过通用日志确认实现读写分离
在mysql中查看通用日志
show variables like 'general_log'; #查看日志是否开启
set global general_log=on; #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置
- 1
- 2
- 3
- 4
在主和从服务器分别启用通用日志，查看读写分离
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
general_log=ON
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#tail -f /var/lib/mysql/centos8.log
- 1
- 2
- 3
- 4
- 5
- 6
9、停止从节点，MyCAT自动调度读请求至主节点 （需要exit一次）
[root@slave ~]#systemctl stop mariadb
[root@client ~]#mysql -uroot -pmagedu -h10.0.0.8 -P8066
MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
#停止主节点，MyCAT不会自动调度读请求至从节点
MySQL [TESTDB]> insert teachers values(5,'wang',30,'M');
ERROR 1184 (HY000): java.net.ConnectException: Connection refused
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
6.2.5 ProxySQL
6.2.5.1 ProxySQL 介绍
ProxySQL： MySQL中间件
两个版本：官方版和percona版，percona版是基于官方版基础上修改
C++语言开发，轻量级但性能优异，支持处理千亿级数据
具有中间件所需的绝大多数功能，包括：
多种方式的读/写分离
定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
缓存查询结果
后端节点监控
- 1
- 2
- 3
- 4
- 5
- 6
- 7
官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki
6.2.5.2 ProxySQL安装
基于YUM仓库安装
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
- 1
- 2
- 3
- 4
- 5
- 6
- 7
基于RPM下载安装：https://github.com/sysown/proxysql/releases
yum install proxysql
- 1
ProxySQL组成
- 服务脚本：/etc/init.d/proxysql
- 配置文件：/etc/proxysql.cnf
- 主程序：/usr/bin/proxysql
- 基于SQLITE的数据库文件：/var/lib/proxysql/
启动ProxySQL：
service proxysql start
- 1
启动后会监听两个默认端口
- 6032：ProxySQL的管理端口
- 6033：ProxySQL对外提供服务的端口
连接ProxySQL的管理端口
使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：
mysql -uadmin -padmin -P6032 -h127.0.0.1
- 1
数据库说明：
- main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示
- proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载
- disk 是持久化到硬盘的配置，sqlite数据文件
- stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等
- monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
说明：
在main和monitor数据库中的表， runtime开头的是运行时的配置，不能修改，只能修改非runtime表
修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效
执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中
global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
- 1
- 2
- 3
- 4
参考: https://github.com/sysown/proxysql/wiki/Global-variables
7.2.5.3 实战案例：利用ProxySQL实现读写分离
-
环境准备：
准备三台主机，一台ProxySQL服务器:192.168.8.7，另外两台主机实现主从复制192.168.8.17,27
注意：slave节点需要设置read_only=1 -
安装ProxySQL，并向ProxySQL中添加MySQL节点，以下操作不需要use main也可成功
MySQL> show tables; MySQL > select * from sqlite_master where name='mysql_servers'\G MySQL > select * from mysql_servers; MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.17',3306); MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.27',3306); MySQL > load mysql servers to runtime; MySQL > save mysql servers to disk;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
-
添加监控后端节点的用户，连接每个节点的read_only值来自动调整主从节点是属于读组还是写组
#在master上执行 MySQL> grant replication client on *.* to monitor@'192.168.8.%' identified by 'magedu'; #ProxySQL上配置监控 MySQL [(none)]> set mysql-monitor_username='monitor'; MySQL [(none)]> set mysql-monitor_password='magedu'; #加载到RUNTIME，并保存到disk MySQL [(none)]> load mysql variables to runtime; MySQL [(none)]> save mysql variables to disk;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
-
查看监控
监控模块的指标保存在monitor库的log表中#查看监控连接是否正常的 (对connect指标的监控），如果connect_error的结果为NULL则表示正常 MySQL> select * from mysql_server_connect_log; #查看监控心跳信息 (对ping指标的监控)： MySQL> select * from mysql_server_ping_log;- 1
- 2
- 3
- 4
-
设置分组信息
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：
writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20MySQL> insert into mysql_replication_hostgroups values(10,20,"test"); 将mysql_replication_hostgroups表的修改加载到RUNTIME生效 MySQL> load mysql servers to runtime; MySQL> save mysql servers to disk; #Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组 MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+--------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+--------------+------+--------+--------+ | 10 | 192.168.8.17 | 3306 | ONLINE | 1 | | 20 | 192.168.8.27 | 3306 | ONLINE | 1 |- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
配置访问数据库的SQL 用户
#在master节点上创建访问用户 MySQL> grant all on *.* to sqluser@'192.168.8.%' identified by 'magedu'; #在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置 为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库 MySQL> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10); MySQL> load mysql users to runtime; MySQL> save mysql users to disk; #使用sqluser用户测试是否能路由到默认的10写组实现读、写数据 mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id' mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'create database testdb' mysql -usqluser -pmagedu testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
在proxysql上配置路由规则，实现读写分离
与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持
插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT…FOR UPDATE它会申请写锁，应路由到10的写组
MySQL> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); MySQL> load mysql query rules to runtime; MySQL> save mysql query rules to disk; #注意：因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id- 1
- 2
- 3
- 4
- 5
- 6
-
测试ProxySQL
#读操作是否路由给20的读组 mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id' #测试写操作，以事务方式进行测试 mysql -usqluser -pmagedu -P6033 -h127.0.0.1 \ -e 'start transaction;select @@server_id;commit;select @@server_id' mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)' mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select id from testdb.t' #路由的信息：查询stats库中的stats_mysql_query_digest表 MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; #测试读操作是否路由给20的读组 mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id' #测试写操作，以事务方式进行测试 mysql -usqluser -pmagedu -P6033 -h127.0.0.1 \ -e 'start transaction;select @@server_id;commit;select @@server_id' mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)' mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select id from testdb.t' #路由的信息：查询stats库中的stats_mysql_query_digest表 MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
6.3 MySQL高可用
6.3.1 MySQL高可用解决方案
MySQL官方和社区里推出了很多高可用的解决方案，大体如下，仅供参考（数据引用自Percona）
- MMM: Multi-Master Replication Manager for MySQL，Mysql主主复制管理器是一套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管理mysql MasterMaster复制的配置(同一时间只有一个节点是可写的)
官网： http://www.mysql-mmm.org
https://code.google.com/archive/p/mysql-master-master/downloads - MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从
官方网站：https://code.google.com/archive/p/mysql-master-ha/
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads - Galera Cluster：wsrep(MySQL extended with the Write Set Replication)
通过wsrep协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主读写 - GR（Group Replication）：MySQL官方提供的组复制技术(MySQL 5.7.17引入的技术)，基于原生复制技术Paxos算法，实现了多主更新，复制组由多个server成员构成，组中的每个server可独立地执行事务，但所有读写事务只在冲突检测成功后才会提交
这3个节点互相通信，每当有事件发生，都会向其他节点传播该事件，然后协商，如果大多数节点都同意这次的事件，那么该事件将通过，否则该事件将失败或回滚。这些节点可以是单主模型的(single-primary)，也可以是多主模型的(multi-primary)。单主模型只有一个主节点可以接受写操作，主节点故障时可以自动选举主节点。多主模型下，所有节点都可以接受写操作，所以没有master-slave的概念。
6.3.2 MHA Master High Availability
6.3.2.1 MHA 工作原理和架构
MHA集群架构
MHA工作原理
-
从宕机崩溃的master保存二进制日志事件（binlog events）
-
识别含有最新更新的slave
-
应用差异的中继日志（relay log）到其他的slave
-
应用从master保存的二进制日志事件（binlog events）
-
提升一个slave为新的master
-
使其他的slave连接新的master进行复制
MHA软件
MHA软件由两部分组成，Manager工具包和Node工具包
Manager工具包主要包括以下几个工具：
- masterha_check_ssh 检查MHA的SSH配置状况
- masterha_check_repl 检查MySQL复制状况
- masterha_manger 启动MHA
- masterha_check_status 检测当前MHA运行状态
- masterha_master_monitor 检测master是否宕机
- masterha_master_switch 故障转移（自动或手动）
- masterha_conf_host 添加或删除配置的server信息
Node工具包：这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：
- save_binary_logs 保存和复制master的二进制日志
- apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slave
- filter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用此工具）
- purge_relay_logs 清除中继日志（不会阻塞SQL线程）
注意：为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制
MHA自定义扩展：
- secondary_check_script： 通过多条网络路由检测master的可用性
- master_ip_ailover_script： 更新Application使用的masterip
- shutdown_script： 强制关闭master节点
- report_script： 发送报告
- init_conf_load_script： 加载初始配置参数
- master_ip_online_change_script：更新master节点ip地址
MHA配置文件：
- global配置，为各application提供默认配置，默认文件路径/etc/masterha_default.cnf
- application配置：为每个主从复制集群
6.3.2.2 实现MHA实战案例
环境：四台主机
1 10.0.0.7 CentOS7 MHA管理端
2 10.0.0.8 CentOS8 Master
3 10.0.0.18 CentOS8 salve1
4 10.0.0.28 CentOS8 slave2
- 1
- 2
- 3
- 4
- 5
-
在管理节点上安装两个包(不支持CentOS8，只支持CentOS7 以下版本),
#会有依赖关系 mha4mysql-manager #管理包 mha4mysql-node #节点包 yum -y install mha*.rpm- 1
- 2
- 3
- 4
-
在被管理节点安装（支持CentOS 8，7，6）
mha4mysql-node- 1
-
在所有节点实现相互之间ssh key验证
[root@mha-manager ~]#ssh-keygen [root@mha-manager ~]#ssh-copy-id 10.0.0.7 ??? [root@mha-manager ~]#rsync -av .ssh 10.0.0.8:/root/ [root@mha-manager ~]#rsync -av .ssh 10.0.0.18:/root/ [root@mha-manager ~]#rsync -av .ssh 10.0.0.28:/root/- 1
- 2
- 3
- 4
- 5
-
在管理节点建立配置文件
mkdir /etc/mastermha/ #名称可以改 vim /etc/mastermha/app1.cnf #监控的一组服务器的集合，可以还有app2...名称可以改 [server default] user=mhauser #用于远程连接mysql所有节点的用户，需要具有一定的管理员权限 password=magedu manager_workdir=/data/mastermha/app1/ #工作目录，指定文件夹名自动生成 manager_log=/data/mastermha/app1/manager.log #很重要，了解工作情况 remote_workdir=/data/mastermha/app1/ #工作目录，指定文件夹名自动生成 ssh_user=root #用于实现远程ssh基于KEY的连接（不需要输密码），来访问二进制日志，用来预防mysql宕掉，Linux还可以利用二进制日志恢复 repl_user=repluser #主从复制的复制账户 repl_password=magedu ping_interval=1 #健康性检查的时间间隔 [server1] hostname=10.0.0.8 candidate_master=1 [server2] hostname=10.0.0.18 candidate_master=1 #期望谁优先成为新的主节点Master [server3] hostname=10.0.0.28- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
-
实现Master
vim /etc/my.cnf [mysqld] log-bin server_id=1 skip_name_resolve=1 mysql>show master logs mysql>grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu'; mysql>grant all on *.* to mhauser@'10.0.0.%' identified by 'magedu';- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
-
实现slave
vim /etc/my.cnf [mysqld] server_id=2 #不同节点此值各不相同 log-bin #必须启用,万一未来会当主节点 read_only #若主服务器挂了,从服务器上位,mhauser会自动修改此项,但是主服务器宕机后别忘了修改已上位的从服务器的配置文件,不然重启服务后就会变成只读 relay_log_purge=0 #不会自动清理中继日志，中继日志可以实现二进制复制 skip_name_resolve=1 #禁止反向解析 mysql>CHANGE MASTER TO MASTER_HOST=‘MASTER_IP', MASTER_USER='repluser', MASTER_PASSWORD=‘magedu', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245; mysql>START SLAVE;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
-
检查Mha的环境
#检查环境 masterha_check_ssh --conf=/etc/mastermha/app1.cnf masterha_check_repl --conf=/etc/mastermha/app1.cnf- 1
- 2
- 3
范例:
[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf Thu Jun 18 17:47:12 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 18 17:47:12 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 17:47:12 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 17:47:12 2020 - [info] Starting SSH connection tests.. Thu Jun 18 17:47:14 2020 - [debug] Thu Jun 18 17:47:12 2020 - [debug] Connecting via SSH from root@10.0.0.8(10.0.0.8:22) to root@10.0.0.18(10.0.0.18:22).. Thu Jun 18 17:47:13 2020 - [debug] ok. Thu Jun 18 17:47:13 2020 - [debug] Connecting via SSH from root@10.0.0.8(10.0.0.8:22) to root@10.0.0.28(10.0.0.28:22).. Warning: Permanently added '10.0.0.28' (ECDSA) to the list of known hosts. Thu Jun 18 17:47:13 2020 - [debug] ok. Thu Jun 18 17:47:15 2020 - [debug] Thu Jun 18 17:47:13 2020 - [debug] Connecting via SSH from root@10.0.0.28(10.0.0.28:22) to root@10.0.0.8(10.0.0.8:22).. Thu Jun 18 17:47:14 2020 - [debug] ok. Thu Jun 18 17:47:14 2020 - [debug] Connecting via SSH from root@10.0.0.28(10.0.0.28:22) to root@10.0.0.18(10.0.0.18:22).. Thu Jun 18 17:47:14 2020 - [debug] ok. Thu Jun 18 17:47:15 2020 - [debug] Thu Jun 18 17:47:13 2020 - [debug] Connecting via SSH from root@10.0.0.18(10.0.0.18:22) to root@10.0.0.8(10.0.0.8:22).. Thu Jun 18 17:47:13 2020 - [debug] ok. Thu Jun 18 17:47:13 2020 - [debug] Connecting via SSH from root@10.0.0.18(10.0.0.18:22) to root@10.0.0.28(10.0.0.28:22).. Thu Jun 18 17:47:14 2020 - [debug] ok. Thu Jun 18 17:47:15 2020 - [info] All SSH connection tests passed successfully. [root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf Thu Jun 18 17:48:32 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 18 17:48:32 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 17:48:32 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 17:48:32 2020 - [info] MHA::MasterMonitor version 0.56. Creating directory /data/mastermha/app1/.. done. Thu Jun 18 17:48:33 2020 - [info] GTID failover mode = 0 Thu Jun 18 17:48:33 2020 - [info] Dead Servers: Thu Jun 18 17:48:33 2020 - [info] Alive Servers: Thu Jun 18 17:48:33 2020 - [info] 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:48:33 2020 - [info] 10.0.0.18(10.0.0.18:3306) Thu Jun 18 17:48:33 2020 - [info] 10.0.0.28(10.0.0.28:3306) Thu Jun 18 17:48:33 2020 - [info] Alive Slaves: Thu Jun 18 17:48:33 2020 - [info] 10.0.0.18(10.0.0.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 17:48:33 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:48:33 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jun 18 17:48:33 2020 - [info] 10.0.0.28(10.0.0.28:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 17:48:33 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:48:33 2020 - [info] Current Alive Master: 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:48:33 2020 - [info] Checking slave configurations.. Thu Jun 18 17:48:33 2020 - [info] Checking replication filtering settings.. Thu Jun 18 17:48:33 2020 - [info] binlog_do_db= , binlog_ignore_db= Thu Jun 18 17:48:33 2020 - [info] Replication filtering check ok. Thu Jun 18 17:48:33 2020 - [info] GTID (with auto-pos) is not supported Thu Jun 18 17:48:33 2020 - [info] Starting SSH connection tests.. Thu Jun 18 17:48:35 2020 - [info] All SSH connection tests passed successfully. Thu Jun 18 17:48:35 2020 - [info] Checking MHA Node version.. Thu Jun 18 17:48:36 2020 - [info] Version check ok. Thu Jun 18 17:48:36 2020 - [info] Checking SSH publickey authentication settings on the current master.. Thu Jun 18 17:48:36 2020 - [info] HealthCheck: SSH to 10.0.0.8 is reachable. Thu Jun 18 17:48:37 2020 - [info] Master MHA Node version is 0.56. Thu Jun 18 17:48:37 2020 - [info] Checking recovery script configurations on 10.0.0.8(10.0.0.8:3306).. Thu Jun 18 17:48:37 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --start_file=mariadb-bin.000002 Thu Jun 18 17:48:37 2020 - [info] Connecting to root@10.0.0.8(10.0.0.8:22).. Creating /data/mastermha/app1 if not exists.. Creating directory /data/mastermha/app1.. done. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to mariadb-bin.000002 Thu Jun 18 17:48:37 2020 - [info] Binlog setting check done. Thu Jun 18 17:48:37 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Thu Jun 18 17:48:37 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.18 --slave_ip=10.0.0.18 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.3.17-MariaDB-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Thu Jun 18 17:48:37 2020 - [info] Connecting to root@10.0.0.18(10.0.0.18:22).. Creating directory /data/mastermha/app1/.. done. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Thu Jun 18 17:48:37 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.28 --slave_ip=10.0.0.28 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.3.17-MariaDB-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Thu Jun 18 17:48:37 2020 - [info] Connecting to root@10.0.0.28(10.0.0.28:22).. Creating directory /data/mastermha/app1/.. done. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Thu Jun 18 17:48:38 2020 - [info] Slaves settings check done. Thu Jun 18 17:48:38 2020 - [info] 10.0.0.8(10.0.0.8:3306) (current master) +--10.0.0.18(10.0.0.18:3306) +--10.0.0.28(10.0.0.28:3306) Thu Jun 18 17:48:38 2020 - [info] Checking replication health on 10.0.0.18.. Thu Jun 18 17:48:38 2020 - [info] ok. Thu Jun 18 17:48:38 2020 - [info] Checking replication health on 10.0.0.28.. Thu Jun 18 17:48:38 2020 - [info] ok. Thu Jun 18 17:48:38 2020 - [warning] master_ip_failover_script is not defined. Thu Jun 18 17:48:38 2020 - [warning] shutdown_script is not defined. Thu Jun 18 17:48:38 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
-
启动Mha
#开启MHA，默认是前台运行（后台运行再加上）只要主服务器一挂，它就会执行 nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null nohup 后台执行 #查看状态 masterha_check_status --conf=/etc/mastermha/app1.cnf- 1
- 2
- 3
- 4
- 5
- 6
范例：
[root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf Thu Jun 18 17:55:47 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 18 17:55:47 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 17:55:47 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..- 1
- 2
- 3
- 4
-
排错日志：
/data/mastermha/app1/manager.log- 1
范例：
[root@mha-manager ~]#cat /data/mastermha/app1/manager.log Thu Jun 18 17:55:47 2020 - [info] MHA::MasterMonitor version 0.56. Thu Jun 18 17:55:48 2020 - [info] GTID failover mode = 0 Thu Jun 18 17:55:48 2020 - [info] Dead Servers: Thu Jun 18 17:55:48 2020 - [info] Alive Servers: Thu Jun 18 17:55:48 2020 - [info] 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:55:48 2020 - [info] 10.0.0.18(10.0.0.18:3306) Thu Jun 18 17:55:48 2020 - [info] 10.0.0.28(10.0.0.28:3306) Thu Jun 18 17:55:48 2020 - [info] Alive Slaves: Thu Jun 18 17:55:48 2020 - [info] 10.0.0.18(10.0.0.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 17:55:48 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:55:48 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jun 18 17:55:48 2020 - [info] 10.0.0.28(10.0.0.28:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 17:55:48 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:55:48 2020 - [info] Current Alive Master: 10.0.0.8(10.0.0.8:3306) Thu Jun 18 17:55:48 2020 - [info] Checking slave configurations.. Thu Jun 18 17:55:48 2020 - [info] Checking replication filtering settings.. Thu Jun 18 17:55:48 2020 - [info] binlog_do_db= , binlog_ignore_db= Thu Jun 18 17:55:48 2020 - [info] Replication filtering check ok. Thu Jun 18 17:55:48 2020 - [info] GTID (with auto-pos) is not supported Thu Jun 18 17:55:48 2020 - [info] Starting SSH connection tests.. Thu Jun 18 17:55:51 2020 - [info] All SSH connection tests passed successfully. Thu Jun 18 17:55:51 2020 - [info] Checking MHA Node version.. Thu Jun 18 17:55:51 2020 - [info] Version check ok. Thu Jun 18 17:55:51 2020 - [info] Checking SSH publickey authentication settings on the current master.. Thu Jun 18 17:55:51 2020 - [info] HealthCheck: SSH to 10.0.0.8 is reachable. Thu Jun 18 17:55:52 2020 - [info] Master MHA Node version is 0.56. Thu Jun 18 17:55:52 2020 - [info] Checking recovery script configurations on 10.0.0.8(10.0.0.8:3306).. Thu Jun 18 17:55:52 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --start_file=mariadb-bin.000002 Thu Jun 18 17:55:52 2020 - [info] Connecting to root@10.0.0.8(10.0.0.8:22).. Creating /data/mastermha/app1 if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to mariadb-bin.000002 Thu Jun 18 17:55:52 2020 - [info] Binlog setting check done. Thu Jun 18 17:55:52 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Thu Jun 18 17:55:52 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.18 --slave_ip=10.0.0.18 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.3.17-MariaDB-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Thu Jun 18 17:55:52 2020 - [info] Connecting to root@10.0.0.18(10.0.0.18:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Thu Jun 18 17:55:53 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.28 --slave_ip=10.0.0.28 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.3.17-MariaDB-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Thu Jun 18 17:55:53 2020 - [info] Connecting to root@10.0.0.28(10.0.0.28:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Thu Jun 18 17:55:53 2020 - [info] Slaves settings check done. Thu Jun 18 17:55:53 2020 - [info] 10.0.0.8(10.0.0.8:3306) (current master) +--10.0.0.18(10.0.0.18:3306) +--10.0.0.28(10.0.0.28:3306) Thu Jun 18 17:55:53 2020 - [warning] master_ip_failover_script is not defined. Thu Jun 18 17:55:53 2020 - [warning] shutdown_script is not defined. Thu Jun 18 17:55:53 2020 - [info] Set master ping interval 1 seconds. Thu Jun 18 17:55:53 2020 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Thu Jun 18 17:55:53 2020 - [info] Starting ping health check on 10.0.0.8(10.0.0.8:3306).. Thu Jun 18 17:55:53 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
-
故障
#当 master down机后,mha自动退出 [root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf Thu Jun 18 17:55:47 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 18 17:55:47 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 17:55:47 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 18:00:47 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 18 18:00:47 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 18:00:47 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 18:00:41 2020 - [warning] Got timeout on MySQL Ping(SELECT) child process and killed it! at /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm line 431. Thu Jun 18 18:00:41 2020 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --binlog_prefix=mariadb-bin Thu Jun 18 18:00:42 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.8' (4)) Thu Jun 18 18:00:42 2020 - [warning] Connection failed 2 time(s).. Thu Jun 18 18:00:43 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.8' (4)) Thu Jun 18 18:00:43 2020 - [warning] Connection failed 3 time(s).. Thu Jun 18 18:00:44 2020 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.8' (4)) Thu Jun 18 18:00:44 2020 - [warning] Connection failed 4 time(s).. Thu Jun 18 18:00:46 2020 - [warning] HealthCheck: Got timeout on checking SSH connection to 10.0.0.8! at /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm line 342. Thu Jun 18 18:00:46 2020 - [warning] Master is not reachable from health checker! Thu Jun 18 18:00:46 2020 - [warning] Master 10.0.0.8(10.0.0.8:3306) is not reachable! Thu Jun 18 18:00:46 2020 - [warning] SSH is NOT reachable. Thu Jun 18 18:00:46 2020 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mastermha/app1.cnf again, and trying to connect to all servers to check server status.. Thu Jun 18 18:00:46 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Jun 18 18:00:46 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 18:00:46 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Thu Jun 18 18:00:47 2020 - [info] GTID failover mode = 0 Thu Jun 18 18:00:47 2020 - [info] Dead Servers: Thu Jun 18 18:00:47 2020 - [info] 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:47 2020 - [info] Alive Servers: Thu Jun 18 18:00:47 2020 - [info] 10.0.0.18(10.0.0.18:3306) Thu Jun 18 18:00:47 2020 - [info] 10.0.0.28(10.0.0.28:3306) Thu Jun 18 18:00:47 2020 - [info] Alive Slaves: Thu Jun 18 18:00:47 2020 - [info] 10.0.0.18(10.0.0.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:47 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:47 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jun 18 18:00:47 2020 - [info] 10.0.0.28(10.0.0.28:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:47 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:47 2020 - [info] Checking slave configurations.. Thu Jun 18 18:00:47 2020 - [info] Checking replication filtering settings.. Thu Jun 18 18:00:47 2020 - [info] Replication filtering check ok. Thu Jun 18 18:00:47 2020 - [info] Master is down! Thu Jun 18 18:00:47 2020 - [info] Terminating monitoring script. Thu Jun 18 18:00:47 2020 - [info] Got exit code 20 (Master dead). Thu Jun 18 18:00:47 2020 - [info] MHA::MasterFailover version 0.56. Thu Jun 18 18:00:47 2020 - [info] Starting master failover. Thu Jun 18 18:00:47 2020 - [info] Thu Jun 18 18:00:47 2020 - [info] * Phase 1: Configuration Check Phase.. Thu Jun 18 18:00:47 2020 - [info] Thu Jun 18 18:00:49 2020 - [info] GTID failover mode = 0 Thu Jun 18 18:00:49 2020 - [info] Dead Servers: Thu Jun 18 18:00:49 2020 - [info] 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:49 2020 - [info] Checking master reachability via MySQL(double check)... Thu Jun 18 18:00:50 2020 - [info] ok. Thu Jun 18 18:00:50 2020 - [info] Alive Servers: Thu Jun 18 18:00:50 2020 - [info] 10.0.0.18(10.0.0.18:3306) Thu Jun 18 18:00:50 2020 - [info] 10.0.0.28(10.0.0.28:3306) Thu Jun 18 18:00:50 2020 - [info] Alive Slaves: Thu Jun 18 18:00:50 2020 - [info] 10.0.0.18(10.0.0.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:50 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:50 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jun 18 18:00:50 2020 - [info] 10.0.0.28(10.0.0.28:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:50 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:50 2020 - [info] Starting Non-GTID based failover. Thu Jun 18 18:00:50 2020 - [info] Thu Jun 18 18:00:50 2020 - [info] ** Phase 1: Configuration Check Phase completed. Thu Jun 18 18:00:50 2020 - [info] Thu Jun 18 18:00:50 2020 - [info] * Phase 2: Dead Master Shutdown Phase.. Thu Jun 18 18:00:50 2020 - [info] Thu Jun 18 18:00:50 2020 - [info] Forcing shutdown so that applications never connect to the current master.. Thu Jun 18 18:00:50 2020 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address. Thu Jun 18 18:00:50 2020 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Thu Jun 18 18:00:51 2020 - [info] * Phase 2: Dead Master Shutdown Phase completed. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 3: Master Recovery Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] The latest binary log file/position on all slaves is mariadb-bin.000002:2526238 Thu Jun 18 18:00:51 2020 - [info] Latest slaves (Slaves that received relay log files to the latest): Thu Jun 18 18:00:51 2020 - [info] 10.0.0.18(10.0.0.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:51 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:51 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jun 18 18:00:51 2020 - [info] 10.0.0.28(10.0.0.28:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:51 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:51 2020 - [info] The oldest binary log file/position on all slaves is mariadb-bin.000002:2526238 Thu Jun 18 18:00:51 2020 - [info] Oldest slaves: Thu Jun 18 18:00:51 2020 - [info] 10.0.0.18(10.0.0.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:51 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:51 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jun 18 18:00:51 2020 - [info] 10.0.0.28(10.0.0.28:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:51 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [warning] Dead Master is not SSH reachable. Could not save it's binlogs. Transactions that were not sent to the latest slave (Read_Master_Log_Pos to the tail of the dead master's binlog) were lost. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 3.3: Determining New Master Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] Finding the latest slave that has all relay logs for recovering other slaves.. Thu Jun 18 18:00:51 2020 - [info] All slaves received relay logs to the same position. No need to resync each other. Thu Jun 18 18:00:51 2020 - [info] Searching new master from slaves.. Thu Jun 18 18:00:51 2020 - [info] Candidate masters from the configuration file: Thu Jun 18 18:00:51 2020 - [info] 10.0.0.18(10.0.0.18:3306) Version=10.3.17-MariaDB-log (oldest major version between slaves) log-bin:enabled Thu Jun 18 18:00:51 2020 - [info] Replicating from 10.0.0.8(10.0.0.8:3306) Thu Jun 18 18:00:51 2020 - [info] Primary candidate for the new Master (candidate_master is set) Thu Jun 18 18:00:51 2020 - [info] Non-candidate masters: Thu Jun 18 18:00:51 2020 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Thu Jun 18 18:00:51 2020 - [info] New master is 10.0.0.18(10.0.0.18:3306) Thu Jun 18 18:00:51 2020 - [info] Starting master failover.. Thu Jun 18 18:00:51 2020 - [info] From: 10.0.0.8(10.0.0.8:3306) (current master) +--10.0.0.18(10.0.0.18:3306) +--10.0.0.28(10.0.0.28:3306) To: 10.0.0.18(10.0.0.18:3306) (new master) +--10.0.0.28(10.0.0.28:3306) Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 3.3: New Master Diff Log Generation Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 3.4: Master Log Apply Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed. Thu Jun 18 18:00:51 2020 - [info] Starting recovery on 10.0.0.18(10.0.0.18:3306).. Thu Jun 18 18:00:51 2020 - [info] This server has all relay logs. Waiting all logs to be applied.. Thu Jun 18 18:00:51 2020 - [info] done. Thu Jun 18 18:00:51 2020 - [info] All relay logs were successfully applied. Thu Jun 18 18:00:51 2020 - [info] Getting new master's binlog name and position.. Thu Jun 18 18:00:51 2020 - [info] mariadb-bin.000002:344 Thu Jun 18 18:00:51 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344, MASTER_USER='repluser', MASTER_PASSWORD='xxx'; Thu Jun 18 18:00:51 2020 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address. Thu Jun 18 18:00:51 2020 - [info] Setting read_only=0 on 10.0.0.18(10.0.0.18:3306).. Thu Jun 18 18:00:51 2020 - [info] ok. Thu Jun 18 18:00:51 2020 - [info] ** Finished master recovery successfully. Thu Jun 18 18:00:51 2020 - [info] * Phase 3: Master Recovery Phase completed. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 4: Slaves Recovery Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase.. Thu Jun 18 18:00:51 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] -- Slave diff file generation on host 10.0.0.28(10.0.0.28:3306) started, pid: 4902. Check tmp log /data/mastermha/app1//10.0.0.28_3306_20200618180047.log if it takes time.. Thu Jun 18 18:00:52 2020 - [info] Thu Jun 18 18:00:52 2020 - [info] Log messages from 10.0.0.28 ... Thu Jun 18 18:00:52 2020 - [info] Thu Jun 18 18:00:51 2020 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Thu Jun 18 18:00:52 2020 - [info] End of log messages from 10.0.0.28. Thu Jun 18 18:00:52 2020 - [info] -- 10.0.0.28(10.0.0.28:3306) has the latest relay log events. Thu Jun 18 18:00:52 2020 - [info] Generating relay diff files from the latest slave succeeded. Thu Jun 18 18:00:52 2020 - [info] Thu Jun 18 18:00:52 2020 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase.. Thu Jun 18 18:00:52 2020 - [info] Thu Jun 18 18:00:52 2020 - [info] -- Slave recovery on host 10.0.0.28(10.0.0.28:3306) started, pid: 4904. Check tmp log /data/mastermha/app1//10.0.0.28_3306_20200618180047.log if it takes time.. Thu Jun 18 18:00:53 2020 - [info] Thu Jun 18 18:00:53 2020 - [info] Log messages from 10.0.0.28 ... Thu Jun 18 18:00:53 2020 - [info] Thu Jun 18 18:00:52 2020 - [info] Starting recovery on 10.0.0.28(10.0.0.28:3306).. Thu Jun 18 18:00:52 2020 - [info] This server has all relay logs. Waiting all logs to be applied.. Thu Jun 18 18:00:52 2020 - [info] done. Thu Jun 18 18:00:52 2020 - [info] All relay logs were successfully applied. Thu Jun 18 18:00:52 2020 - [info] Resetting slave 10.0.0.28(10.0.0.28:3306) and starting replication from the new master 10.0.0.18(10.0.0.18:3306).. Thu Jun 18 18:00:52 2020 - [info] Executed CHANGE MASTER. Thu Jun 18 18:00:52 2020 - [info] Slave started. Thu Jun 18 18:00:53 2020 - [info] End of log messages from 10.0.0.28. Thu Jun 18 18:00:53 2020 - [info] -- Slave recovery on host 10.0.0.28(10.0.0.28:3306) succeeded. Thu Jun 18 18:00:53 2020 - [info] All new slave servers recovered successfully. Thu Jun 18 18:00:53 2020 - [info] Thu Jun 18 18:00:53 2020 - [info] * Phase 5: New master cleanup phase.. Thu Jun 18 18:00:53 2020 - [info] Thu Jun 18 18:00:53 2020 - [info] Resetting slave info on the new master.. Thu Jun 18 18:00:53 2020 - [info] 10.0.0.18: Resetting slave info succeeded. Thu Jun 18 18:00:53 2020 - [info] Master failover to 10.0.0.18(10.0.0.18:3306) completed successfully. Thu Jun 18 18:00:53 2020 - [info] ----- Failover Report ----- app1: MySQL Master failover 10.0.0.8(10.0.0.8:3306) to 10.0.0.18(10.0.0.18:3306) succeeded Master 10.0.0.8(10.0.0.8:3306) is down! Check MHA Manager logs at mha-manager:/data/mastermha/app1/manager.log for details. Started automated(non-interactive) failover. The latest slave 10.0.0.18(10.0.0.18:3306) has all relay logs for recovery. Selected 10.0.0.18(10.0.0.18:3306) as a new master. 10.0.0.18(10.0.0.18:3306): OK: Applying all logs succeeded. 10.0.0.28(10.0.0.28:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 10.0.0.28(10.0.0.28:3306): OK: Applying all logs succeeded. Slave started, replicating from 10.0.0.18(10.0.0.18:3306) 10.0.0.18(10.0.0.18:3306): Resetting slave info succeeded. Master failover to 10.0.0.18(10.0.0.18:3306) completed successfully. [root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf app1 is stopped(2:NOT_RUNNING). #新上位的从服务器10.0.0.18,只读已经自动关闭 MariaDB [(none)]> select @@read_only; +-------------+ | @@read_only | +-------------+ | 0 | +-------------+ 1 row in set (0.000 sec) #而且另一个从服务器10.0.0.28也换了新主人 MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.18 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 344 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 557 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
6.3.3 Galera Cluster
6.3.3.1 Galera Cluster介绍
Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案
Galera Cluster特点
- 多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的
- 同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失
- 并发复制：从节点APPLY数据时，支持并行执行，更好的性能
- 故障切换：在出现数据库故障时，因支持多点写入，切换容易
- 热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在节点故障期间，节点本身对集群的影响非常小
- 自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一致
- 对应用透明：集群的维护，对应用程序是透明的
Galera Cluster 缺点
- 由于DDL 需全局验证通过，则集群性能由集群中最差性能节点决定（一般集群节点配置都是一样的）
- 新节点加入或延后较大的节点重新加入需全量拷贝数据(SST，State Snapshot Transfer),作为donor( 贡献者，如： 同步数据时的提供者)的节点在同步过程中无法提供读写
- 只支持innodb存储引擎的表
Galera Cluster工作过程
Galera Cluster官方文档：
http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://www.percona.com/doc/percona-xtradb-cluster/LATEST/index.html
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
Galera Cluster包括两个组件
Galera replication library (galera-3)
WSREP：MySQL extended with the Write Set Replication
WSREP复制实现：
- PXC：Percona XtraDB Cluster，是Percona对Galera的实现
参考仓库：
https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
- MariaDB Galera Cluster：
参考仓库：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/
注意：两者都需要至少三个节点，不能安装mysql server 或 mariadb-server
6.3.3.2 PXC 原理
PXC最常使用如下4个端口号：
- 3306：数据库对外服务的端口号
- 4444：请求SST的端口号
- 4567：组成员之间进行沟通的端口号
- 4568：用于传输IST的端口号
PXC中涉及到的重要概念和核心参数：
（1）集群中节点的数量：整个集群中节点数量应该控制在最少3个、最多8个的范围内。最少3个节点是为了防止出现脑裂现象，因为只有在2个节点下才会出现此现象。脑裂现象的标志就是输入任何命令，返回的结果都是unknown command。节点在集群中，会因新节点的加入或故障、同步失效等原因发生状态的切换。
（2）节点状态的变化阶段：
- open：节点启动成功，尝试连接到集群时的状态
- primary：节点已处于集群中，在新节点加入并选取donor进行数据同步时的状态
- joiner：节点处于等待接收同步文件时的状态
- joined：节点完成数据同步工作，尝试保持和集群进度一致时的状态
- synced：节点正常提供服务时的状态，表示已经同步完成并和集群进度保持一致
- donor：节点处于为新加入的节点提供全量数据时的状态
备注：donor节点就是数据的贡献者，如果一个新节点加入集群，此时又需要大量数据的SST数据传输，就有可能因此而拖垮整个集群的性能，所以在生产环境中，如果数据量较小，还可以使用SST全量数据传输，但如果数据量很大就不建议使用这种方式，可以考虑先建立主从关系，然后再加入集群。
（3）节点的数据传输方式：
- SST：State Snapshot Transfer，全量数据传输
- IST：Incremental State Transfer，增量数据传输
SST数据传输有xtrabackup、mysqldump和rsync三种方式，而增量数据传输就只有一种方式 xtrabackup，但生产环境中一般数据量较小时，可以使用SST全量数据传输，但也只使用xtrabackup方法。
（4）GCache模块：在PXC中一个特别重要的模块，它的核心功能就是为每个节点缓存当前最新的写集。如果有新节点加入进来，就可以把新数据的增量传递给新节点，而不需要再使用SST传输方式，这样可以让节点更快地加入集群中，涉及如下参数：
- gcache.size：缓存写集增量信息的大小，它的默认大小是128MB，通过wsrep_provider_options参数设置，建议调整为2GB~4GB范围，足够的空间便于缓存更多的增量信息。
- gcache.mem_size：GCache中内存缓存的大小，适度调大可以提高整个集群的性能
- gcache.page_size：如果内存不够用（GCache不足），就直接将写集写入磁盘文件中
6.3.3.3 实战案例：Percona XtraDB Cluster(PXC 5.7）
1 环境准备
四台主机
pxc1:10.0.0.7
pxc1:10.0.0.17
pxc1:10.0.0.27
pxc4:10.0.0.37
- 1
- 2
- 3
- 4
OS 版本目前不支持CentOS 8
[root@pxc1 ~]#cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
- 1
- 2
关闭防火墙和SELinux，保证时间同步
注意：如果已经安装MySQL，必须卸载
2 安装 Percona XtraDB Cluster 5.7
#此处使用清华大学yum源，官方源太慢了
[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona_repo
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.17:/etc/yum.repos.d
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.27:/etc/yum.repos.d
#在三个节点都安装好PXC 5.7
[root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]#yum install Percona-XtraDB-Cluster-57 -y
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
3 在各个节点上分别配置mysql及集群配置文件
/etc/my.cnf为主配置文件，当前版本中，其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三个文件
#主配置文件不需要修改 [root@pxc1 ~]#cat /etc/my.cnf # The Percona XtraDB Cluster 5.7 configuration file. ...省略... !includedir /etc/my.cnf.d/ !includedir /etc/percona-xtradb-cluster.conf.d/ [root@pxc1 ~]#ls /etc/my.cnf.d/ [root@pxc1 ~]#ls /etc/percona-xtradb-cluster.conf.d/ mysqld.cnf mysqld_safe.cnf wsrep.cnf #下面配置文件不需要修改 [root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld.cnf ...省略... [client] socket=/var/lib/mysql/mysql.sock [mysqld] server-id=1 #建议各个节点不同 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid log-bin #建议启用，非必须项 log_slave_updates expire_logs_days=7 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #下面配置文件不需要修改 [root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf ...省略... [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock nice = 0 #PXC的配置文件必须修改 [root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc1 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27 #三个节点的IP binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=10.0.0.7 #各个节点，指定自已的IP wsrep_cluster_name=pxc-cluster wsrep_node_name=pxc-cluster-node-1 #各个节点，指定自已节点名称 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释 [root@pxc2 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 #各个节点，指定自已的IP wsrep_node_address=10.0.0.17 wsrep_cluster_name=pxc-cluster wsrep_node_name=pxc-cluster-node-2 #各个节点，指定自已节点名称 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释 [root@pxc3 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=10.0.0.27 #各个节点，指定自已的IP wsrep_cluster_name=pxc-cluster wsrep_node_name=pxc-cluster-node-3 #各个节点，指定自已的IP pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
注意：尽管Galera Cluster不再需要通过binlog的形式进行同步，但还是建议在配置文件中开启二进制日志功能，原因是后期如果有新节点需要加入，老节点通过SST全量传输的方式向新节点传输数据，很可能会拖垮集群性能，所以让新节点先通过binlog方式完成同步后再加入集群会是一种更好的选择
配置文件各项配置意义
| 配置 | 说明 |
|---|---|
| wsrep_provider | 指定Galera库的路径 |
| wsrep_cluster_name | Galera集群的名称 |
| wsrep_cluster_address | Galera集群中各节点地址。地址使用组通信协议 gcomm://(group communication) |
| wsrep_node_name | 本节点在Galera集群中的名称 |
| wsrep_node_address | 本节点在Galera集群中的通信地址 |
| wsrep_sst_method | state_snapshot_transfer(SST)使用的传输方法，可用方法有 mysqldump、rsync和xtrabackup，前两者在传输时都需要对 Donor加全局只读锁(FLUSH TABLES WITH READ LOCK)， xtrabackup则不需要(它使用percona自己提供的backup lock)。 强烈建议采用xtrabackup |
| wsrep_sst_auth | 在SST传输时需要用到的认证凭据，格式为：“用户：密码” |
| pxc_strict_mode | 是否限制PXC启用正在试用阶段的功能，ENFORCING是默认值， 表示不启用 |
| binlog_format | 二进制日志的格式。Galera只支持row格式的二进制日志 |
| default_storage_engine | 指定默认存储引擎。Galera的复制功能只支持InnoDB |
| innodb_autoinc_lock_mode | 只能设置为2，设置为0或1时会无法正确处理死锁问题 |
4 启动PXC集群中第一个节点
[root@Centos7 ~]#ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:111 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 [::]:111 [::]:* LISTEN 0 128 [::]:22 [::]:* #启动第一个节点 [root@Centos7 ~]#systemctl start mysql@bootstrap.service [root@Centos7 ~]#ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:111 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 *:4567 *:* LISTEN 0 80 [::]:3306 [::]:* LISTEN 0 128 [::]:111 [::]:* LISTEN 0 128 [::]:22 [::]:* #查看root密码 [root@Centos7 ~]#grep "temporary password" /var/log/mysqld.log 2020-06-18T14:16:34.320363Z 1 [Note] A temporary password is generated for root@localhost: Wy4GgKwWB%,z [root@Centos7 ~]#mysql -uroot -p'Wy4GgKwWB%,z' 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.29-32-57-log Copyright (c) 2009-2020 Percona LLC and/or its affiliates Copyright (c) 2000, 2020, 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. #修改root密码 mysql> alter user 'root'@'localhost' identified by 'magedu'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; Query OK, 0 rows affected (0.01 sec) #查看相关变量 mysql> SHOW VARIABLES LIKE 'wsrep%'\G *************************** 1. row *************************** Variable_name: wsrep_OSU_method Value: TOI *************************** 2. row *************************** Variable_name: wsrep_RSU_commit_timeout Value: 5000 *************************** 3. row *************************** Variable_name: wsrep_auto_increment_control Value: ON *************************** 4. row *************************** Variable_name: wsrep_causal_reads Value: OFF *************************** 5. row *************************** Variable_name: wsrep_certification_rules Value: strict *************************** 6. row *************************** Variable_name: wsrep_certify_nonPK Value: ON *************************** 7. row *************************** Variable_name: wsrep_cluster_address Value: gcomm://10.0.0.7,10.0.0.17,10.0.0.27 *************************** 8. row *************************** Variable_name: wsrep_cluster_name Value: pxc-cluster-magedu *************************** 9. row *************************** Variable_name: wsrep_convert_LOCK_to_trx Value: OFF *************************** 10. row *************************** Variable_name: wsrep_data_home_dir Value: /var/lib/mysql/ *************************** 11. row *************************** Variable_name: wsrep_dbug_option Value: *************************** 12. row *************************** Variable_name: wsrep_debug Value: OFF *************************** 13. row *************************** Variable_name: wsrep_desync Value: OFF *************************** 14. row *************************** Variable_name: wsrep_dirty_reads Value: OFF *************************** 15. row *************************** Variable_name: wsrep_drupal_282555_workaround Value: OFF *************************** 16. row *************************** Variable_name: wsrep_forced_binlog_format Value: NONE *************************** 17. row *************************** Variable_name: wsrep_load_data_splitting Value: ON *************************** 18. row *************************** Variable_name: wsrep_log_conflicts Value: ON *************************** 19. row *************************** Variable_name: wsrep_max_ws_rows Value: 0 *************************** 20. row *************************** Variable_name: wsrep_max_ws_size Value: 2147483647 *************************** 21. row *************************** Variable_name: wsrep_node_address Value: 10.0.0.7 *************************** 22. row *************************** Variable_name: wsrep_node_incoming_address Value: AUTO *************************** 23. row *************************** Variable_name: wsrep_node_name Value: pxc-cluster-magedu-node-1 *************************** 24. row *************************** Variable_name: wsrep_notify_cmd Value: *************************** 25. row *************************** Variable_name: wsrep_on Value: ON *************************** 26. row *************************** Variable_name: wsrep_preordered Value: OFF *************************** 27. row *************************** Variable_name: wsrep_provider Value: /usr/lib64/galera3/libgalera_smm.so *************************** 28. row *************************** Variable_name: wsrep_provider_options Value: base_dir = /var/lib/mysql/; base_host = 10.0.0.7; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs *************************** 29. row *************************** Variable_name: wsrep_recover Value: OFF *************************** 30. row *************************** Variable_name: wsrep_reject_queries Value: NONE *************************** 31. row *************************** Variable_name: wsrep_replicate_myisam Value: OFF *************************** 32. row *************************** Variable_name: wsrep_restart_slave Value: OFF *************************** 33. row *************************** Variable_name: wsrep_retry_autocommit Value: 1 *************************** 34. row *************************** Variable_name: wsrep_slave_FK_checks Value: ON *************************** 35. row *************************** Variable_name: wsrep_slave_UK_checks Value: OFF *************************** 36. row *************************** Variable_name: wsrep_slave_threads Value: 8 *************************** 37. row *************************** Variable_name: wsrep_sst_auth Value: ******** *************************** 38. row *************************** Variable_name: wsrep_sst_donor Value: *************************** 39. row *************************** Variable_name: wsrep_sst_donor_rejects_queries Value: OFF *************************** 40. row *************************** Variable_name: wsrep_sst_method Value: xtrabackup-v2 *************************** 41. row *************************** Variable_name: wsrep_sst_receive_address Value: AUTO *************************** 42. row *************************** Variable_name: wsrep_start_position Value: 00000000-0000-0000-0000-000000000000:-1 *************************** 43. row *************************** Variable_name: wsrep_sync_wait Value: 0 43 rows in set (0.01 sec) #查看相关状态变量 mysql> SHOW STATUS LIKE 'wsrep%'\G *************************** 1. row *************************** Variable_name: wsrep_local_state_uuid Value: 5663812d-b16e-11ea-9be1-ab469ea047d3 *************************** 2. row *************************** Variable_name: wsrep_protocol_version Value: 9 *************************** 3. row *************************** Variable_name: wsrep_last_applied Value: 3 *************************** 4. row *************************** Variable_name: wsrep_last_committed Value: 3 *************************** 5. row *************************** Variable_name: wsrep_replicated Value: 3 *************************** 6. row *************************** Variable_name: wsrep_replicated_bytes Value: 760 *************************** 7. row *************************** Variable_name: wsrep_repl_keys Value: 3 *************************** 8. row *************************** Variable_name: wsrep_repl_keys_bytes Value: 96 *************************** 9. row *************************** Variable_name: wsrep_repl_data_bytes Value: 463 *************************** 10. row *************************** Variable_name: wsrep_repl_other_bytes Value: 0 *************************** 11. row *************************** Variable_name: wsrep_received Value: 2 *************************** 12. row *************************** Variable_name: wsrep_received_bytes Value: 157 *************************** 13. row *************************** Variable_name: wsrep_local_commits Value: 0 *************************** 14. row *************************** Variable_name: wsrep_local_cert_failures Value: 0 *************************** 15. row *************************** Variable_name: wsrep_local_replays Value: 0 *************************** 16. row *************************** Variable_name: wsrep_local_send_queue Value: 0 *************************** 17. row *************************** Variable_name: wsrep_local_send_queue_max Value: 1 *************************** 18. row *************************** Variable_name: wsrep_local_send_queue_min Value: 0 *************************** 19. row *************************** Variable_name: wsrep_local_send_queue_avg Value: 0.000000 *************************** 20. row *************************** Variable_name: wsrep_local_recv_queue Value: 0 *************************** 21. row *************************** Variable_name: wsrep_local_recv_queue_max Value: 1 *************************** 22. row *************************** Variable_name: wsrep_local_recv_queue_min Value: 0 *************************** 23. row *************************** Variable_name: wsrep_local_recv_queue_avg Value: 0.000000 *************************** 24. row *************************** Variable_name: wsrep_local_cached_downto Value: 1 *************************** 25. row *************************** Variable_name: wsrep_flow_control_paused_ns Value: 0 *************************** 26. row *************************** Variable_name: wsrep_flow_control_paused Value: 0.000000 *************************** 27. row *************************** Variable_name: wsrep_flow_control_sent Value: 0 *************************** 28. row *************************** Variable_name: wsrep_flow_control_recv Value: 0 *************************** 29. row *************************** Variable_name: wsrep_flow_control_interval Value: [ 100, 100 ] *************************** 30. row *************************** Variable_name: wsrep_flow_control_interval_low Value: 100 *************************** 31. row *************************** Variable_name: wsrep_flow_control_interval_high Value: 100 *************************** 32. row *************************** Variable_name: wsrep_flow_control_status Value: OFF *************************** 33. row *************************** Variable_name: wsrep_cert_deps_distance Value: 1.000000 *************************** 34. row *************************** Variable_name: wsrep_apply_oooe Value: 0.000000 *************************** 35. row *************************** Variable_name: wsrep_apply_oool Value: 0.000000 *************************** 36. row *************************** Variable_name: wsrep_apply_window Value: 1.000000 *************************** 37. row *************************** Variable_name: wsrep_commit_oooe Value: 0.000000 *************************** 38. row *************************** Variable_name: wsrep_commit_oool Value: 0.000000 *************************** 39. row *************************** Variable_name: wsrep_commit_window Value: 1.000000 *************************** 40. row *************************** Variable_name: wsrep_local_state Value: 4 *************************** 41. row *************************** Variable_name: wsrep_local_state_comment Value: Synced *************************** 42. row *************************** Variable_name: wsrep_cert_index_size Value: 1 *************************** 43. row *************************** Variable_name: wsrep_cert_bucket_count Value: 22 *************************** 44. row *************************** Variable_name: wsrep_gcache_pool_size Value: 2200 *************************** 45. row *************************** Variable_name: wsrep_causal_reads Value: 0 *************************** 46. row *************************** Variable_name: wsrep_cert_interval Value: 0.000000 *************************** 47. row *************************** Variable_name: wsrep_open_transactions Value: 0 *************************** 48. row *************************** Variable_name: wsrep_open_connections Value: 0 *************************** 49. row *************************** Variable_name: wsrep_ist_receive_status Value: *************************** 50. row *************************** Variable_name: wsrep_ist_receive_seqno_start Value: 0 *************************** 51. row *************************** Variable_name: wsrep_ist_receive_seqno_current Value: 0 *************************** 52. row *************************** Variable_name: wsrep_ist_receive_seqno_end Value: 0 *************************** 53. row *************************** Variable_name: wsrep_incoming_addresses Value: 10.0.0.7:3306 *************************** 54. row *************************** Variable_name: wsrep_cluster_weight Value: 1 *************************** 55. row *************************** Variable_name: wsrep_desync_count Value: 0 *************************** 56. row *************************** Variable_name: wsrep_evs_delayed Value: *************************** 57. row *************************** Variable_name: wsrep_evs_evict_list Value: *************************** 58. row *************************** Variable_name: wsrep_evs_repl_latency Value: 0/0/0/0/0 *************************** 59. row *************************** Variable_name: wsrep_evs_state Value: OPERATIONAL *************************** 60. row *************************** Variable_name: wsrep_gcomm_uuid Value: 56623dc3-b16e-11ea-ad86-9a043828dac2 *************************** 61. row *************************** Variable_name: wsrep_cluster_conf_id Value: 1 *************************** 62. row *************************** Variable_name: wsrep_cluster_size Value: 1 *************************** 63. row *************************** Variable_name: wsrep_cluster_state_uuid Value: 5663812d-b16e-11ea-9be1-ab469ea047d3 *************************** 64. row *************************** Variable_name: wsrep_cluster_status Value: Primary *************************** 65. row *************************** Variable_name: wsrep_connected Value: ON *************************** 66. row *************************** Variable_name: wsrep_local_bf_aborts Value: 0 *************************** 67. row *************************** Variable_name: wsrep_local_index Value: 0 *************************** 68. row *************************** Variable_name: wsrep_provider_name Value: Galera *************************** 69. row *************************** Variable_name: wsrep_provider_vendor Value: Codership Oy <info@codership.com> *************************** 70. row *************************** Variable_name: wsrep_provider_version Value: 3.43(ra60e019) *************************** 71. row *************************** Variable_name: wsrep_ready Value: ON 71 rows in set (0.00 sec) #重点关注下面内容 mysql> show status like 'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | aad2c02e-131c-11ea-9294-b2e80a6c08c4 | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+--------------------------------------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
说明：
wsrep_cluster_size表示，该Galera集群中只有一个节点
wsrep_local_state_comment 状态为Synced(4)，表示数据已同步完成(因为是第一个引导节点，无数据需要同步)。 如果状态是Joiner, 意味着 SST 没有完成. 只有所有节点状态是Synced，才可以加新节点
wsrep_cluster_status为Primary，且已经完全连接并准备好
5 启动PXC集群中其它所有节点
[root@Centos7 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 128 [::]:111 [::]:*
[root@Centos7 ~]#systemctl start mysql
[root@Centos7 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 128 *:4567 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 [::]:3306 [::]:*
LISTEN 0 128 [::]:111 [::]:*
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
6 查看集群状态，验证集群是否成功
#在任意节点，查看集群状态 [root@Centos7 ~]#mysql -uroot -pmagedu mysql> SHOW VARIABLES LIKE 'wsrep_node_name'; +-----------------+---------------------------+ | Variable_name | Value | +-----------------+---------------------------+ | wsrep_node_name | pxc-cluster-magedu-node-1 | +-----------------+---------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'wsrep_node_address'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | wsrep_node_address | 10.0.0.7 | +--------------------+----------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'wsrep_on'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wsrep_on | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec) #在任意节点查看数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) #在任意节点创建数据库 mysql> create database testdb1; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb1 | +--------------------+ 5 rows in set (0.00 sec) #在任意其它节点验证数据是否同步 [root@pxc2 ~]#mysql -uroot -pmagedu mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb1 | +--------------------+ 5 rows in set (0.00 sec) #利用Xshell软件，同时在三个节点数据库，在其中一个节点成功 mysql> create database testdb2; Query OK, 1 row affected (0.01 sec) #在其它节点都提示失败 mysql> create database testdb2; ERROR 1007 (HY000): Can't create database 'testdb2'; database exists
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
7 在PXC集群中加入节点
一个节点加入到Galera集群有两种情况：新节点加入集群、暂时离组的成员再次加入集群
1）新节点加入Galera集群
新节点加入集群时，需要从当前集群中选择一个Donor节点来同步数据，也就是所谓的 state_snapshot_tranfer(SST)过程。SST同步数据的方式由选项wsrep_sst_method决定，一般选择的是xtrabackup。
必须注意，新节点加入Galera时，会删除新节点上所有已有数据，再通过xtrabackup(假设使用的是该方式)从Donor处完整备份所有数据进行恢复。所以，如果数据量很大，新节点加入过程会很慢。而且，在一个新节点成为Synced状态之前，不要同时加入其它新节点，否则很容易将集群压垮。
如果是这种情况，可以考虑使用wsrep_sst_method=rsync来做增量同步，既然是增量同步，最好保证新节点上已经有一部分数据基础，否则和全量同步没什么区别，且这样会对Donor节点加上全局read only锁。
2）旧节点加入Galera集群
如果旧节点加入Galera集群，说明这个节点在之前已经在Galera集群中呆过，有一部分数据基础，缺少的只是它离开集群时的数据。这时加入集群时，会采用IST(incremental snapshot transfer)传输机制，即使用增量传输。
但注意，这部分增量传输的数据源是Donor上缓存在GCache文件中的，这个文件有大小限制，如果缺失的数据范围超过已缓存的内容，则自动转为SST传输。如果旧节点上的数据和Donor上的数据不匹配(例如这个节点离组后人为修改了一点数据)，则自动转为SST传输。
#在PXC集群中再加一台新的主机PXC4：10.0.0.37 [root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y [root@pxc4 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@Centos7 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27,10.0.0.37 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=10.0.0.37 wsrep_cluster_name=pxc-cluster-magedu wsrep_node_name=pxc-cluster-magedu-node-4 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:s3cretPass" [root@Centos7 ~]#systemctl start mysql [root@Centos7 ~]#mysql -uroot -pmagedu mysql> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 4 | +--------------------+-------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db2 | | mysql | | performance_schema | | sys | | testdb1 | +--------------------+ 6 rows in set (0.00 sec) #将其它节点的配置文件加以修改 [root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27,10.0.0.37 [root@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
8 在PXC集群中修复故障节点
#在任意节点停止服务 [root@pxc4 ~]#systemctl stop mysql #在其它任意节点查看wsrep_cluster_size变量少了一个节点 [root@pxc1 ~]#mysql -uroot -pmagedu Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30,Revision mysql> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec) mysql> create database testdb4; #在其它任意节点可看到数据已同步 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db2 | | mysql | | performance_schema | | sys | | testdb1 | | testdb4 | +--------------------+ 7 rows in set (0.00 sec) #恢复服务，数据同步 [root@pxc4 ~]#systemctl start mysql [root@pxc4 ~]#mysql -uroot -pmagedu mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db2 | | mysql | | performance_schema | | sys | | testdb1 | | testdb4 | +--------------------+ 7 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 4 | +--------------------+-------+ 1 row in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
6.3.3.4 实战案例：MariaDB Galera Cluster
范例：在centos8 实现MariaDB Galera Cluster
#在三个节点上都实现 [root@centos8 ~]#dnf install mariadb-server-galera -y [root@centos8 ~]#vim /etc/my.cnf.d/galera.cnf #wsrep_cluster_address="dummy://" wsrep_cluster_address="gcomm://10.0.0.8,10.0.0.18,10.0.0.28" #启动第一节点 [root@centos8 ~]#galera_new_cluster #再启动其它节点 [root@centos8 ~]#systemctl start mariadb [root@centos8 ~]#ss -ntul [root@centos8 ~]#mysql MariaDB [(none)]> show status like "wsrep_ready"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wsrep_ready | ON | +---------------+-------+ 1 row in set (0.001 sec) MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.001 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_%'\G MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
范例：CentOS 7 实现 MariaDB Galera Cluster 5.5
#参考仓库：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/ yum install MariaDB-Galera-server vim /etc/my.cnf.d/server.cnf [galera] wsrep_provider = /usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://10.0.0.7,10.0.0.17,10.0.0.27" binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 #下面配置可选项 wsrep_cluster_name = 'mycluster' 默认my_wsrep_cluster wsrep_node_name = 'node1' wsrep_node_address = '10.0.0.7’ #首次启动时，需要初始化集群，在其中一个节点上执行命令 /etc/init.d/mysql start --wsrep-new-cluster #而后正常启动其它节点 service mysql start #查看集群中相关系统变量和状态变量 SHOW VARIABLES LIKE 'wsrep_%'; SHOW STATUS LIKE 'wsrep_%'; SHOW STATUS LIKE 'wsrep_cluster_size';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
6.3.4 TiDB概述
TiDB 是 PingCAP 公司受 Google Spanner / F1 论文启发而设计的开源分布式 HTAP (Hybrid Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。
TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB和mysql几乎完全兼容
TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和MySQL 协议，具有数据强一致的高可用特性，是一个不仅适合 OLTP 场景还适合 OLAP 场景的混合数据库。
TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。
6.3.4.1 TiDB 核心特点
-
高度兼容 MySQL 大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的MySQL 集群亦可通过 TiDB 工具进行实时迁移
-
水平弹性扩展 通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景
-
分布式事务 TiDB 100% 支持标准的 ACID 事务
-
真正金融级高可用 相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可实现故障的自动恢复 (autofailover)，无需人工介入
-
一站式 HTAP 解决方案 TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合TiSpark，可提供一站式 HTAP解决方案，一份存储同时处理OLTP & OLAP(OLAP、OLTP的介绍和比较 )无需传统繁琐的 ETL 过程
-
云原生 SQL 数据库 TiDB 是为云而设计的数据库，同 Kubernetes 深度耦合，支持公有云、私有云和混合云，使部署、配置和维护变得十分简单。 TiDB 的设计目标是 100% 的 OLTP 场景和80% 的 OLAP 场景，更复杂的 OLAP 分析可以通过 TiSpark 项目来完成。 TiDB 对业务没有任何侵入性，能优雅的替换传统的数据库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale 的细节问题，专注于业务开发，极大的提升研发的生产力
6.3.4.2 TiDB整体架构
TiDB Server
TiDB Server 负责接收SQL请求，处理SQL相关的逻辑，并通过PD找到存储计算所需数据的TiKV地址，与TiKV交互获取数据，最终返回结果。TiDB Server 是无状态的，其本身并不存储数据，只负责计算，可以无限水平扩展，可以通过负载均衡组件（LVS、HAProxy或F5）对外提供统一的接入地址。
PD Server
Placement Driver（简称PD）是整个集群的管理模块，其主要工作有三个：一是存储集群的元信息（某个Key存储在那个TiKV节点）；二是对TiKV集群进行调度和负载均衡（如数据的迁移、Raft groupleader的迁移等）；三是分配全局唯一且递增的事务ID
PD 是一个集群，需要部署奇数个节点，一般线上推荐至少部署3个节点。PD在选举的过程中无法对外提供服务，这个时间大约是3秒
TiKV Server
TiKV Server 负责存储数据，从外部看TiKV是一个分布式的提供事务的Key-Value存储引擎。存储数据的基本单位是Region，每个Region负责存储一个Key Range（从StartKey到EndKey的左闭右开区间）的数据，每个TiKV节点会负责多个Region。TiKV使用Raft协议做复制，保持数据的一致性和容灾。副本以Region为单位进行管理，不同节点上的多个Region构成一个Raft Group，互为副本。数据在多个TiKV之间的负载均衡由PD调度，这里也就是以Region为单位进行调度
7 性能优化
数据库服务衡量指标：
- Qps：query per second
- Tps：transaction per second
7.1 压力测试工具
7.1.1 常见MySQl压力测试工具
- mysqlslap
- Sysbench：功能强大，官网： https://github.com/akopytov/sysbench
- tpcc-mysql
- MySQL Benchmark Suite
- MySQL super-smack
- MyBench
7.1.2 mysqlslap
mysqlslap：来自于mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用–only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹
使用格式：
mysqlslap [options]
- 1
常用参数 [options] 说明：
--auto-generate-sql, -a #自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力 --auto-generate-sql-load-type=type #测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认) --auto-generate-sql-add-auto-increment #代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持 --number-char-cols=N, -x N #自动生成的测试表中包含多少个字符类型的列，默认1 --number-int-cols=N, -y N #自动生成的测试表中包含多少个数字类型的列，默认1 --number-of-queries=N #总的测试查询次数(并发客户数×每客户查询次数) --query=name,-q #使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试 --create-schema #代表自定义的测试库名称，测试的schema --commint=N #多少条DML后提交一次 --compress, -C #如服务器和客户端都支持压缩，则压缩信息 --concurrency=N, -c N #表示并发量，即模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定值做为分隔符,如：--concurrency=100,200,500 --engine=engine_name, -e engine_name #代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb --iterations=N, -i N #测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次 --only-print #只打印测试语句而不实际执行。 --detach=N #执行N条语句后断开重连 --debug-info, -T #打印内存和CPU的相关信息
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
mysqlslap示例
#单线程测试 mysqlslap -a -uroot -pmagedu #多线程测试。使用--concurrency来模拟并发连接 mysqlslap -a -c 100 -uroot -pmagedu #迭代测试。用于需要多次执行测试得到平均值 mysqlslap -a -i 10 -uroot -pmagedu mysqlslap ---auto-generate-sql-add-autoincrement -a mysqlslap -a --auto-generate-sql-load-type=read mysqlslap -a --auto-generate-secondary-indexes=3 mysqlslap -a --auto-generate-sql-write-number=1000 mysqlslap --create-schema world -q "select count(*) from City” mysqlslap -a -e innodb -uroot -pmagedu mysqlslap -a --number-of-queries=10 -uroot -pmagedu #测试同时不同的存储引擎的性能进行对比 mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -pmagedu #执行一次测试，分别50和100个并发，执行1000次总查询 mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -pmagedu #50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次 mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -pmagedu
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
7.2 生产环境 my.cnf 配置案例
配置文件生成工具参考链接：https://imysql.com/my_cnf_generator
参考硬件：内存32G
#打开独立表空间 innodb_file_per_table = 1 #MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值 max_connections = 8000 #所有线程所打开表的数量 open_files_limit = 10240 #back_log 是操作系统在监听队列中所能保持的连接数 back_log = 300 #每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息 max_connect_errors = 1000 #每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值 max_allowed_packet = 32M #指定一个请求的最大连接时间 wait_timeout = 10 # 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序 sort_buffer_size = 16M #不带索引的全表扫描.使用的buffer的最小值 join_buffer_size = 16M #查询缓冲大小 query_cache_size = 128M #指定单个查询能够使用的缓冲区大小，缺省为1M query_cache_limit = 4M #设定默认的事务隔离级别 transaction_isolation = REPEATABLE-READ #线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次连接时被预留. thread_stack = 512K #二进制日志功能 log-bin=/data/mysqlbinlogs/ #二进制日志格式 binlog_format=row #InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80% innodb_buffer_pool_size = 24G #用来同步IO操作的IO线程的数量 innodb_file_io_threads = 4 #在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍 innodb_thread_concurrency = 16 #用来缓冲日志数据的缓冲区的大小 innodb_log_buffer_size = 16M #在日志组中每个日志文件的大小 innodb_log_file_size = 512M #在日志组中的文件总数 innodb_log_files_in_group = 3 #SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间 innodb_lock_wait_timeout = 120 #慢查询时长 long_query_time = 2 #将没有使用索引的查询也记录下来 log-queries-not-using-indexes
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
7.3 MySQL配置最佳实践
高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”
参考资料：
- 阿里巴巴Java开发手册：https://developer.aliyun.com/topic/java2020
- 58到家数据库30条军规解读：http://zhuanlan.51cto.com/art/201702/531364.htm
以下规范适用场景：并发量大、数据量大的互联网业务
7.3.1 基础规范
(1)必须使用InnoDB存储引擎
解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
(2)使用UTF8MB4字符集
解读：万国码，无需转码，无乱码风险，节省空间，支持表情包及生僻字
(3)数据表、数据字段必须加入中文注释
解读：N年后谁知道这个r1,r2,r3字段是干嘛的
(4)禁止使用存储过程、视图、触发器、Event
解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧！
(5)禁止存储大文件或者大照片
解读：为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统，数据库里存URI多好。
7.3.2 命名规范
(6)只允许使用内网域名，而不是ip连接数据库
(7)线上环境、开发环境、测试环境数据库内网域名遵循命名规范
业务名称：xxx
线上环境：xxx.db
开发环境：xxx.rdb
测试环境：xxx.tdb
从库在名称后加-s标识，备库在名称后加-ss标识
线上从库：xxx-s.db
线上备库：xxx-sss.db
(8)库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用
(9)库名与应用名称尽量一致，表名:t_业务名称_表的作用，主键名:pk_xxx，非唯一索引名:idx_xxx，唯一键索引名:uk_xxx
7.3.3 表设计规范
(10)单实例表数目必须小于500
单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表
(11)单表列数目必须小于30
(12)表必须有主键，例如自增主键
解读：
a)主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
b)主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率
c) 无主键的表删除，在row模式的主从架构，会导致备库夯住
(13)禁止使用外键，如果有外键完整性约束，需要应用程序控制
解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先
7.3.4 字段设计规范
(14)必须把字段定义为NOT NULL并且提供默认值
解读：
a)null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
b)null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性;同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
c)null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
d)对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录
(15)禁止使用TEXT、BLOB类型
解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
(16)禁止使用小数存储货币
解读：使用整数吧，小数容易导致钱对不上
(17)必须使用varchar(20)存储手机号
解读：
a)涉及到区号或者国家代号，可能出现±()
b)手机号会去做数学运算么?
c)varchar可以支持模糊查询，例如：like“138%”
(18)禁止使用ENUM，可使用TINYINT代替
解读：
a)增加新的ENUM值要做DDL操作
b)ENUM的内部实际存储就是整数，你以为自己定义的是字符串?
7.3.5索引设计规范
(19)单表索引建议控制在5个以内
(20)单索引字段数不允许超过5个
解读：字段超过5个时，实际已经起不到有效过滤数据的作用了
(21)禁止在更新十分频繁、区分度不高的属性上建立索引
解读：
a)更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
b)“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
(22)建立组合索引，必须把区分度高的字段放在前面
解读：能够更加有效的过滤数据
7.3.6 SQL使用规范
(23)禁止使用SELECT *，只获取必要的字段，需要显示说明列属性
解读：
a)读取不需要的列会增加CPU、IO、NET消耗
b)不能有效的利用覆盖索引
c)使用SELECT *容易在增加或者删除字段后出现程序BUG
(24)禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
解读：容易在增加或者删除字段后出现程序BUG
(25)禁止使用属性隐式转换
解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么?(这个线上问题不止出现过一次)
(26)禁止在WHERE条件的属性上使用函数或者表达式
解读：SELECT uid FROM t_user WHERE from_unixtime(day)>=‘2017-02-15’ 会导致全表扫描
正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00’)
(27)禁止负向查询，以及%开头的模糊查询
解读：
a)负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
b)%开头的模糊查询，会导致全表扫描
(28)禁止大表使用JOIN查询，禁止大表使用子查询
解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能
(29)禁止使用OR条件，必须改为IN查询
解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢?
(30)应用程序必须捕获SQL异常，并有相应处理
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/252091
推荐阅读
相关标签
