设为首页 收藏本站

运维网

 找回密码
 立即注册
查看: 2270|回复: 0

[经验分享] Mysql组复制故障恢复测试

[复制链接]
6要terwq

尚未签到
发表于 2017-10-30 13:39:46 | 显示全部楼层 |阅读模式
在前面的两篇文章中，介绍了mysql组复制的特点及配置过程，本文演示mysql单组复制下的模拟故障测试。

一、组复制所有成员服务器宕机重启后的恢复
连接所有的mysql实例查询当前的组复制成员情况，状态都是OFFLINE，这种情况下如何恢复组复制？
1
2
3
4
5
6
7
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)



在第一台mysql实例上将group_replication_bootstrap_group设置为ON，然后开启组复制，完成后再设置group_replication_bootstrap_group参数为OFF
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.01 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.19 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 | 8b643791-6d30-11e7-986a-000c29d53b31 | vm1         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)




abeb43898ec0c8cc6bb63e908591fdd9.png-wh_500x0-wm_3-wmp_4-s_2398506611.png

第二台mysql实例上开启组复制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> select * from performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+-----------+-------------+-------------+--------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |
+---------------------------+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 35c331eb-6d3d-11e7-91e3-000c29e07281 | vm2         |        3306 | ONLINE       |
| group_replication_applier | 941bce76-6d40-11e7-b2fe-000c2909332c | vm3         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)




e93b94111e0f608c0f63ac1fbee5aebc.png-wh_500x0-wm_3-wmp_4-s_3401596668.png

在第三台mysql实例开启组复制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 35c331eb-6d3d-11e7-91e3-000c29e07281 | vm2         |        3306 | ONLINE       |
| group_replication_applier | 8b643791-6d30-11e7-986a-000c29d53b31 | vm1         |        3306 | ONLINE       |
| group_replication_applier | 941bce76-6d40-11e7-b2fe-000c2909332c | vm3         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)




e1b99721a7bf0fe42ae03e2a72e33a6e.png-wh_500x0-wm_3-wmp_4-s_140707973.png

一般来说，第一台启动group replication的为组复制中的master角色，我们可以使用如下的SQL进行确认
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    *
FROM
    PERFORMANCE_SCHEMA .replication_group_members
WHERE
    member_id = (
        SELECT
            variable_value
        FROM
            PERFORMANCE_SCHEMA .global_status
        WHERE
            VARIABLE_NAME = 'group_replication_primary_member'
    );



二、如果master宕机，组复制会如何？下面我们来测试一下
首先确认当前的master主机是VM2


a16ff5c7c37eb364490e3686271de5a8.png-wh_500x0-wm_3-wmp_4-s_2226477760.png

1
2
[iyunv@vm2 ~]# service mysqld stop
Shutting down MySQL............. SUCCESS!



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
[iyunv@vm2 ~]# tail -f /mydata/vm2.err
2017-10-27T15:42:19.999227Z 0 [Note] Giving 7 client threads a chance to die gracefully
2017-10-27T15:42:19.999375Z 0 [Note] Shutting down slave threads
2017-10-27T15:42:22.000341Z 0 [Note] Forcefully disconnecting 7 remaining clients
2017-10-27T15:42:22.000556Z 0 [Note] Plugin group_replication reported: 'Going to wait for view modification'
2017-10-27T15:42:22.002961Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-10-27T15:42:25.462993Z 0 [Note] Plugin group_replication reported: 'state 4345 action xa_terminate'
2017-10-27T15:42:25.463472Z 0 [Note] Plugin group_replication reported: 'new state x_start'
2017-10-27T15:42:25.463514Z 0 [Note] Plugin group_replication reported: 'state 4272 action xa_exit'
2017-10-27T15:42:25.463650Z 0 [Note] Plugin group_replication reported: 'Exiting xcom thread'
2017-10-27T15:42:25.463675Z 0 [Note] Plugin group_replication reported: 'new state x_start'
2017-10-27T15:42:30.555581Z 0 [Note] Plugin group_replication reported: 'auto_increment_increment is reset to 1'
2017-10-27T15:42:30.555639Z 0 [Note] Plugin group_replication reported: 'auto_increment_offset is reset to 1'
2017-10-27T15:42:30.556496Z 19 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed
2017-10-27T15:42:30.564277Z 16 [Note] Plugin group_replication reported: 'The group replication applier thread was killed'
2017-10-27T15:42:30.565453Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2017-10-27T15:42:30.613823Z 0 [Note] Binlog end
2017-10-27T15:42:30.619483Z 0 [Note] Shutting down plugin 'group_replication'
2017-10-27T15:42:30.619582Z 0 [Note] Plugin group_replication reported: 'All Group Replication server observers have been successfully unregistered'
2017-10-27T15:42:30.620753Z 0 [Note] Shutting down plugin 'ngram'
2017-10-27T15:42:30.620801Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2017-10-27T15:42:30.620820Z 0 [Note] Shutting down plugin 'ARCHIVE'
2017-10-27T15:42:30.620832Z 0 [Note] Shutting down plugin 'partition'
2017-10-27T15:42:30.620841Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2017-10-27T15:42:30.620853Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-10-27T15:42:30.620937Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-10-27T15:42:30.620946Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-10-27T15:42:30.620951Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-10-27T15:42:30.620957Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-10-27T15:42:30.620961Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-10-27T15:42:30.620966Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-10-27T15:42:30.620971Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-10-27T15:42:30.620976Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-10-27T15:42:30.620980Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-10-27T15:42:30.620985Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-10-27T15:42:30.620990Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-10-27T15:42:30.620994Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-10-27T15:42:30.620999Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-10-27T15:42:30.621003Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-10-27T15:42:30.621008Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2017-10-27T15:42:30.621013Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2017-10-27T15:42:30.621017Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-10-27T15:42:30.621022Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-10-27T15:42:30.621027Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-10-27T15:42:30.621032Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-10-27T15:42:30.621042Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-10-27T15:42:30.621051Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-10-27T15:42:30.621056Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-10-27T15:42:30.621061Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-10-27T15:42:30.621066Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2017-10-27T15:42:30.621071Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-10-27T15:42:30.621076Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-10-27T15:42:30.621081Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2017-10-27T15:42:30.621085Z 0 [Note] Shutting down plugin 'InnoDB'
2017-10-27T15:42:30.621309Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-10-27T15:42:30.622401Z 0 [Note] InnoDB: Starting shutdown...
2017-10-27T15:42:30.723215Z 0 [Note] InnoDB: Dumping buffer pool(s) to /mydata/ib_buffer_pool
2017-10-27T15:42:30.723542Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 171027 11:42:30
2017-10-27T15:42:32.472064Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2767811
2017-10-27T15:42:32.474350Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-10-27T15:42:32.474373Z 0 [Note] Shutting down plugin 'MEMORY'
2017-10-27T15:42:32.474385Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-10-27T15:42:32.474419Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2017-10-27T15:42:32.474431Z 0 [Note] Shutting down plugin 'MyISAM'
2017-10-27T15:42:32.474459Z 0 [Note] Shutting down plugin 'CSV'
2017-10-27T15:42:32.474471Z 0 [Note] Shutting down plugin 'sha256_password'
2017-10-27T15:42:32.474481Z 0 [Note] Shutting down plugin 'mysql_native_password'
2017-10-27T15:42:32.475429Z 0 [Note] Shutting down plugin 'binlog'
2017-10-27T15:42:32.477356Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete




vm1主机的日志
1
2
3
4
5
[iyunv@vm1 ~]# tail -f /mydata/vm1.err
2017-10-27T15:42:22.634326Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-10-27T15:42:23.095198Z 0 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'
2017-10-27T15:42:23.095627Z 0 [Note] Plugin group_replication reported: 'Unsetting super_read_only.'
2017-10-27T15:42:23.095987Z 5 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'



vm3主机的日志
1
2
3
4
5
[iyunv@vm3 ~]# tail -f /mydata/vm3.err
2017-10-27T15:42:22.235922Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-10-27T15:42:22.696811Z 0 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'
2017-10-27T15:42:22.697087Z 0 [Note] Plugin group_replication reported: 'Setting super_read_only.'
2017-10-27T15:42:22.697303Z 5 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'



可以看到这个时候VM1自动提升为主了，且写入的数据可以继续同步到vm3，vm3的角色为slave,不能写入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> use yang
Database changed
mysql> insert into t1 values (2,'two');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | one  |
|  2 | two  |
+----+------+
2 rows in set (0.00 sec)




e41e9f3c5e7655bfedc83cdc9984a93d.png-wh_500x0-wm_3-wmp_4-s_2352865671.png

1
2
3
4
5
6
7
8
9
10
11
mysql> select * from yang.t1;
+----+------+
| id | name |
+----+------+
|  1 | one  |
|  2 | two  |
+----+------+
2 rows in set (0.00 sec)

mysql> insert into yang.t1 values (3,'three');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement




5646f20dcae12337eada39c05b0f11a2.png-wh_500x0-wm_3-wmp_4-s_2495166489.png

如果此时vm1也跪了会如何呢？
1
2
[iyunv@vm1 ~]# service mysqld stop
Shutting down MySQL............. SUCCESS!



观察vm3的日志

1
2
3
4
5
6
7
8
9
[iyunv@vm3 ~]# tail -f /mydata/vm3.err
2017-10-27T15:42:22.235922Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-10-27T15:42:22.696811Z 0 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'
2017-10-27T15:42:22.697087Z 0 [Note] Plugin group_replication reported: 'Setting super_read_only.'
2017-10-27T15:42:22.697303Z 5 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'
2017-10-27T15:46:54.618998Z 5 [Note] Plugin group_replication reported: 'Primary had applied all relay logs, disabled conflict detection'
2017-10-27T15:49:57.546061Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-10-27T15:49:58.612942Z 0 [Note] Plugin group_replication reported: 'Unsetting super_read_only.'
2017-10-27T15:49:58.613092Z 5 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'



VM3写入测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 941bce76-6d40-11e7-b2fe-000c2909332c | vm3         |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

mysql> insert into yang.t1 values (3,'three');
Query OK, 1 row affected (0.02 sec)

mysql> select * from yang.t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
+----+-------+
3 rows in set (0.00 sec)




1a028c93d753515a123904c6f10469fe.png-wh_500x0-wm_3-wmp_4-s_4047350128.png

三、将VM1和VM2恢复后会如何？我们来测试一下
71a95e3eafb2deff712eb135d324fc4d.png-wh_500x0-wm_3-wmp_4-s_2486387583.png

在最早宕机VM2查询数据是否正常
564a9090cd1280bcfebea5467fe516bf.png-wh_500x0-wm_3-wmp_4-s_324375038.png

四、总结
通过上述简单的测试，发现mysql的组复制还是挺好用的，3台mysql实例组成的组复制结构下，只要有1台主机存活，整个mysql服务就可用，在故障主机恢复之后，组复制会自动同步数据，恢复组复制状态。但在实际的使用过程中，需要考虑到客户端或者数据库中间件的连接问题。因为在单组复制模式下，slave是自读的，所有的写入请求都通过master，如果master宕机，程序或者客户端、数据库中间件读写分离的连接如何平滑处理？


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表，该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有，请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意，请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息，一经发现立即删除。若您因此触犯法律，一切后果自负，我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集，我们仅提供一个展示、介绍、观摩学习的平台，我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责，亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏，不得用于商业或者其他用途，否则，一切后果均由您自己承担，我们对此不承担任何法律责任
7、如涉及侵犯版权等问题，请您及时通知我们，我们将立即采取措施予以解决
8、联系人Email：admin@iyunv.com 网址：www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址：https://www.yunweiku.com/thread-406213-1-1.html 上篇帖子： 基于mysql的可视化日志管理——loganalyzer 下篇帖子： linux下采用cmake或gmake方式编译安装mysql
回复

使用道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群，最新一手资源尽在官方微信交流群！快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:iyunvcom@gmail.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

豫ICP备20007574号-1 Copyright © 2012-2025

关于我们

发展历程

联系我们

关注我们

广告服务

广告案例

隐私条款

免责声明

用户登录

用户注册

版主守则

申请版主

手机版

金币获取

帮助中心

每日签到

Good Good

Study

Day Day

UP


客服E-mail:kefu@iyunv.com 客服QQ：1061981298


QQ群⑦：运维网交流群⑦ QQ群⑧：运维网交流群⑧ k8s群：运维网kubernetes交流群


提醒：禁止发布任何违反国家法律、法规的言论与图片等内容；本站内容均来自个人观点与网络等信息，非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来，其版权均归原作者及其网站所有，我们尊重他人的合法权益，如有内容侵犯您的合法权益，请及时与我们联系进行核实删除！



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表