主页 / user-105575

TheDataGuy's questions

Martin Hope
TheDataGuy
Asked: 2022-12-19 02:16:52 +0800 CST
  • 7

在了解逻辑复制过程的同时，我仍然不清楚一件事。

解码过程何时开始？

我的理解是，可以说我有一个出版物和一个广告位。但是没有订阅者。在这种情况下，插槽将只维护同步所需的 wal 信息，但实际的 wal 数据在订阅者启动之前不会被解码。

一旦订阅者被激活，槽就会知道来自哪个墙 lsn，然后实际的解码处理就会开始。

表述有误请指正。

postgresql
Martin Hope
TheDataGuy
Asked: 2022-12-17 23:10:24 +0800 CST
  • 5

通常当一个事务在 PostgreSQL 中提交时，逻辑解码过程将立即启动并将 WAL 数据转换为消费者的数据流。我的理解是

提交 » WAL » 解码过程 » 发布以过滤表 » 存储数据的插槽

我想了解的是：

  1. 解码过程 - 无论您的消费者是启动还是关闭，这都会解码 WAL，只需解码并将其推送到插槽即可。或者，如果这是错误的，那么 PostgreSQL 将不会进行解码并等待消费者启动。
  2. 插槽是保存解码数据的存储区域，或者它只是保持状态。
  3. 解码过程 - 我为特定表启用了逻辑复制。但是在另一张桌子上，我进行了批量提交。此更改也应该是解码过程的一部分，它将解码数据，然后检查发布。如果该表不存在，则只需保留它并解码下一个事件。这是正确的吗？
postgresql
Martin Hope
TheDataGuy
Asked: 2020-12-02 03:15:21 +0800 CST
  • 2

在 postgres 中，我们可以通过 COPY 将数据导出到文件中。但是所有的数据都会被保存到一个文件中。但我想将该文件分块成小文件。我们可以在导出后执行此操作。

但是有没有办法在复制过程中导出它？

或者在 psycopg2 中我们可以这样做吗？

postgresql copy
Martin Hope
TheDataGuy
Asked: 2020-11-28 02:49:26 +0800 CST
  • 0

我知道 COPY 是将数据从表或查询导出到输出文件的最佳实用程序。但是如果我们正在做一个像

\COPY (select * from table) to '/tmp/file.csv' WITH CSV;

它与运行选择查询并稍后保存结果有何不同（如在 GUI 工具中）或psql -c'select * from table'> output.csv

在性能和缓存方面使用 COPY 有什么优势吗？

总体思路是，我试图了解通过从 select 中读取与从 python 中的 COPY 导出数据的性能优势psycopg

postgresql copy
Martin Hope
TheDataGuy
Asked: 2020-09-11 21:47:10 +0800 CST
  • 0

我有一个使用 PostgreSQL 服务器的活动 pgBouncer 设置。

我们计划在同一个 pgBouncer 中再添加一个 PostgreSQL 服务器。但问题是两个数据库服务器的用户名相同，但密码不同。

服务器1：

  • 用户：管理员
  • 密码：admin123

服务器2：

  • 用户：管理员
  • 密码：admin@213

此处已请求此功能，有人可以帮助我提供任何可能的解决方案吗？

postgresql authentication
Martin Hope
TheDataGuy
Asked: 2020-08-26 03:18:21 +0800 CST
  • 1

我有一个 pgBouncer 作为连接池的 PostgreSQL 服务器。

我的应用程序正在 Elixir 上运行。

这是我的 pgBouncer 配置文件：

* = host=X.X.X.X  port=5432
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin
pool_mode = transaction
ignore_startup_parameters = extra_float_digits
server_check_query = select 1
server_check_delay = 30
max_client_conn = 10000
default_pool_size = 5
min_pool_size = 3
reserve_pool_size = 3
server_reset_query = DEALLOCATE ALL;

当我使用事务池时，出现此错误：

ERROR 08P01 (protocol_violation) bind message supplies 4 parameters, but prepared statement "ecto_323" requires 2

然后我将其更改为会话池模式。这次我收到了这个错误：

ERROR 26000 (invalid_sql_statement_name) prepared statement "ecto_83" does not exist

如何从 pgBouncer 解决这个问题？

当我直接连接数据库时，我没有看到任何错误。它在没有任何代理的情况下运行了一年多。我们现在正在实施 pgBouncer。

postgresql prepared-statement
Martin Hope
TheDataGuy
Asked: 2020-07-06 22:52:57 +0800 CST
  • 1

最近在一个测试基础设施上，为了恢复多个表（通过copy命令），我删除了所有外键。在删除 FK 之前，我已将此数据库模式转储恢复到不同的数据库中。

有什么方法可以从information_schema或生成创建 FKpg_constraint吗？

我试图从转储文件中获取它，但create table语句本身具有 FK（不是 Alter 表语句）。

有什么建议么？

postgresql foreign-key
Martin Hope
TheDataGuy
Asked: 2020-05-08 09:15:47 +0800 CST
  • 0

我正在尝试设置金门以将数据同步到 BigQuery。当我开始推送初始负载时，我的提取器导出了所有数据，甚至从复制统计信息中我能够看到记录已收到，但未推送到 Bigquery。

提取器

GGSCI (bhuvi-oracle) 12> info extract load1

EXTRACT    LOAD1     Last Started 2020-05-07 16:52   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table HR.TEST
                     2020-05-07 16:52:41  Record 32768
Task                 SOURCEISTABLE

复制统计信息

GGSCI (bhuvi-1) 1> stats bq

Sending STATS request to REPLICAT BQ ...
gg.handlerlist=bigquery

Start of Statistics at 2020-05-07 16:53:33.

Replicating from HR.TEST to bhuvi_orcl.test:

*** Total statistics since 2020-05-07 16:52:42 ***
        Total inserts                                  32768.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               32768.00

BigData Golen Gate 上的日志文件：

2020-05-07T16:59:04.895+0000  INFO    OGG-00987  Oracle GoldenGate Command Interpreter:  GGSCI command (root): start bq.
2020-05-07T16:59:04.904+0000  INFO    OGG-00963  Oracle GoldenGate Manager, mgr.prm:  Command received from GGSCI on host [10.18.0.61]:42130 (START REPLICAT BQ ).
2020-05-07T16:59:04.905+0000  INFO    OGG-00975  Oracle GoldenGate Manager, mgr.prm:  REPLICAT BQ starting.
2020-05-07T16:59:04.964+0000  INFO    OGG-00995  Oracle GoldenGate Delivery, bq.prm:  REPLICAT BQ starting.
2020-05-07T16:59:04.967+0000  INFO    OGG-03059  Oracle GoldenGate Delivery, bq.prm:  Operating system character set identified as UTF-8.
2020-05-07T16:59:04.967+0000  INFO    OGG-02695  Oracle GoldenGate Delivery, bq.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2020-05-07T16:59:05.173+0000  INFO    OGG-15052  Oracle GoldenGate Delivery, bq.prm:  Using Java class path: .:ggjava/ggjava.jar:ggjava/resources/lib/optional/log4j-api-2.9.1.jar:ggjava/resources/lib/optional/log4j-core-2.9.1.jar:ggjava/resources/lib/optional/log4j-slf4j-impl-2.9.1.jar.
2020-05-07T16:59:10.243+0000  INFO    OGG-01815  Oracle GoldenGate Delivery, bq.prm:  Virtual Memory Facilities for: COM
                                  anon alloc: mmap(MAP_ANON)  anon free: munmap
                                  file alloc: mmap(MAP_SHARED)  file free: munmap
                                  target directories:
                                  /opt/ogg/dirtmp.
2020-05-07T16:59:10.243+0000  INFO    OGG-00996  Oracle GoldenGate Delivery, bq.prm:  REPLICAT BQ started.
2020-05-07T16:59:10.251+0000  INFO    OGG-02243  Oracle GoldenGate Delivery, bq.prm:  Opened trail file /opt/ogg/dirdat/oo000000 at 2020-05-07 16:59:10.249063.
2020-05-07T16:59:10.251+0000  INFO    OGG-03506  Oracle GoldenGate Delivery, bq.prm:  The source database character set, as determined from the trail file, is UTF-8.
2020-05-07T16:59:10.253+0000  INFO    OGG-06505  Oracle GoldenGate Delivery, bq.prm:  MAP resolved (entry HR.TEST): MAP "HR"."TEST", TARGET bhuvi_orcl.test.
2020-05-07T16:59:10.253+0000  INFO    OGG-02756  Oracle GoldenGate Delivery, bq.prm:  The definition for table HR.TEST is obtained from the trail file.
2020-05-07T16:59:10.253+0000  INFO    OGG-06511  Oracle GoldenGate Delivery, bq.prm:  Using following columns in default map by name: ID.
2020-05-07T16:59:10.253+0000  INFO    OGG-06510  Oracle GoldenGate Delivery, bq.prm:  Using the following key columns for target table bhuvi_orcl.test: ID.
oracle oracle-12c
Martin Hope
TheDataGuy
Asked: 2019-11-14 09:39:31 +0800 CST
  • -1

我启动了一个表转储，无论 NULL 值出现在哪里，转储文件都有\N。

在还原期间，我收到此错误。

无效命令\N

数据库编码是UTF8.

我接受了转储并尝试在同一台服务器上恢复，但在不同的数据库上。有一些解决方法。但我仍然在尝试修复它，而不对数据库编码做任何进一步的更改。

表结构：

CREATE TABLE person.person (
    businessentityid integer NOT NULL,
    persontype character(2) NOT NULL,
    namestyle public."NameStyle" DEFAULT false NOT NULL,
    title character varying(8),
    firstname public."Name" NOT NULL,
    middlename public."Name",
    lastname public."Name" NOT NULL,
    suffix character varying(10),
    emailpromotion integer DEFAULT 0 NOT NULL,
    additionalcontactinfo xml,
    demographics xml,
    rowguid uuid DEFAULT public.uuid_generate_v1() NOT NULL,
    modifieddate timestamp without time zone DEFAULT now() NOT NULL,
    CONSTRAINT "CK_Person_EmailPromotion" CHECK (((emailpromotion >= 0) AND (emailpromotion <= 2))),
    CONSTRAINT "CK_Person_PersonType" CHECK (((persontype IS NULL) OR (upper((persontype)::text) = ANY (ARRAY['SC'::text, 'VC'::text, 'IN'::text, 'EM'::text, 'SP'::text, 'GC'::text]))))
);

错误线

COPY person.person (businessentityid, persontype, namestyle, title, firstname, middlename, lastname, suffix, emailpromotion, additionalcontactinfo, demographics, rowguid, modifieddate) FROM stdin;
1       EM      f       \N      Ken     J       Sánchez \N      0       \N      <IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>     92c4279f-1207-48a3-8448-4636514eb7e2    2009-01-07 00:00:00
postgresql psql
Martin Hope
TheDataGuy
Asked: 2019-10-10 09:00:22 +0800 CST
  • 0

我被困在 MongoDB 3.6 中归档大量数据

我想删除集合中的 5.06 亿条记录。我尝试使用 bulk.remove() 删除，但这也很慢。每秒删除 50 条记录。

但在我读到的某个地方，TTL 索引并每 1 小时进行一次扫描间隔。所以它会以更快的方式删除。

但是如果我在前台创建这个索引，它会锁定集合。所以我正在考虑使用滚动索引创建方法。

如果这样做，假设在 3 节点副本集上，分离 node3 然后创建索引。一旦创建，它将开始自动删除数据。然后一旦我将节点添加回副本集，也许主节点会在我创建索引后执行删除，到时候它会尝试复制，在最坏的情况下，该节点上的数据已经被删除，然后它'会打破复制？

mongodb archive
Martin Hope
TheDataGuy
Asked: 2019-10-06 04:47:20 +0800 CST
  • 0

我正在编写一个存储过程来获取变量中的数据库和表名，然后我将把这些值推送到 IN 子句中。 

CREATE OR REPLACE PROCEDURE bhuvi(db varchar(100), tbls varchar(100))
 LANGUAGE plpgsql
 AS $$
 DECLARE
   rec RECORD;
    v timestamp;
   tables_s varchar(100);

 BEGIN
 select '''' + REPLACE(tbls, ',', ''',''') + '''' into tables_s;
    FOR rec IN select table_schema, table_name from information_schema.tables where table_catalog in (db) and table_type='BASE TABLE' and table_schema not in ('pg_catalog','information_schema')  and table_name in (tables_s)
   LOOP
   select now() into v;
     RAISE INFO ' % printing... schema = % and table = %',v, rec.table_schema, rec.table_name;
 END LOOP;
 END;
 $$;

如果我调用此过程，它只会显示 CALL。没有结果。

但是在我的 FOR 循环中，如果我删除and table_name in (tables_s)，我能够打印所有表名和模式。

但是变量中的多个表名有问题。没有它的工作。

有人可以帮我弄清楚我的脚本有什么问题吗？

postgresql plpgsql
Martin Hope
TheDataGuy
Asked: 2019-08-27 04:22:45 +0800 CST
  • 0

Redshift 支持通过执行此 SQL 查询将数据导出到 CSV。

unload ('select * from bhuvi') 
to 's3://bhuvi-bucket/folder//user-data.csv' 
iam_role 'arn:aws:iam::1111111:role/myredshiftrole' 
delimiter '|'  GZIP";

我有不同的表，需要将它们导出到不同的位置。所以我有一个如下所示的存储过程。所以我以后可以在变量中传递位置。

CREATE OR REPLACE PROCEDURE test_variable()
 LANGUAGE plpgsql AS
$$
DECLARE
   sql text;
   s3_loc text;
   role text;
   deli text;
   query text;
BEGIN
   sql:='select * from bhuvi';
   s3_loc:='s3://bhuvi-bucket/folder/';
   role:='arn:aws:iam::1111111:role/myredshiftrole';
   deli:='|';
   query := "unload (sql) to 's3_loc/user-data.csv' iam_role 'role';
   execute query;
END
$$;

但在某处语法是错误的。我找不到那个。当我调用此过程时，我收到以下错误。

test=# call test_variable();
ERROR:  column "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' " does not exist
CONTEXT:  SQL statement "SELECT  "unload (sql) to 's3_loc/user-data.csv' iam_role 'role' ""
PL/pgSQL function "test_variable" line 12 at assignment
test=#
redshift plpgsql
Martin Hope
TheDataGuy
Asked: 2018-10-17 02:39:29 +0800 CST
  • 4

在 MySQL 5.6 中，我有大约 6000 万行。我想根据 Id 删除行。这里 Id 是主键。

我今天有一个奇怪的行为。

delete from tbl where id=1;

它以毫秒为单位执行。

然后我做了这个。

CREATE TEMPORARY TABLE DELETE_ID (ID int);
Insert into DELETE_ID select id from (subery1(subquery2(subquery3)));

DELETE_ID 有大约 150 个 ID。

Delete from tbl where id in (select id from DELETE_ID);

它花费了 30 多分钟，但删除了一行。

没有锁或死锁。

在 innodb_trx 中，有时状态fetching rows为unlocking rows.

谁能帮我理解为什么会这样？

mysql innodb
Martin Hope
TheDataGuy
Asked: 2018-09-16 00:49:12 +0800 CST
  • 0

我有一个 5.6 的 mysql 数据库。

  • 启用二进制日志，它有 5 个从站。

  • 启用 GTID 模式。

  • 最大 binlog 文件大小为 1GB。

  • sync_binlog = 0

当我检查free -gh

              total        used        free      shared  buff/cache   available
Mem:           117G         79G        1.4G         25M         37G         37G
Swap:            0B          0B          0B

37GB 缓存。

我想检查一下所有与Mysql相关的内容。所以我使用了fincore并且我可以看到从mysql-bin.000557到mysql-bin.000590都在操作系统缓存中。

binlog 的总缓存为 35GB。

这是预期的行为吗？

从站中没有复制滞后。那么为什么mysql需要在Cache中缓存旧的binlog呢？

mysql linux
Martin Hope
TheDataGuy
Asked: 2018-08-15 09:23:51 +0800 CST
  • 0

我试图恢复具有 DEFINER= test@的数据库转储%。

备份文件为 50GB。

操作系统 - CentOS 7.5

在恢复这个时，我得到了以下错误。

ERROR 1227 (42000) at line 30908: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

第 30908 行是，

CREATE DEFINER=`test`@`%` PROCEDURE `myprod`(IN id INT)

所以我尝试使用 perl 删除定义器。

perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" my_dump_file.sql

它已执行，但在那之后，我也遇到了同样的问题，并且文件中没有更改。

然后我试了，

sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' < my_dump_file.sql > my_dump_file.new

这也被执行，并试图恢复 my_dump_file.new 但在同一行上仍然存在相同的问题。

最后我尝试在 MySQL 上创建一个用户。

create user `test`@`%` identified by 'test';
grant all on *.* to `test`@`%`;
flush privileges;

同样，我遇到了同样的问题。

谁能帮我解决这个问题？

mysql restore
Martin Hope
TheDataGuy
Asked: 2018-03-10 01:51:49 +0800 CST
  • 2

我试图在mariadb(10.1.31-MariaDB - MASTER)和之间设置复制mysql(5.7.12-log - SLAVE)。执行启动从站后，出现以下错误。

致命错误：slave I/O 线程停止，因为在尝试从 master 获取 SERVER_UUID 变量的值时遇到致命错误。

然后我尝试获取master_uuid的值。

MariaDB [(none)]> show variables like '%server_uuid%';
Empty set (0.00 sec)

然后尝试在 mysql 提示符下进行设置。

MariaDB [(none)]> set global  server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33;
ERROR 1193 (HY000): Unknown system variable 'server_uuid'

而且我还尝试在数据目录中设置 auto.cnf。

[auto]
server_uuid = 11123c25-cac0-3523-8089-4c54d9b46f33

chmod 644 auto.cnf

但我仍然得到fatal error is encountered when it tries to get the value of SERVER_UUID variable from master.

mysql replication
Martin Hope
TheDataGuy
Asked: 2017-11-06 09:27:04 +0800 CST
  • 2

我刚刚在 ubuntu 中配置了一个带有 2 个节点的 percona xtradb 集群。

这就是我所做的。

  1. 推出 2 台新服务器。
  2. 在两个节点上都安装了 percona 集群。
  3. 在两个节点上都停止了 mysql。

在服务器 1 上：

/etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://172.31.5.172,172.31.13.81
binlog_format=ROW
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=172.31.5.172
wsrep_cluster_name=pxc-cluster
pxc_strict_mode=DISABLED
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"

在服务器 2 上：

/etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://172.31.5.172,172.31.13.81
binlog_format=ROW
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=172.31.13.81
wsrep_cluster_name=pxc-cluster
pxc_strict_mode=DISABLED
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"

在 server1 上： /etc/init.d/mysql bootstrap-pxc 然后 mysql 启动。

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

在服务器 2 上：

service mysql start

截至目前没有问题，测试了复制素。然后我重新启动了两个节点。

之后mysql将无法启动。

服务器1：

2017-11-05T17:22:03.505946Z mysqld_safe Logging to '/var/log/mysqld.log'.
2017-11-05T17:22:03.515607Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-11-05T17:22:03.520623Z mysqld_safe Skipping wsrep-recover for cc6a7fb6-c24b-11e7-bd13-ba1518e34f61:0 pair
2017-11-05T17:22:03.521158Z mysqld_safe Assigning cc6a7fb6-c24b-11e7-bd13-ba1518e34f61:0 to wsrep_start_position
2017-11-05T17:22:03.706708Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-05T17:22:03.708134Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.19-17-57-log) starting as process 11089 ...
2017-11-05T17:22:03.709661Z 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=ip-172-31-5-172-bin' to avoid this problem.
2017-11-05T17:22:03.709964Z 0 [Note] WSREP: Setting wsrep_ready to false
2017-11-05T17:22:03.709977Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2017-11-05T17:22:03.709982Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib/galera3/libgalera_smm.so'
2017-11-05T17:22:03.712249Z 0 [Note] WSREP: wsrep_load(): Galera 3.22(r8678538) by Codership Oy <[email protected]> loaded successfully.
2017-11-05T17:22:03.712290Z 0 [Note] WSREP: CRC-32C: using hardware acceleration.
2017-11-05T17:22:03.712549Z 0 [Note] WSREP: Found saved state: cc6a7fb6-c24b-11e7-bd13-ba1518e34f61:0, safe_to_bootsrap: 1
2017-11-05T17:22:03.713823Z 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 172.31.5.172; base_port = 4567; cert.log_conflicts = no; 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.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 = 1; 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 = 7; socket.checksum = 2; socket.recv_buf_size = 212992;
2017-11-05T17:22:03.727615Z 0 [Note] WSREP: Assign initial position for certification: 0, protocol version: -1
2017-11-05T17:22:03.727630Z 0 [Note] WSREP: Preparing to initiate SST/IST
2017-11-05T17:22:03.727634Z 0 [Note] WSREP: Starting replication
2017-11-05T17:22:03.727640Z 0 [Note] WSREP: Setting initial position to cc6a7fb6-c24b-11e7-bd13-ba1518e34f61:0
2017-11-05T17:22:03.727741Z 0 [Note] WSREP: Using CRC-32C for message checksums.
2017-11-05T17:22:03.727792Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0
2017-11-05T17:22:03.727852Z 0 [Warning] WSREP: Fail to access the file (/var/lib/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2017-11-05T17:22:03.727859Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
2017-11-05T17:22:03.728266Z 0 [Note] WSREP: GMCast version 0
2017-11-05T17:22:03.728398Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
2017-11-05T17:22:03.728405Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
2017-11-05T17:22:03.728686Z 0 [Note] WSREP: EVS version 0
2017-11-05T17:22:03.728771Z 0 [Note] WSREP: gcomm: connecting to group 'pxc-cluster', peer '172.31.5.172:,172.31.13.81:'
2017-11-05T17:22:03.728686Z 0 [Note] WSREP: EVS version 0
2017-11-05T17:22:03.728771Z 0 [Note] WSREP: gcomm: connecting to group 'pxc-cluster', peer '172.31.5.172:,172.31.13.81:'
2017-11-05T17:22:03.729595Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') connection established to d73a400b tcp://172.31.5.172:4567
2017-11-05T17:22:03.729609Z 0 [Warning] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') address 'tcp://172.31.5.172:4567' points to own listening address, blacklisting
2017-11-05T17:22:06.729830Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://172.31.13.81:4567 timed out, no messages seen in PT3S
2017-11-05T17:22:06.729954Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') connection to peer d73a400b with addr tcp://172.31.5.172:4567 timed out, no messages seen in PT3S
2017-11-05T17:22:06.730072Z 0 [Warning] WSREP: no nodes coming from prim view, prim not possible
2017-11-05T17:22:06.730101Z 0 [Note] WSREP: Current view of cluster as seen by this node
view (view_id(NON_PRIM,d73a400b,1)
memb {
        d73a400b,0
        }
joined {
        }
left {
        }
partitioned {
        }
)
2017-11-05T17:22:07.230228Z 0 [Warning] WSREP: last inactive check more than PT1.5S ago (PT3.50154S), skipping check
2017-11-05T17:22:10.730540Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://172.31.13.81:4567 timed out, no messages seen in PT3S
2017-11-05T17:22:14.731257Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') connection to peer 00000000 with addr tcp://172.31.13.81:4567 timed out, no messages seen in PT3S
2017-11-05T17:22:16.154486Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') connection established to de905d70 tcp://172.31.13.81:4567
2017-11-05T17:22:16.154851Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2017-11-05T17:22:16.654815Z 0 [Note] WSREP: declaring de905d70 at tcp://172.31.13.81:4567 stable
2017-11-05T17:22:16.655411Z 0 [Warning] WSREP: no nodes coming from prim view, prim not possible
2017-11-05T17:22:16.655466Z 0 [Note] WSREP: Current view of cluster as seen by this node
view (view_id(NON_PRIM,d73a400b,2)
memb {
        d73a400b,0
        de905d70,0
        }
joined {
        }
left {
        }
partitioned {
        }
)
2017-11-05T17:22:19.232193Z 0 [Note] WSREP: (d73a400b, 'tcp://0.0.0.0:4567') turning message relay requesting off

服务器2：

2017-11-05T17:22:15.979415Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-05T17:22:15.981265Z 0 [Note] mysqld (mysqld 5.7.19-17-57-log) starting as process 1325 ...
2017-11-05T17:22:15.983852Z 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=ip-172-31-13-81-bin' to avoid this problem.
2017-11-05T17:22:15.984031Z 0 [Note] WSREP: Setting wsrep_ready to false
2017-11-05T17:22:15.984043Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2017-11-05T17:22:15.984047Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib/galera3/libgalera_smm.so'
2017-11-05T17:22:16.013201Z 0 [Note] WSREP: wsrep_load(): Galera 3.22(r8678538) by Codership Oy <[email protected]> loaded successfully.
2017-11-05T17:22:16.013252Z 0 [Note] WSREP: CRC-32C: using hardware acceleration.
2017-11-05T17:22:16.015682Z 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootsrap: 0
2017-11-05T17:22:16.019709Z 0 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 172.31.13.81; base_port = 4567; cert.log_conflicts = no; 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.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 = 1; 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 = 7; socket.checksum = 2; socket.recv_buf_size = 212992;
2017-11-05T17:22:16.034431Z 0 [Note] WSREP: GCache history reset: cc6a7fb6-c24b-11e7-bd13-ba1518e34f61:0 -> 00000000-0000-0000-0000-000000000000:-1
2017-11-05T17:22:16.035026Z 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
2017-11-05T17:22:16.035038Z 0 [Note] WSREP: Preparing to initiate SST/IST
2017-11-05T17:22:16.035041Z 0 [Note] WSREP: Starting replication
2017-11-05T17:22:16.035048Z 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
2017-11-05T17:22:16.035159Z 0 [Note] WSREP: Using CRC-32C for message checksums.
2017-11-05T17:22:16.035214Z 0 [Note] WSREP: gcomm thread scheduling priority set to other:0
2017-11-05T17:22:16.036228Z 0 [Warning] WSREP: Fail to access the file (/var/lib/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2017-11-05T17:22:16.036250Z 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
2017-11-05T17:22:16.036685Z 0 [Note] WSREP: GMCast version 0
2017-11-05T17:22:16.036828Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
2017-11-05T17:22:16.036836Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
2017-11-05T17:22:16.037997Z 0 [Note] WSREP: EVS version 0
2017-11-05T17:22:16.038072Z 0 [Note] WSREP: gcomm: connecting to group 'pxc-cluster', peer '172.31.5.172:,172.31.13.81:'
2017-11-05T17:22:16.039496Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') connection established to de905d70 tcp://172.31.13.81:4567
2017-11-05T17:22:16.039513Z 0 [Warning] WSREP: (de905d70, 'tcp://0.0.0.0:4567') address 'tcp://172.31.13.81:4567' points to own listening address, blacklisting
2017-11-05T17:22:16.039960Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') connection established to d73a400b tcp://172.31.5.172:4567
2017-11-05T17:22:16.040014Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2017-11-05T17:22:16.540329Z 0 [Note] WSREP: declaring d73a400b at tcp://172.31.5.172:4567 stable
2017-11-05T17:22:16.540836Z 0 [Warning] WSREP: no nodes coming from prim view, prim not possible
2017-11-05T17:22:16.540862Z 0 [Note] WSREP: Current view of cluster as seen by this node
view (view_id(NON_PRIM,d73a400b,2)
memb {
        d73a400b,0
        de905d70,0
        }
joined {
        }
left {
        }
partitioned {
        }
)
2017-11-05T17:22:19.039641Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') connection to peer de905d70 with addr tcp://172.31.13.81:4567 timed out, no messages seen in PT3S
2017-11-05T17:22:19.539775Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') turning message relay requesting off
2017-11-05T17:22:37.061048Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://172.31.5.172:4567
2017-11-05T17:22:38.061306Z 0 [Note] WSREP: (de905d70, 'tcp://0.0.0.0:4567') reconnecting to d73a400b (tcp://172.31.5.172:4567), attempt 0
2017-11-05T17:22:41.562259Z 0 [Note] WSREP: evs::proto(de905d70, OPERATIONAL, view_id(REG,d73a400b,2)) suspecting node: d73a400b
2017-11-05T17:22:41.562325Z 0 [Note] WSREP: evs::proto(de905d70, OPERATIONAL, view_id(REG,d73a400b,2)) suspected node without join message, declaring inactive
2017-11-05T17:22:42.562445Z 0 [Note] WSREP: Current view of cluster as seen by this node
view (view_id(NON_PRIM,d73a400b,2)
memb {
        de905d70,0
        }
joined {
        }
left {
        }
partitioned {
        d73a400b,0
        }
)
2017-11-05T17:22:42.562525Z 0 [Warning] WSREP: no nodes coming from prim view, prim not possible
2017-11-05T17:22:42.562550Z 0 [Note] WSREP: Current view of cluster as seen by this node
view (view_id(NON_PRIM,de905d70,3)
memb {
        de905d70,0
        }
joined {
        }
left {
        }
partitioned {
        d73a400b,0
        }
)
2017-11-05T17:22:47.071058Z 0 [Note] WSREP: Current view of cluster as seen by this node
view ((empty))
2017-11-05T17:22:47.071334Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
         at gcomm/src/pc.cpp:connect():158
2017-11-05T17:22:47.071356Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
2017-11-05T17:22:47.071412Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1513: Failed to open channel 'pxc-cluster' at 'gcomm://172.31.5.172,172.31.13.81': -110 (Connection timed out)
2017-11-05T17:22:47.071421Z 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2017-11-05T17:22:47.071426Z 0 [ERROR] WSREP: Provider/Node (gcomm://172.31.5.172,172.31.13.81) failed to establish connection with cluster (reason: 7)
2017-11-05T17:22:47.071430Z 0 [ERROR] Aborting

2017-11-05T17:22:47.071433Z 0 [Note] Giving 0 client threads a chance to die gracefully
2017-11-05T17:22:47.071437Z 0 [Note] WSREP: Waiting for active wsrep applier to exit
2017-11-05T17:22:47.071440Z 0 [Note] WSREP: Service disconnected.
2017-11-05T17:22:47.071443Z 0 [Note] WSREP: Waiting to close threads......
2017-11-05T17:22:52.072202Z 0 [Note] WSREP: Some threads may fail to exit.
2017-11-05T17:22:52.072285Z 0 [Note] Binlog end
2017-11-05T17:22:52.072905Z 0 [Note] mysqld: Shutdown complete

2017-11-05T17:22:52.322273Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-05T17:22:52.325253Z 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=ip-172-31-13-81-bin' to avoid this problem.
2017-11-05T17:22:52.344397Z 0 [Warning] WSREP: Fail to access the file (/var/lib/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
2017-11-05T17:22:52.346176Z 0 [Warning] WSREP: (f4348c81, 'tcp://0.0.0.0:4567') address 'tcp://172.31.13.81:4567' points to own listening address, blacklisting
2017-11-05T17:22:52.847728Z 0 [Warning] WSREP: no nodes coming from prim view, prim not possible
2017-11-05T17:23:22.856010Z 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
         at gcomm/src/pc.cpp:connect():158
2017-11-05T17:23:22.856051Z 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
2017-11-05T17:23:22.856128Z 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1513: Failed to open channel 'pxc-cluster' at 'gcomm://172.31.5.172,172.31.13.81': -110 (Connection timed out)
2017-11-05T17:23:22.856140Z 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2017-11-05T17:23:22.856147Z 0 [ERROR] WSREP: Provider/Node (gcomm://172.31.5.172,172.31.13.81) failed to establish connection with cluster (reason: 7)
2017-11-05T17:23:22.856151Z 0 [ERROR] Aborting
mysql percona