MySQL 运维篇 -- 主从集群搭建

L小Ray想有腮

已于 2022-02-09 20:51:13 修改

阅读量235

点赞数

分类专栏： MySQL 文章标签： mysql 运维数据库

于 2020-09-30 17:05:07 首次发布

本文链接：https://blog.csdn.net/weixin_42480750/article/details/108814742

版权

MySQL 专栏收录该内容

28 篇文章 10 订阅

订阅专栏

文章目录

主从复制
- 主从复制原理
- 主从故障处理
延时从库
主从GTID复制
主从GR复制*****
- 搭建组复制
- 观察组复制

主从复制

主从同步过程
（1）主丛复制基于binlog来实现的
（2）主库发生新的操作，都会记录binlog
（3）从库取得主库的binlog进行回放
（4）主从复制是异步的

准备主从的初始MySQL环境（YUM安装）

[root@master ~]# hostname
master
[root@master ~]# hostname -I
13.13.6.6 
[root@master ~]# tail -2 /etc/hosts
13.13.6.6 master
13.13.7.7 slave
[root@master ~]# mysql -uroot -p -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@master ~]#

[root@slave ~]# hostname
slave
[root@slave ~]# hostname -I
13.13.7.7 
[root@slave ~]# tail -2 /etc/hosts
13.13.6.6 master
13.13.7.7 slave
[root@slave ~]# mysql -uroot -p -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@slave ~]#

修改主从的server_id

[root@master ~]# vi /etc/my.cnf.d/mysql-server.cnf 
[root@master ~]# tail -7 /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=6

[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
[root@master ~]#

[root@slave ~]# vi /etc/my.cnf.d/mysql-server.cnf 
[root@slave ~]# tail -7 /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=7

[root@slave ~]# systemctl restart mysqld
[root@slave ~]# mysql -uroot -p -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@slave ~]#

确认mater的binlog已开启

[root@master ~]# mysql -uroot -p -e "show variables like '%log_bin%'"
Enter password: 
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
[root@master ~]#

模拟主库原始数据

模拟库SQL脚本参考：https://blog.csdn.net/horses/article/details/106795844

mysql> select * from world.city limit 3;
+----+----------+-------------+----------+------------+
| ID | Name     | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
|  1 | Kabul    | AFG         | Kabol    |    1780000 |
|  2 | Qandahar | AFG         | Qandahar |     237500 |
|  3 | Herat    | AFG         | Herat    |     186800 |
+----+----------+-------------+----------+------------+
3 rows in set (0.00 sec)

mysql> select * from world.country limit 1\G
*************************** 1. row ***************************
          Code: ABW
          Name: Aruba
     Continent: North America
        Region: Caribbean
   SurfaceArea: 193.00
     IndepYear: NULL
    Population: 103000
LifeExpectancy: 78.4
           GNP: 828.00
        GNPOld: 793.00
     LocalName: Aruba
GovernmentForm: Nonmetropolitan Territory of The Netherlands
   HeadOfState: Beatrix
       Capital: 129
         Code2: AW
1 row in set (0.00 sec)

mysql> select * from world.countrylanguage limit 3;
+-------------+------------+------------+------------+
| CountryCode | Language   | IsOfficial | Percentage |
+-------------+------------+------------+------------+
| ABW         | Dutch      | T          |        5.3 |
| ABW         | English    | F          |        9.5 |
| ABW         | Papiamento | F          |       76.7 |
+-------------+------------+------------+------------+
3 rows in set (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql>

模拟主库备份（记录下备份时binlog位置）

[root@master ~]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full_`date +%F`.sql
Enter password: 
[root@master ~]# head -25 /tmp/full_2020-09-27.sql | tail -3

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=736697;

[root@master ~]#

模拟主库数据变化

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

mysql> create database d1;
Query OK, 1 row affected (0.01 sec)

mysql> use d1;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

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

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> use d2;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql>

恢复主库的最近一次备份数据到从库

[root@slave ~]# rsync -avz master:/tmp/full_2020-09-27.sql /tmp
root@master's password: 
receiving incremental file list
full_2020-09-27.sql

sent 43 bytes  received 317,866 bytes  90,831.14 bytes/sec
total size is 1,315,151  speedup is 4.14
[root@slave ~]# mysql -uroot -p
....
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/full_2020-09-27.sql
....
Query OK, 0 rows affected (0.00 sec)
....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from world.city limit 3;
+----+----------+-------------+----------+------------+
| ID | Name     | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
|  1 | Kabul    | AFG         | Kabol    |    1780000 |
|  2 | Qandahar | AFG         | Qandahar |     237500 |
|  3 | Herat    | AFG         | Herat    |     186800 |
+----+----------+-------------+----------+------------+
3 rows in set (0.00 sec)

mysql>

主库创建replication用户

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> create user daemon_repl@'13.13.%' identified by 'abcd1234..';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to daemon_repl@'13.13.%';
Query OK, 0 rows affected (0.00 sec)

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

mysql>

主库开放防火墙

[root@master ~]# firewall-cmd --zone=public --permanent --add-service=mysql
success
[root@master ~]# firewall-cmd --reload
success
[root@master ~]#

从库连接主库

mysql> help change master to;
....
CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;
....
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='master',
    ->   MASTER_USER='daemon_repl',
    ->   MASTER_PASSWORD='abcd1234..',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='binlog.000002',
    ->   MASTER_LOG_POS=736697,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.01 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: master
                  Master_User: daemon_repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 799
               Relay_Log_File: slave-relay-bin.000004
                Relay_Log_Pos: 1008
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
mysql> select * from d1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from d2.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

主从复制原理

库	类别	名称	说明
主库	文件	binlog.xxxxxx	The binary log contains “events” that describe database changes such as table creation operations or changes to table data.
从库	文件	slave-relay-bin.xxxxxx	The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files. The default location for relay log files is the data directory.
从库	表	mysql.slave_master_info	The replica’s connection metadata repository contains information that the replication I/O thread needs to connect to the replication source server and retrieve transactions from the source’s binary log.
从库	表	mysql.slave_relay_log_info	The replica’s applier metadata repository contains information that the replication SQL thread needs to read and apply transactions from the replica’s relay log.
主库	线程	Binary log dump thread	The source creates a thread to send the binary log contents to a replica when the replica connects.
从库	线程	Replication I/O thread	The replication I/O thread reads the updates that the source’s Binlog Dump thread sends (see previous item) and copies them to local files that comprise the replica’s relay log.
从库	线程	Replication SQL thread	The replica creates an SQL thread to read the relay log that is written by the replication I/O thread and execute the transactions contained in it.

bin log：https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

relay log：https://dev.mysql.com/doc/refman/8.0/en/replica-logs-relaylog.html

表：https://dev.mysql.com/doc/refman/8.0/en/replica-logs-status.html

线程：https://dev.mysql.com/doc/refman/8.0/en/replication-implementation-details.html

[root@slave ~]# ll /var/lib/mysql | grep -E "binlog|slave"
-rw-r-----. 1 mysql mysql      818 Sep 27 16:00 binlog.000001
-rw-r-----. 1 mysql mysql     1745 Sep 27 17:25 binlog.000002
-rw-r-----. 1 mysql mysql       32 Sep 27 16:00 binlog.index
-rw-r-----. 1 mysql mysql      253 Sep 27 17:18 slave-relay-bin.000003
-rw-r-----. 1 mysql mysql     1275 Sep 27 17:25 slave-relay-bin.000004
-rw-r-----. 1 mysql mysql       50 Sep 27 17:18 slave-relay-bin.index
[root@slave ~]#

mysql> select * from mysql.slave_master_info\G
*************************** 1. row ***************************
              Number_of_lines: 31
              Master_log_name: binlog.000003
               Master_log_pos: 156
                         Host: master
                    User_name: daemon_repl
                User_password: abcd1234..
                         Port: 3306
                Connect_retry: 10
                  Enabled_ssl: 0
                       Ssl_ca: 
                   Ssl_capath: 
                     Ssl_cert: 
                   Ssl_cipher: 
                      Ssl_key: 
       Ssl_verify_server_cert: 0
                    Heartbeat: 30
                         Bind: 
           Ignored_server_ids: 0
                         Uuid: 1845a09a-0094-11eb-830a-000c29ac6be7
                  Retry_count: 86400
                      Ssl_crl: 
                  Ssl_crlpath: 
        Enabled_auto_position: 0
                 Channel_name: 
                  Tls_version: 
              Public_key_path: 
               Get_public_key: 0
            Network_namespace: 
 Master_compression_algorithm: uncompressed
Master_zstd_compression_level: 3
             Tls_ciphersuites: NULL
1 row in set (0.00 sec)

mysql>

mysql> select * from mysql.slave_relay_log_info\G
*************************** 1. row ***************************
                Number_of_lines: 12
                 Relay_log_name: ./slave-relay-bin.000004
                  Relay_log_pos: 1275
                Master_log_name: binlog.000003
                 Master_log_pos: 1066
                      Sql_delay: 0
              Number_of_workers: 0
                             Id: 1
                   Channel_name: 
      Privilege_checks_username: NULL
      Privilege_checks_hostname: NULL
             Require_row_format: 0
Require_table_primary_key_check: STREAM
1 row in set (0.00 sec)

mysql>

在这里插入图片描述

第一次主从同步

从库执行change master to 命令时会将主库连接信息记录到mysql.slave_master_info表中
从库执行start slave命令时，会立即开启IO_T和SQL_T线程
从库IO_T线程读取mysql.slave_master_info表，请求和主库连接
主库专门提供一个DUMP_T线程，负责与从库IO_T线程交互
IO_T根据mysql.slave_master_info表中记录的binlog的位置信息，请求主库新的binlog
主库通过DUMP_T将最新的binlog通过网络传输给从库IO_T
IO_T接受到新的binlog：
（1）将新的binlog存储到TCP/IP缓存中
（2）返回ACK给主库
（3）更新mysql.slave_master_info表中的binlog位置信息
（4）将TCP/IP缓存中的数据转储到磁盘的relaylog中
SQL_T读取mysql.slave_relay_log_info表中的信息，获取到上次relaylog的位置信息
SQL_T回放relaylog中新的事件，并更新mysql.slave_relay_log_info表中的位置信息

主从同步连接建立成功后：每当主库发生新的事件，立即通过DUMP_T通知IO_T

主从故障处理

情形一：Connection（网络、连接信息错误或变更、防火墙、连接数上限）

从库

mysql> show slave status\G
....
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 156
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
mysql>

主库修改连接用户密码

mysql> alter user daemon_repl@'13.13.%' identified by '..4321dcba';
Query OK, 0 rows affected (0.00 sec)

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

mysql>

从库连接主库失败

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

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

mysql> show slave status\G
....
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
....
                Last_IO_Error: error connecting to master 'daemon_repl@master:3306' - retry-time: 10 retries: 1 message: Access denied for user 'daemon_repl'@'slave' (using password: YES)
....
mysql>

恢复从库

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

mysql> change master to MASTER_PASSWORD='..4321dcba';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status\G
....
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 618
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
mysql>

情形二：请求binlog（二进制日志重置、损坏）

主库

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

从库

mysql> show slave status\G
....
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 156
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
mysql>

主库执行reset msater

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

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

mysql>

从库请求binlog失败

mysql> show slave status\G
....
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 156
....
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
....
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'binlog.000003' at 1066, the last event read from './binlog.000004' at 156, the last byte read from './binlog.000004' at 156.'
....
mysql>

恢复从库

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

mysql> change master to MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=156;
Query OK, 0 rows affected (0.01 sec)

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

mysql> show slave status\G
....
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 156
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
mysql>

情形三：SQL线程执行失败

从库误操作

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

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

mysql>

主库正常操作

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

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

mysql>

从库SQL线程错误

mysql> show slave status\G
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
....
               Last_SQL_Errno: 1007
               Last_SQL_Error: Error 'Can't create database 'mytest'; database exists' on query. Default database: 'mytest'. Query: 'create database mytest'
....
mysql>

解决办法
（1）撤回从库操作：drop database mytest;
（2）跳过错误：set global sql_slave_skip_counter=1;

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

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

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

mysql> show slave status\G
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
mysql>

避免SQL线程故障
（1）从库只读

mysql> show variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.04 sec)

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

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

mysql> create database anothertest;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
mysql>

（2）使用读写分离中间件：atlas、mycat、ProxtSQL、MaxScale

延时从库

查看延时从库

mysql> show slave status\G
....
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
....
mysql>

配置延时从库(秒)

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

mysql> change master to MASTER_DELAY=900;
Query OK, 0 rows affected (0.00 sec)

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

mysql> show slave status\G
....
                    SQL_Delay: 900
          SQL_Remaining_Delay: NULL
....
mysql>

延时从库故障处理逻辑

在这里插入图片描述

情形描述：
（1）时间11:37发生了误操作“Bad Ops”，直到11:45该操作产生的影响被发觉“Alert”；
（2）因设置了延时从库“master_relay”为15分钟，所有在11:30到11:45发生的事件都处于“Holding”状态；
问：从库应该如何挽回主库的误操作?

立即停止从库的SQL线程：stop slave sql_thread;
查找主库误操作发生位置的前一事件开始位置（741848），在从库执行：STOP SLAVE; START SLAVE UNTIL MASTER_LOG_FILE = 'binlog.000001', MASTER_LOG_POS=741848;以回放误操作前的所有事件。
查找主库误操作发生的结束位置（742032），在从库执行：CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=742032; START SLAVE;以跳过误操作；
在从库导出被误操作的数据还原会主库。

故障演练

配置延时从库

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

mysql> change master to master_delay=900;
Query OK, 0 rows affected (0.01 sec)

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

mysql> show slave status \G
....
                    SQL_Delay: 900
          SQL_Remaining_Delay: NULL
....
mysql>

模拟主库数据变化

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

mysql> use db1;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

模拟主库误操作

mysql> drop database world;
Query OK, 3 rows affected (0.01 sec)

mysql>

模拟主库误操作后的数据变化

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

mysql> use db2;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert t1 values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

模拟主库误操作后的影响被发现

mysql> select * from world.city;
ERROR 1049 (42000): Unknown database 'world'
mysql>

立即停止从库的SQL线程

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

mysql> show slave status \G
....
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 742682
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
....
          Exec_Master_Log_Pos: 741198
....
mysql>

分析误操作事件

误操作前一事件开始位置（741848）

误操作事件：| binlog.000001 | 741925 | Query | 6 | 742032 | drop database world|

误操作后一事件开始位置（742032）

mysql> show binlog events in 'binlog.000001' from 741198;
+---------------+--------+----------------+-----------+-------------+-----------------------------------------------------+
| Log_name      | Pos    | Event_type     | Server_id | End_log_pos | Info                                                |
+---------------+--------+----------------+-----------+-------------+-----------------------------------------------------+
| binlog.000001 | 741198 | Anonymous_Gtid |         6 |      741275 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binlog.000001 | 741275 | Query          |         6 |      741380 | create database db1 /* xid=10932 */                 |
| binlog.000001 | 741380 | Anonymous_Gtid |         6 |      741457 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binlog.000001 | 741457 | Query          |         6 |      741567 | use `db1`; create table t1 (id int) /* xid=10937 */ |
| binlog.000001 | 741567 | Anonymous_Gtid |         6 |      741646 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binlog.000001 | 741646 | Query          |         6 |      741720 | BEGIN                                               |
| binlog.000001 | 741720 | Table_map      |         6 |      741767 | table_id: 137 (db1.t1)                              |
| binlog.000001 | 741767 | Write_rows     |         6 |      741817 | table_id: 137 flags: STMT_END_F                     |
| binlog.000001 | 741817 | Xid            |         6 |      741848 | COMMIT /* xid=10938 */                              |
| binlog.000001 | 741848 | Anonymous_Gtid |         6 |      741925 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binlog.000001 | 741925 | Query          |         6 |      742032 | drop database world /* xid=10939 */                 |
| binlog.000001 | 742032 | Anonymous_Gtid |         6 |      742109 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binlog.000001 | 742109 | Query          |         6 |      742214 | create database db2 /* xid=10941 */                 |
| binlog.000001 | 742214 | Anonymous_Gtid |         6 |      742291 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binlog.000001 | 742291 | Query          |         6 |      742401 | use `db2`; create table t1 (id int) /* xid=10946 */ |
| binlog.000001 | 742401 | Anonymous_Gtid |         6 |      742480 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                |
| binlog.000001 | 742480 | Query          |         6 |      742554 | BEGIN                                               |
| binlog.000001 | 742554 | Table_map      |         6 |      742601 | table_id: 138 (db2.t1)                              |
| binlog.000001 | 742601 | Write_rows     |         6 |      742651 | table_id: 138 flags: STMT_END_F                     |
| binlog.000001 | 742651 | Xid            |         6 |      742682 | COMMIT /* xid=10947 */                              |
+---------------+--------+----------------+-----------+-------------+-----------------------------------------------------+
20 rows in set (0.00 sec)

mysql>

在从库上回放误操作前的事件（Util 741848）

由于master_realy的持续生效，所以不会立刻回放。可以在回放前修改该参数。

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

mysql> START SLAVE UNTIL MASTER_LOG_FILE = 'binlog.000001', MASTER_LOG_POS = 741848;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status \G
....
                    SQL_Delay: 900
          SQL_Remaining_Delay: 248
....
mysql> show slave status \G
....
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 742682
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
....
          Exec_Master_Log_Pos: 741848
....
              Until_Condition: Master
               Until_Log_File: binlog.000001
                Until_Log_Pos: 741848
....
                    SQL_Delay: 900
          SQL_Remaining_Delay: NULL
....
mysql>

使从库跳过主库的误操作事件（CHANGE TO 742032）

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

mysql> CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=742032;
Query OK, 0 rows affected (0.01 sec)

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

mysql> show slave status \G
....
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 742682
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
          Exec_Master_Log_Pos: 742682
....
                    SQL_Delay: 900
          SQL_Remaining_Delay: NULL
....
mysql>

检查误操作前、误操作、误操作后的数据完整性

mysql> select * from db1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)

mysql> select * from db2.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql>

从库导出误操作数据

[root@slave ~]# mysqldump -uroot -p -B world > /tmp/world.sql 
Enter password: 
[root@slave ~]# rsync -avz /tmp/world.sql root@master:/tmp
root@master's password: 
sending incremental file list
world.sql

sent 87,229 bytes  received 3,455 bytes  20,152.00 bytes/sec
total size is 244,296  speedup is 2.69
[root@slave ~]#

主库导入误操作数据（临时关闭二进制日志）

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

mysql> source /tmp/world.sql
....
Query OK, 0 rows affected (0.01 sec)
....
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

主从GTID复制

核心参数

参数	说明
gtid-mode=on	开启GTID
enforce-gtid-consistency	开启GTID
log-slave-updates	强制slave更新日志
MASTER_AUTO_POSITION=1	读取relaylog最后一个事务的GTID，对比主库新的binlog确认是否回放

GTID复制和普通复制的区别

在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
额外的功能参数：gtid-mode、enforce-gtid-consistency、log-slave-updates
CHANGE MASTER TO不一样的参数：去除MASTER_LOG_FILE、MASTER_LOG_POS添加MASTER_AUTO_POSITION
在复制过程中，从库不再依赖master.info中记录的binlog的位置信息，而是直接读取relaylog最后一个事务的GTID号

构建环境

主机环境
（1）三台主机的server_id和mysql_uuid必须不同
（2）开启GTID
（3）主库为历史库多了world数据库，而两个从库为新搭建的初始库

[root@master ~]# hostname
master
[root@master ~]# hostname -I
13.13.6.6 
[root@master ~]# tail -12 /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=6
autocommit=0
gtid-mode=on
enforce-gtid-consistency
log-slave-updates
[mysql]
prompt=master [\\d]>
[root@master ~]# cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=1845a09a-0094-11eb-830a-000c29ac6be7
[root@master ~]# mysql -uroot -p -e "show databases"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
[root@master ~]#

[root@slave ~]# hostname
slave
[root@slave ~]# hostname -I
13.13.7.7 
[root@slave ~]# tail -12 /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=7
autocommit=0
gtid-mode=on
enforce-gtid-consistency
log-slave-updates
[mysql]
prompt=slave [\\d]>
[root@slave ~]# cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=acc718b0-fb9a-4eea-83af-a487adbc7438
[root@slave ~]# mysql -uroot -p -e "show databases"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@slave ~]#

[root@slave2 ~]# hostname
slave2
[root@slave2 ~]# hostname -I
13.13.8.8 
[root@slave2 ~]# tail -12 /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=8
autocommit=0
gtid-mode=on
enforce-gtid-consistency
log-slave-updates
[mysql]
prompt=slave2 [\\d]>
[root@slave2 ~]# cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=783a76bc-0094-11eb-a216-000c29f89301
[root@slave2 ~]# mysql -uroot -p -e "show databases"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@slave2 ~]#

备份主库

[root@master ~]# mysqldump -uroot -p -A -E -R --triggers --single-transaction > /tmp/full.sql
Enter password: 
[root@master ~]#

模拟主库备份后数据变化

master [(none)]>create database d1;
Query OK, 1 row affected (0.00 sec)

master [(none)]>use d1;
Database changed
master [d1]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

master [d1]>insert t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

master [d1]>commit;
Query OK, 0 rows affected (0.00 sec)

master [d1]>

从库初始化主库数据

[root@slave ~]# rsync -az root@master:/tmp/full.sql /tmp
root@master's password: 
[root@slave ~]# mysql -uroot -p
....
slave [(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

slave [(none)]>source /tmp/full.sql;
....
Query OK, 0 rows affected (0.00 sec)
....
slave [world]>set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

slave [world]>

[root@slave2 ~]# rsync -az root@master:/tmp/full.sql /tmp
root@master's password: 
[root@slave2 ~]# mysql -uroot -p
....
slave2 [(none)]>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

slave2 [(none)]>source /tmp/full.sql
....
Query OK, 0 rows affected (0.00 sec)
....
slave2 [world]>set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

slave2 [world]>

构建主从

主库需拥有供从库连接的用户

master [(none)]>select user,host,Repl_slave_priv from mysql.user where user='daemon_repl';
+-------------+---------+-----------------+
| user        | host    | Repl_slave_priv |
+-------------+---------+-----------------+
| daemon_repl | 13.13.% | Y               |
+-------------+---------+-----------------+
1 row in set (0.00 sec)

master [(none)]>

slave [world]>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

slave [world]>reset slave all;
Query OK, 0 rows affected (0.00 sec)

slave [world]>CHANGE MASTER TO
    -> MASTER_HOST='master',
    -> MASTER_USER='daemon_repl',
    -> MASTER_PASSWORD='abcd1234..',
    -> MASTER_PORT=3306,
    -> MASTER_CONNECT_RETRY=10,
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

slave [world]>start slave;
Query OK, 0 rows affected (0.00 sec)

slave [world]>show slave status\G
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
           Retrieved_Gtid_Set: 1845a09a-0094-11eb-830a-000c29ac6be7:1-3
            Executed_Gtid_Set: 1845a09a-0094-11eb-830a-000c29ac6be7:1-3
                Auto_Position: 1
....
slave [world]>show databases;
+--------------------+
| Database           |
+--------------------+
| d1                 |
....
| world              |
+--------------------+
6 rows in set (0.00 sec)

slave [world]>

slave2 [world]>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

slave2 [world]>reset slave all;
Query OK, 0 rows affected (0.00 sec)

slave2 [world]>CHANGE MASTER TO
    -> MASTER_HOST='master',
    -> MASTER_USER='daemon_repl',
    -> MASTER_PASSWORD='abcd1234..',
    -> MASTER_PORT=3306,
    -> MASTER_CONNECT_RETRY=10,
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

slave2 [world]>start slave;
Query OK, 0 rows affected (0.00 sec)

slave2 [world]>show slave status\G
....
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....
           Retrieved_Gtid_Set: 1845a09a-0094-11eb-830a-000c29ac6be7:1-3
            Executed_Gtid_Set: 1845a09a-0094-11eb-830a-000c29ac6be7:1-3
                Auto_Position: 1
....
slave2 [world]>show databases;
+--------------------+
| Database           |
+--------------------+
| d1                 |
....
| world              |
+--------------------+
6 rows in set (0.00 sec)

slave2 [world]>

模拟主库GITD主从复制搭建完成后数据变化

master [(none)]>create database db2;
Query OK, 1 row affected (0.00 sec)

master [(none)]>use db2;
Database changed
master [db2]>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

master [db2]>insert t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

master [db2]>commit;
Query OK, 0 rows affected (0.00 sec)

master [db2]>select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-29 12:11:52 |
+---------------------+
1 row in set (0.00 sec)

master [db2]>

从库的表现有点匪夷所思
（1）show命令看不到库
（2）但库确实存在
（3）表看不到也不存在
（4）重新登录万事大吉

slave2 [world]>select now();
+---------------------+
| now()               |
+---------------------+
| 2020-09-29 12:13:39 |
+---------------------+
1 row in set (0.00 sec)

slave2 [world]>show databases;
+--------------------+
| Database           |
+--------------------+
| d1                 |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

slave2 [world]>select * from db2.t1;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
slave2 [world]>use db2;
Database changed
slave2 [db2]>select * from t1;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
slave2 [db2]>show tables;
Empty set (0.00 sec)

slave2 [db2]>quit
Bye
[root@slave2 ~]# mysql -uroot -p
....
slave2 [(none)]>select * from db2.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

slave2 [(none)]>

主从GR复制*****

搭建组复制

准备组复制服务器

三台实例：db01、db02、db03
三台实例需拥有不同的server_id和server_uuid
配置/etc/hosts文件
组复制组的uuid作为成员交流的标识
组复制需要用到额外的端口号 e.g. 33061

[root@db01 ~]# hostname
db01
[root@db01 ~]# hostname -I
13.13.6.6 
[root@db01 ~]# tail -3 /etc/hosts
13.13.6.6 db01
13.13.7.7 db02
13.13.8.8 db03
[root@db01 ~]# uuidgen
5fd8a2c9-85ae-4166-9d1f-bb76f6f80d79
[root@db01 ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

#
# Replication configuration parameters
#
server-id=6
gtid-mode=on
enforce-gtid-consistency
log-slave-updates

#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="5fd8a2c9-85ae-4166-9d1f-bb76f6f80d79"
group_replication_start_on_boot=off
group_replication_local_address= "db01:33061"
group_replication_group_seeds= "db01:33061,db02:33061,db03:33061"
group_replication_ip_whitelist="13.13.0.0/16"
group_replication_bootstrap_group= off

[mysql]
prompt=db01 [\\d]>
[root@db01 ~]# mysql --version
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)
[root@db01 ~]# cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=0e53c1ac-02f0-11eb-9e04-000c29886b73
[root@db01 ~]#

[root@db02 ~]# hostname
db02
[root@db02 ~]# hostname -I
13.13.7.7 
[root@db02 ~]# tail -3 /etc/hosts
13.13.6.6 db01
13.13.7.7 db02
13.13.8.8 db03
[root@db02 ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

#
# Replication configuration parameters
#
server-id=7
gtid-mode=on
enforce-gtid-consistency
log-slave-updates

#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="5fd8a2c9-85ae-4166-9d1f-bb76f6f80d79"
group_replication_start_on_boot=off
group_replication_local_address= "db02:33061"
group_replication_group_seeds= "db01:33061,db02:33061,db03:33061"
group_replication_ip_whitelist="13.13.0.0/16"
group_replication_bootstrap_group= off

[mysql]
prompt=db02 [\\d]>
[root@db02 ~]# mysql --version
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)
[root@db02 ~]# cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=0e50cc3e-02f0-11eb-8dda-000c29c2f2fa
[root@db02 ~]#

[root@db03 ~]# hostname
db03
[root@db03 ~]# hostname -I
13.13.8.8 
[root@db03 ~]# tail -3 /etc/hosts
13.13.6.6 db01
13.13.7.7 db02
13.13.8.8 db03
[root@db03 ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

#
# Replication configuration parameters
#
server-id=8
gtid-mode=on
enforce-gtid-consistency
log-slave-updates

#
# Group Replication configuration
#
plugin_load_add='group_replication.so'
group_replication_group_name="5fd8a2c9-85ae-4166-9d1f-bb76f6f80d79"
group_replication_start_on_boot=off
group_replication_local_address= "db03:33061"
group_replication_group_seeds= "db01:33061,db02:33061,db03:33061"
group_replication_ip_whitelist="13.13.0.0/16"
group_replication_bootstrap_group= off

[mysql]
prompt=db03 [\\d]>
[root@db03 ~]# mysql --version
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)
[root@db03 ~]# cat /var/lib/mysql/auto.cnf 
[auto]
server-uuid=0e566402-02f0-11eb-8201-000c29670f95
[root@db03 ~]#

开启防火墙

[root@db01 ~]# firewall-cmd --permanent --add-service=mysql
success
[root@db01 ~]# firewall-cmd --permanent --add-port=33061/tcp
success
[root@db01 ~]# firewall-cmd --reload
success
[root@db01 ~]#

[root@db02 ~]# firewall-cmd --permanent --add-service=mysql
success
[root@db02 ~]# firewall-cmd --permanent --add-port=33061/tcp
success
[root@db02 ~]# firewall-cmd --reload
success
[root@db02 ~]#

[root@db03 ~]# firewall-cmd --permanent --add-service=mysql
success
[root@db03 ~]# firewall-cmd --permanent --add-port=33061/tcp
success
[root@db03 ~]# firewall-cmd --reload
success
[root@db03 ~]#

创建组复制用户

db01 [(none)]>SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>CREATE USER rpl_user@'13.13.%' IDENTIFIED BY 'abcd1234..';
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>GRANT REPLICATION SLAVE ON *.* TO rpl_user@'13.13.%';
Query OK, 0 rows affected (0.01 sec)

db01 [(none)]>GRANT BACKUP_ADMIN ON *.* TO rpl_user@'13.13.%';
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>CHANGE MASTER TO 
    -> MASTER_USER='rpl_user', 
    -> MASTER_PASSWORD='abcd1234..'
    -> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

db01 [(none)]>

db02 [(none)]>SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

db02 [(none)]>CREATE USER rpl_user@'13.13.%' IDENTIFIED BY 'abcd1234..';
Query OK, 0 rows affected (0.00 sec)

db02 [(none)]>GRANT REPLICATION SLAVE ON *.* TO rpl_user@'13.13.%';
Query OK, 0 rows affected (0.00 sec)

db02 [(none)]>GRANT BACKUP_ADMIN ON *.* TO rpl_user@'13.13.%';
Query OK, 0 rows affected (0.00 sec)

db02 [(none)]>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

db02 [(none)]>SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

db03 [(none)]>CHANGE MASTER TO 
    -> MASTER_USER='rpl_user', 
    -> MASTER_PASSWORD='abcd1234..'
    -> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

db03 [(none)]>

db03 [(none)]>SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

db03 [(none)]>CREATE USER rpl_user@'13.13.%' IDENTIFIED BY 'abcd1234..';
Query OK, 0 rows affected (0.01 sec)

db03 [(none)]>GRANT REPLICATION SLAVE ON *.* TO rpl_user@'13.13.%';
Query OK, 0 rows affected (0.00 sec)

db03 [(none)]>GRANT BACKUP_ADMIN ON *.* TO rpl_user@'13.13.%';
Query OK, 0 rows affected (0.01 sec)

db03 [(none)]>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

db03 [(none)]>SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

db03 [(none)]>CHANGE MASTER TO 
    -> MASTER_USER='rpl_user', 
    -> MASTER_PASSWORD='abcd1234..'
    -> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

db03 [(none)]>

初始化组复制组

初始化组的工作在一台实例上做一次就可以了。

尝试初始化

db01 [(none)]>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
db01 [(none)]>

排错

[root@db01 ~]# tail /var/log/mysql/mysqld.log
....
2020-09-30T03:50:19.029900Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group. Local port: 33061'
2020-09-30T03:50:24.132217Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Unable to bind to INADDR_ANY:33061 (socket=57, errno=98)!'
2020-09-30T03:50:24.132271Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Unable to announce tcp port 33061. Port already in use?'
2020-09-30T03:50:24.132540Z 0 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error joining the group while waiting for the network layer to become ready.'
....

[root@db01 ~]# ss -tlunp | grep 33061
[root@db01 ~]# 
[root@db01 ~]# journalctl -xe
....
Sep 30 11:45:40 db01 setroubleshoot[3176]: SELinux is preventing mysqld from name_connect access on the tcp_socket port 33061. For complete SELinux messages run: sealert -l 6f63c9d0-ae98-48d1-
Sep 30 11:45:40 db01 platform-python[3176]: SELinux is preventing mysqld from name_connect access on the tcp_socket port 33061.
                                            
                                            *****  Plugin catchall_boolean (47.5 confidence) suggests   ******************
                                            
                                            If you want to allow nis to enabled
                                            Then you must tell SELinux about this by enabling the 'nis_enabled' boolean.
                                            
                                            Do
                                            setsebool -P nis_enabled 1
....
[root@db01 ~]# setsebool -P nis_enabled 1
[root@db01 ~]#

再次尝试

db01 [(none)]>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.07 sec)

db01 [(none)]>SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>

应用到其它实例

[root@db02 ~]# setsebool -P nis_enabled 1
[root@db02 ~]#

[root@db03 ~]# setsebool -P nis_enabled 1
[root@db03 ~]#

添加其它实例添加进组

db02 [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.56 sec)

db02 [(none)]>

db03 [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.71 sec)

db03 [(none)]>

配置成功后开启自启动参数

ON：mysqld重启时自动START GROUP_REPLICATION;

[root@db01 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[root@db01 ~]# cat /etc/my.cnf.d/mysql-server.cnf
....
group_replication_start_on_boot=on
....
[root@db01 ~]#

[root@db02 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[root@db02 ~]# cat /etc/my.cnf.d/mysql-server.cnf
....
group_replication_start_on_boot=on
....
[root@db02 ~]#

[root@db03 ~]# vi /etc/my.cnf.d/mysql-server.cnf
[root@db03 ~]# cat /etc/my.cnf.d/mysql-server.cnf
....
group_replication_start_on_boot=on
....
[root@db03 ~]#

观察组复制

从库只读

db02 [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0e50cc3e-02f0-11eb-8dda-000c29c2f2fa | db02        |        3306 | ONLINE       | SECONDARY   | 8.0.21         |
| group_replication_applier | 0e53c1ac-02f0-11eb-9e04-000c29886b73 | db01        |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | 0e566402-02f0-11eb-8201-000c29670f95 | db03        |        3306 | ONLINE       | SECONDARY   | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

db02 [(none)]>create database test;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
db02 [(none)]>

主库可写可读

db01 [(none)]>CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

db01 [(none)]>USE test;
Database changed
db01 [test]>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.02 sec)

db01 [test]>INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.04 sec)

db01 [test]>SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

db01 [test]>

主库的操作同步到从库

db02 [(none)]>SELECT * FROM test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)

db02 [(none)]>

主库宕机，从库升主

[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]#

db02 [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0e50cc3e-02f0-11eb-8dda-000c29c2f2fa | db02        |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | 0e566402-02f0-11eb-8201-000c29670f95 | db03        |        3306 | ONLINE       | SECONDARY   | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

db02 [(none)]>INSERT INTO test.t1 VALUES (2, 'Joshua');
Query OK, 1 row affected (0.01 sec)

db02 [(none)]>

老主库恢复变身为从

[root@db01 ~]# systemctl start mysqld
[root@db01 ~]# mysql -uroot -p
....
db01 [(none)]>SELECT * FROM test.t1;
+----+--------+
| c1 | c2     |
+----+--------+
|  1 | Luis   |
|  2 | Joshua |
+----+--------+
2 rows in set (0.00 sec)

db01 [(none)]>CREATE DATABASE d1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
db01 [(none)]>

组员团灭重新构建

db01 [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0e53c1ac-02f0-11eb-9e04-000c29886b73 | db01        |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

db01 [(none)]>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.08 sec)

db01 [(none)]>SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

db01 [(none)]>

db02 [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (3.20 sec)

db02 [(none)]>

db03 [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.61 sec)

db03 [(none)]>

db01 [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0e50cc3e-02f0-11eb-8dda-000c29c2f2fa | db02        |        3306 | ONLINE       | SECONDARY   | 8.0.21         |
| group_replication_applier | 0e53c1ac-02f0-11eb-9e04-000c29886b73 | db01        |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | 0e566402-02f0-11eb-8201-000c29670f95 | db03        |        3306 | ONLINE       | SECONDARY   | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

db01 [(none)]>

L小Ray想有腮

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 运维篇 -- 主从集群搭建

文章目录主从复制主从复制原理主从故障处理延时从库主从GTID复制主从GR复制*****搭建组复制观察组复制主从复制主从同步过程（1）主丛复制基于binlog来实现的（2）主库发生新的操作，都会记录binlog（3）从库取得主库的binlog进行回放（4）主从复制是异步的准备主从的初始MySQL环境（YUM安装）[root@master ~]# hostnamemaster[root@master ~]# hostname -I13.13.6.6 [root@master ~]
复制链接

扫一扫