ProxySQL 2.0安装和Percona Cluster5.7集成

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:ProxySQL 2.0安装和Percona Cluster5.7集成
ProxySQL是MySQL的一款中间件产品,是灵活和强大的MySQL代理层,它可以实现像查询缓存,多路路由(multiplexing),镜像,读/写分离,路由等。在ProxySQL的早期版本中并不能原生支持Galera支持,需要通过schedule调度脚本实现。但是在2.0版本后,就增加了原生支持,只需要配置好参数表即可。

首先我们来安装它来控制我们安装好的PXC集群。

IP地址 主机名
192.168.56.161 pxc1
192.168.56.162 pxc2
192.168.56.163 pxc3
192.168.56.160 proxysql

1.添加yum源

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

2.使用yum进行安装

yum install proxysql

3.启动proxysql服务

[root@localhost ~]# service proxysql start
Starting ProxySQL: 2019-07-02 14:03:13 [INFO] Using config file /etc/proxysql.cnf
2019-07-02 14:03:13 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
DONE!

4.在PXC集群中安装视图
参考上一篇文章:传送门
 
5.连接管理端

[root@localhost /]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
bash: mysql: 未找到命令...

这里报错是因为没有安装mysql客户端。随便安装一个mysql客户端就行了。这里直接安装pxc,也可以安装其他MySQL产品。

yum install Percona-XtraDB-Cluster-57

安装好之后再次运行就可以登录了

[root@localhost /]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

这里自带了5个数据库.
main库:内存配置数据库,即memory,表里面存放后端db实例、用户验证、路由规则等信息。
disk库:持久化磁盘的配置。
stats库:统计信息的汇总。
monitor库:一些监控的收集信息,包括数据库的健康状态等。
stats_history 统计信息历史库

5.添加pxc信息到ProxySQL的mysql_servers 表

admin> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
24 rows in set (0.00 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values
-> (10,'192.168.56.161',3306),
-> (10,'192.168.56.162',3306),
-> (10,'192.168.56.163',3306);
Query OK, 3 rows affected (0.00 sec)

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.56.161',3306,100);
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.56.162',3306,10);
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (3,'192.168.56.163',3306,100);

增加galera的配置

INSERT INTO mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind)
VALUES (2,4,3,1,1,1,0,100);

load mysql servers to runtime;
save mysql servers to disk;

之后可以查看三台机器的状态。

Admin> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 192.168.56.161 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.56.162 | 3306 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
| 3 | 192.168.56.163 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)

Admin> select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 2 | 4 | 3 | 1 | 1 | 1 | 0 | 100 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

可以看到,我们通过插入数据,配置了Galera集群的一些设置。192.168.56.161和192.168.56.162属于组2,而192.168.56.163属于组3。而组2是writer_hostgroup,组3是reader_hostgroup。而在我们的组2中,192.168.56.161的权重是大于192.168.56.162的,因为161的weight是100,而162的weight是10。这里我们还设置了max_writers为1,也就是最多只有一个提供写入。而一旦该写入组出现故障,就会切换到备用的写入组backup_writer_hostgroup,也就是组4。

我们现在来看一下整个集群的连接情况。因为暂时没有连接。所以现在把192.168.56.163设置是写入组,而192.168.56.161和192.168.56.162设置的是备份写入组。

Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+--------+----------+-------------+---------+------------+
| hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us |
+-----------+----------------+--------+----------+-------------+---------+------------+
| 4 | 192.168.56.161 | ONLINE | 0 | 0 | 0 | 1226 |
| 4 | 192.168.56.162 | ONLINE | 0 | 0 | 0 | 1117 |
| 2 | 192.168.56.163 | ONLINE | 0 | 0 | 0 | 1160 |
+-----------+----------------+--------+----------+-------------+---------+------------+
3 rows in set (0.01 sec

当前的状态和我们插入到mysql_server表的设置不符。但是也符合当前的配置,为什么这么说呢?因为PXC默认三个节点是可写的,而163的延迟最低,所以他默认就把3当做了主要写入的节点。

6.创建监控用户
在pxc集群中的任意一个主机上创建监控用户,并赋予权限;

create user monitor@'192.168.56.%' identified by 'monitor';
grant all privileges on *.* to monitor@'192.168.56.%';

在pxc上创建完账号之后就回到ProxySQL节点上配置

UPDATE global_variables SET variable_value='monitor' where variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password';

load mysql variables to runtime;
save mysql variables to disk;

之后就可以验证监控信息:

Admin> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.56.162 | 3306 | 1562313405719594 | 4853 | NULL |
| 192.168.56.161 | 3306 | 1562313405093564 | 2650 | NULL |
| 192.168.56.163 | 3306 | 1562313404467721 | 5336 | NULL |
| 192.168.56.161 | 3306 | 1562313345470323 | 1261 | NULL |
| 192.168.56.163 | 3306 | 1562313344968944 | 3461 | NULL |
| 192.168.56.162 | 3306 | 1562313344467233 | 2646 | NULL |
+----------------+------+------------------+-------------------------+---------------+
6 rows in set (0.00 sec)

Admin> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.56.163 | 3306 | 1562313454473362 | 847 | NULL |
| 192.168.56.161 | 3306 | 1562313454363177 | 900 | NULL |
| 192.168.56.162 | 3306 | 1562313454253065 | 1398 | NULL |
| 192.168.56.161 | 3306 | 1562313444460262 | 1114 | NULL |
| 192.168.56.163 | 3306 | 1562313444356808 | 2072 | NULL |
| 192.168.56.162 | 3306 | 1562313444253269 | 1643 | NULL |
+----------------+------+------------------+----------------------+------------+
6 rows in set (0.00 sec)

7.设置ProxySQL,连接后端使用的PXC的用户,配置mysql_uses表。
在pxc集群的任意节点上创建一个连接用户。这里使用root用户来测试。

mysql> create user 'buddy'@'%' identified by 'buddy';
Query OK, 0 rows affected (0.03 sec)
mysql> grant all on *.* to 'buddy'@'%';
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

然后回到ProxySQL,配置mysql_users表,将刚才的用户添加到表中。

admin> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('buddy','buddy',2,1);
Query OK, 1 row affected (0.00 sec)

Admin> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| buddy | buddy | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)

admin> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
admin> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)

9.接下来就可以连接pxc集群了
通过6033端口进行连接

[root@localhost ~]# mysql -ubuddy -pbuddy -h 127.0.0.1 -P 6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.26 (ProxySQL)

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| pxc3 |
+------------+
1 row in set (0.01 sec)

配置ProxySQL出现“OFFLINE_HARD”的解决办法

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:配置ProxySQL出现“OFFLINE_HARD”的解决办法
这两天在做ProxySQL的一系列实验,不过在实验中遇到一个小问题。当我查询runtime_mysql_servers或者是stats.stats_mysql_connection_pool视图的时候,经常发现配置PXC主机status处于”OFFLINE_HARD”状态。这个状态的含义如下,硬离线”状态,不再接受新的连接,已建立的连接或被强制中断。当后端实例宕机或网络不可达会出现这种状态。但是我通过PXC集群连接进去发现集群的状态都是正常的。

[root@localhost proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26 (ProxySQL Admin Module)

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+--------------+----------+-------------+---------+------------+
| hostgroup | srv_host       | status       | ConnUsed | MaxConnUsed | Queries | Latency_us |
+-----------+----------------+--------------+----------+-------------+---------+------------+
| 4         | 192.168.56.161 | ONLINE       | 0        | 0           | 0       | 1362       |
| 4         | 192.168.56.162 | ONLINE       | 0        | 0           | 0       | 1608       |
| 2         | 192.168.56.163 | ONLINE       | 0        | 0           | 0       | 969        |
| 3         | 192.168.56.163 | OFFLINE_HARD | 0        | 0           | 0       | 969        |
+-----------+----------------+--------------+----------+-------------+---------+------------+

针对这个问题,我们要做的首先是检查proxysql.log日志,该日志位于/var/lib/proxysql下面。

[root@localhost proxysql]# grep -i OFFLINE proxysql.log | grep because | cut -b 124- | sort | uniq -c
      1 setting host 192.168.56.161:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist
      1 setting host 192.168.56.162:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist
      1 setting host 192.168.56.163:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist

这里说明了offline问题的原因,是因为有个表不存在。叫sys.gr_member_routing_candidate_status,这个视图额外提供了一些复制组成员的信息。这些信息需要被ProxySQL使用。这个脚本需要下载。下载地址如下:传送门

[root@pxc1 mysql]# mysql -p < addition_to_sys.sql 
Enter password:

安装完成之后再次执行相关查询,OFFLINE_HARD的状态已经消失了。不过我发现需要先查询。runtime_mysql_servers状态再次查询stats.stats_mysql_connection_pool才会消失。

Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+--------+----------+-------------+---------+------------+
| hostgroup | srv_host       | status | ConnUsed | MaxConnUsed | Queries | Latency_us |
+-----------+----------------+--------+----------+-------------+---------+------------+
| 4         | 192.168.56.161 | ONLINE | 0        | 0           | 0       | 1016       |
| 4         | 192.168.56.162 | ONLINE | 0        | 0           | 0       | 1003       |
| 2         | 192.168.56.163 | ONLINE | 0        | 0           | 0       | 1041       |
+-----------+----------------+--------+----------+-------------+---------+------------+
3 rows in set (0.00 sec)

强行关机之后,Percona XtraDB Cluster集群无法启动

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:强行关机之后,Percona XtraDB Cluster集群无法启动

事情是这样的,忙着下班,就匆匆忙忙的暴力的把安装的三台PXC集群虚拟机关闭了。回家在打开的时候,怎么也开不了。直接运行启动服务的命令是报错的。

[root@10 mysqld]# systemctl start mysql@bootstrap.service
Job for mysql@bootstrap.service failed because the control process exited with error code. See "systemctl status mysql@bootstrap.service" and "journalctl -xe" for details.

通过报错的提示去查看相关的日志

[root@10 mysqld]# systemctl status mysql@bootstrap.service
● mysql@bootstrap.service - Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap
Loaded: loaded (/usr/lib/systemd/system/mysql@.service; disabled; vendor preset: disabled)
Active: failed (Result: exit-code) since 二 2019-07-02 20:57:54 CST; 2min 1s ago
Process: 4896 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
Process: 4867 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=2)
Process: 4296 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=1/FAILURE)
Process: 4295 ExecStart=/usr/bin/mysqld_safe --basedir=/usr ${EXTRA_ARGS} (code=exited, status=1/FAILURE)
Process: 4255 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
Main PID: 4295 (code=exited, status=1/FAILURE)

7月 02 20:57:54 10.0.2.15 mysql-systemd[4296]: ERROR! mysqld_safe with PID 4295 has already exited: FAILURE
7月 02 20:57:54 10.0.2.15 systemd[1]: mysql@bootstrap.service: control process exited, code=exited status=1
7月 02 20:57:54 10.0.2.15 mysql-systemd[4867]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
7月 02 20:57:54 10.0.2.15 mysql-systemd[4867]: ERROR! mysql already dead
7月 02 20:57:54 10.0.2.15 systemd[1]: mysql@bootstrap.service: control process exited, code=exited status=2
7月 02 20:57:54 10.0.2.15 mysql-systemd[4896]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
7月 02 20:57:54 10.0.2.15 mysql-systemd[4896]: WARNING: mysql may be already dead
7月 02 20:57:54 10.0.2.15 systemd[1]: Failed to start Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap.
7月 02 20:57:54 10.0.2.15 systemd[1]: Unit mysql@bootstrap.service entered failed state.
7月 02 20:57:54 10.0.2.15 systemd[1]: mysql@bootstrap.service failed.
[root@10 mysqld]# systemctl start mysql@bootstrap.service
Job for mysql@bootstrap.service failed because the control process exited with error code. See "systemctl status mysql@bootstrap.service" and "journalctl -xe" for details.

这些错误信息看不出什么东西来。于是我进一步检查了MySQL的error日志。通过查看error日志发现一些信息。

2019-07-02T13:00:21.399956Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-07-02T13:00:21.400952Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.26-29-57-log) starting as process 7045 ...
2019-07-02T13:00:21.402588Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=10-bin' to avoid this problem.
2019-07-02T13:00:21.402718Z 0 [Note] WSREP: Setting wsrep_ready to false
2019-07-02T13:00:21.402729Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2019-07-02T13:00:21.402732Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera3/libgalera_smm.so'
2019-07-02T13:00:21.404735Z 0 [Note] WSREP: wsrep_load(): Galera 3.37(rff05089) by Codership Oy <info@codership.com> loaded successfully.
2019-07-02T13:00:21.404791Z 0 [Note] WSREP: CRC-32C: using hardware acceleration.
2019-07-02T13:00:21.405060Z 0 [Note] WSREP: Found saved state: 428f9095-9980-11e9-b8b6-1322440f5dbe:14, safe_to_bootstrap: 0
2019-07-02T13:00:21.406406Z 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.56.161; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; 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.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 9; socket.checksum = 2; socket.recv_buf_size = 212992;
2019-07-02T13:00:21.413402Z 0 [Note] WSREP: GCache history reset: 428f9095-9980-11e9-b8b6-1322440f5dbe:0 -> 428f9095-9980-11e9-b8b6-1322440f5dbe:14
2019-07-02T13:00:21.414049Z 0 [Note] WSREP: Assign initial position for certification: 14, protocol version: -1
2019-07-02T13:00:21.414070Z 0 [Note] WSREP: Preparing to initiate SST/IST
2019-07-02T13:00:21.414072Z 0 [Note] WSREP: Starting replication
2019-07-02T13:00:21.414081Z 0 [Note] WSREP: Setting initial position to 428f9095-9980-11e9-b8b6-1322440f5dbe:14
2019-07-02T13:00:21.414085Z 0 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
2019-07-02T13:00:21.414088Z 0 [ERROR] WSREP: Provider/Node (gcomm://192.168.56.161,192.168.56.162,192.168.56.163) failed to establish connection with cluster (reason: 7)
2019-07-02T13:00:21.414093Z 0 [ERROR] Aborting

2019-07-02T13:00:21.414096Z 0 [Note] Giving 0 client threads a chance to die gracefully
2019-07-02T13:00:21.414100Z 0 [Note] WSREP: Waiting for active wsrep applier to exit
2019-07-02T13:00:21.414104Z 0 [Note] WSREP: Service disconnected.
2019-07-02T13:00:21.414105Z 0 [Note] WSREP: Waiting to close threads......
2019-07-02T13:00:26.414590Z 0 [Note] WSREP: Some threads may fail to exit.
2019-07-02T13:00:26.414701Z 0 [Note] Binlog end
2019-07-02T13:00:26.414959Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

这里主要是这个地方启动出现了error。这段话翻译过来意思就是:从这个节点引导集群可能不安全。它不是最后一个离开集群的,可能不包含所有更新。要强制使用此节点进行群集引导,请手动编辑grastate.dat文件,并将safe_to_bootstrap参数设置为1。所以这里我想可以尝试使用最后一个离开集群的来引导集群。当然咱们也可以修改这个文件,把这个文件的safe_to_bootstrap修改成1,但是还是不推荐这么干啊。

[root@10 /]# vi /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid: 428f9095-9980-11e9-b8b6-1322440f5dbe
seqno: 14
safe_to_bootstrap: 0

通过对比三个节点的这个文件,发现节点3的这个文件的值为1。于是使用节点3来引导集群,顺利引导成功。

[root@10 ~]# systemctl start mysql@bootstrap.service

CentOS 7安装Percona XtraDB Cluster 5.7

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:CentOS 7安装Percona XtraDB Cluster 5.7
很久没写文章了,主要一个原因是从年初开始就逐步转入到开源阵营了。上半年是一个逐步摸索的过程。我现在主要是学习MySQL、PostgreSQL、Redis、Kafka等等,学的东西很多很杂。今天是下半年第一天,准备把自己学的MySQL相关的东西做个记录吧。那么首先我就来写写Percona XtraDB Cluster的安装。它基于Galera的协议。
说实话,在很久以前,我学习RAC的时候,同事给了我一份RAC安装文档,结果我安装了3天都没安装上,那几天几夜搞的我没睡好,精神压力还很大。后面研究了很久才发现同事给的文档是阉割版的文档,那种根本就装不了的。所以后来我安装喜欢参考的是官方文档,PXC安装我参考的文档是Percona XtraDB Cluster Documentation Release 5.7.26-31.37
安装之前,我们先来初步看一下架构吧。如图所示,PXC是一个组复制的架构,图中有三个节点,这三个节点都是可以对外提供读和写的。客户端写入和读取数据时,连接哪个实例都是一样的,读取到的数据都是相同的,写入任意一个实例之后,集群自己会将新写入的数据同步到其他实例上。

感觉很酷啊,让我们开始吧。
PXC安装,首先要准备三台虚拟机,IP配置如下所示。

192.168.56.161 node1
192.168.56.162 node2
192.168.56.163 node3
1.安装之前要保证三台机器的防火墙和selinux都要做设置。

确保以下端口未被防火墙阻止或未被其他软件使用。PXC集群需要它们进行通信。

  • 3306 数据库服务的端口号
  • 4444请求SST的端口,SST是指数据库一个备份全量文件的传输
  • 4567 组成员之间进行沟通的一个端口号
  • 4568 用于传输IST(相对SST来说的一个增量)

为了实验的简洁性,我这里直接关闭防火墙。当然最好是防火墙打开,并相对应端口放开限制。

systemctl stop firewalld.service            #停止firewall
systemctl disable firewalld.service        #禁止firewall开机启动

修改/etc/selinux/config
设置SELINUX=permissive
2.创建MySQL用户。
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
3.安装Percona Repository
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

安装了源才可以方便下载Percona软件包。

4.安装Percona XtraDB Cluster 5.7相关软件
yum install Percona-XtraDB-Cluster-57

这一步会把Percona XtraDB Cluster需要的软件包全部安装上。

5.启动数据库并修改密码,修改完成停止服务。
[root@pxc1 ~]# service mysql start
Redirecting to /bin/systemctl start mysql.service
找到root密码,登录修改密码
grep 'temporary password' /var/log/mysqld.log
mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
停止mysql服务
service mysql stop
6.配置pxc,增加相关参数
vi /etc/my.cnf

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.56.161,192.168.56.162,192.168.56.163
wsrep_node_name=pxc1
wsrep_node_address=192.168.56.161
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

这些参数的含义如下:
wsrep_cluster_name   ##标识该集群的名字
wsrep_cluster_address=gcomm:  ##列出集群中的成员
wsrep_node_address   ##当前节点的IP地址
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so   ##指定Galera库的路径和文件名
wsrep_sst_method=xtrabackup-v2         ##传输数据的方法,现在都推荐使用xtrabackup-v2方式
wsrep_sst_auth=sstuser:passw0rd          ##节点使用SST来同步的数据库用户账号和密码
pxc_strict_mode                                    ##PXC严格模式旨在避免在Percona XtraDB群集中使用实验性和不受支持的功能。它在启动时和运行时执行一些验证。推荐设置成ENFORCING
binlog_format                                       ##Galera只支持row级别的复制
default_storage_engine                        ## Galera只支持InnoDB存储引擎
innodb_autoinc_lock_mode                ##Galera仅支持InnoDB的交错锁模式,也就是2。设置传统模式(0)或连续锁定模式(1)可能会导致复制由于未解决的死锁而失败。

7.节点1启动
systemctl start mysql@bootstrap.service

需要注意的是centos 7需要使用systemctl命令来启动,并在后面跟上@bootstrap.service,启动完成后可以查看服务的状态。

[root@pxc1 init.d]# systemctl status mysql@bootstrap.service
● mysql@bootstrap.service - Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap
   Loaded: loaded (/usr/lib/systemd/system/mysql@.service; disabled; vendor preset: disabled)
   Active: active (running) since 一 2019-07-01 15:53:37 CST; 8s ago
  Process: 16985 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS)
  Process: 16944 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
 Main PID: 16984 (mysqld_safe)
   CGroup: /system.slice/system-mysql.slice/mysql@bootstrap.service
           ├─16984 /bin/sh /usr/bin/mysqld_safe --basedir=/usr  --wsrep-new-cluster
           └─17545 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera3/libgaler...

7月 01 15:53:27 pxc1.localdomain systemd[1]: Starting Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap...
7月 01 15:53:27 pxc1.localdomain mysql-systemd[16985]: State transfer in progress, setting sleep higher
7月 01 15:53:27 pxc1.localdomain mysqld_safe[16984]: 2019-07-01T07:53:27.913874Z mysqld_safe Logging to '/var/log/mysqld.log'.
7月 01 15:53:27 pxc1.localdomain mysqld_safe[16984]: 2019-07-01T07:53:27.916617Z mysqld_safe Logging to '/var/log/mysqld.log'.
7月 01 15:53:27 pxc1.localdomain mysqld_safe[16984]: 2019-07-01T07:53:27.938342Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
7月 01 15:53:27 pxc1.localdomain mysqld_safe[16984]: 2019-07-01T07:53:27.948292Z mysqld_safe Skipping wsrep-recover for 428f9095-9980-11e9-b8b6-1322440f5dbe:7 pair
7月 01 15:53:27 pxc1.localdomain mysqld_safe[16984]: 2019-07-01T07:53:27.949778Z mysqld_safe Assigning 428f9095-9980-11e9-b8b6-1322440f5dbe:7 to wsrep_start_position
7月 01 15:53:37 pxc1.localdomain mysql-systemd[16985]: SUCCESS!
7月 01 15:53:37 pxc1.localdomain systemd[1]: Started Percona XtraDB Cluster with config /etc/sysconfig/mysql.bootstrap.
8.创建SST用户
mysql> create user 'sstuser'@'localhost' identified by 'passw0rd';
Query OK, 0 rows affected (0.01 sec)
mysql> grant reload,lock tables,process,replication client on *.* to 'sstuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
9.查看集群的状态

此时可以查看集群的状态。

mysql> show status like 'wsrep%';
+----------------------------------+-----------------------------------------+
| Variable_name                    | Value                                   |
+----------------------------------+-----------------------------------------+
| wsrep_local_state_uuid           | 428f9095-9980-11e9-b8b6-1322440f5dbe    |
| wsrep_local_state                | 4                                       |
| wsrep_local_state_comment        | Synced                                  |
| wsrep_cluster_size               | 1                                       |
| wsrep_cluster_status             | Primary                                 |
| wsrep_connected                  | ON                                      |
| wsrep_ready                      | ON                                      |
+----------------------------------+-----------------------------------------+

这里说明下这些参数的含义:
wsrep_local_state_uuid:集群中所有的值都应该是相同的,如果有不同的值,而说明这个节点没有连接到集群,我们观察三个节点的值都是428f9095-9980-11e9-b8b6-1322440f5dbe
wsrep_local_state: 该值为4表示正常,节点状态有4个值。和下面wsrep_local_state_comment参数相呼应。
wsrep_local_state_comment:
joining —表示节点正在加入集群
donori —表示当前节点是数据奉献者,正在为新加入的节点同步数据
joined —当前节点已经成功加入集群
synced —当前节点与整个集群是同步状态
wsrep_ready 当为on的时候表示节点可以正常服务,如果值为off,则该节点可能发生脑裂或者是网络问题。
wsrep_cluster_status:集群组成的状态,如果不是”primary”,说明出现“分区”或者“脑裂”现象。
wsrep_cluster_size 这个值代表节点数,如果这个值和预期的节点数是一致的,则说明所有的集群节点已经连接。

10.接下来是节点2的操作

节点2和上面的操作类似,需要注意的地方主要有两点:
a.参数配置需要修改,节点名称和节点的IP要更换一下。
wsrep_node_name=pxc2
wsrep_node_address=192.168.56.162
b.配置完成启动的命令使用,不要像节点1一样增加参数。
systemctl start mysql

11.查看节点2的状态

当你配置好参数,通过服务启动之后,会自动和节点1进行同步,此时的root密码还有SST用户都会同步更新,不需要在做修改密码操作。

mysql> show status like 'wsrep%';
+----------------------------------+-----------------------------------------+
| Variable_name                    | Value                                   |
+----------------------------------+-----------------------------------------+
| wsrep_local_state_uuid           | 428f9095-9980-11e9-b8b6-1322440f5dbe    |
| wsrep_local_state                | 4                                       |
| wsrep_local_state_comment        | Synced                                  |
| wsrep_incoming_addresses         | 192.168.56.162:3306,192.168.56.161:3306 |
| wsrep_cluster_size               | 2  =》可以看到集群节点数这里变成了2          |
| wsrep_cluster_status             | Primary                                 |
| wsrep_connected                  | ON                                      |
| wsrep_ready                      | ON                                      |
+----------------------------------+-----------------------------------------+
12.配置节点3

节点3和节点2的操作类似,需要注意的地方主要有两点:
a.参数配置需要修改,节点名称和节点的IP要更换一下。
wsrep_node_name=pxc2
wsrep_node_address=192.168.56.162
b.配置完成启动的命令使用,不要像节点1一样增加参数。
systemctl start mysql

12.查看节点3的状态
mysql> show status like 'wsrep%';
+----------------------------------+-------------------------------------------------------------+
| Variable_name                    | Value                                                       |
+----------------------------------+-------------------------------------------------------------+
| wsrep_local_state_uuid           | 428f9095-9980-11e9-b8b6-1322440f5dbe                        |
| wsrep_local_state                | 4                                                           |
| wsrep_local_state_comment        | Synced                                                      |
| wsrep_incoming_addresses         | 192.168.56.162:3306,192.168.56.161:3306,192.168.56.163:3306 |
| wsrep_cluster_size               | 3  =》可以看到集群节点数这里变成了3                              |
| wsrep_cluster_status             | Primary                                                     |
| wsrep_connected                  | ON                                                          |
| wsrep_ready                      | ON                                                          |
+----------------------------------+-------------------------------------------------------------+
71 rows in set (0.01 sec)
13.当集群配置好之后,可以进行验证verify

a.首先在2节点创建数据库.

create database pxcdb;

b.然后在3节点进入新的数据库,创建一个测试表.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pxcdb              |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

mysql> use pxcdb;
Database changed
mysql>
mysql> create table t1(node_id int primary key,node_name varchar(30));
Query OK, 0 rows affected (0.05 sec)

c.最后在1节点插入一些数据.

mysql> use pxcdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into t1 values(1,'pxc1');
Query OK, 1 row affected (0.02 sec)

d.然后在2节点上验证数据

mysql> select * from pxcdb.t1;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | pxc1      |
+---------+-----------+
1 row in set (0.00 sec)

结尾语,整个安装过程还算比较顺利,比安装RAC简单多了。下一步将要学习在上面搭建Proxy SQL。

记一次ora.asm服务无法启动和GPnP Get Item的问题

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:记一次ora.asm服务无法启动和GPnP Get Item的问题
昨天晚上去打DB PSU,这是一套11.2.0.3 RAC。很快我就把数据库和集群全部停止,然后把补丁安装完了。我心想还是蛮顺利的啊,结果在重启集群的时候,突然发现集群没办法起来。通过crsctl check crs可以发现无法启动crsd的服务,而我去查看crsd.log的时候发现根本没有任何记录。一直是空的。然后我通过ocrcheck命令检查会一直hang住,执行crsctl query css votedisk确是好的,很快都能查到。然后我又执行了kfed去读取asm磁盘,发现也是能读的。通过进一步检查,发现是ora.asm服务启动不成功。进一步登陆到asm实例上查看asm的alert日志,发现能读取spfile文件,并且实例是能正常启动的。我通过查看asm的日志发现,磁盘组并没有走到最后一步mount成功的地方。如下所示:

SQL> ALTER DISKGROUP ALL MOUNT /* asm agent call crs *//* {0:0:2} */ 
NOTE: Diskgroups listed in ASM_DISKGROUPS are
 	 DG_FRA
 	 DG_DATA
NOTE: Diskgroup used for Voting files is:
 	 DG_OCR
Diskgroup with spfile:DG_OCR
Diskgroup used for OCR is:DG_OCR
NOTE: cache registered group DG_DATA number=1 incarn=0x50ec3256
NOTE: cache began mount (not first) of group DG_DATA number=1 incarn=0x50ec3256
NOTE: cache registered group DG_FRA number=2 incarn=0x50fc3257
NOTE: cache began mount (not first) of group DG_FRA number=2 incarn=0x50fc3257
NOTE: cache registered group DG_OCR number=3 incarn=0x50fc3258
NOTE: cache began mount (not first) of group DG_OCR number=3 incarn=0x50fc3258
NOTE: Assigning number (1,3) to disk (/dev/diskgroup/dg_data4)
NOTE: Assigning number (1,2) to disk (/dev/diskgroup/dg_data3)
NOTE: Assigning number (1,1) to disk (/dev/diskgroup/dg_data2)
NOTE: Assigning number (1,0) to disk (/dev/diskgroup/dg_data1)
NOTE: Assigning number (2,0) to disk (/dev/diskgroup/dg_fra)
NOTE: Assigning number (3,0) to disk (/dev/diskgroup/dg_ocr)
GMON querying group 1 at 4 for pid 23, osid 128640
NOTE: cache opening disk 0 of grp 1: DG_DATA_0000 path:/dev/diskgroup/dg_data1
NOTE: F1X0 found on disk 0 au 2 fcn 0.0
NOTE: cache opening disk 1 of grp 1: DG_DATA_0001 path:/dev/diskgroup/dg_data2
NOTE: cache opening disk 2 of grp 1: DG_DATA_0002 path:/dev/diskgroup/dg_data3
NOTE: cache opening disk 3 of grp 1: DG_DATA_0003 path:/dev/diskgroup/dg_data4
NOTE: cache mounting (not first) external redundancy group 1/0x50EC3256 (DG_DATA)

可以看到日志前面都是正常能够读盘的,中间做了一些其他的动作,在最后面出现下列错误:

NOTE: detached from domain 3
NOTE: cache dismounted group 3/0x50FC3258 (DG_OCR) 
NOTE: cache ending mount (fail) of group DG_OCR number=3 incarn=0x50fc3258
NOTE: cache deleting context for group DG_OCR 3/0x50fc3258
GMON dismounting group 3 at 9 for pid 23, osid 128640
NOTE: Disk  in mode 0x8 marked for de-assignment
ERROR: diskgroup DG_OCR was not mounted

接下来我通过asm实例进入到数据库查了一下,在正常实例的磁盘组是mounted状态,而异常的数据库实例里面磁盘组的状态是mounting状态。也就是正在mount的状态。这里的一个疑点就是怎么mount不上。
为了把这个疑点弄清楚,我做了一个全局的hang分析。这里必须要做全局的才能知道磁盘组为什么无法mount。

*** 2018-10-26 04:36:34.280
===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): +asm.+asm2, +asm.+asm1
  oradebug_node_dump_level: 3
  analysis initiated by oradebug
  os thread scheduling delay history: (sampling every 1.000000 secs)
    0.000000 secs at [ 04:36:34 ]
      NOTE: scheduling delay has not been sampled for 0.184529 secs    0.000000 secs from [ 04:36:30 - 04:36:35 ], 5 sec avg
    0.000000 secs from [ 04:35:34 - 04:36:35 ], 1 min avg
    0.000000 secs from [ 04:31:34 - 04:36:35 ], 5 min avg
===============================================================================
 
Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'GPnP Get Item'<='DFS lock handle'
     Chain 1 Signature Hash: 0xe08712d6
 [b] Chain 2 Signature: 'GPnP Get Item'<='rdbms ipc reply'<='enq: DD - contention'
     Chain 2 Signature Hash: 0xec6b634f
 [c] Chain 3 Signature: 'GPnP Get Item'<='rdbms ipc reply'<='enq: DD - contention'
     Chain 3 Signature Hash: 0xec6b634f
 
===============================================================================
Non-intersecting chains:
 
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 2 (+asm.+asm2)
                   os id: 88049
              process id: 23, oracle@KSPX-DB2 (TNS V1-V3)
              session id: 1427
        session serial #: 3
    }
    is waiting for 'DFS lock handle' with wait info:
    {
                      p1: 'type|mode'=0x43490005
                      p2: 'id1'=0x3c
                      p3: 'id2'=0x2
            time in wait: 49 min 17 sec
           timeout after: never
                 wait id: 107
                blocking: 0 sessions
             current sql: ALTER DISKGROUP ALL MOUNT /* asm agent call crs *//* {0:0:2} */
             short stack: ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-kjusuc()+3619<-ksigeti()+3781<-ksbcic_int()+21066<-ksbcic()+12<-kfgbSendPeelWait()+53<-kfgFinalizeMount()+4736<-kfgscFinalize()+1405<-kfgForEachKfgsc()+193<-kfgsoFinalize()+135<-kfgFinalize()+396<-kfxdrvMount()+4781<-kfxdrvEntry()+2218<-opiexe()+20368<-opiosq0()+2948<-kpooprx()+274<-kpoal8()+829<-opiodr()+916<-ttcpip()+2242<-opitsk()+1673<-opiino()+966<-opiodr()+916<-opidr wait history: * time between current wait and wait #1: 0.000149 sec 1. event: 'rdbms ipc reply' time waited: 0.000338 sec wait id: 106 p1: 'from_process'=0x12 p2: 'timeout'=0x7fffffff * time between wait #1 and #2: 0.000054 sec 2. event: 'kfk: async disk IO' time waited: 0.000182 sec wait id: 105 p1: 'count'=0x1 p2: 'intr'=0x0 p3: 'timeout'=0xffffffff * time between wait #2 and #3: 0.000157 sec 3. event: 'rdbms ipc reply' time waited: 0.000372 sec wait id: 104 p1: 'from_process'=0x12 p2: 'timeout'=0x7fffffff } and is blocked by => Oracle session identified by:
    {
                instance: 1 (+asm.+asm1)
                   os id: 15643
              process id: 18, oracle@KSPX-DB1 (RBAL)
              session id: 1117
        session serial #: 1
    }
    which is waiting for 'GPnP Get Item' with wait info:
    {
            time in wait: 221831 min 33 sec
           timeout after: never
                 wait id: 124844244
                blocking: 44 sessions
             current sql: 
             short stack: ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-__poll()+47<-sgipcwWaitHelper()+5271<-sgipcwWait()+475<-gipcWaitOsd()+281<-gipcInternalWait()+14965<-gipcWaitF()+3234<-gipcInternalRecvSync()+8749<-gipcRecvSyncF()+3343<-clsgpnpm_gipcGets()+297<-clsgpnpm_receiveMsg()+408<-clsgpnpm_exchange()+1059<-clsgpnp_profileCallUrlInt()+3568<-clsgpnp_getProfileEx()+325<-clsgpnp_dbmsGetItem()+263<-kggpnpAttrGet()+1272<-kfdParseProfileString()+885<-kfdDiscoverShallow()+2023<-kfgbDriver()+1745<-ksbabs()+
            wait history:
              * time between current wait and wait #1: 0.000007 sec
              1.       event: 'GPnP Initialization'
                 time waited: 0.018700 sec
                     wait id: 124844243       
              * time between wait #1 and #2: 0.000207 sec
              2.       event: 'CSS initialization'
                 time waited: 0.000027 sec
                     wait id: 124844242       
              * time between wait #2 and #3: 0.000062 sec
              3.       event: 'rdbms ipc message'
                 time waited: 0.002787 sec
                     wait id: 124844241       p1: 'timeout'=0x12c
    }

通过全局的hang分析,我们可以发现,在节点2 asm启动之后,运行了”ALTER DISKGROUP ALL MOUNT”的命令,而现在这个命令没有执行成功,被阻塞了40多分钟。它是被节点1的rbal进程阻塞的,而该进程正在等待一个叫 “GPnP Get Item”的等待事件。那么既然我们知道问题的原因了,我们就可以开始在mos上查找相关资料了。通过查看文档Diskgroup Mount Hangs with RBAL Waiting on ‘GPnP Get Item’ and ‘enq: DD – contention’ (文档 ID 1375505.1),发现这个问题是这是由未发布的bug:12398300引起的。这个问题的最根本原因就是gpnp进程stuck了,然后就会导致rbal进程产生等待。解决办法其实很简单,就是干掉节点1的gpnp进程就会释放。而干掉gpnp进程集群不会重启,它只会单独的把该进程重启。
通过kill gpnp之后,故障恢复,节点2的集群顺利启动。
这里有一个技巧,以后针对ora.asm服务无法启动的问题,其实都可以使用全局的hang分析。

PostgreSQL识别阻塞会话

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:PostgreSQL识别阻塞会话
在Oracle当中,一般使用v$session中的获取阻塞信息(blocking_session),而在PostgreSQL中,我们来看看怎么识别阻塞会话。我们首先来做个测试。

postgres=# create table t1(id int);
CREATE TABLE

现在我们来给表增加一个字段,我们使用begin来开始事务,但是我们不用end块来结束。

postgres=# begin;
BEGIN
postgres=# alter table t1 add column name text;
ALTER TABLE

此时在另外一个节点执行插入就会hang住,一直等待,因为修改的DDL语句的事务仍然没有结束。

postgres=# insert into t1(id) values(1);

我们可以使用postgresql中的pg_stat_activity视图,可以看到它并不像oracle中的v$session视图,有blocking_session字段。而pg是通过backend_type来实现的。

postgres=# \d pg_stat_activity
                      View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
 datid            | oid                      |           |          |
 datname          | name                     |           |          |
 pid              | integer                  |           |          |
 usesysid         | oid                      |           |          |
 usename          | name                     |           |          |
 application_name | text                     |           |          |
 client_addr      | inet                     |           |          |
 client_hostname  | text                     |           |          |
 client_port      | integer                  |           |          |
 backend_start    | timestamp with time zone |           |          |
 xact_start       | timestamp with time zone |           |          |
 query_start      | timestamp with time zone |           |          |
 state_change     | timestamp with time zone |           |          |
 wait_event_type  | text                     |           |          |
 wait_event       | text                     |           |          |
 state            | text                     |           |          |
 backend_xid      | xid                      |           |          |
 backend_xmin     | xid                      |           |          |
 query            | text                     |           |          |
 backend_type     | text                     |           |          |


postgres=# select datname,pid,usename,wait_event_type,wait_event,state,query from pg_stat_activity where backend_type = 'client backend'
and pid != pg_backend_pid();
 datname  | pid  | usename  | wait_event_type | wait_event |        state        |                query
----------+------+----------+-----------------+------------+---------------------+--------------------------------------
 postgres | 3231 | postgres | Client          | ClientRead | idle in transaction | alter table t1 add column name text;
 postgres | 3386 | postgres | Lock            | relation   | active              | insert into t1(id) values(1);

(2 rows)

我们可以很容易看到阻塞的对象和他们执行的SQL语句。当然这个是会话数比较少的情况,当会话数比较多,通过查看pg_stat_activity来识别阻塞的会话会变得很棘手。

当您想知道PostgreSQL中当前持有或者是授权那些锁的时候,可以查看pg_locks,主要看RowExclusiveLock和AccessExclusiveLock。

postgres=# \d pg_locks
                   View "pg_catalog.pg_locks"
       Column       |   Type   | Collation | Nullable | Default
--------------------+----------+-----------+----------+---------
 locktype           | text     |           |          |
 database           | oid      |           |          |
 relation           | oid      |           |          |
 page               | integer  |           |          |
 tuple              | smallint |           |          |
 virtualxid         | text     |           |          |
 transactionid      | xid      |           |          |
 classid            | oid      |           |          |
 objid              | oid      |           |          |
 objsubid           | smallint |           |          |
 virtualtransaction | text     |           |          |
 pid                | integer  |           |          |
 mode               | text     |           |          |
 granted            | boolean  |           |          |
 fastpath           | boolean  |           |          |

postgres=# select locktype,database,relation,pid,mode,granted from pg_locks where pid != pg_backend_pid();
   locktype    | database | relation | pid  |        mode         | granted
---------------+----------+----------+------+---------------------+---------
 virtualxid    |          |          | 3386 | ExclusiveLock       | t
 virtualxid    |          |          | 3231 | ExclusiveLock       | t
 relation      |    13806 |    16434 | 3231 | AccessExclusiveLock | t
 transactionid |          |          | 3231 | ExclusiveLock       | t
 relation      |    13806 |    16434 | 3386 | RowExclusiveLock    | f
 relation      |    13806 |    16439 | 3231 | AccessExclusiveLock | t
 relation      |    13806 |    16437 | 3231 | ShareLock           | t
(7 rows)

上面,可以看到会话3386尝试访问行级别锁,但是没有授予。这个是当前插入的会话。我们可以把pg_locks与pg_database和pg_class,通过加入pid来获取更多的信息,如下所示:

postgres=# select b.locktype,d.datname,c.relname,b.pid,b.mode from pg_locks b,pg_database d,pg_class c where b.pid in (3386,3231) and b.database = d.oid and b.relation = c.oid;
 locktype | datname  | relname | pid  |        mode
----------+----------+---------+------+---------------------
 relation | postgres | t1      | 3231 | AccessExclusiveLock
 relation | postgres | t1      | 3386 | RowExclusiveLock
(2 rows)

那么我们如何识别阻塞会话呢?很简单,使用pg_blocking_pids函数传递被堵塞的会话,就可以看到阻塞者。

postgres=# select pg_blocking_pids(3386);
 pg_blocking_pids 
------------------
 {3231}
(1 row)

然后如果想杀掉这个会话可以使用pg_terminate_backend函数传入阻塞的源头会话。然后就可以插入成功了。

postgres=# select pg_terminate_backend(3231);
 pg_terminate_backend 
----------------------
 t

PostgreSQL中如何获取对象的DDL语句

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:PostgreSQL中如何获取对象的DDL语句
我们在Oracle当中,可以使用dbms_metadata.get_ddl()方法去查看创建的语句。也可以使用expdp/impdp生成元数据dmp文件,在转换成sqlfile去查看。但是在大PG里面我们应该怎么做呢?

postgres=# create table t1(id int,name varchar(30));
CREATE TABLE
postgres=# insert into t1(id,name) values (generate_series(1,1000000),'test'||generate_series(1,1000000));
INSERT 0 1000000
postgres=# select count(*) from t1;
  count
---------
 1000000
(1 row)

postgres=# create unique index idx1 on t1(id);
CREATE INDEX

postgres=# create index idx2 on t1(name);
CREATE INDEX

postgres=# create view v1 as select id from t1;
CREATE VIE

postgres=# alter table t1 add constraint con1 check (id< 2000000);
ALTER TABLE

create function add(int,int) returns int
as
'select $1 + $2;'
language sql
immutable returns null on null input;

postgres=# select add(1,2);
 add
-----
   3
(1 row)

PostgreSQL附带了一组函数,具体可以查看https://www.postgresql.org/docs/current/static/functions-info.html,一些函数可以获得对象的定义。如获取视图的定义可以使用pg_get_viewde,获取触发器可以使用pg_get_triggerdef,获取函数可以使用pg_get_functiondef,获取约束可以使用pg_get_constraintdef。

postgres=# select pg_get_viewdef('v1');
 pg_get_viewdef
----------------
  SELECT t1.id +
    FROM t1;
(1 row)

postgres=# SELECT conname, pg_get_constraintdef(r.oid, true) as definition FROM pg_constraint r WHERE r.conrelid = 't1'::regclass;
 conname |      definition
---------+----------------------
 con1    | CHECK (id < 2000000)
(1 row)

postgres=# SELECT proname,pg_get_functiondef(a.oid) FROM pg_proc a WHERE a.proname = 'add';
 proname |                   pg_get_functiondef
---------+---------------------------------------------------------
 add     | CREATE OR REPLACE FUNCTION public.add(integer, integer)+
         |  RETURNS integer                                       +
         |  LANGUAGE sql                                          +
         |  IMMUTABLE STRICT                                      +
         | AS $function$select $1 + $2;$function$                 +
         |
(1 row)

postgres=# select pg_get_indexdef('idx1'::regclass);
                    pg_get_indexdef
--------------------------------------------------------
 CREATE UNIQUE INDEX idx1 ON public.t1 USING btree (id)
(1 row)

但是这些功能里面没有获取表定义的。唯一的办法是使用pg_dump。 使用pg_dump我们可以把表还有索引的语句都dump出来。这里使用-s选项(schema only)和-t选项(tables)。

-bash-4.2$ pg_dump -s -t t1 postgres | egrep -v "^--|^$"
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE public.t1 (
    id integer,
    name character varying(30),
    CONSTRAINT con1 CHECK ((id < 2000000))
);
ALTER TABLE public.t1 OWNER TO postgres;
CREATE UNIQUE INDEX idx1 ON public.t1 USING btree (id);
CREATE INDEX idx2 ON public.t1 USING btree (name);

当然还可以使用pl/perl语言的扩展来实现这件事。

postgres=# create extension plperlu;
CREATE EXTENSION

postgres=# \dx
                                List of installed extensions
    Name     | Version |   Schema   |                      Description
-------------+---------+------------+-------------------------------------------------------
 pageinspect | 1.6     | public     | inspect the contents of database pages at a low level
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 plperlu     | 1.0     | pg_catalog | PL/PerlU untrusted procedural language
(3 rows)

postgres=# CREATE OR REPLACE FUNCTION system(text) RETURNS text
AS 'my $cmd=shift; return `cd /tmp;$cmd`;' LANGUAGE plperlu;
CREATE FUNCTION

postgres=# select system('pg_dump -s -t t1 ddl | egrep -v "^--|^$"');
                    system
----------------------------------------------------------------
SET statement_timeout = 0;                                     +
SET lock_timeout = 0;                                          +
SET idle_in_transaction_session_timeout = 0;                   +
SET client_encoding = 'UTF8';                                  +
SET standard_conforming_strings = on;                          +
SELECT pg_catalog.set_config('search_path', '', false);        +
SET check_function_bodies = false;                             +
SET client_min_messages = warning;                             +
SET row_security = off;                                        +
SET default_tablespace = '';                                   +
SET default_with_oids = false;                                 +
CREATE TABLE public.t1 (                                       +
    id integer,                                                +
    name character varying(30),                                +
    CONSTRAINT con1 CHECK ((id < 2000000))                     +
);                                                             +
ALTER TABLE public.t1 OWNER TO postgres;                       +
CREATE UNIQUE INDEX idx1 ON public.t1 USING btree (id);        +
CREATE INDEX idx2 ON public.t1 USING btree (name);             +