ProxySQL升级时对默认部署的实例的影响

公司有使用默认安装目录的ProxySQL跑业务. 在升级2.0.8 至 2.0.12的时候发现卸载2.0.8后这些使用默认方式安装的ProxySQL实例就会被关闭.

原因是rpm包安装和卸载前后执行了一些动作

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
[root@bj1-mysql-manager-prod-01 tmp]# rpm --scripts -qp  /tmp/proxysql-2.0.8-1-centos7.x86_64.rpm 
preinstall scriptlet (using /bin/sh):
# Cleanup artifacts
if [ -f /var/lib/proxysql/PROXYSQL_UPGRADE ]; then 
    rm -fr /var/lib/proxysql/PROXYSQL_UPGRADE
fi
postinstall scriptlet (using /bin/sh):
# Create relevant user, directories and configuration files
if [ ! -d /var/run/proxysql ]; then /bin/mkdir /var/run/proxysql ; fi
if [ ! -d /var/lib/proxysql ]; then /bin/mkdir /var/lib/proxysql ; fi
if ! id -u proxysql > /dev/null 2>&1; then useradd -r -U -s /bin/false -d /var/lib/proxysql -c "ProxySQL Server" proxysql; fi
/bin/chown -R proxysql: /var/lib/proxysql /var/run/proxysql
/bin/chown root:proxysql /etc/proxysql.cnf
/bin/chmod 640 /etc/proxysql.cnf
# Configure systemd appropriately.
/bin/systemctl daemon-reload
/bin/systemctl enable proxysql.service
# Notify that a package update is in progress in order to start service.
if [ $1 -eq 2 ]; then /bin/touch /var/lib/proxysql/PROXYSQL_UPGRADE ; fi
preuninstall scriptlet (using /bin/sh):
# When uninstalling always try stop the service, ignore failures
/bin/systemctl stop proxysql || true
postuninstall scriptlet (using /bin/sh):
if [ $1 -eq 0 ]; then
    # This is a pure uninstall, systemd unit file removed
    # only daemon-reload is needed.
    /bin/systemctl daemon-reload
else
    # This is an upgrade, ProxySQL should be started. This
    # logic works for packages newer than 2.0.7 and ensures
    # a faster restart time.
    /bin/systemctl start proxysql.service
    /bin/rm -fr /var/lib/proxysql/PROXYSQL_UPGRADE
fi
posttrans scriptlet (using /bin/sh):
if [ -f /var/lib/proxysql/PROXYSQL_UPGRADE ]; then
    # This is a safeguard to start the service after an update
    # which supports legacy "preun" / "postun" logic and will
    # only execute for packages before 2.0.7.
    /bin/systemctl start proxysql.service
    /bin/rm -fr /var/lib/proxysql/PROXYSQL_UPGRADE
fi

MySQL动态行转列

通过max行转列不是动态的, 你还是要知道所有列名才可以

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
root@localhost 17:46:56 [fanboshi]> select * from t3;
+----+---------+------+-----------+
| id | title   | env  | progress  |
+----+---------+------+-----------+
|  1 | 工单1   |    1 | 完成      |
|  2 | 工单1   |    2 | 完成      |
|  3 | 工单1   |    3 | 待审核    |
|  4 | 工单2   |    1 | 待审核    |
+----+---------+------+-----------+
4 rows in set (0.00 sec)

root@localhost 17:48:20 [fanboshi]> select title,max(if(env=1,progress,-1)) 'RC',max(if(env=2,progress,-1)) 'Stage',max(if(env=3,progress,-1)) 'Prod' from t3 group by title;
+---------+-----------+--------+-----------+
| title   | RC        | Stage  | Prod      |
+---------+-----------+--------+-----------+
| 工单1   | 完成      | 完成   | 待审核    |
| 工单2   | 待审核    | -1     | -1        |
+---------+-----------+--------+-----------+
2 rows in set (0.00 sec)

死锁案例2 两个UPDATE死锁

问题描述

2020.05.18 16:12 收到报警

登录主机, 查看error log( 开启了innodb_print_all_deadlocks参数)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*** (1) TRANSACTION:

TRANSACTION 1261729280, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 3645828, OS thread handle 139681739994880, query id 1522438991 172.16.23.82 yos_rw Searching rows for update
UPDATE f_log SET D_UPDATED_AT = NOW() , I_STATUS = 17 WHERE I_STATUS = 1 AND I_TYPE = 1
2020-05-18T16:11:11.696061+08:00 3056367 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1208 page no 29439 n bits 160 index PRIMARY of table `yos`.`f_log` trx id 1261729280 lock_mode X locks rec but not gap waiting
2020-05-18T16:11:11.696079+08:00 3056367 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 1261729278, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 3
MySQL thread id 3056367, OS thread handle 139682403342080, query id 1522438990 172.16.23.82 yos_rw updating
UPDATE f_log SET D_UPDATED_AT='2020-05-18 16:11:11.693998',I_STATUS=3 WHERE (I_FILE_ID='92b52d6589ce11a9a3c985e7c5f37462efb66ac7')
2020-05-18T16:11:11.696099+08:00 3056367 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1208 page no 29439 n bits 160 index PRIMARY of table `yos`.`f_log` trx id 1261729278 lock_mode X locks rec but not gap
2020-05-18T16:11:11.696112+08:00 3056367 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1208 page no 17 n bits 1120 index idx_status_type of table `yos`.`f_log` trx id 1261729278 lock_mode X locks rec but not gap waiting
2020-05-18T16:11:11.696223+08:00 3056367 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)

简单解释一下上面的死锁日志

MGR单主到底做不做冲突检测?

和同事探讨一个问题, MGR单主做不做冲突检测.

我理解是不需要做的, 因为已经明确只有主节点才能写入数据了, 那么必然不会有数据冲突的可能, 没必要再做冲突检测浪费性能了.

看了下官方文档

1
In single-primary mode, Group Replication enforces that only a single server writes to the group, so compared to multi-primary mode, consistency checking can be less strict and DDL statements do not need to be handled with any extra care

译文 New Feature in Percona XtraDB Cluster 8.0 – Streaming Replication

New Feature in Percona XtraDB Cluster 8.0 – Streaming Replication

Percona XtraDB Cluster 8.0附带了一个升级的Galera 4.0库, 它提供了一个新特性—Streaming Replication.让我们回顾一下它是什么, 什么时候可能有用

以前版本的Percona XtraDB集群和Galera 3.x在处理大事务方面有限制.

让我们看一下sysbench-tpcc工作负载下的性能, 与此同时, 我们对一个表执行了一个大的更新语句, 该更新甚至与主工作负载中的表都不相关.

Powered by Hexo and Hexo-theme-hiker

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

访客数 : | 访问量 :