mysql - galera 故障恢复

最新推荐文章于 2024-09-11 15:20:59 发布
最新推荐文章于 2024-09-11 15:20:59 发布
阅读量8.7k 收藏 6
点赞数
分类专栏： mysql galera 文章标签： mysql 故障
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/signmem/article/details/76577082
版权

因机房断电, mysql 集群重启后, mysql.galera 无法完成主从同步

故障日志如下

170703 00:44:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170703 00:44:14 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.ImJsjZ' --pid-file='/var/lib/mysql/hh-yun-db-129042.-
recover.pid'
170703 00:44:22 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
170703  0:44:22 [Note] WSREP: wsrep_start_position var submitted: '00000000-0000-0000-0000-000000000000:-1'
170703  0:44:22 [Note] WSREP: Read nil XID from storage engines, skipping position init
170703  0:44:22 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so'
170703  0:44:22 [Note] WSREP: wsrep_load(): Galera 3.5(rXXXX) by Codership Oy <info@codership.com> loaded successfully.
170703  0:44:22 [Note] WSREP: CRC-32C: using hardware acceleration.
170703  0:44:22 [Warning] WSREP: Could not open saved state file for reading: /var/lib/mysql//grastate.dat
170703  0:44:22 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1
170703  0:44:22 [Note] WSREP: Passing config to GCS: base_host = 240.10.129.42; base_port = 4567; cert.log_conflicts = no; debug = no; evs.inactive_check_period = PT0.5S; ev
s.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 1; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; ev
s.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.
cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_
throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout
= PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = P30S; pc.weight = 1
; proton
170703  0:44:22 [Note] WSREP: Service thread queue flushed.
170703  0:44:22 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
170703  0:44:22 [Note] WSREP: wsrep_sst_grab()
170703  0:44:22 [Note] WSREP: Start replication
170703  0:44:22 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
170703  0:44:22 [Note] WSREP: protonet asio version 0
170703  0:44:22 [Note] WSREP: Using CRC-32C (optimized) for message checksums.
170703  0:44:22 [Note] WSREP: backend: asio
170703  0:44:22 [Note] WSREP: GMCast version 0
170703  0:44:22 [Note] WSREP: (b3573054-5f45-11e7-964d-3a604abdf474, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
170703  0:44:22 [Note] WSREP: (b3573054-5f45-11e7-964d-3a604abdf474, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
170703  0:44:22 [Note] WSREP: EVS version 0
170703  0:44:22 [Note] WSREP: PC version 0
170703  0:44:22 [Note] WSREP: gcomm: connecting to group 'yun_clutster', peer '240.10.129.41:'
170703  0:44:22 [Note] WSREP: declaring 456416f2-5f43-11e7-9f2c-7e2e30ef17dd stable
170703  0:44:22 [Note] WSREP: Node 456416f2-5f43-11e7-9f2c-7e2e30ef17dd state prim
170703  0:44:22 [Note] WSREP: view(view_id(PRIM,456416f2-5f43-11e7-9f2c-7e2e30ef17dd,2) memb {
        456416f2-5f43-11e7-9f2c-7e2e30ef17dd,0
        b3573054-5f45-11e7-964d-3a604abdf474,0
} joined {
} left {
} partitioned {
})
170703  0:44:23 [Note] WSREP: gcomm: connected
170703  0:44:23 [Note] WSREP: Changing maximum packet size to 64500, resulting msg size: 32636
170703  0:44:23 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
170703  0:44:23 [Note] WSREP: Opened channel 'yun_clutster'
170703  0:44:23 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
170703  0:44:23 [Note] WSREP: Waiting for SST to complete.
170703  0:44:23 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
170703  0:44:23 [Note] WSREP: STATE EXCHANGE: sent state msg: b368f83d-5f45-11e7-b169-2e0622f98c98
170703  0:44:23 [Note] WSREP: STATE EXCHANGE: got state msg: b368f83d-5f45-11e7-b169-2e0622f98c98 from 0 (hh-yun-db-129041.)
170703  0:44:23 [Note] WSREP: STATE EXCHANGE: got state msg: b368f83d-5f45-11e7-b169-2e0622f98c98 from 1 (hh-yun-db-129042.)
170703  0:44:23 [Note] WSREP: Quorum results:
        version    = 3,
        component  = PRIMARY,
        conf_id    = 1,
        members    = 1/2 (joined/total),
        act_id     = 3524,
        last_appl. = -1,
        protocols  = 0/5/2 (gcs/repl/appl),
        group UUID = 45649783-5f43-11e7-a935-3e74e94aac23
170703  0:44:23 [Note] WSREP: Flow-control interval: [23, 23]
170703  0:44:23 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 3524)
170703  0:44:23 [Note] WSREP: State transfer required:
        Group state: 45649783-5f43-11e7-a935-3e74e94aac23:3524
        Local state: 00000000-0000-0000-0000-000000000000:-1
170703  0:44:23 [Note] WSREP: New cluster view: global state: 45649783-5f43-11e7-a935-3e74e94aac23:3524, view# 2: Primary, number of nodes: 2, my index: 1, protocol version
2
170703  0:44:23 [Warning] WSREP: Gap in state sequence. Need state transfer.
170703  0:44:25 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address 'xxxx.42' --auth 'xxxxx:xxxxx' --datadir '/var/lib/mysql/' --defaults-fil
e '/etc/my.cnf' --parent '162980''
170703  0:44:25 [Note] WSREP: Prepared SST request: rsync|240.10.129.42:4444/rsync_sst
170703  0:44:25 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
170703  0:44:25 [Note] WSREP: REPL Protocols: 5 (3, 1)
170703  0:44:25 [Note] WSREP: Service thread queue flushed.
170703  0:44:25 [Note] WSREP: Assign initial position for certification: 3524, protocol version: 3
170703  0:44:25 [Note] WSREP: Service thread queue flushed.
170703  0:44:25 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (4
5649783-5f43-11e7-a935-3e74e94aac23): 1 (Operation not permitted)
         at galera/src/replicator_str.cpp:prepare_for_IST():447. IST will be unavailable.
170703  0:44:25 [Note] WSREP: Member 1.0 (hh-yun-db-129042.) requested state transfer from '*any*'. Selected 0.0 (hh-yun-db-129041.)(SYNCED) as donor.
170703  0:44:25 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 3524)
170703  0:44:25 [Note] WSREP: Requesting state transfer: success, donor: 0
170703  0:45:12 [Note] WSREP: 0.0 (hh-yun-db-129041.): State transfer to 1.0 (hh-yun-db-129042.) complete.
170703  0:45:12 [Note] WSREP: Member 0.0 (hh-yun-db-129041.) synced with group.
WSREP_SST: [INFO] Joiner cleanup. (20170703 00:45:13.006)
WSREP_SST: [INFO] Joiner cleanup done. (20170703 00:45:14.276)
170703  0:45:14 [Note] WSREP: SST complete, seqno: 3524
170703  0:45:14 InnoDB: The InnoDB memory heap is disabled
170703  0:45:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
170703  0:45:14 InnoDB: Compressed tables use zlib 1.2.3
170703  0:45:14 InnoDB: Using Linux native AIO
170703  0:45:14 InnoDB: Initializing buffer pool, size = 16.0G
170703  0:45:15 InnoDB: Completed initialization of buffer pool
170703  0:45:15 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
170703  0:45:16  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
170703  0:45:16  InnoDB: Waiting for the background threads to start
170703  0:45:17 Percona XtraDB (http://www.percona.com) 5.5.36-MariaDB-33.0 started; log sequence number 3781527514
170703  0:45:17 [Note] Plugin 'FEEDBACK' is disabled.
170703  0:45:17 [Note] Server socket created on IP: '0.0.0.0'.
170703  0:45:17 [Note] Event Scheduler: Loaded 0 events
170703  0:45:17 [Note] WSREP: Signalling provider to continue.
170703  0:45:17 [Note] WSREP: SST received: 45649783-5f43-11e7-a935-3e74e94aac23:3524
170703  0:45:17 [Note] WSREP: 1.0 (hh-yun-db-129042.): State transfer from 0.0 (hh-yun-db-129041.) complete.
170703  0:45:17 [Note] WSREP: Shifting JOINER -> JOINED (TO: 3524)
170703  0:45:17 [Note] WSREP: Member 1.0 (hh-yun-db-129042.) synced with group.
170703  0:45:17 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 3524)
170703  0:45:17 [Note] WSREP: Synchronized with group, ready for connections
170703  0:45:17 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
170703  0:45:17 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.36-MariaDB-wsrep'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server, wsrep_25.9.r3961
170703  1:08:22 [Note] WSREP: Node b3573054-5f45-11e7-964d-3a604abdf474 state prim
170703  1:08:22 [Note] WSREP: view(view_id(PRIM,b3573054-5f45-11e7-964d-3a604abdf474,3) memb {
        b3573054-5f45-11e7-964d-3a604abdf474,0
} joined {
} left {
} partitioned {
        456416f2-5f43-11e7-9f2c-7e2e30ef17dd,0
})
170703  1:08:22 [Note] WSREP: forgetting 456416f2-5f43-11e7-9f2c-7e2e30ef17dd (tcp://240.10.129.41:4567)
170703  1:08:22 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1
170703  1:08:22 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 0d6d078d-5f49-11e7-bba6-bac92eaecf2e
170703  1:08:22 [Note] WSREP: STATE EXCHANGE: sent state msg: 0d6d078d-5f49-11e7-bba6-bac92eaecf2e
170703  1:08:22 [Note] WSREP: STATE EXCHANGE: got state msg: 0d6d078d-5f49-11e7-bba6-bac92eaecf2e from 0 (hh-yun-db-129042)
170703  1:08:22 [Note] WSREP: Quorum results:
        version    = 3,
        component  = PRIMARY,
        conf_id    = 2,
        members    = 1/1 (joined/total),
        act_id     = 69983,
        last_appl. = 69915,
        protocols  = 0/5/2 (gcs/repl/appl),
        group UUID = 45649783-5f43-11e7-a935-3e74e94aac23
170703  1:08:22 [Note] WSREP: Flow-control interval: [16, 16]
170703  1:08:22 [Note] WSREP: New cluster view: global state: 45649783-5f43-11e7-a935-3e74e94aac23:69983, view# 3: Primary, number of nodes: 1, my index: 0, protocol version
 2
170703  1:08:22 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
170703  1:08:22 [Note] WSREP: REPL Protocols: 5 (3, 1)
170703  1:08:22 [Note] WSREP: Service thread queue flushed.
170703  1:08:22 [Note] WSREP: Assign initial position for certification: 69983, protocol version: 3
170703  1:08:22 [Note] WSREP: Service thread queue flushed.
170703  1:08:22 [Warning] WSREP: Releasing seqno 69983 before 69984 was assigned.
170703  1:08:27 [Note] WSREP:  cleaning up 456416f2-5f43-11e7-9f2c-7e2e30ef17dd (tcp://240.10.129.41:4567)
170703 11:29:43 [Note] /usr/libexec/mysqld: Normal shutdown

170703 11:29:43 [Note] WSREP: Stop replication
170703 11:29:43 [Note] WSREP: Closing send monitor...
170703 11:29:43 [Note] WSREP: Closed send monitor.
170703 11:29:43 [Note] WSREP: gcomm: terminating thread
170703 11:29:43 [Note] WSREP: gcomm: joining thread
170703 11:29:43 [Note] WSREP: gcomm: closing backend
170703 11:29:43 [Note] WSREP: view((empty))
170703 11:29:43 [Note] WSREP: Received self-leave message.
170703 11:29:43 [Note] WSREP: gcomm: closed
170703 11:29:43 [Note] WSREP: Flow-control interval: [0, 0]
170703 11:29:43 [Note] WSREP: Received SELF-LEAVE. Closing connection.
170703 11:29:43 [Note] WSREP: Shifting SYNCED -> CLOSED (TO: 69983)
170703 11:29:43 [Note] WSREP: RECV thread exiting 0: Success
170703 11:29:43 [Note] WSREP: recv_thread() joined.
170703 11:29:43 [Note] WSREP: New cluster view: global state: 45649783-5f43-11e7-a935-3e74e94aac23:69983, view# -1: non-Primary, number of nodes: 0, my index: -1, protocol v
ersion 2
170703 11:29:43 [Note] WSREP: Closing replication queue.
170703 11:29:43 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
170703 11:29:43 [Note] WSREP: Closing slave action queue.
170703 11:29:43 [Note] WSREP: applier thread exiting (code:0)
170703 11:29:43 [Note] WSREP: applier thread exiting (code:6)
170703 11:29:43 [Note] WSREP: applier thread exiting (code:6)
170703 11:29:43 [Note] WSREP: applier thread exiting (code:6)

故障解决可尝试通过下面两种方法进行

1. 需进行数据同步的事务数量太大, 因此, 需要一个比较大的  innodb_log file, 假如之前的 log file 太小, 是无法正常恢复到集群同步状态
2. 在执行集群恢复期间,  需要使用参数 tc-heuristic-recover=ROLLBACK, 对需要恢复的事务执行 ROOLBACK 操作

操作

修改 /etc/my.cnf
innodb-log-file-size = 2048M
tc-heuristic-recover=ROLLBACK

对于日志, 需要执行手动把日志文件删除操作, 在启动服务器时候, MySQL 会自动重新创建响应的日志文件

Terry_Tsang
关注
专栏目录
MariaDB Galera集群之数据恢复.pdf
07-11
MariaDB Galera集群之数据恢复.pdf 学习资料 复习资料 教学资源
mysql galera 恢复_galera mariadb集群恢复策略
weixin_28958727的博客
02-07 542
1 galera mariadb首先MariaDB是一个数据库，可以看成是MySQL的一个分支，由于MySQL被SUN收购，所以MySQL面临着闭源的风险，当时MySQL之父Widenius并没有加入SUN，而是基于MySQL的代码开发新的分支，命名为MariaDB，并全部开源。Galera是Galera Cluster，是一种为数据库设计的新型的、数据不共享的、高度冗余的高可用方案，galera...
mariadb galera 故障恢复
Terry Tsang
12-04 618
mysql galera 服务集群恢复步骤
galera集群中mariadb无法启动处理方法
最新发布
weixin_43948680的博客
09-11 393
3个节点的mariadb galera集群，其中2个节点mariadb启动正常，第3个节点无法启动，而且容器日志与服务日志均没有ERROR报错
异常断电mysql集群数据库恢复
forsakening的专栏
01-24 5403
说明：项目相关，有些命令不一定适用于其他场景，仅供参考20180121日，xxxx项目，超融合异常掉电，导致数据库启动不了。 首先要备份/var/lib/mysql文件夹！！！！恢复：1）不强制恢复180121 20:00:37 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql//wsrep_...
galera-mariadb集群
待满茶杯的博客
07-23 1224
环境：系统centos7.3，全初始化环境 IP：192.168.2.111,192.168.2.106,192.168.2.107 MariaDB 10.1及更高版本不再单独使用MariaDB Galera Cluster版本。只需下载MariaDB（10.1或更高版本）并正常配置您的群集。（官网原话） 所以我就直接安装10.1版本了，如果直接安装将会默认安装mariadb 5.5...
mysql galera 集群常见问题处理
热门推荐
u011816753的专栏
09-18 2万+
一、mysql HA集群在断网过久或者所有节点都down了之后的恢复有以下的方法： 解决方案1： 1、等三台机器恢复网络通讯后，因为此时的mysql已经异常无法加入集群，因此需要先保证所有的mysql都是down的，再上台执行/usr/libexec/mysqld --wsrep-new-cluster --wsrep-cluster-address='gcomm://' & 这条命令，并进入
Mariadb集群故障恢复方法2
bai0324lin的专栏
07-29 2866
感谢朋友支持本博客，欢迎共同探讨交流，由于能力和时间有限，错误之处在所难免，欢迎指正！ 如果转载，请保留作者信息。 博客地址：https://blog.csdn.net/bai0324lin邮箱地址：brin.zhang@qq.com 问题现象：查找所有控制节点的mariadb日志文件/var/lib/docker/volumes/kolla_logs/mariadb/mairadb.log，如...
mysql-galera-3-mysql5.7.rar
09-09
MySQL Galera集群是一种高度可用的、分布式的关系型数据库解决方案，特别适用于需要高容错性和低延迟的应用场景。在本压缩包“mysql-galera-3-mysql5.7.rar”中，包含了针对MySQL 5.7版本的Galera组件，主要用于在...
mysql-galera多读多写
12-20
4. **高可用性与故障恢复**： Galera Cluster具备自我修复能力，如果某个节点出现故障，集群会自动检测并隔离故障节点，其他节点会继续提供服务。当故障节点恢复后，可以通过重新加入集群来同步丢失的数据，从而...
galera cluster for mysql集群部署方案
11-29
故障恢复** - 如果因断电等原因导致所有节点关闭，则需要按照特定步骤进行恢复： - 删除 `/var/lib/mysql/data/grastate.dat` 文件。 - 重启第一台服务器时添加 `wsrep-new-cluster` 参数。 - 其余服务器依次...
mariadb-ansible-galera-cluster：使用Ansible自动安装MariaDB Galera Cluster
01-30
MariaDB Galera Cluster是MySQL的一个分支，它提供了一个高可用性、多主复制的数据库解决方案。使用Ansible自动化部署此集群可以显著简化设置过程，并确保一致性。本文将深入探讨如何利用Ansible Playbook来实现...
galeracluster高可用集群异常恢复
github_39319229的博客
06-28 1301
galeracluster高可用集群异常恢复
PXC(Percona-XtraDB-Cluster)5.7版本集群安装和使用初探
csd753111111的博客
10-25 1331
环境介绍： pxc三个节点: 192.168.1.6 test06 192.168.1.7 test07 192.168.1.8 test08 操作系统版本:C...
galera mysql docker话_bug排查之空想导致的祸之MariaDB Galera Cluster的docker镜像构建中的遇到的坑爹事...
weixin_33895572的博客
03-04 858
摘要唉，技术不行就不能装逼，不然就我这样，自己把自己搞死了。什么事情都需要用实际验证之后才能下结论，不能用经验主义和理论主义。之前自己构建了一个Docker的MariaDB Galera Cluster的镜像，测试的时候我只测试了普通功能，集群功能其实自己没测试，只是想当然的用自己之前的经验认为是OK的，谁知道今天测试的时候还真发现问题了，1、第一次发现我在构建镜像的时候系统上没装which命令，...
mysql/mariadb 集群一个启动，另外一个启动不起来
qq_38093703的博客
12-01 682
mysql/mariadb 集群一个启动，另外一个启动不起来 问题截图 [root@ds2 my.cnf.d]# service mariadb start Redirecting to /bin/systemctl start mariadb.service Job for mariadb.service failed because a fatal signal was delivered to the control process. See "systemctl status mariadb.ser
mysql with wsrep_MySQL Galera 集群的安装过程
weixin_39537049的博客
01-19 390
MySQL Galera 集群的安装过程MySQL Galera集群版的mysql是经过codeship打了wsrep补丁的,不是普通的mysql版本.可以从github下载。环境:redhat 7mysql:5.5.37galera: 25.3.5解压，新增mysql用户，然后初始化mysql[root@os1 ~]# ls -l mysql-5.5.37_wsrep_25.10-linux-x...
mysql safe无法启动_（转）mysqld_safe无法启动的解决办法
weixin_39754267的博客
01-30 475
启动mysql时报错：Starting mysqld daemon with databases from /var/lib/mysqlSTOPPING server from pid file /var/run/mysqld/mysqld.pid071112 00:22:06 mysqld ended查看日志：＃less /var/log/mysqld.log其中有一段如下：071112 0:2...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Terry_Tsang

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值