Linux----------mysql主从及高可用

目录

• 一、MySQL复制
  • 1.1复制的功用：
  • 1.2 MySQL复制原理
  • 1.3 MySQL主从复制
  • 1.4 主从配置过程：
    • 1.41验证
  • 1.5 注意事项
  • 1.6 复制架构中应该注意的问题：
  • 1.7 如何保证主从复制的事务安全
• 二、主主复制
  • 2.1 主主复制：互为主从
  • 2.2 主主复制
  • 2.3 半同步复制
  • 2.4 半同步复制实现：
  • 2.5 复制过滤器：
    • 2.51复制过滤器从服务器上的相关设置
  • 2.6 MySQL复制加密
    • 2.61 配置实现：
    • 2.62 复制的监控和维护：
• 三、MySQL高可用
  • 3.1 MHA工作原理
    • 3.11 MHA工作原理
  • 3.2 MHA组成
  • 3.3 自定义扩展：
• 四、Galera Cluster
  • 4.1 Galera Cluster特点
  • 4.2 Galera Cluster工作过程
  • 4.3 Galera Cluster包括两个组件
  • 4.4 MySQL复制
  • 4.5 复制注意事项
  • 4.6 性能衡量指标
  • 4.7 MYSQL压力测试
    • 4.71 mysqlslap示例
    • 4.72测试同时不同的存储引擎的性能进行对比：

一、MySQL复制

扩展方式： Scale Up ，Scale Out
MySQL的扩展
复制：每个节点都有相同的数据集
向外扩展
二进制日志
单向

1.1复制的功用：

数据分布
负载均衡读
备份
高可用和故障切换
MySQL升级测试

1.2 MySQL复制原理

mysql复制原理：

mysql复制原理：

MySQL垂直分区：

MySQL水平分片（Sharding）：

对应shard中查询相关数据

1.3 MySQL主从复制

主从复制线程：
主节点：dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

MySQL复制
主从复制特点：
异步复制
主从数据不一致比较常见

复制架构：
Master/Slave, Master/Master, 环状复制
一主多从:适用于读操作的负载均衡
从服务器还可以再有从服务器
一从多主:适用于大量写作操作的负载均衡

复制需要考虑二进制日志事件记录格式
STATEMENT（5.0之前）、ROW（5.1之后，推荐）、MIXED

各种复制模型实战：
主从、主主、半同步复制、复制过滤器

MySQL复制模型

1.4 主从配置过程：

参看：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=mysql-bin
(2) 为当前节点设置一个全局惟的ID号
[mysqld]
server_id= 1

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
log-bin = mysql-bin
server-id = 1
symbolic-links = 0
log-error =/var/log/mysqld.log

(3) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON . TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

从节点配置：
(1) 启动中继日志
[mysqld]
server_id= 2 为当前节点设置一个全局惟的ID号，比主服务器大
relay_log= relay-log-bin relay log的文件路径，默认值hostname-relay-bin
relay_log_index= relay-log.index 默认值 hostname -relay-bin.index
(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
mysql> CHANGE MASTER TO MASTER_HOST='（主服务器IP）',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=（show master status \G查看）;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G;

1.41验证

（1）主从服务器都有数据

主服务器添加数据

mysql> show tables;
+----------------+
| Tables_in_wang |
+----------------+
| st             |
+----------------+
1 row in set (0.00 sec)

mysql> create table wan(name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_wang |
+----------------+
| st             |
| wan            |
+----------------+
2 rows in set (0.01 sec)

从服务器上验证是否同步

mysql> show tables;
+----------------+
| Tables_in_wang |
+----------------+
| st             |
+----------------+
1 row in set (0.00 sec)


mysql> show tables
    -> ;
+----------------+
| Tables_in_wang |
+----------------+
| st             |
| wan            |
+----------------+
2 rows in set (0.00 sec)

（2）主服务器有数据，从服务器为空
主服务器

mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)

从服务器

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

1.5 注意事项

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
通过备份恢复数据至从服务器
复制起始位置为备份时，二进制日志文件及其POS
如果要启用级联复制,需要在从服务器启用以下配置
[mysqld]
log_bin
log_slave_updates

1.6 复制架构中应该注意的问题：

1、限制从服务器为只读
在从服务器上设置read_only=ON
注意：此限制对拥有SUPER权限的用户均无效
阻止所有用户, 包括主服务器复制的更新
mysql> FLUSH TABLES WITH READ LOCK; ----加全局读锁

1.7 如何保证主从复制的事务安全

参看https://mariadb.com/kb/en/library/server-system-variables/

在master节点启用参数：
sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
如果用到的为InnoDB存储引擎：
innodb_flush_logs_at_trx_commit=1
每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
sync_master_info=# 多少次事件后master.info同步到磁盘

在slave节点启用服务器选项：
--skip_slave_start=ON 不自动启动slave
在slave节点启用参数：
sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=#多个次事务后同步relay-log.info到磁盘

二、主主复制

2.1 主主复制：互为主从

容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2

2.2 主主复制

主主复制的配置步骤：
(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程

2.3 半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

2.4 半同步复制实现：

主服务器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL VARIABLES rpl_semi_sync_master_enabled=1;
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql> SHOW GLOBAL STATUS LIKE '%semi%‘;

从服务器配置:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL VARIABLES rpl_semi_sync_slave_enabled=1;

2.5 复制过滤器：

让从节点仅复制指定的数据库，或指定数据库的指定表两种实现方式：
(1) 主服务器仅向二进制日志中记录与特定数据库(特定表)相关的事件
问题：时间还原无法实现；不建议使用
binlog_do_db= 数据库白名单列表，用逗号分隔
binlog_ignore_db= 数据库黑名单列表，和前项不要同时使用
(2) 从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
问题：会造成网络及磁盘IO浪费

2.51复制过滤器从服务器上的相关设置

replicate_do_db= 指定复制库的白名单
replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table=

2.6 MySQL复制加密

基于SSL复制：
在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性

2.61 配置实现：

参看：https://mariadb.com/kb/en/library/replication-with-secure-connections/
主服务器开启SSL：[mysqld] 加一行ssl
master配置证书和私钥；并且创建一个要求必须使用SSL连接的复制账号
slave端使用CHANGER MASTER TO 命令时指明ssl相关

Master配置
[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

Slave配置
mysql>CHANGE MASTER TO MASTER_HOST='MASTERIP',
MASTER_USER='rep',
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
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';

2.62 复制的监控和维护：

(1) 清理日志
PURGE
(2) 复制监控
SHOW MASTER STATUS
SHOW BINLOG EVENTS
SHOW BINARY LOGS
SHOW SLAVE STATUS
SHOW PROCESSLIST
(3) 从服务器是否落后于主服务
Seconds_Behind_Master: 0
(4) 如何确定主从节点数据是否一致
percona-tools
(5) 数据不一致如何修复删除从数据库，重新复制

三、MySQL高可用

Master HA或多主模型
MMM: Multi Master MySQL，基于主从复制实现
MHA：Master HA，对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从
https://code.google.com/archive/p/mysql-master-ha/

Galera Cluster：wresp
通过wresp协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主可读可写

3.1 MHA工作原理

3.11 MHA工作原理

1 从宕机崩溃的master保存二进制日志事件（binlog events）
2 识别含有最新更新的slave
3 应用差异的中继日志（relay log）到其他的slave
4 应用从master保存的二进制日志事件（binlog events）
5 提升一个slave为新的master
6 使其他的slave连接新的master进行复制

3.2 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的半同步复制

3.3 自定义扩展：

    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地址
配置文件：
    global配置，为各application提供默认配置
    application配置：为每个主从复制集群

四、Galera Cluster

Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案
右图图示：三个节点组成了一个集群，与普通的主从架构不同，它们都可以作为主节点，三个节点是对等的，称为multi-master架构，当有客户端要写入或者读取数据时，连接哪个实例都是一样的，读到的数据是相同的，写入某一个节点之后，集群自己会将新数据同步到其它节点上面，这种架构不共享任何数据，是一种高冗余架构

4.1 Galera Cluster特点

多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的。
同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失。
并发复制：从节点APPLY数据时，支持并行执行，更好的性能
故障切换：在出现数据库故障时，因支持多点写入，切换容易
热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在节点故障期间，节点本身对集群的影响非常小
自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一致
对应用透明：集群的维护，对应用程序是透明的

4.2 Galera Cluster工作过程

Galera Cluster
Galera Cluster官方文档：
http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

4.3 Galera Cluster包括两个组件

Galera replication library (galera-3)
WSREP：MySQL扩展Write Set Replication API
wresp复制实现：
percona-cluster
MariaDB-Cluster
至少需要三个节点，不能安装mariadb-server

4.4 MySQL复制

yum install MariaDB-Galera-server
vim /etc/my.cnf.d/server.cnf
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address = "gcomm://172.16.0.7,172.16.0.17,172.16.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 = ‘172.16.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‘;

4.5 复制注意事项

读写分离：
mysql-proxy --> Atlas (Qihoo)
Amoeba：https://sourceforge.net/projects/amoeba/
dbproxy：https://github.com/Meituan-Dianping/DBProxy

复制的问题和解决方案：
(1) 数据损坏或丢失
Master：MHA + semi repl
Slave： 重新复制
(2) 混合使用存储引擎
MyISAM：不支持事务
InnoDB：支持事务
(3) 不惟一的server id
重新复制
(4) 复制延迟
需要额外的监控工具的辅助
一从多主
多线程复制：对多个数据库复制

4.6 性能衡量指标

数据库服务衡量指标：
qps: query per second
tps: transaction per second
压力测试工具：
mysqlslap
Sysbench：功能强大
https://github.com/akopytov/sysbench
tpcc-mysql
MySQL Benchmark Suite
MySQL super-smack
MyBench

4.7 MYSQL压力测试

Mysqlslap：来自于mariadb包，测试的过程默认生成一个
mysqlslap的schema,生成测试表t1，查询和插入测试数据，
mysqlslap库自动生成，如果已经存在则先删除。用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹

    使用格式：mysqlslap [options]    
    常用参数 [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，MySQL中schema也就是database
    --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的相关信息

4.71 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

4.72测试同时不同的存储引擎的性能进行对比：

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