MySQL6

MySQL数据库6

1. 集群概述

性能达到瓶颈的解决方案
1. Scale Up
  向上扩展能力，如增加更好的硬件固态磁盘，加大内存等，成本高，效果不显著
2. Scale Out
  向外扩展，例如建立更多的服务器
MySQL扩展采用的方式读写分离
将数据库的访问拆分成两种访问，读（select）和写(增删改)，通过调度器，将用户的不同请求分别发送给读服务器或者写服务器，在写服务器增加数据时，需要使用主从复制机制来同步数据到读服务器
主从复制特征
1. 每个节点都有相同的数据集
2. 向外扩展
3. 使用二进制日志功能实现主从复制
4. 单向
主从复制的功用
1. 负载均衡读
  在主从复制的基础上，配合读写分离，可以由主节点提供写服务，由从节点提供读服务，分担服务器负载；尤其是在写少读多的场景下，通过多个从节点分担读负载，可以大大提高Redis服务器的并发量
2. 数据冗余
  可以防止物理上的损坏，如果主服务器数据删除，从服务器数据也会丢失，所以还要备份
3. 高可用和故障切换
  主服务器损坏，更改从服务器设置替换为主，从服务器损坏，调整调度器策略，跳过损坏的从服务器
4. MySQL升级测试
  类似灰度发布，升级集群中的一部分服务器
读写分离应用：
1. mysql-proxy：Oracle公司开源产品
  https://downloads.mysql.com/archives/proxy/
2. Atlas：Qihoo（360公司），基于mysql-proxy的二次修改
  https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
3. dbproxy：美团
  https://github.com/Meituan-Dianping/DBProxy
4. Cetus：网易乐得
  https://github.com/Lede-Inc/cetus
5. Amoeba：
  https://sourceforge.net/projects/amoeba/
主从复制原理
1. 主服务器
  要求必须启用二进制日志服务，执行写操作（例如update），只要事务提交，就会写入二进制日志（Bin Log）里，然后通过启用的Slave服务器线程，通过网络将二进制日志读出来，传送给从服务器的线程io Thread
2. 从服务器
  1. io Thread线程接收到主服务器传送的二进制文件，放入relay log中
  2. SQL Thread线程读出relay log中的二进制文件，写入数据库中，完成同步。
3. 主从复制线程作用
  1. 主节点（dump Thread）
    为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
  2. 从节点：
    1. I/O Thread
      向Master请求二进制日志事件，并保存于中继日志中
    2. SQL Thread
      从中继日志中读取日志事件，在本地完成重放
4. 从服务器跟复制功能相关的文件：
  1. master.info
    用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
  2. relay-log.info
    保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系
5. 缺点
  主服务器写操作可以实现并行操作，因为有事务功能可以隔离操作，二进制日志生成后推送给从服务器时，是串行的，由多车道变为单车道，当用户访问量很大时，就会产生延迟很大的问题。
MySQL垂直分区
1. 工作原理
  如图所示，服务器中有三部分数据库Part1、Part2、Part3，当服务器负载过大时，可以将其拆分放到三个服务器中来分别管理
2. 缺点
  在同一个服务器中可以使用多表查询，拆分存放不支持此功能，需要配合开发进行
MySQL水平分片（Sharding）
1. 工作原理
  将数据按照一定的逻辑切成小片，以用户为例，有100万用户信息，可以将用户按奇偶数切分，奇数号的用户放在Shard1中，偶数号的用户放在Shard2中。也需要结合业务来分
2. 缺点
  需要增加分片调度器，来判断用户请求，找打相应服务器
主从复制架构：
1. Master/Slave（主从）
2. Master/Master（主主）
  都可以写入，容易发生数据冲突，当服务器同步延迟，不能及时发现冲突，用于对数据要求不高情况
3. 一主多从
  从服务器还可以再有从服务器
4. 一从多主:
  适用于多个不同数据库，版本较高的数据库支持，将从服务器分为多个部分，分别存放主服务器数据

2. MySQL主从复制实现

主从配置官网资料
https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
主节点配置
1. mariadb配置文件，通常为/etc/my.cnf，在[mysqld]下添加
  1. 必须项
    log_bin：启用二进制日志，在mariadb配置文件，[mysqld]内添加项
    server_id=#：为当前节点设置一个全局惟一的ID号，判断数据来源的标识，[mysqld]内添加项
  2. 可选项
    log-basename=master：设置datadir中日志名称
    sync_binlog=1：每次写后立即同步二进制日志到磁盘，性能差
    innodb_flush_log_at_trx_commit=1：每次事务提交立即同步日志写磁盘
    innodb_support_xa=ON：默认值，分布式事务MariaDB10.3.0废除
    sync_master_info=# ：#次事件后master.info同步到磁盘
2. 创建有复制权限的用户账号
  1. 语法
```
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
1
```
  2. 选项描述
    REPLICATION SLAVE：给予从服务器的权限，只可以同步复制二进制日志
    repluser：用户账号命名
    HOST：从服务器IP或网段
    IDENTIFIED BY ：设置口令关键字
从节点配置：
1. 配置文件
  1. 必须项，[mysqld]内
    server_id=#：为当前节点设置一个全局惟的ID号
  2. 可选项
    relay_log=relay-log：中继日志的文件路径，默认值文件名为hostname-relay-bin
    relay_log_index=relay-log.index：中继日志索引文件路径，默认值hostname-relay-bin.index
    skip_slave_start=ON：不自动启动slave
    sync_relay_log=# ：#次写后同步relay log到磁盘
    sync_relay_log_info=#：#次事务后同步relay-log.info到磁盘
2. 使用有复制权限的用户账号连接至主服务器，并启动复制线程
  1. 从服务器复制同步设置
```
mysql> CHANGE MASTER TO             <==同步关键字
MASTER_HOST='host',    		        <==主服务器名
MASTER_USER='repluser',    		    <==主服务器授权的账号
MASTER_PASSWORD='replpass', ,       <==授权账号的密码
MASTER_LOG_FILE='mysql-bin.xxxxx',  <==数据同步开始位置来自哪个二进制日志
MASTER_LOG_POS=#;  					<==二进制日志的开始位置			
1
2
3
4
5
6
```
  2. 启动从服务器复制线程
```
mysql> START SLAVE [IO_THREAD|SQL_THREAD];
1
```
复制架构中应该注意的问题
1. 限制从服务器为只读
  在从服务器配置文件[mysqld]中设置read_only=ON
  注意：此限制对拥有SUPER权限的用户均无效，例如root账号
2. 阻止所有用户, 包括主服务器复制
  mysql> FLUSH TABLES WITH READ LOCK;
3. 从新设置从服务器同步，清除master.info，relay-log.info，relay log 等信息
  1. 先停止从服务器同步线程
    STOP SLAVE
  2. 清空同步数据
    RESET SLAVE ALL
4. 从服务器忽略几个主服务器的复制事件，
  1. 忽略用到的global变量
    sql_slave_skip_counter = N
  2. 示例
    在从服务器上建立一条teachers表上id为13的记录
```
MariaDB [hellodb]> INSERT teachers VALUES(13,'k',26,'M');
1
```
    主服务器在此之后推送来一条相同Id的记录，就会在从服务器中出现如下报错
```
'查看从服务器同步信息'
MariaDB [hellodb]> SHOW SLAVE STATUS\G
'出现如下错误提示'
Last_SQL_Error: Error 'Duplicate entry '13' for key 'PRIMARY'' on query. Default database: 'hellodb'. Query: 'INSERT teachers VALUES(13,'k',30,'M')'   
1
2
3
4
```
    之后主服务器推送的所有记录，都将失败，可以使用sql_slave_skip_counter = N来忽略这种错误，解决此堵塞现象
```
1. '在堵塞的从服务器上，先停止线程'
MariaDB [hellodb]> STOP SLAVE;
2. '执行全局变量sql_slave_skip_counter，通过第2步得知只有1个错误，所以值为1'
MariaDB [hellodb]> SET GLOBAL sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.01 sec)
3. '启动线程，恢复主从复制'
MariaDB [hellodb]> START SLAVE;
1
2
3
4
5
6
7
```
5. 如何保证主从复制的事务安全
  参看https://mariadb.com/kb/en/library/server-system-variables/

3. 主从复制实现

理论上应该保持主从服务器版本一致，避免出现未知错误

主从复制

安装mariadb服务，设置主服务器配置文件

'修改配置文件'
[root@hai7-6 ~]$vim /etc/my.cnf
[mysqld]
server_id=1            <==设置主服务器server_id
innodb_file_per_table  <==将表数据文件单独存放
skip_name_resolve      <==不允许反向解析为主机名
log_bin                <==启动二进制
'启动服务'
[root@hai7-6 ~]$systemctl restart mariadb
1
2
3
4
5
6
7
8
9

授权从服务器
记录二进制日志文件位置，将来复制数据到从服务器，是将授权账号信息一起复制过去，还是复制授权账号后的信息

'授权账户为repluser在网段192.168.50.%，密码为centos，可以复制数据'
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%'  IDENTIFIED BY 'centos';
'授权账号后二进制日志位置'
MariaDB [(none)]> show master status;
| mysql_bin.000007 |      401 |
1
2
3
4
5

配置从服务器，从服务器这里没有设下级从服务器，暂时不需要加二进制日志功能

[root@hai7-8 ~]$vim /etc/my.cnf
[mysqld]
server_id=2      <==id号只要唯一就可以
innodb_file_per_table
skip_name_resolve
1
2
3
4
5

在主服务器导入一些数据，便于查看主从复制功能是否启用

[root@hai7-6 ~]$mysql < hellodb_innodb.sql
'二进制日志大小为'
MariaDB [(none)]> show master status;
| mysql_bin.000007 |     7811 |      
1
2
3
4

在从服务器上执行复制命令，这个命令需要记住CHANGE关键字，用help补齐剩下部分

MariaDB [(none)]> help change   <==帮助关键字
topics:
ALTER TABLE
CHANGE MASTER TO        <==得到CHANGE MASTER TO
'再次help关键字`CHANGE MASTER TO`，得到修改模板，拿来用，修改为我们需要的配置，执行'
MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='192.168.50.107',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='centos',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql_bin.000007',
    ->   MASTER_LOG_POS=245;
1
2
3
4
5
6
7
8
9
10
11
12

同步命令执行后查看从服务器同步状态

MariaDB [(none)]> SHOW SLAVE STATUS\G
			  Master_Log_File: mysql_bin.000007   <==从这个二进制日志复制     
          Read_Master_Log_Pos: 245       	  	  <==从以上日志的什么位置开始复制       
         Relay_Master_Log_File: mysql_bin.000007  <==将来生成的文件
               Relay_Log_File: mariadb-relay-bin.000001  <==中继日志
                Relay_Log_Pos: 4           		  <==中继位置
             Slave_IO_Running: No         		  <==IO线程启动状态  
            Slave_SQL_Running: No   	  		  <==SQL线程启动状态
 	 	Seconds_Behind_Master: NULL	      		  <==和主服务器复制延迟有多久
 	 	        Exec_Master_Log_Pos: 245	      <==写入从服务器时，当前写入的主服务器的位置
1
2
3
4
5
6
7
8
9
10

启动从服务器同步复制线程Slave_IO_Running和Slave_SQL_Running
```
MariaDB [(none)]> START SLAVE;
1
```

测试是否可以同步数据，修改hellodb数据库的表teachers

'增加一条记录'
MariaDB [hellodb]> insert teachers values(5,'a',20,'F');
1
2

在从服务器上查看，是否已经同步

MariaDB [hellodb]> select * from teachers;
|   5 | a             |  20 | F      |      <==同步成功
1
2

已经有主从服务器，额外添加一个从服务器

在准备新增的从服务B上修改配置文件

[root@hai7-7 data]$vim /etc/my.cnf
[mysqld]
server_id=3
innodb_file_per_table
skip_name_resolve
1
2
3
4
5

将主服务器数据做完全备份，并传送给从服务器B

'完全备份主服务器数据'
[root@hai7-6 data]$mysqldump -A -F --single-transaction --master-data=1 > all.sql
'传送给从服务器B'
[root@hai7-6 data]$scp all.sql 192.168.50.115:/data
1
2
3
4

完全备份时--master-data=1选项，会自动在备份文件中加入CHANGE MASTER TO关键词，修改完全备份文件，在导入时备份文件时，使其可以直接执行从服务器同步复制指令

`修改前完全备份--master-data=1选项，添加的行`
[root@hai7-7 data]$vim all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000008', MASTER_LOG_POS=245;
`修改为从服务器执行的CHANGE MASTER TO复制指令`
[root@hai7-7 data]$vim all.sql
CHANGE MASTER TO 
MASTER_HOST='192.168.50.107',MASTER_USER='repluser',
MASTER_PASSWORD='centos',MASTER_PORT=3306,
MASTER_LOG_FILE='mysql_bin.000008', MASTER_LOG_POS=245;
1
2
3
4
5
6
7
8
9

修改完成后，将完全备份文件导入从服务器B的数据库中，查看从服务器配置情况

[root@hai7-7 data]$mysql < all.sql
MariaDB [(none)]> show slave status\G
               Slave_IO_State: 
                  Master_Host: 192.168.50.107    <==已自动执行CHANGE MASTER TO
                  Master_User: repluser
1
2
3
4
5

启动从服务器线程，在主服务器添加数据，测试主从复制功能

1. '启动从服务器线程'
MariaDB [(none)]> START SLAVE;
2. '在主服务器增加数据'
MariaDB [hellodb]> INSERT teachers VALUES(7,'c',30,'F');
3. '在从服务器查看是否自动同步'
MariaDB [hellodb]> SELECT * FROM teachers;
|   7 | c             |  30 | F      |    <==同步成功
1
2
3
4
5
6
7

如果主服务器A宕机，在从服务器（B、C）中，选一个来充当主服务器

选择合适的从服务器作为提升机，参考文件master.info二进制日志保存位置，判断那台从服务器数据较新，就拿那台当做提升机
```
'查看从服务器文件master.info，可以得到二进制日志信息'
[root@hai7-8 ~]$cat /var/lib/mysql/master.info 
18
mysql_bin.000008
445
1
2
3
4
5
```

将准备提升的从服务器C的线程停止

'停止线程'
MariaDB [(none)]> STOP SLAVE;
'查看线程状态'
MariaDB [(none)]> show slave status\G
             Slave_IO_Running: No     <==已停止
            Slave_SQL_Running: No
1
2
3
4
5
6

清理从服务器其他配置信息，比如家目录下的master.info，relay-log.info

'清理从服务器信息命令'
MariaDB [(none)]> RESET SLAVE ALL; 
'执行清理命令后，从服务器信息将清空'
MariaDB [(none)]> show slave status\G
Empty set (0.00 sec)      <==信息被清空
1
2
3
4
5

修改配置文件，打开二进制日志功能，清除read_only、中继日志等从服务器配置

[root@hai7-8 ~]$vim /etc/my.cnf
[mysqld]
server_id=2     <==id只要唯一就可以
log-bin         <==打开二进制日志功能
1
2
3
4

授权从服务器账号，如果从服务器已经同步过主服务器授权信息，这里可以跳过，如果从服务器B的数据与其他服务器不同，需要备份还原步骤，来同步数据
```
'授权账户为repluser在网段192.168.50.%，密码为centos，可以复制数据'
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%'  IDENTIFIED BY 'centos';
1
2
```

停止从服务器B线程，清空从服务信息

MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> RESET SLAVE ALL;
1
2

重新配置从服务信息将主服务器地址指向C，启动线程

1. '重新配置从服务器信息'
MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
2. '启动线程'
MariaDB [(none)]> START SLAVE;
1
2
3
4
5
6
7

在主服务器C上添加数据，查看是否可以主从复制

'主服务器C上添加数据'
MariaDB [hellodb]> INSERT teachers VALUES(9,'E',32,'f');
'从服务器B上查看'
MariaDB [hellodb]> select * from teachers;
|   9 | E             |  32 | F      |
1
2
3
4
5

级联复制，给从服务器B再下设从服务器C
主从复制依赖的是二进制日志文件，而从服务器接收主服务器的二进制文件通过中继日志执行写入磁盘，不会产生新的二进制日志（二进制日志产生是对数据库直接进行增删改），想要在从服务器生成二进制文件供级联使用，就需要在配置文件中加入选项log_slave_updates

主服务器为A，从服务器为B，从服务器的下设从服务器为C；在从服务器B上修改配置文件，开启二进制日志功能，重启服务，查看二进制日志位置

[root@hai7-8 ~]$vim /etc/my.cnf
[mysqld]
server_id=3
log_slave_updates     <==级联从服务器关键项，产生二进制日志
log-bin               <==为下设的从服务器复制提供二进制日志文件
read_only=ON
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
1
2
3
4
5
6
7
8
9
10
11

在C上设置从服务器设置，修改配置文件，设置唯一id，设只读设置等，然后进入mysql设置从服务器，将主机指向B

'如果之前为从服务器需要执行清理操作'
MariaDB [hellodb]> reset slave all;
Query OK, 0 rows affected (0.00 sec)
'重新配置从服务器设置'
MariaDB [hellodb]>  CHANGE MASTER TO 
    -> MASTER_HOST='192.168.50.115',MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
'启动线程'
MariaDB [hellodb]> START SLAVE;
1
2
3
4
5
6
7
8
9
10

4. 主主复制(互为主从)

容易产生的问题
数据同步有延迟，两主机可能修改的是同一个数据，容易冲突；因此慎用
考虑要点：自动增长id
1. 配置一个节点使用奇数id
  auto_increment_offset=1 开始点
  auto_increment_increment=2 增长幅度
2. 另一个节点使用偶数id
  auto_increment_offset=2
  auto_increment_increment=2

主主复制的配置步骤：

修改主机配置文件
A主机

[root@hai7-7 data]$vim /etc/my.cnf
[mysqld]
server_id=3
log-bin
auto_increment_offset=2       <==自增ip起始数为2
auto_increment_increment=2    <==自增ip递增为2
1
2
3
4
5
6

B主机

[root@hai7-7 data]$vim /etc/my.cnf
[mysqld]
server_id=2
log-bin
auto_increment_offset=1    <==修改ip起始数为1
auto_increment_increment=2
1
2
3
4
5
6

创建用户
A主机，B主机上也需要建账户，通过同步复制实现即可

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%' IDENTIFIED BY 'centos';
1

达成同步复制功能
B主机，实现复制功能，主服务器地址指向A(192.168.50.111)

1. '先实现同步复制功能'
MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
    ->  MASTER_PASSWORD='centos',MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
2. '启动同步复制线程'
MariaDB [(none)]> START SLAVE;   
3. '在A主机授权的账号信息也一起同步到了B主机'
MariaDB [(none)]> SELECT user , host FROM mysql.user;
| repluser | 192.168.50.% |
1
2
3
4
5
6
7
8
9
10

回到A主机，实现复制功能，主服务器地址指向B(192.168.50.115)

MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.50.115',MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.02 sec)
'启动同步复制线程'
MariaDB [(none)]> START SLAVE;
1
2
3
4
5
6
7

测试
建立一个自动递增id的表，测试主主复制效果

MariaDB [hellodb]> CREATE TABLE test (id int auto_increment primary key,name char(20));
1

在A主机添加两条name字段的记录，让id自动增长

MariaDB [hellodb]> INSERT test (name)VALUES('a'),('b');
MariaDB [hellodb]> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | a    |    <==奇数式增长
|  3 | b    |
+----+------+
1
2
3
4
5
6
7
8

在B主机上添加两条name字段的记录，让id自动增长

MariaDB [hellodb]> INSERT test (name)VALUES('e'),('f');
MariaDB [hellodb]> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | b    |
|  4 | e    |     <==auto_increment只取最大值，不会插入，所以显示偶数为4
|  6 | f    |
1
2
3
4
5
6
7
8
9

5 半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。
由于MySQL的复制内在机制，可能会产生比较大的延迟，这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失；而同步方式效率又不高。
在这里插入图片描述

半同步复制工作原理
1. 用户发送写操作请求，调度器将指令转发给主数据库，主库数据发生改变后，不会马上将数据更改成功的结果发送给用户，而是先把binlog日志发送给从库，在众多从库中只要有一个同步成功，就会返回成功结果给用户；
2. 如果长时间没有同步成功任何一个从库，超过超时时长（timeout）定义的时间后，也会返回成功结果给用户
半同步复制配置：
需要在主从服务器上都安装插件rpl_semi_sync_master，这个插件表现为库so文件
在mariadb-server安装包中已经包含：
主服务器插件文件/usr/lib64/mysql/plugin/semisync_master.so
从服务器插件文件/usr/lib64/mysql/plugin/semisync_slave.so
1. 主服务器
  1. 安装插件语法:
```
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
1
```
  2. 选项描述
    INSTALL PLUGIN：安装插件命令
    rpl_semi_sync_master：插件类型
    SONAME：关键字，后面跟插件文件名
  3. 启用半同步功能
```
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1;    
1
```
  4. 设置超时时长，单位以毫秒为单位
```
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1000;
1
```
2. 从服务器配置:
  1. 安装插件语法
```
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
1
```
  2. 启动半同步功能
```
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
1
```
3. 查看半同步信息
  1. 显示变量设定值
    mysql>SHOW GLOBAL VARIABLES LIKE ‘%semi%’;
  2. 显示服务器当前状态
    mysql>SHOW GLOBAL STATUS LIKE '%semi%‘;

实现步骤，基于主从复制实现

配置主服务器配置文件，启动服务，授权账号

1. '修改主服务器配置文件'
[root@hai7-8 ~]$vim /etc/my.cnf
[mysqld]
server_id=2
log-bin
2. '重启服务'
root@hai7-8 ~]#systemctl start mariadb
3. '授权账户'
[root@hai7-8 ~]$mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%' IDENTIFIED BY 'centos';
1
2
3
4
5
6
7
8
9
10

配置从服务器配置文件
从服务器B

1. '修改配置文件'
[root@hai7-7 data]$vim /etc/my.cnf
[mysqld]
server_id=3
2. '重启服务'
[root@hai7-7 data]$systemctl start mariadb
3. '实现从服务器同步复制'
[root@hai7-7 data]$mysql
MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
4. '启动同步线程'
MariaDB [(none)]> START SLAVE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

从服务器C

1. '修改配置文件'
[root@hai7-6 ~]$vim /etc/my.cnf
[mysqld]
server_id=1
2. '启动服务'
[root@hai7-6 ~]$systemctl restart mariadb
3. '实现从服务器同步复制功能'
[root@hai7-6 ~]$mysql
MariaDB [(none)]> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.50.111',MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
4. '启动同步线程'
MariaDB [(none)]> START SLAVE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

达成主从复制后开始安装插件
主服务器上

1. '查看系统自带插件'
MariaDB [(none)]> SHOW PLUGINS;
2. '安装半同步插件'
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
3. '启动半同步插件，重启失效，生产环境要写入配置文件my.cnf'
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |  <==半同步服务器个数
1
2
3
4
5
6
7
8
9
10

在客户端上

1. '安装插件'
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2. '启动插件'
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
3. '启动后，从服务器线程并没有价值此插件，所以需要重启从服务器线程'
MariaDB [(none)]> stop slave;
MariaDB [(none)]> start slave;
4. '查看插件运行状态，值为ON说明设置半同步成功'
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

测试半同步是否达成

1. '停掉从服务器同步线程，根据半同步原理可知，主服务无法同步数据，会在超过等待时长后返回成功'
MariaDB [(none)]> STOP SLAVE;
2. '在主服务器更改数据'
MariaDB [hellodb]> INSERT teachers VALUES(6,'A',30,'M');
Query OK, 1 row affected (10.00 sec)     <==等待10秒后返回成功结果
1
2
3
4
5

6. 复制过滤器：

让从节点仅复制指定的数据库，或指定数据库的指定表

两种实现方式：
1. 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
  注意：此项和binlog_format相关，基于二进制还原将无法实现；不建议使用
  参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
  binlog-do-db = 数据库白名单列表，多个数据库需多行实现
  binlog-ignore-db = 数据库黑名单列表
2. 从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
  问题：会造成网络及磁盘IO浪费
  1. replicate_do_db= 指定复制库的白名单
  2. replicate_ignore_db= 指定复制库黑名单
  3. replicate_do_table= 指定复制表的白名单
  4. replicate_ignore_table= 指定复制表的黑名单
  5. replicate_wild_do_table= foo%.bar% 支持通配符
  6. replicate_wild_ignore_table=
3. 示例，在从服务器上在白名单中添加数据库hellodb，只能对进入库操作起作用，需要use hellodb进入数据库，改变数据后从服务器才能同步，hellodb.teacher写法会被过滤掉
```
MariaDB [hellodb]> SET GLOBAL replicate_do_db=hellodb;
1
```

7 MySQL复制加密

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

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

配置示例

申请证书
自搭建CA，生产CA秘钥cakey.pem

openssl genrsa 2048 > cakey.pem
1

生成自签名证书

openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
1

为主服务器A建立证书
rsa表示算法1024位，有效期为100天，-nodes表示不加密 -keyout表示输出私钥文件为master.key，然后生成证书申请csr

openssl req -newkey rsa:1024 -days 100 -nodes  -keyout master.key > master.csr
1

为主服务器颁发证书
-CA表示ca的证书文件，-CAkey表示ca的私钥文件，-set_serial表示给证书设置一个序列号从01开始编码

openssl x509 -req -in master.csr  -days 100 -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
1

从服务器B生成私钥，并生成证书申请

openssl req -newkey rsa:1024 -days 100 -nodes  -keyout slave.key > slave.csr
1

为从服务器B颁发证书

openssl x509 -req -in slave.csr -days 100 -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
1

将证书和私钥复制给主从服务器的对应目录下

'传送ca证书、主服务器私钥、主服务器证书给主服务器'
scp cacert.pem master.crt master.key 192.168.50.107:/etc/my.cnf.d/ssl/
'传送ca证书、从服务器私钥、从服务器证书给从服务器'
scp cacert.pem slave.crt slave.key 192.168.50.115:/etc/my.cnf.d/ssl/
1
2
3
4

配置主服务器

1. '修改配置文件'
[root@hai7-8 ssl]$vim /etc/my.cnf
[mysqld]
server_id=2
log-bin
ssl     <==主服务器启用加密功能
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem  <==ca证书
ssl-cert=/etc/my.cnf.d/ssl/master.crt  <==主服务器证书
ssl-key=/etc/my.cnf.d/ssl/master.key   <==主服务器私钥
2. '重启服务'
[root@hai7-8 ssl]$systemctl restart mariadb
1
2
3
4
5
6
7
8
9
10
11

进入数据库查看变量，判断是否配置成功

[root@hai7-8 ssl]$mysql
MariaDB [(none)]> show variables like '%ssl%';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| have_openssl  | YES                          |    <==YES表示配置成功
| have_ssl      | YES                          |
1
2
3
4
5
6
7

建立强制加密账号，强制加密关键词为require ssl

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO ssluser@'192.168.50.%' IDENTIFIED BY 'centos' require ssl;
1

配置从服务器

1. '清空同步信息，重新以加密方式同步' MariaDB [(none)]> STOP SLAVE; MariaDB [(none)]> RESET SLAVE ALL 2. '重新配置加密同步' MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.50.111', -> MASTER_USER='ssluser', -> MASTER_PASSWORD='centos', -> MASTER_LOG_FILE='mariadb-bin.000002', -> MASTER_LOG_POS=411, -> MASTER_SSL=1, <=启用加密 -> MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', <==ca的证书 -> MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', <==从服务器的证书 -> MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key'; <==从服务器的私钥 Query OK, 0 rows affected (0.00 sec) 3. '启动从服务器同步线程' MariaDB [(none)]> start slave;

8 主从复制的监控和维护

清理日志
1. 删除指定部分二进制日志
  PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
2. 删除二进制日志信息
  RESET MASTER
3. 删除从服务器信息
  RESET SLAVE
主从复制监控
1. 查看当前二进制日志位置，白名单和黑名单
  SHOW MASTER STATUS
2. 查看二进制日志记录详细内容
  SHOW BINLOG EVENTS
3. 查看所有二进制日志大小及位置
  SHOW BINARY LOGS
4. 查看从服务器同步复制信息
  SHOW SLAVE STATUS
5. 显示哪些连接线程正在运行
  SHOW PROCESSLIST
从服务器是否落后于主服务，在SHOW SLAVE STATUS列表中
Seconds_Behind_Master: 0
如何确定主从节点数据是否一致
percona-tools
数据不一致如何修复
删除从数据库，重新复制

9 MySQL高可用-集群架构

9.1 集群概述

实现高可用性解决方案
1. MMM: Multi-Master Replication Manager for MySQL，
  Mysql主主复制管理器是一套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)
  官网： http://www.mysql-mmm.org
  https://code.google.com/archive/p/mysql-master-master/downloads
2. MHA： Master High Availability，
  对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从
  官网:https://code.google.com/archive/p/mysql-master-ha/
3. Galera Cluster：wsrep(MySQL extended with the Write Set Replication)
  通过wsrep协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主读写
MHA集群架构
1. 搭建一个Manager的服务器充当管理员，它可以监听管理多组主从集群
2. 管理的主从集群，一旦主服务器（M）宕机，Manager就会自动选取一个从服务器（S）提升为主服务器，使其他的slave连接新的master进行复制
3. Manager服务器在提升从服务器前，会将从服务器读取到中继日志中的数据(i1)，继续执行完成写入数据库
4. 识别含有最新更新的slave，将数据（i2）分别读入各从服务器，保证从服务的数据时一致的
5. 宕机崩溃的master保存二进制日志事件（binlog events），可以将主服务器未同步的数据(X)补齐
主服务器宕机原因
1. 进程故障
  但是搭载MySQL的服务器正常，这种情况MHA可以通过ssh服务将其上的二进制日志读出来，应用到Latest Slave上，让Latest Slave上的数据更新至最新状态
2. 物理服务器损坏
  选择一个数据最新的服务器称为主服务器，其他从服务器自动将新的主服务器识别为主服务器

9.2 MHA

为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL 5.5的半同步复制

MHA软件由两部分组成，Manager工具包和Node工具包
1. 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线程）

工具包安装位置

在管理节点两个都要安装
mha4mysql-manager
mha4mysql-node
在被管理节点安装node包
mha4mysql-node

MHA集群搭建

管理节点
在管理节点建立配置文件，定义集群服务的设置

vim /etc/mastermha/app1.cnf
[server default]
user=mhauser          <==MHA服务器管理各主从服务器使用的账号
password=magedu    	  <==MHA服务器密码
manager_workdir=/data/mastermha/app1/            <==工作目录
manager_log=/data/mastermha/app1/manager.log     <==日志
remote_workdir=/data/mastermha/app1/      <==被管理节点上manager的工作目录，自动生成
ssh_user=root              <==主服务器上设置的管理员权限的账号，用来读取日志、提升从服务器等
repl_user=repluser         <==复制数据的账号密码
repl_password=magedu
ping_interval=1            <==1秒监控一次服务器是否正常工作
[server1]   			   <==被管理的服务器
hostname=192.168.8.17
candidate_master=1         <==可以充当master的从服务器，人为定义充当主服务器
[server2]
hostname=192.168.8.27
candidate_master=1
[server3]
hostname=192.168.8.37

自定义扩展选项
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地址’

实现主服务器（Master）

1. '修改配置文件'
vim /etc/my.cnf
[mysqld]
log-bin
server_id=1
skip_name_resolve=1       <==在MHA中，反向解析为强制项
2. '查看二进制文件，备用配置从服务器'
mysql>show master logs    
3. '创建从服务器复制权限账号'
mysql>grant replication slave on *.* to repluser@'192.168.8.%' identified by 	'magedu';
4. '创建高权限账号查看二进制与提升从服务器'
mysql>grant all on *.* to mhauser@'192.168.8.%’identified by‘magedu';

实现slave

1. '修改配置文件' vim /etc/my.cnf [mysqld] server_id=2 <==不同节点此值各不相同 log-bin read_only <==Manage会自动在提升为主的时候去掉此项 skip_name_resolve=1 2. '实现从服务器同步复制' mysql>CHANGE MASTER TO MASTER_HOST=‘MASTER_IP', MASTER_USER='repluser', MASTER_PASSWORD=‘magedu', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;

在所有节点实现相互之间ssh key验证

mha验证和启动

验证配置文件是否配置正确
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
验证同步复制功能是否正确
masterha_check_repl --conf=/etc/mastermha/app1.cnf
启动服务
masterha_manager --conf=/etc/mastermha/app1.cnf
排错日志
/data/mastermha/app1/manager.log

示例

在所有节点实现相互之间ssh key验证

1. '管理机manager上生成ssh秘钥'
[root@hai7-8 ~]$ssh-keygen
2. '将秘钥导出至本机'
[root@hai7-8 ~/.ssh]$ssh-copy-id 192.168.50.110
3. '将整个目录拷贝至集群所有主机上'
[root@hai7-8 ~]$scp -pr .ssh  192.168.50.115:/root/
......其他主机类似操作

实现主从关系

主服务器B设置修改配置文件，重启服务

[root@hai7-8 ~]$vim /etc/my.cnf
[mysqld]
server_id=1
innodb_file_per_table
log_bin
skip_name_resolve
重启服务
[root@hai7-8 ~]$systemctl start mariadb

建立管理账号

1. '用来主从复制的账号'
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.50.%' IDENTIFIED BY 'magedu';
2. '用于提升从服务器，读取日志等账号'
mysql>GRANT all ON *.* TO mhauser@'192.168.50.%'  IDENTIFIED BY 'magedu';
3. '查看二进制日志文件位置'
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       541 |
+--------------------+-----------+

从服务器设置
从服务器C配置，从服务器D的id为3，其他相同，重启服务

[mysqld]
server_id=2
log-bin
innodb_file_per_table
skip_name_resolve
relay_log_purge=0  <==禁止SQL线程执行完中继日志就删除，保留日志以备宕机补齐数据
read_only
[root@hai7-6 ~]$systemctl restart mariadb

在从服务器上设置同步复制

1. '设置复制命令'
MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.50.108',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='magedu',
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    ->  MASTER_LOG_POS=541;
2. '启动复制线程命令'
MariaDB [(none)]> START SLAVE;  
3. '查看从服务器设置，确定状态'
MariaDB [(none)]> SHOW SLAVE STATUS\G
配置MHA，需要epel源，需要的工具包mha4mysql-node-0.56-0.el6.noarch.rpm和mha4mysql-manager-0.56-0.el6.noarch.rpm

在manage管理端两个都要安装[root@hai7-8 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@hai7-8 ~]$yum -y install mha4mysql-manager-0.56-0.el6.noarch.rpm 
1
2

在被管理节点，也就是主从服务器上只需要安装mha4mysql-node[root@hai7-8 ~]$yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm 
1

在manage管理端手动建立管理配置文件[root@hai7-8 ~]$mkdir /etc/mha/              <==自建存放配置文件的目录，路径没有要求
[root@hai7-8 ~]$vim /etc/mha/appq.conf         <==编辑配置文件，文件名没有要求
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/      <==工作目录，会自动生成
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=magedu
ping_interval=1
[server1]
hostname=192.168.50.108
candidate_master=1
[server2]
hostname=192.168.50.115
candidate_master=1
[server3]
hostname=192.168.50.107
运行检查脚本，查看配置文件是否设置正确[root@hai7-8 ~]$masterha_check_ssh --conf=/etc/mha/app1.conf
1

查看复制是否设置正确[root@hai7-8 ~]$masterha_check_repl --conf=/etc/mha/appq.conf
1

启动管理服务[root@hai7-8 ~]$masterha_manager --conf=/etc/mha/appq.conf
1

测试manage，自动提升从服务器功能，宕掉主服务器

在主服务器端执行，杀掉进程，模拟宕机
[root@hai7-8 ~]$killall mysqld
管理端提升从服务器后，会从前端管理模式退出，为一次性任务，可以通过查看管理端日志，查看被提升从服务器。被提升的从服务器会自动将read_only修改为OFF状态，但是配置文件中内容还在，需要手工清除，避免重启修改回去
1
2
3

9.3 Galera Cluster

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

同步复制与异步复制
1. 同步复制
  使用急切复制的方法。节点通过更新单个事务中的所有副本来保持所有副本的同步。换句话说，当事务提交时，所有节点都具有相同的值
2. 异步复制
  使用延迟复制的方法。主数据库异步地将副本更新传播到其他节点。主节点传播副本后，事务提交。换句话说，当事务提交时，至少在短时间内，某些节点持有不同的值
同步复制优缺点
1. 理论上，与异步复制相比，同步复制有几个优点。
  1. 高可用性
    同步复制提供高度可用的群集，并保证24/7服务的可用性，因为：
    1. 节点崩溃时无数据丢失。
    2. 数据副本保持一致。
    3. 没有复杂的，费时的故障转移。
  2. 改进性能
    同步复制允许您在集群中的所有节点上并行执行事务，从而提高性能。
  3. 跨集群的因果关系
    同步复制保证了整个集群的因果关系。例如，SELECT在事务之后发出的查询总是看到事务的效果，即使事务是在另一个节点上执行的。
2. 同步复制的缺点
  节点数量的任何增加都会导致事务响应时间以及冲突和死锁概率的指数增长，因此，这直接导致异步复制仍然是数据库性能、可伸缩性和可用性的主要复制协议
解决同步复制中的缺点
Galera集群使用的基于证书的复制系统就是建立以下方法之上的
1. Group Communication（群体通信）
2. Write-sets（写集）
  将要被复制广播的事务
3. Database State Machine（数据库状态机）
  在本地处理事务，实现新事务首先在本地执行，然后作为读取集通过广播发送给其他数据库站点认证
4. Transaction Reordering（事务重排序）
  在提交事务广播之前，将事务重排序，实现增加事务认证测试通过数
基于认证的复制需要什么
1. 事务型数据库
  具备回滚功能，具备原子性，一系列数据库操作必须全部发生，否则什么都不会发生
2. 全局排序
  它要求对复制事件进行全局排序。具体而言，它们以相同的顺序应用于所有实例
Galera Cluster特点
1. 多主架构
  真正的多点读写的集群，在任何时候读写数据，都是最新的
2. 同步复制
  集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失
3. 并发复制
  从节点APPLY数据时，支持并行执行，更好的性能
4. 故障切换
  在出现数据库故障时，因支持多点写入，切换容易
5. 热插拔
  在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在节点故障期间，节点本身对集群的影响非常小
6. 自动节点克隆
  在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一致
7. 对应用透明
  集群的维护，对应用程序是透明的
Galera Cluster工作过程
1. 当客户端提交一个commit命令，在事务提交之前，所有对数据库的操作都会被写入write-set中，包括主键
2. 然后数据库会将这个write-set发给所有其他节点，write-set将在每个节点上使用主键进行认证尝试
3. 如果认证失败，节点会丢弃这个write-set，同时集群会回滚到之前的事务点；如果认证成功，commit正常提交，事务会应用到其他节点上
4. Galera Cluster基于认证的复制主要依赖于the global ordering of transactions，我们暂且称其为全局事务序号，复制期间，Galera Cluster会为每一个事务分配一个全局事务序号，类似序列号。
5. 当某个事务到达commit阶段时，节点会检查待提交事务的序号与上一次成功提交事务的序号，检查区间所有事务是否与新事务存在主键冲突，如果检查到冲突，认证就会失败。
6. 所有节点以相同的顺序接受事务，所有节点对事务是否提交做一致性决定。事务提交成功之后，首先生成此事务的节点会通知应用程序事务已正确提交
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/
Galera Cluster包括两个组件
1. Galera replication library (galera-3)
2. WSREP：MySQL extended with the Write Set Replication
WSREP复制实现：
至少需要三个节点，不能安装mariadb-server
1. percona-cluster
2. MariaDB-Cluster
查看集群中相关系统变量和状态变量
1. SHOW VARIABLES LIKE 'wsrep_%‘;
2. SHOW STATUS LIKE 'wsrep_%‘;
3. SHOW STATUS LIKE 'wsrep_cluster_size‘;

Galera Cluster实现步骤

安装，需要配置yum源，这里使用清华的yum源

yum源配置

[root@hai7-8 ~]$vim /etc/yum.repos.d/mysql.repo
[mysql]
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.59/yum/centos7-amd64/
gpgcheck=0
1
2
3
4

安装专门的集群功能版本的mariadb，包名为MariaDB-Galera-server
```
[root@hai7-8 ~]$yum install MariaDB-Galera-server -y
1
```

修改配置文件

[root@hai7-8 ~]$vim  /etc/my.cnf.d/sevim server.cnf
[galera]      <==在此语句块下修改
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so       <==galera提供的驱动模块
wsrep_cluster_address='gcomm://192.168.50.108,192.168.50.115,192.168.50.107'   <==galera组中的IP地址
binlog_format=row               <==基于二进制行的复制格式
default_storage_engine=InnoDB   <==默认存储引擎
innodb_autoinc_lock_mode=2      <==innodb锁级别
bind-address=0.0.0.0            <==绑定的地址，默认就行

下面为配置可选项
wsrep_cluster_name = ‘mycluster‘默认my_wsrep_cluster <==集群名称
wsrep_node_name = ‘node1’ <==自定义节点名称
wsrep_node_address = ‘192.168.8.7’

首次启动时，需要初始化集群，在其中一个节点上执行脚本mysql
mysql脚本来自安装包

[root@hai7-8 init.d]$rpm -qf mysql
MariaDB-Galera-server-5.5.59-1.el7.centos.x86_64
1
2

在其中一个节点上运行此脚本，后面跟start --wsrep-new-cluster表示开启新的组，后续的节点只要启动服务即可

[root@hai7-8 ~]$/etc/init.d/mysql start --wsrep-new-cluster
其他节点启动服务即可
[root@hai7-6 mysqldata]$service mysql start
1
2
3

9.4 复制的问题和解决方案

数据损坏或丢失
1. 主服务器（Master）
  MHA（提升从服务器为主） + semi repl（半同步复制确保数据完整）
2. 从服务器（Slave）
  重新复制
不惟一的server id
重新同步复制
复制延迟
1. 需要额外的监控工具的辅助
2. 一从多主：mariadb10版后支持
3. 多线程复制：对多个数据库复制

10. 性能衡量指标

数据库服务衡量指标：
1. qps
  query per second（每秒查询次数）
2. tps:
  transaction per second（每秒执行的事务次数）
压力测试工具
1. mysqlslap
2. Sysbench：功能强大
  https://github.com/akopytov/sysbench
3. tpcc-mysql
4. MySQL Benchmark Suite
5. MySQL super-smack
6. MyBench
MYSQL压力测试工具Mysqlslap
来自于mariadb包，测试的过程默认生成一个mysqlslap的schema，生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用–only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹
1. 使用格式
  mysqlslap [options]
2. 常用参数 [options] 说明
  1. --auto-generate-sql, -a
    自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
  2. --auto-generate-sql-load-type=type
    测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
  3. --auto-generate-sql-add-auto-increment
    代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持|
  4. --number-char-cols=N, -x
    N 自动生成的测试表中包含多少个字符类型的列，默认1
  5. --number-int-cols=N, -y
    N 自动生成的测试表中包含多少个数字类型的列，默认1
  6. --number-of-queries=N
    总的测试查询次数(并发客户数×每客户查询次数)
  7. --query=name,-q
    使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试
  8. --create-schema
    代表自定义的测试库名称，测试的schema
  9. --commint=N
    多少条DML后提交一次
  10. --compress, -C
    如服务器和客户端都支持压缩，则压缩信息
  11. --concurrency=N, -c
    N 表示并发量，即模拟多少个客户端同时执行select。可指定多个值，以逗号或者–delimiter参数指定值做为分隔符如：--concurrency=100,200,500
  12. --engine=engine_name, -e engine_name
    代表要测试的引擎，可以有多个，用分隔符隔开。例如：–engines=myisam,innodb
  13. --iterations=N, -i
    N测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次|
  14. --only-print
    只打印测试语句而不实际执行
  15. --detach=N
    执行N条语句后断开重连
  16. --debug-info, -T
    打印内存和CPU的相关信息
示例
1. 单线程测试
  mysqlslap -a -uroot -pmagedu
2. 多线程测试。使用–concurrency来模拟并发连接
  mysqlslap -a -c 100 -uroot -pmagedu
3. 测试同时不同的存储引擎的性能进行对比
```
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -pmagedu
1
```
  --number-of-queries 1000：查询一千次
  --concurrency=50,100：并发50和100分别测试
  --iterations=5：迭代5次
  --engine=myisam,innodb：分别对myisam,innodb引擎测试
4. 执行一次测试，分别50和100个并发，执行1000次总查询
```
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -pmagedu
1
```
5. 50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次
```
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -pmagedu
1
```

11 生产环境my.cnf配置示例

高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”

'硬件：内存32G'
innodb_file_per_table = 1
'打开独立表空间'
max_connections = 8000
'MySQL服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值，根据服务器性能判断，防止设置过大超过承载能力崩溃'
back_log = 300
'back_log 是操作系统在监听队列中所能保持的连接数，也就是最大会话数上限后，等待数上限'
max_connect_errors = 1000
'每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息'
open_files_limit = 10240
'所有线程所打开表的数量'
max_allowed_packet = 32M
'每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值'
wait_timeout = 10
'指定一个请求的最大连接时间，超时时长'
sort_buffer_size = 16M
'排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序'
join_buffer_size = 16M
'不带索引的全表扫描.使用的buffer的最小值'
query_cache_size = 128M
'查询缓冲大小'
query_cache_limit = 4M
'指定单个查询能够使用的缓冲区大小，缺省为1M'
transaction_isolation = REPEATABLE-READ
'设定默认的事务隔离级别'
thread_stack = 512K
'线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次连接时被预留.'
log-bin
'二进制日志功能'
binlog_format=row
'二进制日志格式'
innodb_buffer_pool_size = 24G
'InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到服务器物理内存大小的80%'
innodb_file_io_threads = 4
'用来同步IO操作的IO线程的数量'
innodb_thread_concurrency = 16
'在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍'
innodb_log_buffer_size = 16M
'用来缓冲日志数据的缓冲区的大小'
innodb_log_file_size = 512M
'在日志组中每个日志文件的大小'
innodb_log_files_in_group = 3
'在日志组中的文件总数'
innodb_lock_wait_timeout = 120
'SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间'
long_query_time = 2
'慢查询时长'
log-queries-not-using-indexes
'将没有使用索引的查询也记录下来'

posted @ 2018-11-04 13:38 朝圣布达拉阅读(508) 评论(0) 编辑收藏举报

刷新页面返回顶部

朝圣布达拉