MySQL复制

扩展方式： Scale Up ，Scale Out
MySQL的扩展
    读写分离
    复制：每个节点都有相同的数据集
        向外扩展
        二进制日志
        单向
复制的功用
    数据分布
    负载均衡读
    备份
    高可用和故障切换
    MySQL升级测试  

一主一从

image.png

一主多从

image.png

主从复制原理

image.png

MySQL垂直分区

image.png

MySQL水平分片（Sharding）

image.png

对应shard中查询相关数据

image.png

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日志的对应关系

主从复制特点：
    异步复制
    主从数据不一致比较常见
复制架构：
    Master/Slave, Master/Master, 环状复制
    一主多从
    从服务器还可以再有从服务器
    一从多主:适用于多个不同数据库
复制需要考虑二进制日志事件记录格式
    STATEMENT（5.0之前）
    ROW（5.1之后，推荐）
    MIXED

MySQL复制模型

image.png

MySQL复制

主从配置过程：参看官网
    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
    (2) 为当前节点设置一个全局惟一的ID号
        [mysqld]
        server_id=#
        log-basename=master 可选项，设置datadir中日志名称，确保不依赖主机名
    (3) 创建有复制权限的用户账号
        GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';  
从节点配置：
    (1) 启动中继日志
        [mysqld]
        server_id=# 为当前节点设置一个全局惟的ID号
        read_only=ON 设置数据库只读
        relay_log=relay-log relay log的文件路径，默认值hostname-relay-bin
        relay_log_index=relay-log.index 默认值hostname-relay-bin.index
    (2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
        mysql> CHANGE MASTER TO MASTER_HOST='host',
            MASTER_USER='repluser', MASTER_PASSWORD='replpass',
            MASTER_LOG_FILE=' mariadb-bin.xxxxxx', MASTER_LOG_POS=#;
        mysql> START SLAVE [IO_THREAD|SQL_THREAD];  
        
如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
    通过备份恢复数据至从服务器
    复制起始位置为备份时，二进制日志文件及其POS
如果要启用级联复制,需要在从服务器启用以下配置
    [mysqld]
    log_bin
    log_slave_updates
    
复制架构中应该注意的问题：
    1、限制从服务器为只读
        在从服务器上设置read_only=ON
            注意：此限制对拥有SUPER权限的用户均无效
        阻止所有用户, 包括主服务器复制的更新
            mysql> FLUSH TABLES WITH READ LOCK;
    2、 RESET SLAVE：从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
        RESET SLAVE ALL：清除所有从服务器上设置的主服务器同步信息，如PORT, HOST, USER和 PASSWORD 等
        注意：以上都需要先STOP SLAVE
    3、 sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件，global变量
        
    4、 如何保证主从复制的事务安全
    参看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同步到磁盘
    在slave节点启用服务器选项：
        skip-slave-start=ON 不自动启动slave
    在slave节点启用参数：
        sync_relay_log=# #次写后同步relay log到磁盘
        sync_relay_log_info=# #次事务后同步relay-log.info到磁盘        

小笔记：主从配置

#主
vim /etc/my.cnf
    [mysqld]
    server-id=1
    log-bin=/data/logbin/mysql
:wq
systemctl restart mariadb
mysql -uroot
    grant replication slave on *.* to 'repluser'@'192.168.37.%' indentified by 'centos';
    show master logs;       #mysql-bin.00002 |      402 |   
    show processlist;       #查看线程列表
    exit
    
#从 
vim /etc/my.cnf
    [mysqld]
    server-id=2
    log-bin=/data/logbin/mysql
:wq
systemctl restart mariadb
mysql -uroot
    CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=402;
    show slave status;              #查看从服务状态 
    exit
cat /usr/lib/mysql/master.info      #同步的信息(账号密码)
cat /relay-log.info                 #中继日志和二进制日志的关闭
#---------------分割线---------------#

#另一种主从配置(适合新机器)
#主
vim /etc/my.cnf
    [mysqld]
    server-id=1
    log-bin=/data/logbin/mysql
:wq
mysql -uroot
    grant replication slave on *.* to repluser@'192.168.37.%' identified by 'centos';
    exit
mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql
scp /data/all.sql 192.168.37.17:/data

#从(新机器)
vim /data/all.sql
    #把change master to整行语句替换为
    CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=402;
:wq  
vim /etc/my.cnf
    [mysql]
    server-id=2
    read-only
:wq
systemctl start mariadb
mysql -uroot
    source /data/all.sql
    show slave status\G
    start slave;
    
#从服务器有冲突导致无法同步
show slave status;                      #查看从服务器状态.记住错误编号
stop slave;
set global sql_slave_skip_counter=1     #临时忽略1条错误
start slave;
或者
vim /etc/my.cnf
    [mysqld]
    slave-skip-errors=1062
:wq
systemctl restart mariadb

#-------------分割线---------------#

#再添加一个从服务器
#主上
mysqldump -A -F --single-transaction --master-data=1 > /data/all2.sql
scp /data/all2.sql
#从2
vim /etc/my.cnf
    [mysql]
    server-id=3
    read-only
:wq
vim /data/all2.sql
    #把change master to整行语句替换为
    CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=402;
:wq
systemctl start mariadb
mysql < /data/all2.sql

#模拟主服务器dowm机了，从当新主
show slave status\G     #查看两级从服务器哪个同步数据最全
Read_Master_Log_Pos: 328    #这一行编号看谁最大
#例如从1当主：
vim /etc/my.cnf
    [mysql]
    server-id=2
    log_bin=/data/logbin/mysql
:wq
systemctl restart mariadb
mysql -uroot -e 'show master logs;'     #记录二进制日志位置
#从2
vim /etc/my.cnf
    [mysql]
    server-id=3
    log_bin=/data/logbin/mysql
    read-only
:wq
mysql -uroot
    stop slave;
    reset slave all;                #清除所有从数据
    CHANGE MASTER TO  MASTER_HOST='192.168.37.17',  MASTER_USER='repluser',
    MASTER_PASSWORD='centos',MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
    start slave;
    show slave status;
    
#级联(主对级联节点，级联节点对多从)
#主服务
vim /etc/my.cnf
    [mysql]
    server-id=1
    log_bin=/data/logbin/mysql
:wq
mysql -uroot
    grant replication slave on *.* to repluser@'192.168.37.%' identified by 'centos';
    exit
mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql
scp /data/all.sql 192.168.37.17:/data
#从1服务器
vim /etc/my.cnf
    [mysql]
    server-id=2
    log_bin=/data/logbin/mysql
    log_slave_updates               #级联必须参数
    read-only
:wq
vim /data/all.sql
    CHANGE MASTER TO  MASTER_HOST='192.168.37.7',  MASTER_USER='repluser',
    MASTER_PASSWORD='centos',MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=245;      
:wq
systemctl restart mariadb
mysql < /data/all.sql
mysql -uroot -e 'start slave;'
mysql -uroot -e 'show master logs;'
mysqldump -A -F --single-transaction --master-data=1 > /data/all2.sql
scp /data/all2.sql 192.168.37.27:/data
#从2服务器
vim /etc/my.cnf
    [mysql]
    server-id=3
    log_bin=/data/logbin/mysql
    read-only
:wq
vim /data/all2.sql
    CHANGE MASTER TO  MASTER_HOST='192.168.37.17',  MASTER_USER='repluser',
    MASTER_PASSWORD='centos',MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=245;    
:wq
systemctl restart mariadb
mysql < /data/all2.sql
mysql -uroot -e "start slave"
mysql -uroot -e "show slave status"

#测试，在主上创建、插入操作看是否同步
create database db1;

主主复制

主主复制：互为主从
    容易产生的问题：数据不一致；因此慎用
    考虑要点：自动增长id
        配置一个节点使用奇数id
            auto_increment_offset=1 开始点
            auto_increment_increment=2 增长幅度
        另一个节点使用偶数id
            auto_increment_offset=2
            auto_increment_increment=2
主主复制的配置步骤：
    (1) 各节点使用一个惟一server_id
    (2) 都启动binary log和relay log
    (3) 创建拥有复制权限的用户账号
    (4) 定义自动增长id字段的数值范围各为奇偶
    (5) 均把对方指定为主节点，并启动复制线程          

小笔记：主主配置(慎用)

#主1
vim /etc/my.cnf
    server-id=7
    log-bin
    auto_increment_offset=1
    auto_increment_increment=2
:wq
systemctl restart mariadb;
mysql -uroot -e 'grant replication slave on *.* to repluser@'192.168.37.%' identified by 'centos';'
mysql -uroot -e 'flush privileges;'
mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql
scp /data/all2.sql 192.168.37.17:/data
#主2
vim /etc/my.cnf
    server-id=17
    log-bin
    auto_increment_offset=2
    auto_increment_increment=2
:wq
vim /data/all.sql
    CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=405;
:wq
systemctl restart mariadb; 
mysql < /data/all.sql
mysql -e 'show master logs;'    #查看日志位置
#在主1上执行
mysql -uroot
    CHANGE MASTER TO
    MASTER_HOST='192.168.37.7',
    MASTER_USER='repluser',
    MASTER_PASSWORD='centos',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=522609;

半同步复制

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

● 半同步复制
    半同步复制实现：
    主服务器配置:
        mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
        mysql>SET GLOBAL rpl_semi_sync_master_enabled=1;
        mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1000;超时长1s
        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 rpl_semi_sync_slave_enabled=1;

小笔记：半同步配置

#主1
vim /etc/my.cnf
    server-id=7
    log-bin
    auto_increment_offset=1
    auto_increment_increment=2
:wq
systemctl restart mariadb;
#主服务器安装插件
mysql -uroot
    install plugin rpl_semi_sync_master soname 'semisync_master.so';
    show plugins;                               #查看插件
    set global rpl_semi_sync_master_enabled=1;
    show global variables like '%semi%';
    show global status like '%semi%';
mysql -uroot -e 'grant replication slave on *.* to repluser@'192.168.37.%' identified by 'centos';'
mysql -uroot -e 'flush privileges;'
mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql
scp /data/all2.sql 192.168.37.17:/data

#从1
vim /etc/my.cnf
    [mysql]
    server-id=17
    log_bin=/data/logbin/mysql
    read-only
:wq
vim /data/all.sql
    CHANGE MASTER TO  MASTER_HOST='192.168.37.7',  MASTER_USER='repluser',
    MASTER_PASSWORD='centos',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=245;
    start slave;
    show slave status;
:wq
systemctl restart mariadb
mysql < /data/all.sql
#从服务器安装插件
mysql -uroot
    install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
    set global rpl_semi_sync_slave_enabled=1;
    stop slave;
    start slave;
    
#从2
vim /etc/my.cnf
    [mysql]
    server-id=27
    log_bin=/data/logbin/mysql
    read-only
:wq
vim /data/all.sql
    CHANGE MASTER TO  MASTER_HOST='192.168.37.7',  MASTER_USER='repluser',
    MASTER_PASSWORD='centos',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=245;
    start slave;
    show slave status;
:wq
systemctl restart mariadb
mysql < /data/all.sql
#从服务器安装插件
mysql -uroot
    install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
    set global rpl_semi_sync_slave_enabled=1;
    stop slave;
    start slave;

MySQL复制

复制过滤器：
    让从节点仅复制指定的数据库，或指定数据库的指定表
两种实现方式：
    (1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
        注意：此项和binlog_format相关
        参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlog-ignore-db
        binlog-do-db = 数据库白名单列表，多个数据库需多行实现
        binlog-ignore-db = 数据库黑名单列表
        问题：基于二进制还原将无法实现；不建议使用
    (2) 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
        问题：会造成网络及磁盘IO浪费

主服务器定义复制过滤

vim /etc/my.cnf
    [mysql]
    binlog-ignore-db=hellodb    #黑名单

从服务器上的复制过滤器相关变量
    replicate_do_db= 指定复制库的白名单
    replicate_ignore_db= 指定复制库黑名单
    replicate_do_table= 指定复制表的白名单
    replicate_ignore_table= 指定复制表的黑名单
    replicate_wild_do_table= foo%.bar% 支持通配符
    replicate_wild_ignore_table=

从服务器定义复制过滤

vim /etc/my.cnf
    [mysql]
    replicate_do_db=hellodb,db1 #白名单(非白名单不复制)

MySQL复制加密

基于SSL复制：
    在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数
    据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的
    方式进行复制的方法，来进一步提高数据的安全性
配置实现：
    参看：https://mariadb.com/kb/en/library/replication-with-secure-connections/
    主服务器开启SSL，配置证书和私钥路径
    并且创建一个要求必须使用SSL连接的复制账号
    mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.8.%' IDENTIFIED BY ‘magedu' REQUIRE SSL;
    从服务器使用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';

小笔记：基于SSL加密

#主服务器
#生成主证书
mkdir /etc/my.cnf.d/ssl
cd /etc/my.cnf.d/ssl
opensll genrsa 2048 > cakey.pem     #私钥
openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650    #私钥签名
    CN
    Guangdong
    GZ
    magedu
    devops
    ca.magedu.com
openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr   
    CN
    Guangdong
    GZ
    magedu
    devops
    master.megedu.com
openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt    #生成主证书  

#生成从证书
openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave.key > slave.csr
    CN
    Guangdong
    GZ
    magedu
    devops
    slave.magedu.com
openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > slave.crt  
vim /etc/my.cnf
    [mysqld]
    server-id=7
    log-bin
    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
:wq 
systemctl restart mariadb
mysql -uroot
    status;
    show variables like '%ssl%';
    grant replication slave on *.* to repluser2@'192.168.37.%' identified by 'centos' require ssl;  #开启ssl加密

#连接方法
mysql --ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key
scp -r /etc/my.cnf.d/ssl 192.168.37.17:/etc/my.cnf.d

#从服务器
mysql --ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key -h192.168.37.7 -urepluser -pcentos #不安全，选项"--ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key"不加也可以连接
mysql --ssl-ca=cacert.pem --ssl-cert=master.crt --ssl-key=master.key -h192.168.37.7 -urepluser2 -pcentos    #安全,不加ssl不让连接

#从服务器配置复制加密
vim /etc/my.cnf
    [mysqld]
    server-id=17
    log-bin
    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
:wq
systemctl restart mariadb
mysql -uroot
    stop slave;
    reset slave all;
    #主服务查二进制日志记录show master logs;
    CHANGE MASTER TO MASTER_HOST='192.168.37.7', 
    MASTER_USER='repluser2',
    MASTER_PASSWORD='centos',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000004',
    MASTER_LOG_POS=413,
    MASTER_SSL=1;
    start slave;
    show slave status;

#测试 
#主服务删除repluser账号
mysql -uroot
    drop user repluser@'192.168.37.%';

复制的监控和维护

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

GTID复制

● GTID复制：（global transaction id 全局事务标识符） MySQL5.6版本开始支持，GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找同步
● GTID 架构
    GTID = server_uuid:transaction_id，在一组复制中，全局唯一
    server_uuid 来源于 auto.cnf
● GTID服务器相关选项
    gtid_mode gtid模式
    enforce_gtid_consistency 保证GTID安全的参数

GTID在binlog中的结构和GTID event 结构

image.png

GTID配置示例

主服务器
    vim /etc/my.cnf
        [mysqld]
        server-id=1
        log-bin=mysql-bin
        gtid_mode=ON
        enforce_gtid_consistency
        mysql> grant replication slave on *.* to 'repluser'@'192.168.8.%' identified by 'P@ssw0rd!';

从服务器
    vim /etc/my.cnf
        [mysqld]
        server-id=2
        gtid_mode=ON
        enforce_gtid_consistency
        mysql>CHANGE MASTER TO MASTER_HOST='192.168.8.100',
        MASTER_USER='repluser',
        MASTER_PASSWORD='P@ssw0rd!',
        MASTER_PORT=3306,
        MASTER_AUTO_POSITION=1;
        mysql>start slave;

小笔记：mysql5.7 二进制安装

#安装
useradd -r -s /bin/false mysql
tar -xf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -s mysql-5.7.26-el7-x86_64/ mysql
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
mkdir /data/mysql
chown mysql.mysql /data/mysql
mysqld --initialize --user=mysql --datadir=/data/mysql
    # [Note] A temporary password is generated for root@localhost: <G9sV#uF-2sr
cp /etc/my.cnf{,.bak}
vim /etc/my.cnf     #先清空
    [mysqld]
    datadir=/data/mysql
    socket=/data/mysql/mysql.sock
    log-error=/data/mysql/mysql.log
    pid-file=/data/mysql/mysql.pid
    [client]
    socket=/data/mysql/mysql.sock
:wq 
cp mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --list
service mysqld start
mysqladmin -uroot -p"<G9sV#uF-2sr" password centos

#连接测试
mysql -uroot -pcentos
    status;
    show database;

小笔记：mysql5.7 GTID配置主从

#主服务
vim /etc/my.cnf
    [mysql]
    server-id=17
    log-bin
    gtid_mode=ON                #新功能
    enforce_gtid_consistency    #新功能
:wq
service mysqld restart
mysql> grant replication slave on *.* to 'repluser'@'192.168.37.%' identified by 'centos';
mysql> flush privileges;

#从服务
vim /etc/my.cnf
    [mysql]
    server-id=27
    gtid_mode=ON                #新功能
    enforce_gtid_consistency    #新功能
:wq
service mysqld restart
mysql> CHANGE MASTER TO MASTER_HOST='192.168.37.17',
        MASTER_USER='repluser',
        MASTER_PASSWORD='centos',
        MASTER_PORT=3306,
        MASTER_AUTO_POSITION=1;     #新功能，代替手动写二进制日志位置
mysql> start slave;

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的升级版
    Mycat：基于Cobar， http://www.mycat.io/
    ProxySQL：https://proxysql.com/  

ProxySQL

ProxySQL： MySQL中间件
    两个版本：官方版和percona版，percona版是基于官方版基础上修改
    C++语言开发，轻量级但性能优异(支持处理千亿级数据) 具有中间件所需的绝大多数功能，包括：
        多种方式的读/写分离
        定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
        缓存查询结果
        后端节点监控
官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki 

ProxySQL安装

准备：
    实现读写分离前，先实现主从复制
    注意：slave节点需要设置read_only=1
基于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
基于RPM下载安装：https://github.com/sysown/proxysql/releases  

ProxySQL组成
    服务脚本：/etc/init.d/proxysql
    配置文件：/etc/proxysql.cnf
    主程序：/usr/bin/proxysql
    基于SQLITE的数据库文件：/var/lib/proxysql/
启动ProxySQL：service proxysql start
    启动后会监听两个默认端口
    6032：ProxySQL的管理端口
    6033：ProxySQL对外提供服务的端口
使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：
    mysql -uadmin -padmin -P6032 -h127.0.0.1

ProxySQL实现读写分离

数据库说明：
    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 有许多变量可以设置，其中就包括监听的端口、管理账号等
    参考: https://github.com/sysown/proxysql/wiki/Global-variables

向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; 

添加监控后端节点的用户。 ProxySQL通过每个节点的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;

监控模块的指标保存在monitor库的log表中
    查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常)
        MySQL> select * from mysql_server_connect_log;
    查看监控心跳信息 (对ping指标的监控)：
        MySQL> select * from mysql_server_ping_log;
    查看read_only和replication_lag的监控日志
        MySQL> select * from mysql_server_read_only_log;
        MySQL> select * from mysql_server_replication_lag_log; 

设置分组信息
    需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：
    writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20
        MySQL> 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 | 

配置发送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)'
    
在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  
    
测试读操作是否路由给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;    

小笔记：ProxySQL

#先搭主从
#主
yum install mariadb -y
vim /etc/my.cnf
    [mysqld]
    server-id=17
    log-bin
:wq
systemctl restart mariadb
mysql
    grant replication slave on *.* to 'repluser'@'192.168.37.%' identified by 'centos';
    grant replication client on *.* to 'monitor'@'192.168.37.%' identified by 'magedu';
    grant all on *.* to sqluser@'192.168.37.%' identified by 'magedu';  

#从
yum install mariadb -y
vim /etc/my.cnf
    [mysqld]
    server-id=27
    read-only
:wq
systemctl restart mariadb
mysql
    CHANGE MASTER TO MASTER_HOST='192.168.37.17', 
    MASTER_USER='repluser',
    MASTER_PASSWORD='centos',
    MASTER_LOG_FILE='mariadb-bin.000001',
    MASTER_LOG_POS=245,
    MASTER_SSL=1;
    start slave;

#ProxySQL服务器
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
yum install proxysql mariadb -y
service proxysql start

# /etc/proxysql.cnf     #配置文件
vim /etc/proxysql.cnf   
    #:6033改为3306
    interfaces="0.0.0.0:3306"
:wq
service proxysql restart

mysql -uadmin -padmin -P6032 -h127.0.0.1        #管理proxysql
    use main
    select * from sqlite_master where name='mysql_servers'\G
    select * from mysql_servers;
    insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.37.17',3306);
    insert into mysql_servers(hostgroup_id,hostname,port) values(20,'192.168.37.27',3306);
    load mysql servers to runtime;      #加载到内存，立即生效
    save mysql servers to disk;         #保存到磁盘，永久生效

    set mysql-monitor_username='monitor';
    set mysql-monitor_password='magedu';
    load mysql variables to runtime;
    save mysql variables to disk;
    #查询
    select * from mysql_server_connect_log;                 #查看监控连接日志，判断是否正常
    select * from mysql_server_ping_log;                    #查看监控心跳信息
    select * from mysql_server_read_only_log;               #查看read_only的监控日志
    select * from mysql_server_replication_lag_log;         #查看replication_lag的监控日志
    #设置分组
    insert into mysql_replication_hostgroups values(10,20,"test");
    load mysql servers to runtime;
    save mysql servers to disk;
    select hostgroup_id,hostname,port,status,weight from mysql_servers;
    #设置写组 
    insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10);
    load mysql users to runtime;
    save mysql users to disk;  
    #设置读组
    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);
   load mysql query rules to runtime;
   save mysql query rules 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)'
    #测试读操作是否路由给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;

MySQL高可用

● 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
● MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从
    官网:https://code.google.com/archive/p/mysql-master-ha/
● Galera Cluster：wsrep(MySQL extended with the Write Set Replication)通过wsrep协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主读写
● GR（Group Replication）：MySQL官方提供的组复制技术(MySQL 5.7.17引入的技术)，基于原生复制技术Paxos算法

MHA集群架构

image.png

MHA

image.png

MHA工作原理

1 从宕机崩溃的master保存二进制日志事件（binlog events）
2 识别含有最新更新的slave
3 应用差异的中继日志（relay log）到其他的slave
4 应用从master保存的二进制日志事件（binlog events）
5 提升一个slave为新的master
6 使其他的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的半同步复制

● 自定义扩展：
    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配置：为每个主从复制集群

实现MHA

● 在管理节点建立配置文件
    vim /etc/mastermha/app1.cnf
    [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.8.17
    candidate_master=1
    [server2]
    hostname=192.168.8.27
    candidate_master=1
    [server3]
    hostname=192.168.8.37 
    
● 实现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@'192.168.8.%' identified
    by ‘magedu';
    mysql>grant all on *.* to mhauser@'192.168.8.%’identified by‘magedu';

● 实现slave
    vim /etc/my.cnf
    [mysqld]
    server_id=2 不同节点此值各不相同
    log-bin
    read_only
    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;
    
● 在所有节点实现相互之间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    

小笔记：MHA集群配置

4台机器做实验：
A   IP:37.7：mha-master
B   IP:37.17：master
C   IP:37.27：slave1
D   IP:37.37：slave2

#BCD机器安装数据库
yum install mariadb-server -y

#master
vim /etc/my.cnf
    [mysql]
    server-id=17
    log-bin
    skip_name_resolve
:wq
systemctl restart mariadb
mysql -uroot -p
    grant all on *.* to repluser@'192.168.37.%' identified by 'magedu';
    grant all on *.* to mhauser@'192.168.37.%' identified by 'magedu';
    show master logs;   
yum install mha4mysql-node-0.56-0.el6.noarch.rpm

#slave1
vim /etc/my.cnf
    [mysql]
    server-id=27
    log-bin
    read-only
    skip_name_resolve
    relay_log_purge=0
:wq
systemctl restart mariadb
mysql -uroot -p
    CHANGE MASTER TO MASTER_HOST='192.168.37.17',
    MASTER_USER='repluser', MASTER_PASSWORD='magedu',
    MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
yum install mha4mysql-node-0.56-0.el6.noarch.rpm

#slave2
vim /etc/my.cnf
    [mysql]
    server-id=37
    log-bin
    read-only
    skip_name_resolve
    relay_log_purge=0   
:wq
systemctl restart mariadb
mysql -uroot -p
    CHANGE MASTER TO MASTER_HOST='192.168.37.17',
    MASTER_USER='repluser', MASTER_PASSWORD='magedu',
    MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245;
yum install mha4mysql-node-0.56-0.el6.noarch.rpm

#mha-master 
    yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm    #依赖需要epl源
    #基于key验证
    cd ~
    ssh-keygen
    ssh-copy-id 192.168.37.7
    scp -r .ssh 192.168.37.17:/root/
    scp -r .ssh 192.168.37.27:/root/
    scp -r .ssh 192.168.37.27:/root/
    mkdir /etc/mha
    vim /etc/mha/app1.cnf
        [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.37.17
        candidate_master=1
        [server2]
        hostname=192.168.37.27
        candidate_master=1
        [server3]
        hostname=192.168.37.37
        candidate_master=1
    :wq
    masterha_check_ssh --conf=/etc/mha/app1.cnf     #检测是否有错
    masterha_check_repl --conf=/etc/mha/app1.cnf    #检测是否有错
    masterha_manager --conf=/etc/mha/app1.cnf       #启动服务
配置完毕！   

模拟故障

#mha-master
    tail -f /data/mastermha/app1/manager.log
#把B机器关机，查看日志，slave1是否变为主，slave2跟新主同步，从变主后配置文件把read-only注释掉，旧主改为从

Galera Cluster

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

image.png

Galera Cluster特点

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

Galera Cluster工作过程

image.png

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/
● Galera Cluster包括两个组件
    Galera replication library (galera-3)
    WSREP：MySQL extended with the Write Set Replication
● WSREP复制实现：
    PXC：Percona XtraDB Cluster，是Percona对Galera的实现
    MariaDB Galera Cluster
    参考仓库：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/
● 注意：都至少需要三个节点，不能安装mariadb-server  

MySQL复制

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://192.168.8.7,192.168.8.17,192.168.8.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 = '192.168.8.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‘;    

小笔记：Galera Cluster集群安装与配置

#集群主服务器(至少3台机器)，需要卸载原有mariadb-server
cat > /etc/yum.repos.d/mysql.repo <<EOF
[Galera_Cluster]
name=Galera Cluster
baseurl=http://yum.mariadb.org/5.5.63/centos7-amd64/
gpgcheck=0
EOF
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://192.168.37.7,192.168.37.17,192.168.37.27"
    binlog_format=row
    wsrep_cluster_name = 'testcluster'
:wq
scp /etc/my.cnf.d/server.cnf 192.168.37.17:/etc/my.cnf.d/
scp /etc/my.cnf.d/server.cnf 192.168.37.27:/etc/my.cnf.d/

#首次启动时，需要初始化集群，在其中一个节点上执行命令
    /etc/init.d/mysql start --wsrep-new-cluster
#而后正常启动其它节点
    service mysql start

mysql -uroot -p
#查看集群中相关系统变量和状态变量
    SHOW VARIABLES LIKE 'wsrep_%‘;
    SHOW STATUS LIKE 'wsrep_%‘;
    SHOW STATUS LIKE 'wsrep_cluster_size‘;  

复制的问题和解决方案

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

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) 场景提供一站式的解决方案。  

TiDB 具备如下核心特点

● 1 高度兼容 MySQL 大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的 MySQL 集群亦可通过 TiDB 工具进行实时迁移
● 2水平弹性扩展 通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景。
● 3分布式事务 TiDB 100% 支持标准的 ACID 事务
● 4 真正金融级高可用 相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可以实现故障的自动恢复 (auto-failover)，无需人工介入。
● 5 一站式 HTAP 解决方案 TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合 TiSpark，可提供一站式 HTAP解决方案，一份存储同时处理OLTP & OLAP(OLAP、 OLTP的介绍和比较 )无需传统繁琐的 ETL 过程。
● 6 云原生 SQL 数据库 TiDB 是为云而设计的数据库，同 Kubernetes （十分钟带你理解Kubernetes核心概念 ）深度耦合，支持公有云、私有云和混合云，使部署、配置和维护变得十分简单。 TiDB 的设计目标是 100% 的 OLTP 场景和 80% 的 OLAP 场景，更复杂的 OLAP分析可以通过 TiSpark 项目来完成。 TiDB 对业务没有任何侵入性，能优雅的替换传统的数据库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale的细节问题，专注于业务开发，极大的提升研发的生产力. 

性能衡量指标

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

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
    --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的相关信息

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    

生产环境my.cnf配置示例

硬件：内存32G
innodb_file_per_table = 1
#打开独立表空间
max_connections = 8000
#MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值

#----------分割线--------------#
#最大连接数修改
vim /usr/lib/systemd/system/mariadb.service
    [Service]
    LimitNOFILE=6666
vim /etc/my.cnf
    [mysqld]
    max_connections=6666
systemctl deamon-reload
systemctl restart mairadb
mysqlslap -a -c 5000 -uroot -pmagedu        #并发测试
#----------分割线--------------#

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
#将没有使用索引的查询也记录下来

MYSQL配置最佳实践

● 高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”
● 参考：
    阿里巴巴Java开发手册
    58到家数据库30条军规解读
    http://zhuanlan.51cto.com/art/201702/531364.htm