mariadb备份

最新推荐文章于 2023-11-16 15:46:58 发布

孤独风中1匹狼

最新推荐文章于 2023-11-16 15:46:58 发布

阅读量507

点赞数

本文链接：https://blog.csdn.net/richeng_/article/details/93420503

版权

1、使用mysqldump备份数据库并通过备份及二进制日志还原数据(备份完后再写入数据,然后再删库)

[root@localhost ~]# mysql < hellodb_innodb.sql              #导入hellodb表用于测试
[root@localhost ~]# mysqldump -A --single-transaction -F --master-data=2 | gzip > /data/all_back_`date +%F-%T`.sql.gz       #-A做全备份  -F刷新binlog位置  --master-data=2注释chang-master-to语句
MariaDB [hellodb]> show master status;          #查看二进制日志位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     7655 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students;      #查看表中的数据
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test',11);          #插入新数据
Query OK, 1 row affected (0.00 sec)
[root@localhost data]# gzip -d all_back_2019-06-23-17\:55\:42.sql.gz 
[root@localhost data]# ls
all_back_2019-06-23-17:55:42.sql  hellodb_innodb.sql  log  mysql
[root@localhost data]# vim all_back_2019-06-23-17:55:42.sql                 #查看二进制日志位置
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=245;

MariaDB [(none)]> set sql_log_bin=off;          #临时关闭记录二进制日志
Query OK, 0 rows affected (0.00 sec)


[root@localhost log]# mysqlbinlog mysql-bin.00000{5,6} > /root/incr.sql                 #根据之前备份文件中的二进制日志位置将备份中没有的二进制日志导出
[root@localhost ~]# mysql < all_back_2019-06-23-17:55:42.sql            #导入
[root@localhost ~]# mysql < incr.sql

2、使用xtrabackup备份数据并还原

[root@mariadb-server ~]# rpm -ql percona-xtrabackup-24.x86_64           #安装xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.12
/usr/share/doc/percona-xtrabackup-24-2.4.12/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
[root@mariadb-server ~]# xtrabackup --backup --target-dir=./backup/         #备份
[root@mariadb-server ~]# scp -r ./backup/* 192.168.79.27:/root/backup       #将备份文件复制到远程服务器
[root@mariadb-server ~]# xtrabackup --prepare --target-dir=/root/backup/    #准备备份文件
[root@mariadb-backup ~]# xtrabackup --copy-back --target-dir=./backup       #将准备好的备份内容复制到数据库目录下
[root@mariadb-backup mysql]# chown -R mysql.mysql /data/                #更改数据库目录权限
[root@mariadb-backup ~]# systemctl start mariadb            #重启服务

3、MySQL数据备份企业实战(shell或python脚本实现)

使用xtrabackup以每周为一个备份周期做备份(数据库+二进制日志,备份至本地/data/backup)
提示: 周一某个时间点做一次完全备份,周二、三、四、五、六、日增量
备份存储目录"/data/backup/2018/52/1/备份数据" 目录解释"/data/backup/年/本年度的第几周/本周的第几天/数据" 一年52周一周7天

[root@centos7 ~]# cat backup.sh 
#!/bin/bash
#
export week=`date +%W`
export user=''
export password=''
export day=`date +%w`
let yesterday=$day-1
export year=`date +%Y`
export backupdir=/data/backup/$year/$week/$day
export basedir=/data/backup/$year/$week/$yesterday
export binlogdir=/data
#
mkdir -p $backupdir 
echo 'backupdir created successfully'
if [ $day==1 ];then
        xtrabackup --user=$user --password=$password --backup --target-dir=$backupdir &> /dev/null 
else
        xtrabackup --backup --target-dir=$backupdir --incremental-basedir=$basedir &> /dev/null
fi
cp -a $binlogdir/mysql-bin.* $backupdir
unset week
unset user
unset password
unset day
unset yesterday
unset year
unset backupdir
unset basedir
[root@centos7 data]# crontab -e
0 1 * * * bash /root/backup.sh

4、描述MySQL复制工作原理并实现主从,主主,主从级联及半同步复制

Mysql的复制：
- 每个节点都有相同的数据集
- 向外扩展
- 二进制日志
- 单向
复制的作用：
- 数据分布
- 负载均衡读
- 备份
- 高可用和故障切换
- Mysql升级测试

MySQL复制原理

主从复制：异步复制，主从数据不一致比较常见
- 主节点:
  - dump Thread：为每个slave的IO thread启动一个dump线程，用于向其发送binary log event
- 从节点：
  - IO thread：向master请求二进制日志事件，并保存于中继日志中
  - SQL thread：从中继日志中读取日志事件，在本地完成重放
- 复制相关的文件：
  - master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
  - relay-log.info：保存当前slave节点上已经复制的当前二进制日志和本地relay-log之间的对应关系
  - 复制时二进制日志格式推荐使用ROW
复制架构：
- master/slave，master/master，环状复制
- 一主多从
- 从服务器还可以再有从服务器
- 一从多主：适用于多个不用数据库

多种复制架构在这里插入图片描述

[root@mariadb-server log]# vim /etc/my.cnf              #更改master节点配置文件
[mysqld]
server-id       = 1                 #设置节点编号为1
MariaDB [hellodb]> grant replication slave on *.* to 'repluser'@'192.168.79.%' identified by 'replpass';            #创建repluser用户，密码为replpass，并允许在192.168.79.0网段的主机登录并对所有表做复制操作
Query OK, 0 rows affected (0.00 sec)
[root@mariadb-backup log]# vim /etc/my.cnf             #更改slave节点配置文件
[mysqld]
server-id       = 2
read_only=ON

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.37', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;     #指定二进制日志文件位置，即从什么地方开始复制，指定复制数据使用的账号密码和目标主机
MariaDB [(none)]> start slave;                      #开启slave线程
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G           #查看slave状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.79.37
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 8073
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 8357
        Relay_Master_Log_File: mysql-bin.000001
             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: 8073
              Relay_Log_Space: 8653
              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
1 row in set (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test',11);              #添加新数据进行测试
Query OK, 1 row affected (0.00 sec)

[root@mariadb-backup mysql]# cat mariadb-relay-bin.index                        #relaylog索引
./mariadb-relay-bin.000002
./mariadb-relay-bin.000003

[root@mariadb-backup mysql]# cat relay-log.info                                     #记录二进制日志和中继日志之间的位置关系
./mariadb-relay-bin.000003
8599
mysql-bin.000001
8315

#级联复制，在主从复制的基础上再增加一个节点从从节点同步数据
[root@centos7 log]# vim /etc/my.cnf  
[mysqld]
log_bin
log_slave_updates       #slavelog实时更新
read_only=on            #数据库只读
server_id = 3
[root@centos7 log]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.27', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
MariaDB [(none)]> start salve;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.79.27
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 8956
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 9241
        Relay_Master_Log_File: master-bin.000001
             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: 8956
              Relay_Log_Space: 9537
              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: 2
1 row in set (0.00 sec)

#主主复制
#在两台服务器上各自建立一个具有复制权限的用户；让两个数据库互为主从的关系
[root@mariadb-server log]# vim /etc/my.cnf
server-id       = 1
auto-increment-offset = 1
auto-increment-increment = 2
[root@mariadb-backup mysql]# vim /etc/my.cnf
auto-increment-increment = 2
auto-increment-offset = 2
log-basename=master
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.27', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.37', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test13',11);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | test          |  11 | F      |    NULL |      NULL |
|    27 | test12        | 111 | F      |    NULL |      NULL |
|    29 | test13        |  11 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test14',11);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | test          |  11 | F      |    NULL |      NULL |
|    27 | test12        | 111 | F      |    NULL |      NULL |
|    29 | test13        |  11 | F      |    NULL |      NULL |
|    30 | test14        |  11 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
29 rows in set (0.00 sec)

#半同步复制：需要通过插件的方式实现
MariaDB [hellodb]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 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_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     |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)

5、描述MySQL Proxy原理并实现读写分离

ProxySQL:MySQl中间件，有官方版和percona两个版本，percona版是基于官方版基础上修改，轻量级但性能优异，具有中间件所需的绝大多数功能，包括：
- 多种方式的读/写分离
- 定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
- 缓存查询结果
- 后端节点监控
基于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
[root@centos7 ~]# rpm -ql proxysql
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl

MySQL [(none)]> select * from mysql_servers;
Empty set (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.79.37',3306);              #设置mysql_servers主机信息
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.79.27',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql servers to runtime;                  #加载配置
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql servers to disk;                     #存盘
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> select * from mysql_servers;                                             
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.79.37 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.79.27 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
master
MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.79.%' identified by 'sigeling';               #创建监控用户
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> show variables like 'mysql-monitor%';
+-----------------------------------------------------+----------+
| Variable_name                                       | Value    |
+-----------------------------------------------------+----------+
| mysql-monitor_enabled                               | true     |
| mysql-monitor_connect_timeout                       | 600      |
| mysql-monitor_ping_max_failures                     | 3        |
| mysql-monitor_ping_timeout                          | 1000     |
| mysql-monitor_read_only_max_timeout_count           | 3        |
| mysql-monitor_replication_lag_interval              | 10000    |
| mysql-monitor_replication_lag_timeout               | 1000     |
| mysql-monitor_groupreplication_healthcheck_interval | 5000     |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800      |
| mysql-monitor_replication_lag_use_percona_heartbeat |          |
| mysql-monitor_query_interval                        | 60000    |
| mysql-monitor_query_timeout                         | 100      |
| mysql-monitor_slave_lag_when_null                   | 60       |
| mysql-monitor_wait_timeout                          | true     |
| mysql-monitor_writer_is_also_reader                 | true     |
| mysql-monitor_username                              | monitor  |
| mysql-monitor_password                              | sigeling |
| mysql-monitor_history                               | 600000   |
| mysql-monitor_connect_interval                      | 60000    |
| mysql-monitor_ping_interval                         | 10000    |
| mysql-monitor_read_only_interval                    | 1500     |
| mysql-monitor_read_only_timeout                     | 500      |
+-----------------------------------------------------+----------+
22 rows in set (0.00 sec)

MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql variables to disk;
Query OK, 97 rows affected (0.01 sec)
监控模块的指标保存在monitor库的log表中
MySQL [main]> select * from mysql_server_connect_log;               #查看日志看连接情况是否正常
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 192.168.79.27 | 3306 | 1561377241510143 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377242522008 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377301511225 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377302534131 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377361511760 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377362408165 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377421512835 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377422485322 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377481513867 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377482309172 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377541514951 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377542415633 | 0                       | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377550327921 | 1484                    | NULL                                                                   |
| 192.168.79.27 | 3306 | 1561377551092608 | 2051                    | NULL                                                                   |
| 192.168.79.27 | 3306 | 1561377610328914 | 1467                    | NULL                                                                   |
| 192.168.79.37 | 3306 | 1561377611285835 | 2024                    | NULL                                                                   |
| 192.168.79.37 | 3306 | 1561377670329384 | 1941                    | NULL                                                                   |
| 192.168.79.27 | 3306 | 1561377671022958 | 1409                    | NULL                                                                   |
| 192.168.79.37 | 3306 | 1561377730329710 | 793                     | NULL                                                                   |
| 192.168.79.27 | 3306 | 1561377731376983 | 1674                    | NULL                                                                   |
| 192.168.79.27 | 3306 | 1561377790329952 | 2625                    | NULL                                                                   |
| 192.168.79.37 | 3306 | 1561377791464267 | 1471                    | NULL                                                                   |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
22 rows in set (0.00 sec)

MySQL [main]> select * from mysql_replication_hostgroups;
Empty set (0.00 sec)

MySQL [main]> insert into mysql_replication_hostgroups values(10,20,"test");            #设置分组信息，指定写组的id为10，读组的id为20
Query OK, 1 row affected (0.00 sec)

MySQL [main]> load mysql servers to runtime;

MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组
MySQL [main]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname      | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10           | 192.168.79.37 | 3306 | ONLINE | 1      |
| 20           | 192.168.79.27 | 3306 | ONLINE | 1      |
+--------------+---------------+------+--------+--------+
2 rows in set (0.00 sec)


MariaDB [(none)]> grant all on *.* to sqluser@'192.168.79.%' identified by 'sigeling';              #创建访问用户
Query OK, 0 rows affected (0.00 sec)

MySQL [main]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','sigeling',10);             #配置proxysql，将用户sqluser添加到mysql_users表中，default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库
Query OK, 1 row affected (0.00 sec)

[root@centos7 ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'select @@server_id'         #使用sqluser测试是否能酷邮到默认的10写组实现数据读写
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@centos7 ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'create database testdb' 
[root@centos7 ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'use testdb;create table t(id int)'
MariaDB [(none)]> desc testdb.t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)


MySQL [main]> insert into mysql_query_rules                 #配置路由规则，实现读写分离，插入路由规则而，将select语句分离到20的都组，select语句中有一个特殊语句select ... for update会申请写锁，应当路由到10的写组
    -> (rule_id,active,match_digest,destination_hostgroup,apply)VALUES
    -> (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)
注意：因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id

[root@centos7 ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'select @@server_id'  +-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
[root@centos7 ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' 
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

[root@centos7 ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'insert testdb.t values(1)'
[root@centos7 ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'select id from testdb.t'

MySQL [main]> select * from stats_mysql_query_digest order by sum_time desc;            #查询stats库中的stats_mysql_query_digest表
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 10        | testdb             | sqluser  | 0x4E6025FB1E51A2E5 | create table t(id int)           | 1          | 1561378573 | 1561378573 | 5327     | 5327     | 5327     |
| 10        | information_schema | sqluser  | 0xDA65260DF35B8D13 | select @@server_id               | 4          | 1561378489 | 1561379221 | 3979     | 565      | 1841     |
| 20        | information_schema | sqluser  | 0xDA65260DF35B8D13 | select @@server_id               | 2          | 1561379136 | 1561379221 | 2295     | 381      | 1914     |
| 10        | information_schema | sqluser  | 0xA3A8AA9A5EC1ED82 | start trancsaction               | 2          | 1561379207 | 1561379213 | 2205     | 857      | 1348     |
| 10        | information_schema | sqluser  | 0x8CC3B08B69E5ED49 | insert testdb.t values(?)        | 1          | 1561379265 | 1561379265 | 1903     | 1903     | 1903     |
| 10        | information_schema | sqluser  | 0x326F4F2B935EC266 | start transaction                | 1          | 1561379221 | 1561379221 | 1506     | 1506     | 1506     |
| 10        | information_schema | sqluser  | 0x620B328FE9D6D71A | SELECT DATABASE()                | 2          | 1561378565 | 1561378573 | 1377     | 461      | 916      |
| 20        | information_schema | sqluser  | 0x7D040729C574DF03 | select id from testdb.t          | 1          | 1561379279 | 1561379279 | 1140     | 1140     | 1140     |
| 10        | information_schema | sqluser  | 0x9461F19B72760588 | create database testdb           | 1          | 1561378528 | 1561378528 | 628      | 628      | 628      |
| 10        | testdb             | sqluser  | 0x02A595FB8F1DFC7C | create table(id int)             | 1          | 1561378565 | 1561378565 | 599      | 599      | 599      |
| 10        | information_schema | sqluser  | 0x4CD0FEC20B21FB99 | create databases testdb          | 1          | 1561378523 | 1561378523 | 567      | 567      | 567      |
| 10        | information_schema | sqluser  | 0xDB3A841EF5443C35 | commit                           | 1          | 1561379221 | 1561379221 | 378      | 378      | 378      |
| 10        | information_schema | sqluser  | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 13         | 1561378489 | 1561379279 | 0        | 0        | 0        |
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
13 rows in set (0.00 sec)

MySQL [main]> SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 10 | 5327     | 1          | create table t(id int)           |
| 10 | 3979     | 4          | select @@server_id               |
| 20 | 2295     | 2          | select @@server_id               |
| 10 | 2205     | 2          | start trancsaction               |
| 10 | 1903     | 1          | insert testdb.t values(?)        |
| 10 | 1506     | 1          | start transaction                |
| 10 | 1377     | 2          | SELECT DATABASE()                |
| 20 | 1140     | 1          | select id from testdb.t          |
| 10 | 628      | 1          | create database testdb           |
| 10 | 599      | 1          | create table(id int)             |
| 10 | 567      | 1          | create databases testdb          |
| 10 | 378      | 1          | commit                           |
| 10 | 0        | 13         | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+

6、使用MHA及galera实现MySQL的高可用性

MHA：Master High Availability
- 对主节点进行监控，可实现自动故障转移至其他从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA，要求一个复制集群中最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库
- 工作原理：
  - 从宕机崩溃的master保存二进制日志（binlog events）
  - 识别含有最新更新的slave
  - 应用差异的中继日志（relay log）到其他的slave
  - 应用从master保存的二进制日志（binlog event）
  - 提升一个slave为新的master
  - 使其他的slave连接新的master进行复制
- 软件构成：
  - Manager工具包：
    - masterha_check_ssh：检查MHA的SSH配置状况
    - masterha_check_repl：检查数据库复制状况
    - masterha_manager：启动MHA
    - masterha_check_status：检测当前MHA运行状态
    - masterha_master_monitor：检测master是否宕机
    - masterha_master_swith：故障转移（自动或手动）
    - masterha_conf_host：添加或删除配置的server信息
  - Node工具包：通常由MHA manager的脚本出发，无需人为操作
    - save_binary_logs：保存和复制master的二进制日志
    - apply_diff_relay_logs：识别差异的中继日志事件并将其差异的事件应用于其他的slave
    - filter_mysqlbinlog：去除不必要的ROLLBACK事件（MHA已不再使用此工具）
    - purge_relay_logs：清除中继日志（不会阻塞SQL线程）
  - 自定义扩展：
    - secondary_check_script：通过多条网络路由检测master的可用性
    - master_ip_failover_scipt：更新Application使用的masterip
    - shutdown_script：强制关闭master节点
    - report_script：发送报告
    - init_conf_load_script：加载初始配置参数
    - master_ip_online_change_script：更新master节点ip地址
- 注意：为了尽可能减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL5.5的半同步复制
- 配置文件：
  - global配置，为各application提供默认配置
  - application配置：为每个主从复制集群提供配置
    
    mha集群架构
    mha实现原理

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm   #各个节点都需要安装
yum -y install mha4mysql-manager-0.56-0.el6.noarch.rpm         #仅管理节点需要安装
[root@centos7 ~]# vim /etc/mastermha/app1.cnf           #配置mastermha，添加mysql主机以及配置mysql数据目录等属性
[server default]
user=mhauser
password=sigeling
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
master_binlog_dir=/data/log/
ssh_user=root
repl_user=repluser
repl_password=sigeling
ping_interval=1

[server1]
hostname=192.168.79.27
candidate_master=1
[server2]
hostname=192.168.79.37
candidate_master=1
[server3]
hostname=192.168.79.47

[root@mariadb-server ~]# vim /etc/my.cnf            #配置master节点
skip-name-resolve=1
log-bin=/data/log/mysql-bin
server-id       = 1
MariaDB [(none)]> show master logs
    -> ;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     30358 |
| mysql-bin.000002 |   1038814 |
| mysql-bin.000003 |       264 |
| mysql-bin.000004 |       245 |
+------------------+-----------+
4 rows in set (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.79.%' identified by 'sigeling';               #创建拥有复制权限的用户
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to mhauser@'192.168.79.%' identified by 'sigeling';          #创建mhauser用户用于远程对master库进行操作
Query OK, 0 rows affected (0.00 sec)
[root@mariadb-backup ~]# vim /etc/my.cnf            #配置slave节点
log-bin=/data/log/mysql-bin
server-id       = 2
read_only=ON
relay-log-purge=0
skip-name-resolve=1

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.37',MASTER_USER='repluser', MASTER_PASSWORD='sigeling', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245;             #配置主从复制
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status;
Empty set (0.00 sec)

ssh-keygen                  #各节点实现ssh免密登录，各节点使用相同的密钥对
ssh-copy-id 192.168.79.17
ssh-copy-id 192.168.79.27
ssh-copy-id 192.168.79.37


[root@centos7 ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf             #检测各节点ssh通信状态
Mon Jun 24 10:13:28 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:13:28 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:13:28 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:13:28 2019 - [info] Starting SSH connection tests..
Mon Jun 24 10:13:29 2019 - [debug] 
Mon Jun 24 10:13:28 2019 - [debug]  Connecting via SSH from root@192.168.79.27(192.168.79.27:22) to root@192.168.79.37(192.168.79.37:22)..
Mon Jun 24 10:13:28 2019 - [debug]   ok.
Mon Jun 24 10:13:28 2019 - [debug]  Connecting via SSH from root@192.168.79.27(192.168.79.27:22) to root@192.168.79.47(192.168.79.47:22)..
Mon Jun 24 10:13:29 2019 - [debug]   ok.
Mon Jun 24 10:13:30 2019 - [debug] 
Mon Jun 24 10:13:29 2019 - [debug]  Connecting via SSH from root@192.168.79.37(192.168.79.37:22) to root@192.168.79.27(192.168.79.27:22)..
Mon Jun 24 10:13:29 2019 - [debug]   ok.
Mon Jun 24 10:13:29 2019 - [debug]  Connecting via SSH from root@192.168.79.37(192.168.79.37:22) to root@192.168.79.47(192.168.79.47:22)..
Mon Jun 24 10:13:29 2019 - [debug]   ok.
Mon Jun 24 10:13:31 2019 - [debug] 
Mon Jun 24 10:13:29 2019 - [debug]  Connecting via SSH from root@192.168.79.47(192.168.79.47:22) to root@192.168.79.27(192.168.79.27:22)..
Mon Jun 24 10:13:29 2019 - [debug]   ok.
Mon Jun 24 10:13:29 2019 - [debug]  Connecting via SSH from root@192.168.79.47(192.168.79.47:22) to root@192.168.79.37(192.168.79.37:22)..
Mon Jun 24 10:13:30 2019 - [debug]   ok.
Mon Jun 24 10:13:31 2019 - [info] All SSH connection tests passed successfully.

[root@centos7 ~]#  masterha_check_repl --conf=/etc/mastermha/app1.cnf           #检测个节点复制功能是否正常
Mon Jun 24 10:16:41 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:16:41 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:16:41 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:16:41 2019 - [info] MHA::MasterMonitor version 0.56.
Mon Jun 24 10:16:42 2019 - [info] GTID failover mode = 0
Mon Jun 24 10:16:42 2019 - [info] Dead Servers:
Mon Jun 24 10:16:42 2019 - [info] Alive Servers:
Mon Jun 24 10:16:42 2019 - [info]   192.168.79.27(192.168.79.27:3306)
Mon Jun 24 10:16:42 2019 - [info]   192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info]   192.168.79.47(192.168.79.47:3306)
Mon Jun 24 10:16:42 2019 - [info] Alive Slaves:
Mon Jun 24 10:16:42 2019 - [info]   192.168.79.27(192.168.79.27:3306)  Version=5.5.60-MariaDB (oldest major version between slaves) log-bin:enabled
Mon Jun 24 10:16:42 2019 - [info]     Replicating from 192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Jun 24 10:16:42 2019 - [info]   192.168.79.47(192.168.79.47:3306)  Version=5.5.60-MariaDB (oldest major version between slaves) log-bin:enabled
Mon Jun 24 10:16:42 2019 - [info]     Replicating from 192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info] Current Alive Master: 192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info] Checking slave configurations..
Mon Jun 24 10:16:42 2019 - [warning]  relay_log_purge=0 is not set on slave 192.168.79.27(192.168.79.27:3306).
Mon Jun 24 10:16:42 2019 - [warning]  relay_log_purge=0 is not set on slave 192.168.79.47(192.168.79.47:3306).
Mon Jun 24 10:16:42 2019 - [info] Checking replication filtering settings..
Mon Jun 24 10:16:42 2019 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon Jun 24 10:16:42 2019 - [info]  Replication filtering check ok.
Mon Jun 24 10:16:42 2019 - [info] GTID (with auto-pos) is not supported
Mon Jun 24 10:16:42 2019 - [info] Starting SSH connection tests..
Mon Jun 24 10:16:45 2019 - [info] All SSH connection tests passed successfully.
Mon Jun 24 10:16:45 2019 - [info] Checking MHA Node version..
Mon Jun 24 10:16:45 2019 - [info]  Version check ok.
Mon Jun 24 10:16:45 2019 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jun 24 10:16:45 2019 - [info] HealthCheck: SSH to 192.168.79.37 is reachable.
Mon Jun 24 10:16:46 2019 - [info] Master MHA Node version is 0.56.
Mon Jun 24 10:16:46 2019 - [info] Checking recovery script configurations on 192.168.79.37(192.168.79.37:3306)..
Mon Jun 24 10:16:46 2019 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/log/ --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000005 
Mon Jun 24 10:16:46 2019 - [info]   Connecting to root@192.168.79.37(192.168.79.37:22).. 
  Creating /data/mastermha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/log/, up to mysql-bin.000005
Mon Jun 24 10:16:46 2019 - [info] Binlog setting check done.
Mon Jun 24 10:16:46 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon Jun 24 10:16:46 2019 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.79.27 --slave_ip=192.168.79.27 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.60-MariaDB --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Mon Jun 24 10:16:46 2019 - [info]   Connecting to root@192.168.79.27(192.168.79.27:22).. 
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mariadb-relay-bin.000003
    Temporary relay log file is /data/mysql/mariadb-relay-bin.000003
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon Jun 24 10:16:46 2019 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.79.47 --slave_ip=192.168.79.47 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.60-MariaDB --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Mon Jun 24 10:16:46 2019 - [info]   Connecting to root@192.168.79.47(192.168.79.47:22).. 
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mariadb-relay-bin.000003
    Temporary relay log file is /data/mysql/mariadb-relay-bin.000003
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon Jun 24 10:16:46 2019 - [info] Slaves settings check done.
Mon Jun 24 10:16:46 2019 - [info] 
192.168.79.37(192.168.79.37:3306) (current master)
 +--192.168.79.27(192.168.79.27:3306)
 +--192.168.79.47(192.168.79.47:3306)

Mon Jun 24 10:16:46 2019 - [info] Checking replication health on 192.168.79.27..
Mon Jun 24 10:16:46 2019 - [info]  ok.
Mon Jun 24 10:16:46 2019 - [info] Checking replication health on 192.168.79.47..
Mon Jun 24 10:16:46 2019 - [info]  ok.
Mon Jun 24 10:16:46 2019 - [warning] master_ip_failover_script is not defined.
Mon Jun 24 10:16:46 2019 - [warning] shutdown_script is not defined.
Mon Jun 24 10:16:46 2019 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

[root@centos7 ~]# masterha_manager --conf=/etc/mastermha/app1.cnf           #类似于守护进程，当master节点挂了之后会更改slave节点的配置，将slave节点自动提升为master节点，功能完成后脚本将退出，修复完原master节点后不会自动切换回去，可选择将原master节点配置为新的salve节点或者手动切换，重新启动相关进程，重新配置该master节点为slave节点
Mon Jun 24 10:17:08 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:17:08 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:17:08 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..

[root@mariadb-server ~]# systemctl stop mariadb
[root@centos7 app1]# masterha_manager --conf=/etc/mastermha/app1.cnf
Mon Jun 24 10:25:01 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:25:01 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:25:01 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
  Creating /data/mastermha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/log/, up to mysql-bin.000005
Mon Jun 24 10:25:09 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:25:09 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:25:09 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.79.27
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000005
             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: 245
              Relay_Log_Space: 825
              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: 2
1 row in set (0.00 sec)

Galera Cluster集群架构
在这里插入图片描述

如上图所示，三个节点组成了一个集群，与普通的主从架构不同，它们都可以作为主节点，三个节点是对等的，成为multi-master架构，当有客户端要写入或者读取数据时，连接哪一个示例都是一样的，读到的数据是相同的，写入某一个节点之后。集群载自己会将新数据同步到其他节点上，这种架构不共享任何数据，是一种高冗余架构

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

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.59/yum/centos7-amd64/
至少需要三个节点，不能安装mariadb-server

[root@localhost ~]# vim /etc/yum.repos.d/ganlera.repo 
[galera]
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.64/yum/centos7-amd64/
gpgcheck=0
[root@localhost ~]# yum -y install MariaDB-Galera-server
[root@localhost ~]# vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.79.27,192.168.79.37,192.168.79.47"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
wsrep_node_name="node3"
wsrep_node_address="192.168.79.47"
# this is only for embedded server

以上操作三个节点都需要完成，配置文件中指定集群中节点地址和当前主机node名称和ip地址

[root@localhost galera-26.4.2]# /etc/init.d/mysql start --wsrep-new-cluster
Starting MariaDB.190626 14:24:27 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'.
190626 14:24:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
... SUCCESS! 
#在其中一个节点执行如上操作，对集群进行初始化
#而后正常启动其他节点
[root@localhost ~]# service mysql start
Starting MariaDB.190626 06:25:17 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'.
190626 06:25:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
...SST in progress, setting sleep higher. SUCCESS! 
MariaDB [hellodb]> SHOW STATUS LIKE 'wsrep_cluster_size';       #查询节点组件，之后在三个节点分别进行写测试即可
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)