MySQL 服务（下）

接上文：https://blog.csdn.net/lyxpeer2016/article/details/108279317

用binlog恢复数据

MySQL的binlog是数据恢复，主从同步的关键日志；

binlog相关配置：
binlog_format 日志格式
log-bin 日志文件
expire_logs_days 过期清理时间
max_binlog_size 每个日志文件大小
binlog_cache_size 缓存大小
max_binlog_cache_size 最大缓存大小
binlog_do_db 指定要生成binlog的库
binlog_ignore_db 指定不要生成binlog的库

binlog_format 有3种模式：
STATEMENT 每一条会修改数据的sql语句会记录到binlog中；
ROW 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了；
MIXED 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式；

启用binlog之前清空表

mysql> truncate basic_info;
Query OK, 0 rows affected (0.02 sec)

启用binlog

[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# cat /etc/my.cnf
...略
server-id=11
log-bin=master-bin
binlog_format=MIXED
[root@node1 ~]# systemctl restart mysqld

确认是否开启

[root@node1 ~]# mysql -u root -p
Enter password: 
...略
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       154 |
+-------------------+-----------+
1 row in set (0.00 sec)

写入数据，修改数据，删除数据，清空表，后面恢复修改之前的数据

mysql> insert into basic_info(name,age) values('LiLei',18);
Query OK, 1 row affected (0.01 sec)

mysql> insert into basic_info(name,sex,age) values('HanMM','female',17);
Query OK, 1 row affected (0.01 sec)

mysql> insert into basic_info(name,age,hobby) values('Jhon',17,'basketball');
Query OK, 1 row affected (0.01 sec)

mysql> insert into basic_info(name,sex,age,profession,hobby) values('Sarah','female',19,'student','wrestle');
Query OK, 1 row affected (0.00 sec)

mysql> insert into basic_info(name,age,address) values('Ethan',16,'Woodlands');
Query OK, 1 row affected (0.00 sec)

mysql> update basic_info set profession='student',address='Red Hill' where name='Jhon';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from basic_info where age>18;
Query OK, 1 row affected (0.01 sec)

mysql> truncate basic_info;
Query OK, 0 rows affected (0.12 sec)

查看binlog日志

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |      2835 |
+-------------------+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     2835 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'master-bin.000001';
+-------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------+
| Log_name          | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                   |
+-------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------+
...略
| master-bin.000001 |  340 | Query          |        11 |         475 | use `customers`; insert into basic_info(name,age) values('LiLei',18)
...略
| master-bin.000001 | 1829 | Query          |        11 |        1984 | use `customers`; insert into basic_info(name,age,address) values('Ethan',16,'Woodlands')                               |
| master-bin.000001 | 1984 | Xid            |        11 |        2015 | COMMIT /* xid=17 */                                                                                                    |
...略

根据position值来恢复数据

[root@node1 ~]# mysqlbinlog --start-position=340 --stop-position=1984 --database=customers /var/lib/mysql/master-bin.000001 | mysql -u root -p customers
Enter password:

查看数据

[root@node1 ~]# mysql -u root -p
Enter password: 
...略
mysql> select * from customers.basic_info;
+-----+-------+--------+-----+------------+------------+---------+
| uid | name  | sex    | age | profession | hobby      | address |
+-----+-------+--------+-----+------------+------------+---------+
|   1 | LiLei | male   |  18 | NULL       | NULL       | NULL    |
|   2 | HanMM | female |  17 | NULL       | NULL       | NULL    |
|   3 | Jhon  | male   |  17 | NULL       | basketball | NULL    |
|   4 | Sarah | female |  19 | student    | wrestle    | NULL    |
+-----+-------+--------+-----+------------+------------+---------+
4 rows in set (0.00 sec)

主从数据库

从库会自动从主库同步数据，使MySQL高可用，数据更不易丢失；
这里用node1作为MySQL主库，node2和node3作为从库；
node1 配置不变，开启binlog即可；
node2 安装配置

[root@node2 ~]# yum install -y mysql-community-server
...略
[root@node2 ~]# systemctl start mysqld
[root@node2 ~]# systemctl enable mysqld
[root@node2 ~]# grep password /var/log/mysqld.log 
2020-07-04T00:09:06.627041Z 1 [Note] A temporary password is generated for root@localhost: 7iEtGlgrq3=4
[root@node2 ~]# mysql_secure_installation
...略
[root@node2 ~]# vim /etc/my.cnf
[root@node2 ~]# cat /etc/my.cnf
...略
server-id=12
log_slave_updates=1
relay_log=slave-relay-bin
relay_log_recovery=1
read_only=1
[root@node2 ~]# systemctl restart mysqld

node3 跟 node2 一样安装配置， 除了server-id 改为13

node1 主库设置

[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> grant replication slave on *.* to 'replicer'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      895 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

node2 和 node3 从库设置

[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> change master to master_host='10.0.0.11',master_port=3306,master_user='replicer',master_password='Replica111!',master_log_file='master-bin.000002',master_log_pos=895;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.11
                  Master_User: replicer
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 895
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes  # slave 状态
            Slave_SQL_Running: Yes  # slave 状态
              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: 895
              Relay_Log_Space: 528
              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  # slave 状态
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: 11
                  Master_UUID: a739b13a-bc42-11ea-92fb-080027e88dbb
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

可以通过上面表示的几个状态判定从库状态

主库创建数据库，用于保存网站状态数据

[root@node1 ~]# mysql -u root -p
Enter password: 
...略
mysql> create database websites default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use websites;
Database changed
mysql> create table site_status( wid int not null auto_increment primary key, name char(30) not null, address char(50) not null, acquired_time datetime not null, status char(10) not null)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)

写入数据

mysql> use websites;
Database changed
mysql> insert into site_status(name,address,acquired_time,status) values("阿里巴巴开源镜像站","https://developer.aliyun.com/mirror/","2020-07-04 10:19:57","online");
Query OK, 1 row affected (0.01 sec)

mysql> insert into site_status(name,address,acquired_time,status) values("网易开源镜像站","http://mirrors.163.com/","2020-07-04 10:19:58","online");
Query OK, 1 row affected (0.01 sec)

mysql> insert into site_status(name,address,acquired_time,status) values("清华大学开源软件镜像站","https://mirrors.tuna.tsinghua.edu.cn/","2020-07-04 10:20:25","online");
Query OK, 1 row affected (0.01 sec)

mysql> select * from site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name                              | address                               | acquired_time       | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
|   1 | 阿里巴巴开源镜像站                | https://developer.aliyun.com/mirror/  | 2020-07-04 10:19:57 | online |
|   2 | 网易开源镜像站                    | http://mirrors.163.com/               | 2020-07-04 10:19:58 | online |
|   3 | 清华大学开源软件镜像站            | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)

查看是否同步到从库

[root@node3 ~]# mysql -u root -p
Enter password:
...略
mysql> select * from websites.site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name                              | address                               | acquired_time       | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
|   1 | 阿里巴巴开源镜像站                | https://developer.aliyun.com/mirror/  | 2020-07-04 10:19:57 | online |
|   2 | 网易开源镜像站                    | http://mirrors.163.com/               | 2020-07-04 10:19:58 | online |
|   3 | 清华大学开源软件镜像站            | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)

成功同步数据。

主从切换

模拟主库故障，将从库升级为主库；
关闭主库

[root@node1 ~]# systemctl stop mysqld

从库(node2和node3)关闭io线程

[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)

查看relay log是否被完全读取并执行完毕

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect |  607 | Slave has read all relay log; waiting for more updates | NULL             |
|  4 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

对比两个从库的Master_log_pos值，值较大的作为新的主库

[root@node2 ~]# cat /var/lib/mysql/master.info
25
master-bin.000004
3538  # Master_log_pos
10.0.0.11
replicer
Replica111!
3306
60
...略

我这里值是一样的，将node2选为新的主库

将node2切换成主库

[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table  mysql.slave_master_info;
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'replicer2'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@node2 ~]# rm -f /var/lib/mysql/relay-log.info
[root@node2 ~]# vim /etc/my.cnf
[root@node2 ~]# cat /etc/my.cnf
...略
server-id=12
log-bin=new-master-bin
#log_slave_updates=1
#relay_log=slave-relay-bin
#relay_log_recovery=1
#read_only=1
[root@node2 ~]# systemctl restart mysqld
[root@node2 ~]# mysql -uroot -p
Enter password:
...略
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| new-master-bin.000001 |      601 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

修改node3从库的主库信息

[root@node3 ~]# mysql -u root -p
Enter password:
...略
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='10.0.0.12',master_port=3306,master_user='replicer2',master_password='Replica111!',master_log_file='new-master-bin.000001',master_log_pos=601;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.12
                  Master_User: replicer2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: new-master-bin.000001
          Read_Master_Log_Pos: 601
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Master_Log_File: new-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: 601
              Relay_Log_Space: 532
              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: 12
                  Master_UUID: 933d7e7d-bd8a-11ea-bd03-080027b7d17f
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

原本的主库node1此时将其修改为从库

[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# cat /etc/my.cnf
..略
server-id=11
#log-bin=master-bin
#binlog_format=MIXED
log_slave_updates=1
relay_log=slave-relay-bin
relay_log_recovery=1
read_only=1
[root@node1 ~]# systemctl start mysqld
[root@node1 ~]# mysql -u root -p
Enter password:
..略
mysql> change master to master_host='10.0.0.12',master_port=3306,master_user='replicer2',master_password='Replica111!',master_log_file='new-master-bin.000001',master_log_pos=601;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.12
                  Master_User: replicer2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: new-master-bin.000001
          Read_Master_Log_Pos: 601
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Master_Log_File: new-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: 601
              Relay_Log_Space: 532
              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: 12
                  Master_UUID: 933d7e7d-bd8a-11ea-bd03-080027b7d17f
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

主库写入数据测试

[root@node2 ~]# mysql -uroot -p
Enter password:
...略
mysql> use websites;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into site_status(name,address,acquired_time,status) values("中国科学技术大学开源镜像站","https://mirrors.ustc.edu.cn/","2020-07-04 10:25:34","online");
Query OK, 1 row affected (0.01 sec)

查看是否同步

[root@node1 ~]# mysql -u root -p
Enter password: 
...略
mysql> select * from websites.site_status;
+-----+-----------------------------------------+---------------------------------------+---------------------+--------+
| wid | name                                    | address                               | acquired_time       | status |
+-----+-----------------------------------------+---------------------------------------+---------------------+--------+
|   1 | 阿里巴巴开源镜像站                      | https://developer.aliyun.com/mirror/  | 2020-07-04 10:19:57 | online |
|   2 | 网易开源镜像站                          | http://mirrors.163.com/               | 2020-07-04 10:19:58 | online |
|   3 | 清华大学开源软件镜像站                  | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
|   4 | 中国科学技术大学开源镜像站              | https://mirrors.ustc.edu.cn/          | 2020-07-04 10:25:34 | online |
+-----+-----------------------------------------+---------------------------------------+---------------------+--------+
4 rows in set (0.00 sec)

读写分离

使用工具MaxScale实现读写分离，把读操作分散到从服务器中，并且对多个从服务器能实现负载均衡；

下载地址:https://downloads.mariadb.com/MaxScale/

[root@node2 ~]# wget https://downloads.mariadb.com/MaxScale/2.4.9/centos/7/x86_64/maxscale-2.4.9-1.centos.7.x86_64.rpm

在 node2 为主库，node1,node3 为从库的基础上实验；

创建用于监控和路由的MySQL用户

[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> grant replication slave,replication client on *.* to 'maxmon'@'10.0.0.%' identified by 'Maxscale111!';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant select on mysql.* to 'maxrou'@'10.0.0.%' identified by 'Maxscale111!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

安装配置MaxScale

[root@node2 ~]# rpm -ivh maxscale-2.4.9-1.centos.7.x86_64.rpm 
warning: maxscale-2.4.9-1.centos.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 28c12247: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:maxscale-2.4.9-1                 ################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service.
[root@node2 ~]# cp /etc/maxscale.cnf /etc/maxscale.cnf.bak
[root@node2 ~]# vim /etc/maxscale.cnf
[root@node2 ~]# cat /etc/maxscale.cnf
[maxscale]
threads=auto

[server1]
type=server
address=10.0.0.12
port=3306
protocol=MySQLBackend

[server2]
type=server
address=10.0.0.11
port=3306
protocol=MySQLBackend

[server3]
type=server
address=10.0.0.13
port=3306
protocol=MySQLBackend

[MySQL-Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxmon
password=Maxscale111!
monitor_interval=2000
detect_replication_lag=1

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxrou
password=Maxscale111!
max_slave_replication_lag=60

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MySQLClient
port=4006

[CLI]
type=service
router=cli

[CLI-Listener]
type=listener
service=CLI
protocol=maxscaled
port=6603
[root@node2 ~]# systemctl start maxscale
[root@node2 ~]# systemctl enable maxscale

查看maxscale集群状态

[root@node2 ~]# maxadmin --user=admin --password=mariadb
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 10.0.0.12       |  3306 |           0 | Master, Running
server2            | 10.0.0.11       |  3306 |           0 | Slave, Running
server3            | 10.0.0.13       |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> show dbusers Read-Write-Service
User names (Read-Write-Listener): mysql.session@localhost mysql.sys@localhost webmonitor@10.0.0.% replicer2@10.0.0.% maxmon@10.0.0.% maxrou@10.0.0.% mysql.session@localhost maxrou@10.0.0.%
MaxScale> exit

测试

[root@node1 ~]# mysql -u webmonitor -h 10.0.0.12 -P 4006 -p
Enter password:
...略
mysql> select @@hostname;
+--------------------+
| @@hostname         |
+--------------------+
| node1.newbie.local |
+--------------------+
1 row in set (0.01 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname;
+--------------------+
| @@hostname         |
+--------------------+
| node2.newbie.local |
+--------------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname;
+--------------------+
| @@hostname         |
+--------------------+
| node1.newbie.local |
+--------------------+
1 row in set (0.00 sec)

组复制

MySQL Group Replication 是一个高可用与高扩展的解决方案，提供了高可用、高扩展、高可靠的MySQL集群服务；

特点：
高一致性，基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
高容错性，只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制；
高扩展性，节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
高灵活性，有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。

恢复初始快照；
node1 node2 node3重新安装，及初始化MySQL；

以单主模式为例，部署MGR

node1 上操作：
配置MySQL服务

[root@node1 ~]# uuidgen 
ac2b81bb-09aa-45b3-87b6-65541ed5da4b
[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# cat /etc/my.cnf
...略
server_id=11
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW  # MGR的binlog只能是ROW格式

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name='ac2b81bb-09aa-45b3-87b6-65541ed5da4b'
loose-group_replication_start_on_boot=off
loose-group_replication_local_address='10.0.0.11:33061'
loose-group_replication_group_seeds='10.0.0.11:33061,10.0.0.12:33062,10.0.0.13:33063'
loose-group_replication_bootstrap_group=off
[root@node1 ~]# systemctl restart mysqld

配置MySQL

[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'replicer'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='replicer',master_password='Replica111!' for channel 'group_replication_recovery'; 
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
...略
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)

mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.09 sec)

mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 675c9ea7-bf4d-11ea-b1db-080027c01ae1 | node1.newbie.local |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
1 row in set (0.00 sec)

node2 上操作：
配置MySQL服务

[root@node2 ~]# vim /etc/my.cnf
[root@node2 ~]# cat /etc/my.cnf
...略
server_id=12
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name='ac2b81bb-09aa-45b3-87b6-65541ed5da4b'
loose-group_replication_start_on_boot=off
loose-group_replication_local_address='10.0.0.12:33062'
loose-group_replication_group_seeds='10.0.0.11:33061,10.0.0.12:33062,10.0.0.13:33063'
loose-group_replication_bootstrap_group=off
[root@node2 ~]# systemctl restart mysqld

配置MySQL

[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'replicer'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user='replicer',master_password='Replica111!' for channel 'group_replication_recovery'; 
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (5.71 sec)

在node1上查看mgr状态

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 675c9ea7-bf4d-11ea-b1db-080027c01ae1 | node1.newbie.local |        3306 | ONLINE       |
| group_replication_applier | 78028e34-bf50-11ea-9a94-0800275623eb | node2.newbie.local |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
2 rows in set (0.00 sec)

node3 操作跟node2差不多，
不一样的就是server_id和loose-group_replication_local_address两个配置的值；
完成node3的配置之后，再在node1上查看mgr状态

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 675c9ea7-bf4d-11ea-b1db-080027c01ae1 | node1.newbie.local |        3306 | ONLINE       |
| group_replication_applier | 78028e34-bf50-11ea-9a94-0800275623eb | node2.newbie.local |        3306 | ONLINE       |
| group_replication_applier | 7a96e212-bf50-11ea-9b0f-08002787181c | node3.newbie.local |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
3 rows in set (0.00 sec)

写入数据
注意单主模式下，只有主节点可以写入数据

[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> create database websites default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use websites;
Database changed
mysql> create table site_status( wid int not null auto_increment primary key, name char(30) not null, address char(50) not null, acquired_time datetime not null, status char(10) not null)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into site_status(name,address,acquired_time,status) values("阿里巴巴开源镜像站","https://developer.aliyun.com/mirror/","2020-07-04 10:19:57","online");
Query OK, 1 row affected (0.01 sec)

mysql> insert into site_status(name,address,acquired_time,status) values("网易开源镜像站","http://mirrors.163.com/","2020-07-04 10:19:58","online");
Query OK, 1 row affected (0.01 sec)

mysql> insert into site_status(name,address,acquired_time,status) values("清华大学开源软件镜像站","https://mirrors.tuna.tsinghua.edu.cn/","2020-07-04 10:20:25","online");
Query OK, 1 row affected (0.01 sec)

mysql> select * from site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name                              | address                               | acquired_time       | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
|   1 | 阿里巴巴开源镜像站                | https://developer.aliyun.com/mirror/  | 2020-07-04 10:19:57 | online |
|   2 | 网易开源镜像站                    | http://mirrors.163.com/               | 2020-07-04 10:19:58 | online |
|   3 | 清华大学开源软件镜像站            | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)

node2 和 node3 查看数据是否同步

mysql> select * from websites.site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name                              | address                               | acquired_time       | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
|   1 | 阿里巴巴开源镜像站                | https://developer.aliyun.com/mirror/  | 2020-07-04 10:19:57 | online |
|   2 | 网易开源镜像站                    | http://mirrors.163.com/               | 2020-07-04 10:19:58 | online |
|   3 | 清华大学开源软件镜像站            | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)

本文转载自公众号：开源Ops
本文源连接：https://mp.weixin.qq.com/s/WKGhX0S136DA26IFlCMERw