自己写了一些和整理了些filter, 百度是搜不到的哈, 除了最后那俩anemometer的
不输出Database 为 mysql|performance_schema|information_schema|sys|db_monitor 的查询1
((\$event->{db} || '') =~ m/^(?!(mysql|performance_schema|information_schema|sys|db_monitor))/
自己写了一些和整理了些filter, 百度是搜不到的哈, 除了最后那俩anemometer的
不输出Database 为 mysql|performance_schema|information_schema|sys|db_monitor 的查询1
((\$event->{db} || '') =~ m/^(?!(mysql|performance_schema|information_schema|sys|db_monitor))/
最近刚帮业务线拆分完数据库, 源环境遗留了一张700G的大表, 虽说现在不用了, 但是业务方还是不希望删掉, 于是打算把这张表迁移到归档库, 这样有需要是还可以查询.
700G的表想了想, 如果是逻辑导出再导入的话, 感觉会很慢. 于是决定使用传输表空间方式恢复到归档库中
源库和归档库均为MGR集群 Multi-Primary Mode.
过程简单写, 并非本文重点1
2
3
4
5xbstream -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
同事之前一直使用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"}
1 | pip install confluent-kafka |
克隆下来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
4rpm -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
2find / -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 | #python |
成功收到消息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的权限控制. 之前一直看SASL_PLAINTEXT, 结果发现这玩意不能动态创建用户. 今天没死心又查了查,发现这个人也问了这个问题
https://stackoverflow.com/questions/54147460/kafka-adding-sasl-users-dynamically-without-cluster-restart
于是研究了下SCRAM. 写了这篇完整的文档
本篇文档中使用的是自己部署的zookeeper, zookeeper无需做任何特殊配置
使用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 |
|
因此,如果在节点上将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一文中找到有关组复制流控制的更多信息
可以通过global status group_replication_primary_member确定1
2
3
4
5
6
7
8
9mysql> 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
17SELECT
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
21SELECT
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开始, Performance Schema被扩展1
2
3
4
5
6
7
8
9mysql> 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
10mysql> 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/