[译]PXC7中故障场景及恢复方法

与标准的MySQL复制不同,PXC群集就像一个逻辑实体,它负责关注每个节点的状态和一致性以及群集状态。这样可以保持更好的数据完整性,然后您可以从传统的异步复制中获益,同时允许在同一时间在多个节点上进行安全写入.

假设我们有一个PXC集群包含3个节点

情景1

情景1

节点A正常停止, 例如需要停库做一些维护, 配置变更等操作.

在这种情况下,其他节点从该节点接收”good bye”消息, 因此集群大小将会缩小(在这个例子中缩小为2), 并且某些属性如quorum caculation和auto increment(我理解为auto_increment_increment和auto_increment_offset会由于集群扩缩动态调整). 一旦我们重新开始的一个节点,它会根据它的my.cnf中wsrep_cluster_address设置加入群集. 这个过程与普通的复制有很大的不同 - 在A节点再次与集群完全同步之前,A节点不会接受供任何请求,因此仅仅是与集群建立连接是不够的,而是必须要先完成state transfer. 如果B或C节点的writeset cache (gcache.size), 仍然有恢复A节点所需的所有事务, 那么将使用IST 否则使用 SST . 因此,如本文所示,确定最佳donor很重要. 如果由于donor的gcache中缺少交易而导致IST不可用,则由donor作出回退决定,而SST自动启动。

阅读全文

如何做好MySQL的备份

如何做好MySQL的备份

物理备份还是逻辑备份?

其实物理备份和逻辑备份并没有好坏之分, 关键是要适合你的场景.

两种备份方式的备份工具有:

  • 逻辑备份工具: [mysqldump, mysqlpump, mydumper, select into out file]
  • 物理备份工具: [xtrabackup, TokuBackup, Tokudb-xtrabackup]

两种备份方式的对比如下

  • 备份速度
    物理备份比逻辑备份快吗? 不要想当然, 至少我的测试结果并不是这样
  • 恢复速度
    物理备份恢复实际就是mv操作(使用xtrabackup,在备份机做prepare), 而逻辑备份则是漫长的导入.
    同机器 1.5T库, 逻辑备份大小151G, 做恢复需要27小时左右, 而物理备份恢复则完全取决于磁盘iops, 不用测也知道要比逻辑备份快很多
  • 备份集大小
    实际测试1T的库(大部分为InnoDB表), 逻辑备份集大小为46G, 而物理备份为255G

根据以上三点, 就可以选择备份方式了吗? 我认为不能. 还有一点是数据库服务器和备份服务器之间的网络情况, 和你期望的恢复时间是多久

阅读全文

Load data奇怪的问题

Load data奇怪的问题

公司一套所谓的BI库(5.5 innodb引擎),由于太大了,考虑转用TokuDB引擎,最近在测试,直接在一个新环境安装Percona5.7.21,然后恢复备份,转换为TokuDB表, 再做从库和原5.5库同步(5.5 是statement格式)
准备同步上,然后这套环境部署一下应用,再全面的测一下.
结果发现同步报错了

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
root@localhost 10:42: [test]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xx.xx.xx
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.010346
Read_Master_Log_Pos: 715054772
Relay_Log_File: mysql-relay.000010
Relay_Log_Pos: 376792000
Relay_Master_Log_File: mysql-bin.010288
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,union_log%.%,test.%
Last_Errno: 1300
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.010288, end_log_pos 378517485. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 376791811
Relay_Log_Space: 74984691638
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1300
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.010288, end_log_pos 378517485. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 180402 00:07:09
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: a3f4b929-31a0-11e8-9714-f8bc123346cc:1-864
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

阅读全文

5.5.x升级5.7.21步骤

5.5.x升级5.7.21步骤

环境准备

上传软件包

根据官方文档说明

Upgrading to the latest release is recommended before upgrading to the next version

当前版本5.5.46,需要上传三个包

1
2
3
mysql-5.5.59-linux-glibc2.12-x86_64.tar.gz
mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz

创建目录

1
mkdir /usr/local/{mysql-5.5.59,mysql-5.6.39,mysql-5.7.21}

解压

1
2
3
tar -zxvf mysql-5.5.59-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql-5.5.59/
tar -zxvf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql-5.6.39/
tar -zxvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql-5.7.21/

设置环境变量

1
2
3
4
5
6
7
8
9
10
11
12
zst_ps1()
{
Date=$(date +%F)
Time=$(date +%H:%M:%S)
PS1="\\n\[\e[1;37m[\e[m\]\[\e[1;33m\u\e[m\]\[\e[1;33m@\h\e[m\]\[\e[1;35m $Time \e[m\]\e[1;36m\w\e[m\e[1;37m]\e[m\n\\$"
}
PROMPT_COMMAND=zst_ps1
export PATH=/usr/local/mysql/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/mysql/lib

阅读全文

MySQL 5.5.X升级至5.7.21遇到的坑(一)

MySQL 5.5.X升级至5.7.21遇到的坑(一)

发现问题

将一个测试环境的5.5.x升级到5.7.21后,打算将5.7.21作为从库,开始同步主库数据(binlog_format=statement),结果刚一start slave就报错

1
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000004, end_log_pos 812. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

查看error log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
2018-02-10T19:52:52.347979+08:00 3 [Warning] Slave I/O for channel '': Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with er
ror: Unknown system variable 'binlog_checksum', Error_code: 1193
2018-02-10T19:52:52.348080+08:00 3 [Warning] Slave I/O for channel '': Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable i
s not supported on the master (version: 5.5.59-log), even though it is on the slave (version: 5.7.21-log), Error_code: 1193
2018-02-10T19:52:52.445947+08:00 5 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log , end_log_pos 2651; Error
'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GET, POST, FILE, CLASS,
METHOD ) VALUES ( 'maokaixin', 1518159802, 3395959414, '' at line 1' on query. Default database: 'fandb'. Query: 'INSERT INTO postlog ( USERNAME, TIME, IP, GE
T, POST, FILE, CLASS, METHOD ) VALUES ( 'maokaixin', 1518159802, 3395959414, 'gameid:0;', '', '', 'api', 'ajaxGetServers' )', Error_code: 1064
2018-02-10T19:52:52.446217+08:00 4 [Warning] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in incon
sistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead t
o problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
2018-02-10T19:52:52.446235+08:00 4 [Note] Slave SQL thread for channel '' exiting, replication stopped in log 'mysql-bin.000003' at position 2313
2018-02-10T19:53:15.708058+08:00 7 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000003' at position 2313, relay l
og './mysql-relay.000009' position: 304

找出原因

头两个Warning是由于主库没有binlog_checksum参数,也没有SERVER_UUID参数(看来从库开始同步时要先去主库查询这两个参数)
接着的ERROR报的错误竟然是error in your SQL syntax语法错误.

解析binlog后查到SQL语句为

1
INSERT INTO postlog ( USERNAME, TIME, IP, GET, POST, FILE, CLASS, METHOD ) VALUES ( 'maokaixin', 1518159802, 3395959414, 'gameid:0;', '', '', 'api', 'ajaxGetServers' );

我依次在5.5和5.7的环境执行这个SQL发现该SQL在5.5可以正常执行,而在5.7执行就会报错语法错误
于是一点一点分析这个SQL哪有问题,说实话肉眼真没看出来
只好在5.7一点一点执行

1
INSERT INTO postlog ( USERNAME, TIME, IP) VALUES ( 'maokaixin', 1518159802, 3395959414);

没问题可以执行

1
INSERT INTO postlog ( USERNAME, TIME, IP, GET) VALUES ( 'maokaixin', 1518159802, 3395959414, 'gameid:0;');

报错了!,那显然就是GET有问题。 怀疑是保留字?

阅读全文

pyenv装的2.7.14 无法用ansible yum.(其实不是pyenv的原因,自己编译安装的2.7.14也不行)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#ansible OA* -m yum -a"name=mutt state=present"
OA_P | FAILED! => {
"changed": false,
"msg": "python2 yum module is needed for this module"
}
OA_S | FAILED! => {
"changed": false,
"msg": "python2 yum module is needed for this module"
}
#ansible OA* -m shell -a "which python pip"
OA_P | SUCCESS | rc=0 >>
/root/.pyenv/versions/2.7.14/bin/python
/root/.pyenv/versions/2.7.14/bin/pip
OA_S | SUCCESS | rc=0 >>
/root/.pyenv/versions/2.7.14/bin/python
/root/.pyenv/versions/2.7.14/bin/pip
#vi /etc/ansible/hosts/oa
[OA]
OA_P ansible_connection=local
OA_S ansible_ssh_host=172.16.200.209
[OA:vars]
ansible_python_interpreter=/root/.pyenv/shims/python

使用系统默认的python作为解释器可以使用yum模块

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
[root@OA_P 13:04:15 /etc/ansible/roles]
#ansible OA* -m yum -a"name=mutt state=present"
OA_P | FAILED! => {
"changed": false,
"msg": "python2 bindings for rpm are needed for this module. python2 yum module is needed for this module"
}
OA_S | FAILED! => {
"changed": false,
"msg": "python2 bindings for rpm are needed for this module. python2 yum module is needed for this module"
}
^C
[root@OA_P 13:04:28 /etc/ansible/roles]
#ansible OA* -m yum -a"name=mutt state=present" -e "ansible_python_interpreter=/usr/bin/python"
OA_S | SUCCESS => {
"changed": false,
"msg": "",
"rc": 0,
"results": [
"5:mutt-1.5.20-8.20091214hg736b6a.el6.x86_64 providing mutt is already installed"
]
}
OA_P | FAILED! => {
"changed": false,
"msg": "The following packages have pending transactions: mutt-x86_64",
"rc": 125,
"results": []
}

这是因为2.6可以import yum而2.7不行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#python
Python 2.7.14 (default, Dec 15 2017, 23:08:56)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-18)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import yum
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ImportError: No module named yum
#python2.6
Python 2.6.6 (r266:84292, Aug 18 2016, 15:13:37)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-17)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import yum

目前我的解决方法是, 在yum之前先set facts, 之后在set回来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
- set_fact:
ansible_python_default_interpreter: "{{ ansible_python_interpreter }}"
ansible_python_interpreter: "/usr/bin/python"
- name: 安装依赖包
yum:
name: "{{ item.line }}"
state: present
with_items:
- { line: 'openssl' }
- { line: 'openssl-devel' }
- { line: 'mutt' }
- set_fact:
ansible_python_interpreter: "{{ ansible_python_default_interpreter }}"

参考

https://github.com/openshift/openshift-ansible/issues/855
https://stackoverflow.com/questions/29711514/no-module-named-yum-error-with-python2-7-when-using-ansible-on-fedora/36138921#36138921


Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#