MySQL大表传输表空间的坑

MySQL大表传输表空间的坑

最近刚帮业务线拆分完数据库, 源环境遗留了一张700G的大表, 虽说现在不用了, 但是业务方还是不希望删掉, 于是打算把这张表迁移到归档库, 这样有需要是还可以查询.
700G的表想了想, 如果是逻辑导出再导入的话, 感觉会很慢. 于是决定使用传输表空间方式恢复到归档库中

源库和归档库均为MGR集群 Multi-Primary Mode.

恢复备份

过程简单写, 并非本文重点

1
2
3
4
5
xbstream -vx --parallel=10 < mysql_backup_20190618.xbstream -C /rundata/backup/ > /tmp/xb.log 2>&1 &
cd /rundata/backup/datadir
rm 除这张700g表A的所有其他表的qp文件
xtrabackup --decompress --parallel=10 --remove-original --target-dir=/rundata/backup/
innobackupex --apply-log --export /rundata/backup/

限制备份速度防止网卡打满

限制备份速度防止网卡打满

最近在做拆库, 于是就做了很多表迁移工作, 需要使用mysqldump远程备份数据, 然后发现备份时很容易就把源库网卡打满了. 想过用tc命令限速, 发现有点复杂. 今天无意间看xtrabackup文档发现一个方法

Throttling the throughput to 10MB/sec. This requires the ‘pv’ tools; you can find them at the official site or install it from the distribution package (“apt-get install pv”)

1
2
$ innobackupex --stream=tar ./ | pv -q -L10m \
| ssh user@desthost "cat - > /data/backups/backup.tar"

Make a Streaming Backup
https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/recipes_ibkx_stream.html

查看pv的介绍, 主要是辅助查看一些进度 progressbar ETA什么的:

在google搜索mysqldump pv 基本也是些查看进度的文章

http://landcareweb.com/questions/6489/you-mei-you-ban-fa-rang-mysqldumpjin-du-tiao-xian-shi-yong-hu-de-bei-fen-zhuang-tai
https://www.2cto.com/database/201310/248423.html
https://stackoverflow.com/questions/4852933/does-mysqldump-support-a-progress-bar

我们这里主要目的是限速, 照猫画虎, 可以这样实现

导出限速

1
mysqldump | pv -q -L10m > xx.sql

限速导入
1
cat xx.sql | pv -q -L10m | mysql

导入查看进度
1
pv xx.sql | mysql

修改sysbench输出格式为csv或json, 添加自定义指标

修改sysbench输出格式为csv或json, 添加自定义指标

在oltp_common.lua添加(二选一)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- 输出csv

-- function sysbench.hooks.report_intermediate(stat)

--     sysbench.report_csv(stat)

-- end



-- 输出json

-- function sysbench.hooks.report_intermediate(stat)

--     sysbench.report_json(stat)

-- end

自己去掉 -- 注释

使confluent_kafka支持SASL_PLAINTEXT

同事之前一直使用kafka-python开发. 上了ACL以后发现kafka-python居然不支持SASL_PLAINTEXT

https://kafka-python.readthedocs.io/en/master/apidoc/KafkaProducer.html
sasl_mechanism (str) – string picking sasl mechanism when security_protocol is SASL_PLAINTEXT or SASL_SSL. Currently only PLAIN is supported. Default: None

看了一下confluent-kafka是支持的但需要重新编译librdkafka
否则会报错:

1
KafkaException: KafkaError{code=_INVALID_ARG,val=-186,str="Failed to create producer: No provider for SASL mechanism GSSAPI: recompile librdkafka with libsasl2 or openssl support. Current build options: PLAIN SASL_SCRAM"}

安装confluent-kafka

1
2
3
4
pip install confluent-kafka
Collecting confluent-kafka
  Downloading https://files.pythonhosted.org/packages/2a/ba/dccb27376453f91ad8fa57f75a7ba5dc188023700c1789273dec976477b2/confluent_kafka-0.11.6-cp37-cp37m-manylinux1_x86_64.whl (3.9MB)
    100% |████████████████████████████████| 3.9MB 984kB/s

安装librdkafka

克隆下来

1
2
3
4
5
6
7
8
9
[root@node004110 18:21:05 /tmp]
git clone https://github.com/edenhill/librdkafka.git
Cloning into 'librdkafka'...
remote: Enumerating objects: 213, done.
remote: Counting objects: 100% (213/213), done.
remote: Compressing objects: 100% (111/111), done.
remote: Total 18133 (delta 133), reused 149 (delta 98), pack-reused 17920
Receiving objects: 100% (18133/18133), 11.15 MiB | 946.00 KiB/s, done.
Resolving deltas: 100% (13758/13758), done.

检查依赖
1
2
3
4
rpm -qa| grep openssl
openssl-1.0.2k-16.el7.x86_64
openssl-libs-1.0.2k-16.el7.x86_64
openssl-devel-1.0.2k-16.el7.x86_64

The GNU toolchain
GNU make
pthreads
zlib-dev (optional, for gzip compression support)
libssl-dev (optional, for SSL and SASL SCRAM support) –这个对于centos openssl-devel
libsasl2-dev (optional, for SASL GSSAPI support)
libzstd-dev (optional, for ZStd compression support)
安装

1
2
./configure
make && make install

注意下make的时候这里是ok就行
1
2
checking for libssl (by pkg-config)... ok
checking for libssl (by compile)... ok (cached)

替换库文件

查找

1
2
find / -name "librdkafka*" 
/root/.pyenv/versions/3.7.2/lib/python3.7/site-packages/confluent_kafka/.libs/librdkafka.so.1

替换
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
#cd /root/.pyenv/versions/3.7.2/lib/python3.7/site-packages/confluent_kafka/.libs/

[root@node004110 18:24:03 ~/.pyenv/versions/3.7.2/lib/python3.7/site-packages/confluent_kafka/.libs]
#ll
total 10316
-rwxr-xr-x 1 root root 2903144 Mar 8 18:20 libcrypto-4c524931.so.1.0.0
-rwxr-xr-x 1 root root 6944424 Mar 8 18:20 librdkafka.so.1
-rwxr-xr-x 1 root root 584072 Mar 8 18:20 libssl-01b7eff1.so.1.0.0
-rwxr-xr-x 1 root root 87848 Mar 8 18:20 libz-a147dcb0.so.1.2.3
-rw-r--r-- 1 root root 35336 Mar 8 18:20 monitoring-interceptor.so

[root@node004110 18:24:04 ~/.pyenv/versions/3.7.2/lib/python3.7/site-packages/confluent_kafka/.libs]
#mv librdkafka.so.1 librdkafka.so.1.bak

[root@node004110 18:24:11 ~/.pyenv/versions/3.7.2/lib/python3.7/site-packages/confluent_kafka/.libs]
#ln -s /usr/local/lib/librdkafka.so.1 librdkafka.so.1

[root@node004110 18:24:23 ~/.pyenv/versions/3.7.2/lib/python3.7/site-packages/confluent_kafka/.libs]
#ll
total 10316
-rwxr-xr-x 1 root root 2903144 Mar 8 18:20 libcrypto-4c524931.so.1.0.0
lrwxrwxrwx 1 root root 30 Mar 8 18:24 librdkafka.so.1 -> /usr/local/lib/librdkafka.so.1
-rwxr-xr-x 1 root root 6944424 Mar 8 18:20 librdkafka.so.1.bak
-rwxr-xr-x 1 root root 584072 Mar 8 18:20 libssl-01b7eff1.so.1.0.0
-rwxr-xr-x 1 root root 87848 Mar 8 18:20 libz-a147dcb0.so.1.2.3
-rw-r--r-- 1 root root 35336 Mar 8 18:20 monitoring-interceptor.so

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#python
Python 3.7.2 (default, Mar 4 2019, 16:55:21) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-36)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from confluent_kafka import Producer     
>>> p = Producer({'bootstrap.servers': '192.168.4.114:9092', 'security.protocol': 'SASL_PLAINTEXT', 'sasl.mechanism':'SCRAM-SHA-256','sasl.username':'admin','sasl.password':'your-admin-pass'})        
>>> def delivery_report(err, msg):
... if err is not None:
... print('Message delivery failed: {}'.format(err))
... else:
... print('Message delivered to {} [{}]'.format(msg.topic(), msg.partition()))
... 
>>> for data in ['hello','word']:  
... p.produce('test_acl', data.encode('utf-8'), callback=delivery_report)
... 
>>> p.poll(10) 
Message delivered to test_acl [0]
Message delivered to test_acl [0]
1
>>> p.flush()
0
>>> quit()

成功收到消息

1
2
3
[root@node004114 kafka]# bin/kafka-console-consumer.sh --bootstrap-server 192.168.4.114:9092 --topic test_acl --consumer.config config/client-sasl.properties --from-beginning
hello
word

假如Kafka集群中一个broker宕机无法恢复, 应该如何处理?

假如Kafka集群中一个broker宕机无法恢复, 应该如何处理?

坐地铁时想到这个问题, 印象中书中说添加新的broker, 是不会自动同步旧数据的.

笨办法

环境介绍

三个broker的集群, zk,kafka装在一起

1
2
3
4
5
| broker | IP | broker.id |
|---------|---------------|-----------|
| broker1 | 172.18.12.211 | 211 |
| broker2 | 172.18.12.212 | 212 |
| broker3 | 172.18.12.213 | 213 |

Kafka SASL_SCRAM+ACL实现动态创建用户及权限控制

SASL_SCRAM+ACL实现动态创建用户及权限控制

  研究了一段时间Kafka的权限控制. 之前一直看SASL_PLAINTEXT, 结果发现这玩意不能动态创建用户. 今天没死心又查了查,发现这个人也问了这个问题
https://stackoverflow.com/questions/54147460/kafka-adding-sasl-users-dynamically-without-cluster-restart
于是研究了下SCRAM. 写了这篇完整的文档

本篇文档中使用的是自己部署的zookeeper, zookeeper无需做任何特殊配置

MGR vs PXC Benchmark

MGR vs PXC Benchmark

本次测试将MGR与PXC进行对比, 意在比较MGR自身不同流控阈值下性能表现以及观察MGR与PXC相同负载下的性能表现. 测试工具使用sysbench 1.1.0-431660d

基础环将信息如下表所示, 在三个测试服务器部署了3306, 3307两套集群

Host IP MGR PXC
data-191 192.168.8.191 3306 3307
data-219 192.168.8.219 3306 3307
data-220 192.168.8.220 3306 3307

MySQL Group Replication- understanding Flow Control[译文]

MySQL Group Replication: understanding Flow Control

使用MySQL Group Replication时，某些成员可能落后于该组。 由于负载，硬件限制等原因…这种滞后可能会导致不能保持良好的性能认证行为和不能尽可能降低认证失败次数.应用队列(applying queue)越大, 与尚未应用的事务发生冲突的风险就越大（这在Multi-Primary Groups上是有问题的）。

Galera用户已经熟悉这个概念(译注: 指流控的概念). MySQL Group Replication的实现与Galera有两个主要不同点:

  • the Group is never totally stalled(译注: Group不会彻底停止接收写请求)

  • the node having issues doesn’t send flow control messages to the rest of the group asking for slowing down(译注: 短板节点不会向Group的其他节点发送flow control消息来要求集群减速)

实际上，该组的每个成员都会向其他成员发送有关其队列（applier queue and certification queue）的一些统计信息。 然后，如果每个节点意识到一个节点达到了其中一个队列的阈值，则决定是否减速：

1
2
3
4
5

group_replication_flow_control_applier_threshold (default is 25000)

group_replication_flow_control_certifier_threshold (default is 25000)

因此，如果在节点上将group_replication_flow_control_mode设置为QUOTA，并且看到该集群的其他成员之一落后（达到阈值），则会将写入操作限制为最小配额。 此配额是根据最后一秒中应用的transactions数计算的，然后通过减去上一个周期的“over the quota”消息来减少。

and then it is reduced below that by subtracting the “over the quota” messages from the last period.

这意味着与Galera相反，Galera的(flow control)阈值是有短板节点决定的，对于我们在MySQL组复制中，编写事务的节点(译注: 写节点)检查其阈值流量控制值并将它们与来自其他节点的统计数据进行比较以决定是否进行flow control。

您可以在Vitor的文章Zooming-in on Group Replication Performance一文中找到有关组复制流控制的更多信息

如何确定Single-Primary模式下的MGR主节点

如何确定Single-Primary模式下的MGR主节点(文档 ID 2214438.1)

MySQL 5.7

可以通过global status group_replication_primary_member确定

1
2
3
4
5
6
7
8
9
mysql> SELECT VARIABLE_VALUE
         FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'group_replication_primary_member';
+--------------------------------------+
| VARIABLE_VALUE                       |
+--------------------------------------+
| 9d7f8c28-c02c-11e6-9829-08002715584a |
+--------------------------------------+
1 row in set (0.00 sec)

如果是Multi-Primary默认则结果为空

可以结合performance_schema.replication_group_members表 获取主机名和端口信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 
    MEMBER_HOST, MEMBER_PORT
FROM
    performance_schema.replication_group_members
WHERE
    MEMBER_ID = (SELECT 
            VARIABLE_VALUE
        FROM
            performance_schema.global_status
        WHERE
            VARIABLE_NAME = 'group_replication_primary_member');
+-------------+-------------+
| MEMBER_HOST | MEMBER_PORT |
+-------------+-------------+
| ol7         |        3306 |
+-------------+-------------+
1 row in set (0.00 sec)

或者获取全部成员信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
    MEMBER_ID,
    MEMBER_HOST,
    MEMBER_PORT,
    MEMBER_STATE,
    IF(global_status.VARIABLE_NAME IS NOT NULL,
        'PRIMARY',
        'SECONDARY') AS MEMBER_ROLE
FROM
    performance_schema.replication_group_members
        LEFT JOIN
    performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member'
        AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID;
+--------------------------------------+-------------+-------------+--------------+-------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE |
+--------------------------------------+-------------+-------------+--------------+-------------+
| 9d7f8c28-c02c-11e6-9829-08002715584a | ol7         |        3306 | ONLINE       | PRIMARY     |
| f2bbb11d-c0c4-11e6-98ec-08002715584a | ol7         |        3308 | ONLINE       | SECONDARY   |
| f5bb7d78-c02c-11e6-9c56-08002715584a | ol7         |        3307 | ONLINE       | SECONDARY   |
+--------------------------------------+-------------+-------------+--------------+-------------+
3 rows in set (0.00 sec)

MySQL 8.0.2 and Later

MySQL 8.0.2开始, Performance Schema被扩展

1
2
3
4
5
6
7
8
9
mysql> SELECT MEMBER_HOST, MEMBER_PORT
         FROM performance_schema.replication_group_members
        WHERE MEMBER_ROLE = 'PRIMARY';
+-------------+-------------+
| MEMBER_HOST | MEMBER_PORT |
+-------------+-------------+
| ol7         |        3306 |
+-------------+-------------+
1 row in set (0.00 sec)

或者获取全部成员信息:
1
2
3
4
5
6
7
8
9
10
mysql> SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION
         FROM performance_schema.replication_group_members;
+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| 33565eab-731f-11e7-8e94-08002715584a | ol7         |        3307 | ONLINE       | SECONDARY   | 8.0.2          |
| 86034b0a-731f-11e7-9f33-08002715584a | ol7         |        3308 | ONLINE       | SECONDARY   | 8.0.2          |
| a45d3804-731e-11e7-9003-08002715584a | ol7         |        3306 | ONLINE       | PRIMARY     | 8.0.2          |
+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

References
https://dev.mysql.com/doc/refman/en/group-replication-find-primary.html
https://dev.mysql.com/doc/refman/en/group-replication-options.html#sysvar_group_replication_single_primary_mode
https://dev.mysql.com/doc/refman/en/replication-group-members-table.html
http://mysqlhighavailability.com/group-replication-extending-group-replication-performance_schema-tables/
http://lefred.be/content/mysql-group-replication-who-is-the-primary-master-updated/

执行sql文件限制频率避免流控

避免流控

对于pxc, 为了避免流控, 可以在导入.sql文件时, 先对文件做处理

1
awk '1;NR%1000==0{print "select sleep(1);"}' xxx.sql > xxx_dba.sql

上面的命令每1000行增加一行select sleep(1);, 这样执行频率是1k/s

对于mysqldump产生的sql文件, 则需要在导出是指定

1
mysqldump --skip-extended-insert

每行一个insert语句, 之后再使用awk处理

使用pt-fifo-split

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
FLAT_FILE="/tmp/big_file.txt"
FIFO_PATH="${FLAT_FILE}.fifo"
LOAD_FILE="${FLAT_FILE}.load"
CHUNK_SIZE=1000
 
# Split the file
pt-fifo-split --force --lines ${CHUNK_SIZE} ${FLAT_FILE} --fifo ${FIFO_PATH} &
# Sleep 10 seconds to assure ${FIFO_PATH} exists before entering loop
sleep 10
while [ -e ${FIFO_PATH} ]
do
  # Write chunk to disk
  cat ${FIFO_PATH} > ${LOAD_FILE}
  # Load chunk into table
  mysql --database=test \
   --show-warnings \
   -vv < ${LOAD_FILE}
  sleep 1
done

Powered by Hexo and Hexo-theme-hiker

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

访客数 : | 访问量 :