菜单

MySQL高可用之PXC安装配置

2018年12月16日 - MySQL

 

布MySQL高可用集群,部署MySQL可用集群

 

  一.简介

    本文将介绍怎么着使mysql-mmm搭建数据库的赛可用架构.

  二.环境

          

服务器

主机名

Ip

Severed

Mysql版本

系统

Master1

master1

192.168.4.10

10

5.6.15

Centos6.9

Master2

master2

192.168.4.11

11

5.6.15

 

Slave1

slave1

192.168.4.12

12

5.6.15

 

Slave2

slave2

192.168.4.13

13

5.6.15

 

Monitor

monitor

192.168.4.100

 

Client

client

192.168.4.120

5.6.15

 

 

         虚拟IP  

      

虚拟ip

功能

描述

192.168.4.200

Write

主用master写入虚拟Ip

192.168.4.201

read

读服务器虚拟Ip

192.168.4.202

Read

读服务器虚拟Ip

 

                案例图谱

 

图片 1

 

  三.mmm架构

                服务器角色

类型

服务进程

主要用途

管理节点

mmm-monitor

负责所有的监控工作的监控守护进程,决定故障节点的移除或恢复。

数据库节点

mmm-agent

运行所在MySQL服务器殇的代理守护进程,提供简单远程服务集、提供给监控节点(可用来更改只读模式、复制的主服务器等 )

 

    

                   主旨软件包及应用

软件包名

包作用

Net-ARP-1.0.8.tgz

分配虚拟ip

mysql-mmm-2.2.1.tar.gz

MySQL-MMM架构核心进程,安装完成后即可启动管理进程也可启动客户端进程。

 

  四.布置集群基本协会

   我们用配置集群的工作分为两生块,第一块就是布集群基础环境。使用4高RHEL6服务器,如下图所展示。其中192.168.4.10、192.168.4.11作MySQL双主服务器,192.168.4.12、192.168.4.13发为主服务器的于服务器。

  安装服务器时提出管理防火墙及SELINUX.

图片 2

 

 

4.1 mysql服务器的安装

      

脚我会介绍MySQL的安形式。本文将运用64各样之RHEL
6操作系统,MySQL数据库的版是5.6.15。

访问http://dev.mysql.com/downloads/mysql/,找到MySQL Community
Server下载页面,平台选用“Red Hat Enterprise Linux 6 / Oracle Linux
6”,然后择64员之bundle整合包下载,如下图所突显。

 

图片 3

 

*         
 注意:下载MySQL软件时索要盖Oracle网站账户登录,即使没有请求根据页面提醒先注册一个(免费)
。*

*    *

    4.1.1 卸载系统自带的mysql-server、mysql软件包(假若有言语)   

yum -y remove mysql-server mysql

 

    4.1.2 释放MySQL-bundle整合包         

[[email protected] ~]#tar xvf MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar
MySQL-shared-5.6.15-1.el6.x86_64.rpm              //共享库
MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm      //兼容包
MySQL-server-5.6.15-1.el6.x86_64.rpm              //服务端程序
MySQL-client-5.6.15-1.el6.x86_64.rpm              //客户端程序
MySQL-devel-5.6.15-1.el6.x86_64.rpm              //库和头文件
MySQL-embedded-5.6.15-1.el6.x86_64.rpm          //嵌入式版本
MySQL-test-5.6.15-1.el6.x86_64.rpm              //测试包

    4.1.3 安装MySQL数据库

[[email protected]]# rpm -Uvh MySQL-*.rpm

    4.1.4 启动MySQL数据库

[[email protected] ~]# service mysql start && chkconfig --list mysql
Starting MySQL SUCCESS! 
mysql              0:关闭    1:关闭    2:启用    3:启用    4:启用    5:启用    6:关闭

    4.1.5 MySQL密码

    在装完后汇合自动生成于root目录下.mysql_secret文件内
查询后可应用这密码登录Mysql

[[email protected] ~]# cat .mysql_secret 
# The random password set for the root user at Mon Jan  1 16:48:31 2001 (local time): kZ5j71cyZiKKhSeX          // 密码文件

    4.1.6 登录MySQL,并修改密码  使用刚查及之密码进行登录

[[email protected] ~]# mysql -u root -p
Enter password:

mysql> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('123456');    

        

              修改后又登录时既可以新密码了。

              按照上述格局 将4玉服务器都作好MySQL。

 

    4.2 部署对主多从协会

      1.数据库授权(4高数据库主机master1,master2,slave1,slave2执行以下操作)

  部署主从同步独待授权一个基本同步用户即可,可是咱而配备MySQL-MMM架构,所以于此处大家将MySQL-MMM所急需用户同样连举行授权设置。再授权一个测试用户,在搭搭建好时测试用。

 mysql> grant   replication  slave  on  *.*  to  slaveuser@"%" identified by  "pwd123";
Query OK, 0 rows affected (0.01 sec)         //主从同步授权
mysql> grant  replication  client  on *.*  to  monitor@"%" identified by "monitor";  
Query OK, 0 rows affected (0.00 sec)       //MMM所需架构用户授权
mysql> grant  replication client,process,super   on *.*  to  agent@"%" identified by "agent"; 
Query OK, 0 rows affected (0.00 sec)         //MMM所需架构用户授权
mysql> grant  all  on *.*  to  root@"%" identified by "123456";
Query OK, 0 rows affected (0.00 sec)              //测试用户授权

      2.开启主数据库binlog日志、设置server_id(master1,master2)

        master1设置

[[email protected] ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=10                          //指定服务器ID
log_bin                               //启用binlog日志
log_slave_updates=1                  //启用链式复制
 [mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[[email protected] ~]# 
[[email protected] ~]# service mysql restart                //重启MySQL服务
Shutting down MySQL..                                      [确定]
Starting MySQL..                                           [确定]
[[email protected] ~]# ls /var/lib/mysql/master1-bin*        //查看binlog日志是否生成
/var/lib/mysql/master1-bin.000001  /var/lib/mysql/master1-bin.index

          master2设置:

[[email protected] mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=11
log_slave_updates=1                 

log-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[[email protected] mysql]# /etc/init.d/mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[[email protected] mysql]# ls /var/lib/mysql/master2-bin.*
/var/lib/mysql/master2-bin.000001  /var/lib/mysql/master2-bin.000002  /var/lib/mysql/master2-bin.index

 

      

于仓库设置serverid    

slave1设置

[[email protected] mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=12
…

 

[[email protected]
~]# service mysql restart

 

slave2设置

[[email protected] mysql]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=13
…

[[email protected]~]# service mysql restart

 

3.布局基本从自涉嫌

布master2、slave1、slave2成为master1的打服务器

查看master1的master状态 

mysql> show master status\G
*************************** 1. row ***************************
             File: master1-bin.000002
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

准上边参数配置master2为master1的于服务器

mysql>    change  master  to                         
    ->      master_host="192.168.4.10",                
    ->      master_user="slaveuser",                
    ->      master_password="pwd123",               
    ->      master_log_file="master1-bin.000002",     
->      master_log_pos=120; 
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;    
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G 
Slave_IO_Running: Yes                //IO节点正常
Slave_SQL_Running: Yes                //SQL节点正常

用同一的不二法门设置slave1以及slave2为master1的由服务器

4.配备主主从自涉嫌,将master1配置也master2的于服务器

查看master2的master信息:

mysql> show master status \G
*************************** 1. row ***************************
             File: master2-bin.000002
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

安master1成为master2的自服务器

mysql>    change  master  to                         
    ->      master_host="192.168.4.11",                
    ->      master_user="slaveuser",                
    ->      master_password="pwd123",               
    ->      master_log_file="master2-bin.000002",     
    ->      master_log_pos=120; 
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_Running: Yes      //IO节点正常
            Slave_SQL_Running: Yes      //SQL节点正常

5.测试主从架构是否中标

 master1更新数据库,查看其他主机是否中标
,当有着主机分别在本机都可以来看刚刚成立之数据库db1,则正常。

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema  |
| db1                |
| mysql               |
| performance_schema |
| test                 |
+--------------------+
5 rows in set (0.00 sec)

 

    至此,基本条件构建形成。

 

五.MySQL-MMM架构部署

  5.1 MMM集群方案

     使用第4章节的架,192.168.4.10、192.168.4.11看成MySQL双兆服务器,192.168.4.12、192.168.4.13发为主服务器的从服务器,添加192.168.4.100当MySQL-MMM架构中管理监控服务器,实现监督MySQL主从服务器的行事状态和控制故障节点的移除或恢复生机工作,架构搭建好后以客户机192.168.4.120进展走访,客户机需要装MySQL-client软件包。拓扑见图2

图片 4

 

5.2 步骤

 实现此案例需要按如下步骤举办。

    步骤一:安装MySQL-MMM

1.设置倚重关系(MySQL集群内5宝服务器master1,master2,slave1,slave2,monitor)均要安装

[[email protected] mysql]# yum -y install gcc* perl-Date-Manip  perl-Date-Manip  perl-Date-Manip perl-XML-DOM-XPath perl-XML-Parser perl-XML-RegExp rrdtool perl-Class-Singleton perl perl-DBD-MySQL perl-Params-Validate perl-MailTools perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

 

 

2.装置MySQL-MMM软件看重包(MySQL集群内5雅服务器master1,master2,slave1,slave2,monitor)均要安装。

[[email protected] mysql-mmm]#  tar -zxvf Algorithm-Diff-1.1902.tar.gz  
Algorithm-Diff-1.1902/
Algorithm-Diff-1.1902/diffnew.pl
Algorithm-Diff-1.1902/t/
Algorithm-Diff-1.1902/t/oo.t
Algorithm-Diff-1.1902/t/base.t
Algorithm-Diff-1.1902/htmldiff.pl
Algorithm-Diff-1.1902/lib/
Algorithm-Diff-1.1902/lib/Algorithm/
Algorithm-Diff-1.1902/lib/Algorithm/Diff.pm
Algorithm-Diff-1.1902/lib/Algorithm/DiffOld.pm
Algorithm-Diff-1.1902/META.yml
Algorithm-Diff-1.1902/Changes
Algorithm-Diff-1.1902/cdiff.pl
Algorithm-Diff-1.1902/MANIFEST
Algorithm-Diff-1.1902/diff.pl
Algorithm-Diff-1.1902/Makefile.PL
Algorithm-Diff-1.1902/README
[[email protected] mysql-mmm]# cd Algorithm-Diff-1.1902
[[email protected] Algorithm-Diff-1.1902]#  perl  Makefile.PL 
Checking if your kit is complete...
Looks good
Writing Makefile for Algorithm::Diff
[[email protected] Algorithm-Diff-1.1902]# make && make install

3.安装Proc-Daemon类

[email protected] mysql-mmm]# tar -zxvf Proc-Daemon-0.03.tar.gz
Proc-Daemon-0.03/
Proc-Daemon-0.03/t/
Proc-Daemon-0.03/t/00modload.t
Proc-Daemon-0.03/t/01filecreate.t
Proc-Daemon-0.03/README
Proc-Daemon-0.03/Makefile.PL
Proc-Daemon-0.03/Daemon.pm
Proc-Daemon-0.03/Changes
Proc-Daemon-0.03/MANIFEST
[[email protected] mysql-mmm]# cd Proc-Daemon-0.03                
[[email protected] Proc-Daemon-0.03]# perl    Makefile.PL 
Checking if your kit is complete...
Looks good
Writing Makefile for Proc::Daemon
[[email protected] Proc-Daemon-0.03]# make && make install
cp Daemon.pm blib/lib/Proc/Daemon.pm
Manifying blib/man3/Proc::Daemon.3pm
Installing /usr/local/share/perl5/Proc/Daemon.pm
Installing /usr/local/share/man/man3/Proc::Daemon.3pm
Appending installation info to /usr/lib64/perl5/perllocal.pod
[[email protected] Proc-Daemon-0.03]#

 4.安装Net-ARP虚拟IP分配工具:

[[email protected] ~]# gunzip Net-ARP-1.0.8.tgz    
[[email protected]-master1 ~]# tar xvf Net-ARP-1.0.8.tar       
.. ..
[[email protected]-master1 ~]# cd Net-ARP-1.0.8                    
[[email protected]-master1 Net-ARP-1.0.8]# perl Makefile.PL        
Module Net::Pcap is required for make test!
Checking if your kit is complete...
Looks good
Writing Makefile for Net::ARP
[[email protected]-master1 Net-ARP-1.0.8]# make && make install    
.. ..
[[email protected]-master1 Net-ARP-1.0.8]# cd                        
[[email protected]-master1 ~]#

 5. 安装Mysql-MMM软件包:

[[email protected] ~]# tar xvf mysql-mmm-2.2.1.tar.gz       
.. ..
[[email protected]-master1 ~]# cd mysql-mmm-2.2.1                    
[[email protected]-master1 mysql-mmm-2.2.1]# make && make install    
.. ..
[[email protected]-master1 mysql-mmm-2.2.1]#

*  *

手续二:修改配置文件

    本案例被MySQL集群的5光服务器(master1、master2、slave1、slave2、monitor)都急需配备,可以先行放好同一尊后动scp复制。

[email protected] ~]# vim  /etc/mysql-mmm/mmm_common.conf 
active_master_role    writer
<host default>
    cluster_interface        eth0                //设置主从同步的用户
    pid_path                /var/run/mmm_agentd.pid
    bin_path                /usr/lib/mysql-mmm/
 replication_user        slaveuser            //设置主从同步的用户
 replication_password    pwd123            //设置主从同步用户密码
    agent_user            agent       //mmm-agent控制数据库用户
    agent_password        agent         //mmm-agent控制数据库用户密码
</host>
<host master1>                            //设置第一个主服务器
    ip                    192.168.4.10            //master1 IP 地址
    mode                    master
    peer                    master2        //指定另外一台主服务器
</host>
<host master2>                            //指定另外一台主服务器
    ip                    192.168.4.11
    mode                    master
    peer                    master1
</host>
<host slave1>                                //设置第一台从服务器
    ip                    192.168.4.12            //slave1 IP 地址
    mode                slave          //本段落配置的是slave服务器
</host>
<host slave2>
    ip                    192.168.4.13
    mode                    slave
</host>
<role writer>                              //设置写入服务器工作模式
    hosts                master1,master2        //提供写的主服务器
    ips                    192.168.4.200        //设置VIP地址
    mode                    exclusive            //排他模式
</role>
<role reader>                             //设置读取服务器工作模式
    hosts                slave1,slave2        //提供读的服务器信息
    ips                 192.168.4.201,192.168.4.202    //多个虚拟IP
    mode                    balanced                  //均衡模式
</role>

 

  2.改动管理主机配置文件(monitor主机配置)

[[email protected] ~]# vim /etc/mysql-mmm/mmm_mon.conf 
include mmm_common.conf
<monitor>
    ip                        192.168.4.100        //设置管理主机IP地址
    pid_path                /var/run/mmm_mond.pid
    bin_path                /usr/lib/mysql-mmm/
    status_path                /var/lib/misc/mmm_mond.status
    ping_ips                192.168.4.10,192.168.4.11,192.168.4.12,192.168.4.13
                                                //设置被监控数据库
</monitor>
<host default>
    monitor_user            monitor        //监控数据库MySQL用户
    monitor_password        monitor        //监控数据库MySQL用户密码
</host>
debug 0
[[email protected] ~]#

 

  3.改客户端配置文件

   master1、master2、slave1、slave2,都要安排相应名称

[email protected] /]# cat /etc/mysql-mmm/mmm_agent.conf 
include mmm_common.conf
this master1
[[email protected] /]# cat /etc/mysql-mmm/mmm_agent.conf 
include mmm_common.conf
this master2
[[email protected] /]# cat /etc/mysql-mmm/mmm_agent.conf 
include mmm_common.conf
this slave2
[[email protected] /]# cat /etc/mysql-mmm/mmm_agent.conf 
include mmm_common.conf
this slave2    

 

六.MySQL-MMM架使

6.1.启动MySQL-MMM架构

  1.启动mmm-agent

  master1,master2,slave1,slave2均举办以下操作。

[[email protected] ~]# /etc/init.d/mysql-mmm-agent start
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... Ok

  2.启动mmm-monitor

[[email protected] ~]#  /etc/init.d/mysql-mmm-monitor start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: Ok

 

6.2 设置集众多被服务器也online状态。

  控制命令只可以当管理端Monitor上运行。开用命令查看时个服务器状态。 
默认所有服务器也waiting状态,如发生甚,检查各类服务器SELinux及iptabless

[[email protected] ~]# mmm_control show
  master1(192.168.4.10) master/AWAITING_RECOVERY. Roles: 
  master2(192.168.4.11) master/AWAITING_RECOVERY. Roles: 
  slave1(192.168.4.12) slave/ AWAITING_RECOVERY. Roles:   slave2(192.168.4.13) slave/AWAITING_RECOVERY. Roles: 

透过命令设置4贵数据库主机为online

[[email protected] ~]# mmm_control set_online master1
OK: State of 'master1' changed to ONLINE. Now you can wait some time and check its new roles!
[[email protected] ~]# mmm_control set_online master2
OK: State of 'master2' changed to ONLINE. Now you can wait some time and check its new roles!
[[email protected] ~]# mmm_control set_online slave1
OK: State of 'slave1' changed to ONLINE. Now you can wait some time and check its new roles!
[[email protected] ~]# mmm_control set_online slave2
OK: State of 'slave2' changed to ONLINE. Now you can wait some time and check its new roles!
[[email protected] ~]#

又翻开时凑众多被列服务器状态

[[email protected] ~]# mmm_control show
  master1(192.168.4.10) master/ONLINE. Roles: writer(192.168.4.200)
  master2(192.168.4.11) master/ONLINE. Roles: 
  slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.201)
  slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.202)

透过状态,可以见见4令主机全是online状态,写副服务器也master1,ip为虚拟ip192.168.4.200.从服务器也slave1,slave2.

6.3 测试MySQL-MMM架构

  客户端安装MySQL-client

[[email protected] ~]# tar xvf MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar
.. ..
[[email protected] ~]# rpm -ivh MySQL-client-5.6.15-1.el6.x86_64.rpm

   MySQL-MMM虚拟IP访问测试,同时可测试建立插入查看等效能。

[[email protected] /]#  mysql -h192.168.4.200 -uroot -p123456 -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[[email protected] /]# 

6.4 主数据库宕机测试

  我们好认为以主数据库停用达到测试集群的目标。

    [[email protected] ~]# /etc/init.d/mysql stop
Shutting down MySQL.. SUCCESS! 
[[email protected] ~]# 

这时我们查阅monitor日志可以望详细的检测与切换过程。

017/10/24 01:37:07  WARN Check 'rep_backlog' on 'master1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.4.10:3306, user = monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2017/10/24 01:37:07  WARN Check 'rep_threads' on 'master1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.4.10:3306, user = monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2017/10/24 01:37:15 ERROR Check 'mysql' on 'master1' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.4.10:3306, user = monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111
2017/10/24 01:37:16 FATAL State of host 'master1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
2017/10/24 01:37:16  INFO Removing all roles from host 'master1':
2017/10/24 01:37:16  INFO     Removed role 'writer(192.168.4.200)' from host 'master1'
2017/10/24 01:37:16  INFO Orphaned role 'writer(192.168.4.200)' has been assigned to 'master2'

在monitor上重新查看数据库服务器状态,可以发现这master1已经也offline状态,写副服务器和虚拟ip192.168.4.200已经变更为master2

[[email protected] ~]# mmm_control show
  master1(192.168.4.10) master/HARD_OFFLINE. Roles: 
  master2(192.168.4.11) master/ONLINE. Roles: writer(192.168.4.200)
  slave1(192.168.4.12) slave/ONLINE. Roles: reader(192.168.4.201)
  slave2(192.168.4.13) slave/ONLINE. Roles: reader(192.168.4.202)

 查看slave1.slave2底底隶属关系,主服务器因为变更为master2

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.11
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master2-bin.000002
          Read_Master_Log_Pos: 211
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 285
        Relay_Master_Log_File: master2-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

  

*注意服务器恢复生机后由offline状态转换为waiting状态,但切莫汇合变为Online状态,需手动启用。*

 

 至此,我们的MySQL高可用集群已布置了。

 

7.简化版集群。

   
上边大家就安排了了5大服务器组成的MySQL集群,但据悉各类公司实际上情状,可能访问量并不需要如此多的服务器举行集群化。而基本服务器的花样以无克促成主备用中的热备。下边我以上例做了改观,使用3雅服务器搭建集群。此例即无欲极多服务器,又会促成数据库的热备份。

图片 5

 

 

我们现在如举办的就是指向monitor配置的改变,首先调整让监控服务器IP,修改MMM主配置文件。

 [[email protected] ~]# cat /etc/mysql-mmm/mmm_mon.conf 
include mmm_common.conf
<monitor>
    ip                        192.168.4.100
    pid_path                /var/run/mmm_mond.pid
    bin_path                /usr/lib/mysql-mmm/
    status_path                /var/lib/misc/mmm_mond.status
    ping_ips                192.168.4.10,192.168.4.11  //此处是被监控服务器IP
</monitor>

<host default>
    monitor_user            monitor
    monitor_password        monitor
</host>

debug 0
[[email protected] ~]# 

接下来修改公共配置文件,注意master1,master2,monior保持一致,都如修改。

[[email protected] ~]# cat /etc/mysql-mmm/mmm_common.conf
active_master_role    writer
<host default>
    cluster_interface        eth0

    pid_path                /var/run/mmm_agentd.pid
    bin_path                /usr/lib/mysql-mmm/

    replication_user        slaveuser
    replication_password    pwd123

    agent_user                agent
    agent_password            agent
</host>

<host master1>
    ip                      192.168.4.10
    mode                    master
    peer                    master2
</host>

<host master2>
    ip                    192.168.4.11
    mode                    master
    peer                    master1
</host>
<role writer>
    hosts                    master1,master2
    ips                192.168.4.200
    mode                    exclusive
</role>

<role reader>
    hosts                    master1,master2
    ips                                   192.168.4.201,192.168.4.202
    mode                    balanced
</role>
[[email protected] ~]# 

 

 

   配置好后 ,此外配置和5台拓扑类似,启用master1和master2 的 mmm进程
,在 monitor服务器启用online格局后 查看mmm状态 

 [[email protected] ~]# mmm_control show
  master1(192.168.4.10) master/ONLINE. Roles: reader(192.168.4.201), writer(192.168.4.200)
  master2(192.168.4.11) master/ONLINE. Roles: reader(192.168.4.202)

 

 可以观望master1和master2都承担读之干活,而master1又独自承担写的职责。下边测试将master1的数据库关闭看结果。

 [[email protected] ~]# mmm_control show
  master1(192.168.4.10) master/HARD_OFFLINE. Roles: 
  master2(192.168.4.11) master/ONLINE. Roles: reader(192.168.4.201), reader(192.168.4.202), writer(192.168.4.200)

得视 ,当关闭master1时,master2即负责读的工作而且当写的工作
。用户总可以因而链接192.168.4.200拓展数据库操作,实现了双击热备。

 

八.故障分析

考试中相遇了少数不好问题 。列出仅供参考

问题1 

mysql> show slave status \G
*************************** 1. row ***************************
…………..
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
 ………….
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60  retries: 2

1 row in set (0.00 sec)

关闭matse selinux  清空防火墙 ,关闭防火墙后还开slave解决。

 

 

问题2 

搭建过程被,多次移配置后 ,遭遇如下错误

mysql>  start slave;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

本条题材用reset slave all清空所有的复制音信,然后重置master.infor
start slave后复制正常。

*    *

      

 

 

http://www.bkjia.com/Mysql/1234064.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1234064.htmlTechArticle部署MySQL高可用集群,部署MySQL可用集群 一.简介
本文将介绍如何以mysql-mmm搭建数据库的胜可用架构. 二.环境 服务器 主机名
Ip Severed Mys…

Preface

 

    Today,I’m gonna implement a
PXC,Let’s see the procedure.

 

Framework

 

Hostname IP Port OS Version MySQL Version Xtrabackup version
zlm2 192.168.1.101 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm3 192.168.1.102 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm4 192.168.1.103 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)

 

Procedure

 

    The official website of
PXC:

 
  https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/

 

    Choose the Linux-Generic
software.There’re three version relevant with openssl(100,101,102),I
choose the ssl102 version.

 

Install Percona-XtraDB-Cluster
software on three nodes.

 1 [root@zlm2 07:56:03 ~]
 2 #mkdir -p /opt/mysql
 3 
 4 [root@zlm2 07:56:51 ~]
 5 #ls -l|grep Percona
 6 -rwxr-xr-x   1 root root 252395830 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz
 7 
 8 [root@zlm2 07:57:01 ~]
 9 #gzip -d Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar.gz 
10 
11 [root@zlm2 07:57:59 ~]
12 #ls -l|grep Percona
13 -rwxr-xr-x   1 root root 1035939840 Aug  7 07:54 Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar
14 
15 [root@zlm2 07:59:36 ~]
16 #tar xf Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102.tar -C /opt/mysql
17 
18 [root@zlm2 08:02:29 ~]
19 #cd /usr/local
20 
21 [root@zlm2 08:02:22 /usr/local]
22 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
23 
24 [root@zlm2 08:02:28 /usr/local]
25 #ls -l
26 total 4
27 drwxr-xr-x. 2 root   root    105 May  7 10:44 bin
28 drwxr-xr-x. 2 root   root      6 Jun 10  2014 etc
29 drwxr-xr-x. 2 root   root      6 Jun 10  2014 games
30 drwxr-xr-x. 2 root   root      6 Jun 10  2014 include
31 drwxr-xr-x. 2 root   root      6 Jun 10  2014 lib
32 drwxr-xr-x. 3 root   root     18 May  7 09:37 lib64
33 drwxr-xr-x. 2 root   root      6 Jun 10  2014 libexec
34 lrwxrwxrwx  1 root   root     36 Apr 28 14:30 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64/
35 drwxr-xr-x  9 root   root   4096 Aug  7 10:00 mysql-5.7.21-linux-glibc2.12-x86_64
36 drwxrwxr-x  5 root   root     94 May  7 09:28 mysql_performance_monitor-agent
37 drwxr-xr-x. 2 root   root     18 Apr 28 16:06 sbin
38 drwxr-xr-x. 6 root   root     58 May  7 09:37 share
39 drwxr-xr-x. 2 root   root      6 Jun 10  2014 src
40 drwxr-xr-x  8 zabbix zabbix   70 Apr 28 16:45 zabbix
41 
42 [root@zlm2 08:02:32 /usr/local]
43 #rm -f mysql
44 
45 [root@zlm2 08:02:37 /usr/local]
46 #ln -s /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/ mysql
47 
48 [root@zlm2 08:03:04 /usr/local]
49 #ls -l
50 total 0
51 drwxr-xr-x. 2 root root  21 Jul  4 03:21 bin
52 drwxr-xr-x. 2 root root   6 Jun 10  2014 etc
53 drwxr-xr-x. 2 root root   6 Jun 10  2014 games
54 drwxr-xr-x. 2 root root   6 Jun 10  2014 include
55 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib
56 drwxr-xr-x. 2 root root   6 Jun 10  2014 lib64
57 drwxr-xr-x. 2 root root   6 Jun 10  2014 libexec
58 lrwxrwxrwx  1 root root  75 Aug  7 08:23 mysql -> /opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/
59 drwxr-xr-x  9 root root 120 Aug  7 08:22 mysql-5.7.21-linux-glibc2.12-x86_64
60 drwxr-xr-x. 2 root root   6 Jun 10  2014 sbin
61 drwxr-xr-x. 7 root root  71 Jul  4 03:21 share
62 drwxr-xr-x. 2 root root   6 Jun 10  2014 src
63 
64 [root@zlm2 08:32:56 /usr/local]
65 #ldd mysql/bin/mysqld
66     linux-vdso.so.1 =>  (0x00007fff96f3f000)
67     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1119136000)
68     libaio.so.1 => /lib64/libaio.so.1 (0x00007f1118f34000)
69     libnuma.so.1 => /lib64/libnuma.so.1 (0x00007f1118d27000)
70     libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f1118af0000)
71     libssl.so.10 => /lib64/libssl.so.10 (0x00007f1118883000)
72     libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f111849b000)
73     libdl.so.2 => /lib64/libdl.so.2 (0x00007f1118297000)
74     libz.so.1 => /lib64/libz.so.1 (0x00007f1118081000)
75     librt.so.1 => /lib64/librt.so.1 (0x00007f1117e78000)
76     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f1117b71000)
77     libm.so.6 => /lib64/libm.so.6 (0x00007f111786f000)
78     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f1117658000)
79     libc.so.6 => /lib64/libc.so.6 (0x00007f1117297000)
80     /lib64/ld-linux-x86-64.so.2 (0x00007f1119359000)
81     libfreebl3.so => /lib64/libfreebl3.so (0x00007f1117094000)
82     libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f1116e47000)
83     libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f1116b64000)
84     libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f1116960000)
85     libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f111672d000)
86     libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f111651e000)
87     libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f1116319000)
88     libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f11160ff000)
89     libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f1115eda000)
90     libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f1115c78000)
91     liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f1115a53000)
92     
93 ... //Omitted the procedure of installation on the other nodes.

 

**Initialized a PXC node with
proper pxc relevant parameters below.**

 1 [root@zlm2 08:51:49 ~]
 2 #mkdir -p /data/mysql/mysql3308/{data,logs,tmp}
 3 
 4 [root@zlm2 08:52:44 ~]
 5 #chown -R mysql.mysql /data
 6 
 7 [root@zlm2 08:53:27 ~]
 8 #cp /vagrant/my3308.cnf /data/mysql/mysql3308
 9 
10 [root@zlm2 08:54:09 ~]
11 #cd /data/mysql/mysql3308
12 
13 [root@zlm2 08:54:17 /data/mysql/mysql3308]
14 #ls -l
15 total 8
16 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 data
17 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 logs
18 -rwxr-xr-x 1 root  root  4522 Aug  7 08:54 my3308.cnf
19 drwxr-xr-x 2 mysql mysql    6 Aug  7 08:52 tmp
20 
21 [root@zlm2 08:54:30 /data/mysql/mysql3308]
22 #tail -20 my3308.cnf 
23 ...
24 #pxc
25 innodb_locks_unsafe_for_binlog=1
26 innodb_autoinc_lock_mode=2
27 loose-wsrep_cluster_name=pxc_wubx 
28 loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.103
29 loose-wsrep_node_address=192.168.1.101
30 loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
31 loose-wsrep_sst_method=xtrabackup-v2
32 #loose-wsrep_sst_method=rsync
33 loose-wsrep_sst_auth=sst:zlmzlm
34 #loose-wsrep_debug=on
35 #loose-wsrep_provider_options="debug=on"
36 [root@zlm2 08:54:38 /data/mysql/mysql3308]
37 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --initialize
38 
39 [root@zlm2 09:00:56 /data/mysql/mysql3308]
40 #tail ./data/error.log 
41 2018-08-07T09:00:47.560965+01:00 0 [Warning] CA certificate ca.pem is self signed.
42 2018-08-07T09:00:47.603271+01:00 1 [Note] A temporary password is generated for root@localhost: bY2jmV=k)ltM //This is the password we need when first login.
43 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
44 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
45 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
46 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
47 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
48 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
49 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
50 2018-08-07T09:00:48.459948+01:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
51 
52 [root@zlm2 09:02:45 /data/mysql/mysql3308]
53 #cd data
54 
55 [root@zlm2 09:02:57 /data/mysql/mysql3308/data]
56 #ls -l
57 total 409676
58 -rw-r----- 1 mysql mysql        56 Aug  7 09:00 auto.cnf
59 -rw------- 1 mysql mysql      1680 Aug  7 09:00 ca-key.pem
60 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 ca.pem
61 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 client-cert.pem
62 -rw------- 1 mysql mysql      1680 Aug  7 09:00 client-key.pem
63 -rw-r----- 1 mysql mysql      2354 Aug  7 09:00 error.log
64 -rw-r----- 1 mysql mysql       423 Aug  7 09:00 ib_buffer_pool
65 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ibdata1
66 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile0
67 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile1
68 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile2
69 drwxr-x--- 2 mysql mysql      4096 Aug  7 09:00 mysql
70 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 performance_schema
71 -rw------- 1 mysql mysql      1676 Aug  7 09:00 private_key.pem
72 -rw-r--r-- 1 mysql mysql       452 Aug  7 09:00 public_key.pem
73 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 server-cert.pem
74 -rw------- 1 mysql mysql      1676 Aug  7 09:00 server-key.pem
75 -rw-r----- 1 mysql mysql       227 Aug  7 09:00 slow.log
76 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 sys
77 
78 //Omitted the operations on the other nodes.Notice that there's no need to initialize the other node.

 

**Start mysqld of PXC on zlm2 as
the first node in PXC.**

  1 [root@zlm2 09:21:46 /data/mysql/mysql3308/data]
  2 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster &
  3 [1] 6251
  4 
  5 [root@zlm2 09:22:57 /data/mysql/mysql3308/data]
  6 #tail ../data/error.log |grep ERROR
  7 2018-08-07T09:22:57.471628+01:00 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/local/mysql/lib/libgalera_smm.so: symbol SSL_COMP_free_compression_methods, version libssl.so.10 not defined in file libssl.so.10 with link time reference
  8 2018-08-07T09:22:57.471657+01:00 0 [ERROR] WSREP: wsrep_load() failed to load the provider('/usr/local/mysql/lib/libgalera_smm.so'): Invalid argument (22). Need to abort.
  9 2018-08-07T09:22:57.471661+01:00 0 [ERROR] Aborting
 10 [1]+  Exit 1                  mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 11 
 12 [root@zlm2 09:23:19 /data/mysql/mysql3308/data]
 13 #cd /usr/local/mysql/lib
 14 
 15 [root@zlm2 09:26:28 /usr/local/mysql/lib]
 16 #ls -l|grep libgalera_smm.so
 17 -rwxr-xr-x 1 root root 35086192 Jun 29 05:44 libgalera_smm.so
 18 
 19 [root@zlm2 09:26:38 /usr/local/mysql/lib]
 20 #rpm -qa|grep openssl
 21 openssl-libs-1.0.1e-42.el7.9.x86_64
 22 openssl-devel-1.0.1e-42.el7.9.x86_64
 23 openssl-1.0.1e-42.el7.9.x86_64
 24 
 25 [root@zlm2 09:32:25 /usr/local/mysql/lib]
 26 #cd /usr/lib64
 27 
 28 [root@zlm2 09:34:03 /usr/lib64]
 29 #ls -l|grep libssl
 30 -rwxr-xr-x.  1 root root  255888 Mar  6  2015 libssl3.so
 31 lrwxrwxrwx   1 root root      16 Jul 16  2015 libssl.so -> libssl.so.1.0.1e
 32 lrwxrwxrwx   1 root root      16 Jul 16  2015 libssl.so.10 -> libssl.so.1.0.1e
 33 -rwxr-xr-x   1 root root  449864 Jun 29  2015 libssl.so.1.0.1e
 34 
 35 [root@zlm2 09:41:02 /usr/lib64]
 36 #yum install openssl
 37 ... //Omitted.
 38 
 39 Updated:
 40   openssl.x86_64 1:1.0.2k-12.el7                                                                                                                     
 41 
 42 Dependency Updated:
 43   openssl-devel.x86_64 1:1.0.2k-12.el7                                      openssl-libs.x86_64 1:1.0.2k-12.el7                                     
 44 
 45 Complete!
 46 
 47 [root@zlm2 09:44:39 /usr/lib64]
 48 #tail /data/mysql/mysql3308/data/error.log |grep ERROR
 49 
 50 [root@zlm2 09:45:15 /usr/lib64]
 51 #ps aux|grep mysqld
 52 mysql     6345  2.9 20.1 1556468 204812 pts/0  Sl   09:44   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
 53 root      6390  0.0  0.0 112640   960 pts/0    R+   09:45   0:00 grep --color=auto mysqld
 54 
 55 [root@zlm2 09:46:00 /usr/lib64]
 56 #cat /data/mysql/mysql3308/data/error.log |grep password
 57 2018-08-07T09:00:47.603271+01:00 1 [Note] A temporary password is generated for root@localhost: bY2jmV=k)ltM
 58 
 59 [root@zlm2 09:47:28 /usr/lib64]
 60 #mysql -uroot -p -S /tmp/mysql3308.sock
 61 Enter password: 
 62 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.101' (111)
 63 
 64 [root@zlm2 09:48:11 /usr/lib64]
 65 #ps aux|grep mysqld
 66 mysql     6345  0.6 20.1 1556468 204812 pts/0  Sl   09:44   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf --wsrep-new-cluster
 67 root      6433  0.0  0.0 112640   960 pts/0    R+   09:48   0:00 grep --color=auto mysqld
 68 
 69 [root@zlm2 09:57:21 /usr/lib64]
 70 #mysql -uroot -p -hlocalhost -S /tmp/mysql3308.sock
 71 Enter password: 
 72 Welcome to the MySQL monitor.  Commands end with ; or \g.
 73 Your MySQL connection id is 4
 74 Server version: 5.7.22-22-29.26-log
 75 
 76 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 77 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 78 
 79 Oracle is a registered trademark of Oracle Corporation and/or its
 80 affiliates. Other names may be trademarks of their respective
 81 owners.
 82 
 83 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 84 
 85 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to zlm@'192.168.1.%';
 86 ERROR 1133 (42000): Can't find any matching row in the user table
 87 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to zlm@'192.168.1.%' identified by 'zlmzlm';
 88 Query OK, 0 rows affected, 1 warning (0.34 sec)
 89 
 90 (root@localhost mysql3308.sock)[(none)]>grant reload,lock tables,replication client on *.* to sst@localhost identified by 'zlmzlm';
 91 Query OK, 0 rows affected, 2 warnings (0.03 sec)
 92 
 93 (root@localhost mysql3308.sock)[(none)]>select user,host from mysql.user;
 94 +---------------+-------------+
 95 | user          | host        |
 96 +---------------+-------------+
 97 | zlm           | 192.168.1.% |
 98 | mysql.session | localhost   |
 99 | mysql.sys     | localhost   |
100 | root          | localhost   |
101 | sst           | localhost   |
102 +---------------+-------------+
103 5 rows in set (0.00 sec)
104 
105 (root@localhost mysql3308.sock)[(none)]>show global status like '%wsrep%';
106 +----------------------------------+--------------------------------------+
107 | Variable_name                    | Value                                |
108 +----------------------------------+--------------------------------------+
109 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7 |
110 | wsrep_protocol_version           | 8                                    |
111 | wsrep_last_applied               | 2                                    |
112 | wsrep_last_committed             | 2                                    |
113 | wsrep_replicated                 | 2                                    |
114 | wsrep_replicated_bytes           | 432                                  |
115 | wsrep_repl_keys                  | 2                                    |
116 | wsrep_repl_keys_bytes            | 64                                   |
117 | wsrep_repl_data_bytes            | 232                                  |
118 | wsrep_repl_other_bytes           | 0                                    |
119 | wsrep_received                   | 2                                    |
120 | wsrep_received_bytes             | 141                                  |
121 | wsrep_local_commits              | 0                                    |
122 | wsrep_local_cert_failures        | 0                                    |
123 | wsrep_local_replays              | 0                                    |
124 | wsrep_local_send_queue           | 0                                    |
125 | wsrep_local_send_queue_max       | 1                                    |
126 | wsrep_local_send_queue_min       | 0                                    |
127 | wsrep_local_send_queue_avg       | 0.000000                             |
128 | wsrep_local_recv_queue           | 0                                    |
129 | wsrep_local_recv_queue_max       | 2                                    |
130 | wsrep_local_recv_queue_min       | 0                                    |
131 | wsrep_local_recv_queue_avg       | 0.500000                             |
132 | wsrep_local_cached_downto        | 1                                    |
133 | wsrep_flow_control_paused_ns     | 0                                    |
134 | wsrep_flow_control_paused        | 0.000000                             |
135 | wsrep_flow_control_sent          | 0                                    |
136 | wsrep_flow_control_recv          | 0                                    |
137 | wsrep_flow_control_interval      | [ 100, 100 ]                         |
138 | wsrep_flow_control_interval_low  | 100                                  |
139 | wsrep_flow_control_interval_high | 100                                  |
140 | wsrep_flow_control_status        | OFF                                  |
141 | wsrep_cert_deps_distance         | 1.000000                             |
142 | wsrep_apply_oooe                 | 0.000000                             |
143 | wsrep_apply_oool                 | 0.000000                             |
144 | wsrep_apply_window               | 1.000000                             |
145 | wsrep_commit_oooe                | 0.000000                             |
146 | wsrep_commit_oool                | 0.000000                             |
147 | wsrep_commit_window              | 1.000000                             |
148 | wsrep_local_state                | 4                                    |
149 | wsrep_local_state_comment        | Synced                               |
150 | wsrep_cert_index_size            | 1                                    |
151 | wsrep_cert_bucket_count          | 22                                   |
152 | wsrep_gcache_pool_size           | 1832                                 |
153 | wsrep_causal_reads               | 0                                    |
154 | wsrep_cert_interval              | 0.000000                             |
155 | wsrep_ist_receive_status         |                                      |
156 | wsrep_ist_receive_seqno_start    | 0                                    |
157 | wsrep_ist_receive_seqno_current  | 0                                    |
158 | wsrep_ist_receive_seqno_end      | 0                                    |
159 | wsrep_incoming_addresses         | 192.168.1.101:3308                   |
160 | wsrep_desync_count               | 0                                    |
161 | wsrep_evs_delayed                |                                      |
162 | wsrep_evs_evict_list             |                                      |
163 | wsrep_evs_repl_latency           | 0/0/0/0/0                            |
164 | wsrep_evs_state                  | OPERATIONAL                          |
165 | wsrep_gcomm_uuid                 | bd54497d-9a15-11e8-b386-2759969fcd01 |
166 | wsrep_cluster_conf_id            | 1                                    |
167 | wsrep_cluster_size               | 1                                    |
168 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7 |
169 | wsrep_cluster_status             | Primary                              |
170 | wsrep_connected                  | ON                                   |
171 | wsrep_local_bf_aborts            | 0                                    |
172 | wsrep_local_index                | 0                                    |
173 | wsrep_provider_name              | Galera                               |
174 | wsrep_provider_vendor            | Codership Oy <info@codership.com>    |
175 | wsrep_provider_version           | 3.26(r)                              |
176 | wsrep_ready                      | ON                                   |
177 +----------------------------------+--------------------------------------+
178 68 rows in set (0.00 sec)
179 
180 //Check the below global status of PXC.
181 | wsrep_local_state                | 4                                    |
182 | wsrep_local_state_comment        | Synced                               |
183 | wsrep_cluster_status             | Primary                              |
184 | wsrep_connected                  | ON                                   |
185 
186 //It means that the PXC has been worked normally on node zlm2.
187 
188 //The partial error log of PXC which shows the success of starting up of it.
189 2018-08-07T09:44:40.631123+01:00 0 [Note] Event Scheduler: Loaded 0 events
190 2018-08-07T09:44:40.631550+01:00 0 [Note] mysqld: ready for connections.
191 Version: '5.7.22-22-29.26-log'  socket: '/tmp/mysql3308.sock'  port: 3308  Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
192 2018-08-07T09:44:40.635469+01:00 2 [Note] WSREP: Initialized wsrep sidno 2
193 2018-08-07T09:44:40.635501+01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 1) (Increment: 1 -> 1)
194 2018-08-07T09:44:40.635510+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
195 2018-08-07T09:44:40.635525+01:00 2 [Note] WSREP: Assign initial position for certification: 0, protocol version: 3
196 2018-08-07T09:44:40.635540+01:00 0 [Note] WSREP: Service thread queue flushed.
197 2018-08-07T09:44:40.635577+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:0
198 2018-08-07T09:44:40.637360+01:00 2 [Note] WSREP: Synchronized with group, ready for connections 
199 2018-08-07T09:44:40.637369+01:00 2 [Note] WSREP: Setting wsrep_ready to true
200 2018-08-07T09:44:40.637372+01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
201 
202 //Check the files in datadir.
203 [root@zlm2 10:38:25 /data/mysql/mysql3308/data]
204 #ls -l
205 total 556916
206 -rw-r----- 1 mysql mysql        56 Aug  7 09:00 auto.cnf
207 -rw------- 1 mysql mysql      1680 Aug  7 09:00 ca-key.pem
208 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 ca.pem
209 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 client-cert.pem
210 -rw------- 1 mysql mysql      1680 Aug  7 09:00 client-key.pem
211 -rw-r----- 1 mysql mysql     24663 Aug  7 10:30 error.log
212 -rw-r----- 1 mysql mysql 134219048 Aug  7 10:30 galera.cache
213 -rw-r----- 1 mysql mysql       113 Aug  7 10:30 grastate.dat
214 -rw-r----- 1 mysql mysql       170 Aug  7 10:30 gvwstate.dat
215 -rw-r----- 1 mysql mysql       423 Aug  7 09:00 ib_buffer_pool
216 -rw-r----- 1 mysql mysql 104857600 Aug  7 10:16 ibdata1
217 -rw-r----- 1 mysql mysql 104857600 Aug  7 10:16 ib_logfile0
218 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile1
219 -rw-r----- 1 mysql mysql 104857600 Aug  7 09:00 ib_logfile2
220 -rw-r----- 1 mysql mysql  12582912 Aug  7 09:44 ibtmp1
221 drwxr-x--- 2 mysql mysql      4096 Aug  7 09:00 mysql
222 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 performance_schema
223 -rw------- 1 mysql mysql      1676 Aug  7 09:00 private_key.pem
224 -rw-r--r-- 1 mysql mysql       452 Aug  7 09:00 public_key.pem
225 -rw-r--r-- 1 mysql mysql      1120 Aug  7 09:00 server-cert.pem
226 -rw------- 1 mysql mysql      1676 Aug  7 09:00 server-key.pem
227 -rw-r----- 1 mysql mysql       454 Aug  7 09:44 slow.log
228 drwxr-x--- 2 mysql mysql      8192 Aug  7 09:00 sys
229 -rw-r----- 1 mysql mysql   3932160 Aug  7 10:16 xb_doublewrite
230 -rw-r----- 1 mysql mysql         5 Aug  7 09:44 zlm2.pid
231 
232 [root@zlm2 10:38:27 /data/mysql/mysql3308/data]
233 #cat grastate.dat
234 # GALERA saved state
235 version: 2.1
236 uuid:    bd5525ab-9a15-11e8-aa0f-4b830c783fc7
237 seqno:   -1
238 safe_to_bootstrap: 1 //This means the node is the first one who starts the cluster.

 

**Start mysqld of PXC of the
remaining nodes.**

  1 //Let's start the PXC on node zlm3.
  2 [root@zlm3 10:00:12 /data/mysql/mysql3308/data]
  3 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
  4 [1] 4181
  5 
  6 [root@zlm3 10:30:43 /data/mysql/mysql3308/data]
  7 #ps aux|grep mysqld
  8 mysql     4181 20.0 16.9 756016 172580 pts/0   Sl   10:30   0:01 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
  9 root      4596  0.0  0.0 112640   960 pts/0    R+   10:30   0:00 grep --color=auto mysqld
 10 
 11 [root@zlm3 10:31:10 /data/mysql/mysql3308/data]
 12 #tail -1000 error.log 
 13 2018-08-07T09:54:08.825870+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 14 2018-08-07T09:54:08.825960+01:00 0 [Warning] WSREP: Node is running in bootstrap/initialize mode. Disabling pxc_strict_mode checks
 15 2018-08-07T09:54:08.836945+01:00 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
 16  100
 17  100
 18  100
 19  100
 20 2018-08-07T09:54:27.784997+01:00 0 [Warning] InnoDB: New log files created, LSN=45790
 21 2018-08-07T09:54:28.565405+01:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 22 2018-08-07T09:54:29.043051+01:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1ca6f9ae-9a17-11e8-9a7c-080027de0e0e.
 23 2018-08-07T09:54:29.054082+01:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 24 2018-08-07T09:54:29.471412+01:00 0 [Warning] CA certificate ca.pem is self signed.
 25 2018-08-07T09:54:29.536187+01:00 1 [Note] A temporary password is generated for root@localhost: ,M3=why-ok&d
 26 2018-08-07T09:55:01.556746+01:00 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
 27 2018-08-07T09:55:01.556802+01:00 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
 28 2018-08-07T09:55:01.556812+01:00 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
 29 2018-08-07T09:55:01.556830+01:00 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
 30 2018-08-07T09:55:01.556837+01:00 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
 31 2018-08-07T09:55:01.556847+01:00 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
 32 2018-08-07T09:55:01.556920+01:00 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
 33 2018-08-07T09:55:01.556931+01:00 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
 34 
 35 //The starting mysqld operation begins here.
 36 2018-08-07T10:30:43.794296+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 37 2018-08-07T10:30:43.801975+01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
 38 2018-08-07T10:30:43.802040+01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 4181 ...
 39 2018-08-07T10:30:43.805420+01:00 0 [Note] WSREP: Setting wsrep_ready to false //Not ready.
 40 2018-08-07T10:30:43.805476+01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
 41 2018-08-07T10:30:43.805486+01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
 42 2018-08-07T10:30:43.829026+01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
 43 2018-08-07T10:30:43.829070+01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
 44 2018-08-07T10:30:43.829385+01:00 0 [Warning] WSREP: Could not open state file for reading: '/data/mysql/mysql3308/data//grastate.dat'
 45 2018-08-07T10:30:43.829395+01:00 0 [Warning] WSREP: No persistent state found. Bootstraping with default state
 46 2018-08-07T10:30:43.829432+01:00 0 [Note] WSREP: Found saved state: 00000000-0000-0000-0000-000000000000:-1, safe_to_bootstrap: 1
 47 2018-08-07T10:30:43.837823+01:00 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/mysql3308/data/; base_host = 192.168.1.102; 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 = /data/mysql/mysql3308/data/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/mysql3308/data//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 = 8; socket.checksum = 2; socket.recv_buf_size = 212992; 
 48 2018-08-07T10:30:43.861834+01:00 0 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 00000000-0000-0000-0000-000000000000:-1
 49 2018-08-07T10:30:43.866886+01:00 0 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1
 50 2018-08-07T10:30:43.866919+01:00 0 [Note] WSREP: Preparing to initiate SST/IST //Begin to SST or IST.
 51 2018-08-07T10:30:43.866922+01:00 0 [Note] WSREP: Starting replication
 52 2018-08-07T10:30:43.866936+01:00 0 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1
 53 2018-08-07T10:30:43.867102+01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
 54 2018-08-07T10:30:43.867166+01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
 55 2018-08-07T10:30:43.869814+01:00 0 [Warning] WSREP: Fail to access the file (/data/mysql/mysql3308/data//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
 56 2018-08-07T10:30:43.869842+01:00 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
 57 2018-08-07T10:30:43.870450+01:00 0 [Note] WSREP: GMCast version 0
 58 2018-08-07T10:30:43.870721+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
 59 2018-08-07T10:30:43.870729+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
 60 2018-08-07T10:30:43.871017+01:00 0 [Note] WSREP: EVS version 0
 61 2018-08-07T10:30:43.871191+01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.103:'
 62 2018-08-07T10:30:43.873170+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection established to 2cf33746 tcp://192.168.1.102:4567
 63 2018-08-07T10:30:43.873199+01:00 0 [Warning] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.102:4567' points to own listening address, blacklisting
 64 2018-08-07T10:30:43.873754+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection established to bd54497d tcp://192.168.1.101:4567
 65 2018-08-07T10:30:43.873806+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 66 2018-08-07T10:30:44.373051+01:00 0 [Note] WSREP: declaring bd54497d at tcp://192.168.1.101:4567 stable
 67 2018-08-07T10:30:44.373698+01:00 0 [Note] WSREP: Node bd54497d state primary
 68 2018-08-07T10:30:44.374205+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 69 view (view_id(PRIM,2cf33746,2)
 70 memb {
 71     2cf33746,0 //This is node zlm2,first one started the cluster.
 72     bd54497d,0 //This is node zlm3.
 73     }
 74 joined {
 75     }
 76 left {
 77     }
 78 partitioned {
 79     }
 80 )
 81 2018-08-07T10:30:44.374227+01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 82 2018-08-07T10:30:44.374354+01:00 0 [Note] WSREP: discarding pending addr without UUID: tcp://192.168.0.103:4567
 83 2018-08-07T10:30:44.374361+01:00 0 [Note] WSREP: discarding pending addr proto entry 0x378db50
 84 2018-08-07T10:30:44.872036+01:00 0 [Note] WSREP: gcomm: connected
 85 2018-08-07T10:30:44.872107+01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
 86 2018-08-07T10:30:44.872173+01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
 87 2018-08-07T10:30:44.872327+01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 0, memb_num = 2
 88 2018-08-07T10:30:44.872840+01:00 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5
 89 2018-08-07T10:30:44.873577+01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5
 90 2018-08-07T10:30:44.874140+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5 from 0 (zlm3)
 91 2018-08-07T10:30:44.874158+01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 2d8c2e2a-9a1c-11e8-8388-a6fcb0b47ae5 from 1 (zlm2)
 92 2018-08-07T10:30:44.874169+01:00 0 [Note] WSREP: Quorum results:
 93     version    = 4,
 94     component  = PRIMARY,
 95     conf_id    = 1,
 96     members    = 1/2 (primary/total),
 97     act_id     = 18,
 98     last_appl. = -1,
 99     protocols  = 0/8/3 (gcs/repl/appl),
100     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
101 2018-08-07T10:30:44.874173+01:00 0 [Note] WSREP: Flow-control interval: [141, 141] //The scope of fc interval.
102 2018-08-07T10:30:44.874176+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
103 2018-08-07T10:30:44.874179+01:00 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 18)
104 2018-08-07T10:30:44.874224+01:00 2 [Note] WSREP: State transfer required: 
105     Group state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18
106     Local state: 00000000-0000-0000-0000-000000000000:-1
107 2018-08-07T10:30:44.874231+01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
108 2018-08-07T10:30:44.874237+01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18, view# 2: Primary, number of nodes: 2, my index: 0, protocol version 3
109 2018-08-07T10:30:44.874241+01:00 2 [Note] WSREP: Setting wsrep_ready to true
110 2018-08-07T10:30:44.874243+01:00 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
111 2018-08-07T10:30:44.874245+01:00 2 [Note] WSREP: Setting wsrep_ready to false
112 2018-08-07T10:30:44.874315+01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
113 which: no socat in (/usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin)
114     2018-08-07T08:30:45.523175Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
115     2018-08-07T08:30:45.524742Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
116     2018-08-07T08:30:45.525848Z WSREP_SST: [ERROR] ****************************************************** 
117 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
118     Read: '(null)'
119 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
120 2018-08-07T10:30:45.526201+01:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
121 2018-08-07T10:30:45.526208+01:00 2 [ERROR] Aborting
122 
123 2018-08-07T10:30:45.526211+01:00 2 [Note] Giving 2 client threads a chance to die gracefully
124 2018-08-07T10:30:47.373430+01:00 0 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting off
125 2018-08-07T10:30:47.526391+01:00 2 [Note] WSREP: Closing send monitor...
126 2018-08-07T10:30:47.526431+01:00 2 [Note] WSREP: Closed send monitor.
127 2018-08-07T10:30:47.526455+01:00 2 [Note] WSREP: gcomm: terminating thread
128 2018-08-07T10:30:47.526465+01:00 2 [Note] WSREP: gcomm: joining thread
129 2018-08-07T10:30:47.526479+01:00 1 [Note] WSREP: rollbacker thread exiting
130 2018-08-07T10:30:47.526675+01:00 2 [Note] WSREP: gcomm: closing backend
131 2018-08-07T10:30:50.874663+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') connection to peer bd54497d with addr tcp://192.168.1.101:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
132 2018-08-07T10:30:50.874839+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.101:4567 
133 2018-08-07T10:30:51.877925+01:00 2 [Note] WSREP: (2cf33746, 'tcp://0.0.0.0:4567') reconnecting to bd54497d (tcp://192.168.1.101:4567), attempt 0
134 2018-08-07T10:30:53.026867+01:00 2 [Note] WSREP: declaring node with index 1 suspected, timeout PT5S (evs.suspect_timeout)
135 2018-08-07T10:30:53.027032+01:00 2 [Note] WSREP: evs::proto(2cf33746, LEAVING, view_id(REG,2cf33746,2)) suspecting node: bd54497d
136 2018-08-07T10:30:53.027042+01:00 2 [Note] WSREP: evs::proto(2cf33746, LEAVING, view_id(REG,2cf33746,2)) suspected node without join message, declaring inactive
137 2018-08-07T10:30:53.027063+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
138 view (view_id(NON_PRIM,2cf33746,2)
139 memb {
140     2cf33746,0 //Only remains the first node zlm2.
141     }
142 joined {
143     }
144 left {
145     }
146 partitioned {
147     bd54497d,0 //Node zlm3 became a suspected node and be moved here.
148     }
149 )
150 2018-08-07T10:30:53.027092+01:00 2 [Note] WSREP: Current view of cluster as seen by this node
151 view ((empty))
152 2018-08-07T10:30:53.027370+01:00 2 [Note] WSREP: gcomm: closed
153 2018-08-07T10:30:53.027421+01:00 0 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1
154 2018-08-07T10:30:53.027435+01:00 0 [Note] WSREP: Flow-control interval: [100, 100]
155 2018-08-07T10:30:53.027438+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
156 2018-08-07T10:30:53.027443+01:00 0 [Note] WSREP: Received NON-PRIMARY.
157 2018-08-07T10:30:53.027447+01:00 0 [Note] WSREP: Shifting PRIMARY -> OPEN (TO: 18)
158 2018-08-07T10:30:53.027458+01:00 0 [Note] WSREP: Received self-leave message.
159 2018-08-07T10:30:53.027463+01:00 0 [Note] WSREP: Flow-control interval: [0, 0]
160 2018-08-07T10:30:53.027466+01:00 0 [Note] WSREP: Trying to continue unpaused monitor
161 2018-08-07T10:30:53.027469+01:00 0 [Note] WSREP: Received SELF-LEAVE. Closing connection.
162 2018-08-07T10:30:53.027472+01:00 0 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 18)
163 2018-08-07T10:30:53.027477+01:00 0 [Note] WSREP: RECV thread exiting 0: Success
164 2018-08-07T10:30:53.027719+01:00 2 [Note] WSREP: recv_thread() joined.
165 2018-08-07T10:30:53.027732+01:00 2 [Note] WSREP: Closing replication queue.
166 2018-08-07T10:30:53.027738+01:00 2 [Note] WSREP: Closing slave action queue.
167 2018-08-07T10:30:53.027796+01:00 0 [Note] WSREP: Waiting for active wsrep applier to exit
168 2018-08-07T10:30:53.027804+01:00 0 [Note] WSREP: Service disconnected.
169 2018-08-07T10:30:53.027807+01:00 0 [Note] WSREP: Waiting to close threads......
170 2018-08-07T10:30:58.028048+01:00 0 [Note] WSREP: Some threads may fail to exit.
171 2018-08-07T10:30:58.028088+01:00 0 [Note] Binlog end
172 2018-08-07T10:30:58.028341+01:00 0 [Note] mysqld: Shutdown complete
173 
174 [root@zlm3 10:35:04 /data/mysql/mysql3308/data]
175 #ps aux|grep mysqld
176 root      4672  0.0  0.0 112640   960 pts/0    R+   10:35   0:00 grep --color=auto mysqld
177 
178 //Let's see the detail of ERROR message.
179 [root@zlm3 10:35:06 /data/mysql/mysql3308/data]
180 #cat error.log |grep ERROR
181     2018-08-07T08:30:45.523175Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
182     2018-08-07T08:30:45.524742Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
183     2018-08-07T08:30:45.525848Z WSREP_SST: [ERROR] ****************************************************** 
184 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
185 2018-08-07T10:30:45.526151+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.102' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4181' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
186 2018-08-07T10:30:45.526201+01:00 2 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
187 2018-08-07T10:30:45.526208+01:00 2 [ERROR] Aborting
188 
189 //It says that we are absent of "socat" package.
190 
191 [root@zlm3 10:43:15 /data/mysql/mysql3308/data]
192 #yum install socat
193 ... //Omitted.
194 
195 Warning: RPMDB altered outside of yum.
196   Installing : socat-1.7.3.2-2.el7.x86_64                                                                                                        1/1 
197   Verifying  : socat-1.7.3.2-2.el7.x86_64                                                                                                        1/1 
198 
199 Installed:
200   socat.x86_64 0:1.7.3.2-2.el7                                                                                                                       
201 
202 Complete!
203 
204 //Start the PXC on zlm2 again.
205 [root@zlm3 10:35:58 /data/mysql/mysql3308/data]
206 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
207 [1] 4725
208 
209 [root@zlm3 10:53:02 /data/mysql/mysql3308/data]
210 #tail -f error.log 
211 2018-08-07T10:53:15.116919+01:00 0 [Note] WSREP: Member 0.0 (zlm3) requested state transfer from '*any*'. Selected 1.0 (zlm2)(SYNCED) as donor.
212 2018-08-07T10:53:15.117004+01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 18)
213 2018-08-07T10:53:15.117035+01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 1
214 2018-08-07T10:53:15.117051+01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:18
215 2018-08-07T10:53:15.696885+01:00 0 [Warning] WSREP: 1.0 (zlm2): State transfer to 0.0 (zlm3) failed: -2 (No such file or directory)
216 2018-08-07T10:53:15.696918+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
217 2018-08-07T10:53:15.696940+01:00 0 [Note] WSREP: gcomm: terminating thread
218 2018-08-07T10:53:15.696949+01:00 0 [Note] WSREP: gcomm: joining thread
219 2018-08-07T10:53:15.697076+01:00 0 [Note] WSREP: gcomm: closing backend
220 2018-08-07T10:53:17.026257+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') turning message relay requesting off
221 2018-08-07T10:53:19.026327+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') connection to peer bd54497d with addr tcp://192.168.1.101:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)
222 2018-08-07T10:53:19.026394+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.101:4567 
223 2018-08-07T10:53:20.086590+01:00 0 [Note] WSREP: (5167e9c7, 'tcp://0.0.0.0:4567') reconnecting to bd54497d (tcp://192.168.1.101:4567), attempt 0
224 2018-08-07T10:53:20.786605+01:00 0 [Note] WSREP: declaring node with index 1 suspected, timeout PT5S (evs.suspect_timeout)
225 2018-08-07T10:53:20.786778+01:00 0 [Note] WSREP: evs::proto(5167e9c7, LEAVING, view_id(REG,5167e9c7,4)) suspecting node: bd54497d
226 2018-08-07T10:53:20.786826+01:00 0 [Note] WSREP: evs::proto(5167e9c7, LEAVING, view_id(REG,5167e9c7,4)) suspected node without join message, declaring inactive
227 2018-08-07T10:53:20.786941+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
228 view (view_id(NON_PRIM,5167e9c7,4)
229 memb {
230     5167e9c7,0
231     }
232 joined {
233     }
234 left {
235     }
236 partitioned {
237     bd54497d,0
238     }
239 )
240 2018-08-07T10:53:20.787061+01:00 0 [Note] WSREP: Current view of cluster as seen by this node
241 view ((empty))
242 2018-08-07T10:53:20.787324+01:00 0 [Note] WSREP: gcomm: closed
243 2018-08-07T10:53:20.787362+01:00 0 [Note] WSREP: mysqld: Terminated.
244 ^C
245 
246 [root@zlm3 10:53:48 /data/mysql/mysql3308/data]
247 #cat error.log |grep ERROR
248 2018-08-07T10:53:15.696918+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
249     
250 [root@zlm3 11:13:14 /data/mysql/mysql3308/data]
251 #ps aux|grep mysqld
252 root      6785  0.0  0.0 112640   960 pts/0    R+   11:20   0:00 grep --color=auto mysqld
253 [1]+  Aborted                 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
254 
255 [root@zlm3 11:20:16 /data/mysql/mysql3308/data]
256 #cat error.log |grep ERROR
257 2018-08-07T11:13:15.619315+01:00 0 [ERROR] WSREP: gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():766: Will never receive state. Need to abort.
258     2018-08-07T09:13:45.246252Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
259     2018-08-07T09:13:45.247507Z WSREP_SST: [ERROR] Error while getting data from donor node:  exit codes: 255 0
260     2018-08-07T09:13:45.254423Z WSREP_SST: [ERROR] ****************************************************** 
261     2018-08-07T09:13:45.258735Z WSREP_SST: [ERROR] Cleanup after exit with status:32
262     2018-08-07T09:14:10.137993Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
263     2018-08-07T09:14:10.139033Z WSREP_SST: [ERROR] Possible timeout in receving first data from donor in gtid/keyring stage
264     2018-08-07T09:14:10.140912Z WSREP_SST: [ERROR] ****************************************************** 
265     2018-08-07T09:14:10.141951Z WSREP_SST: [ERROR] Cleanup after exit with status:32
266     
267 //There's still one error which cause the mysqld process cannot be started.

 

Start mysqld of PXC on node
zlm4.

 1 [root@zlm4 11:27:18 /data/mysql/mysql3308/data]
 2 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf initialize
 3 
 4 [root@zlm4 11:27:37 /data/mysql/mysql3308/data]
 5 #ls -l
 6 total 131092
 7 -rw-r----- 1 mysql mysql     13275 Aug  7 11:27 error.log
 8 -rw-r----- 1 mysql mysql 134219048 Aug  7 11:27 galera.cache
 9 -rw-r----- 1 mysql mysql         0 Aug  7 11:27 grastate.dat
10 
11 [root@zlm4 11:27:48 /data/mysql/mysql3308/data]
12 #cat error.log |grep ERROR
13     2018-08-07T09:27:24.770212Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
14     2018-08-07T09:27:24.771275Z WSREP_SST: [ERROR] xtrabackup not in path: /usr/sbin:/sbin:/opt/mysql/Percona-XtraDB-Cluster-5.7.22-rel22-29.26.1.Linux.x86_64.ssl102/bin:/root/perl5/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/zabbix/bin:/usr/local/zabbix/sbin
15     2018-08-07T09:27:24.771951Z WSREP_SST: [ERROR] ****************************************************** 
16 2018-08-07T11:27:24.772254+01:00 0 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4363' --binlog '/data/mysql/mysql3308/logs/mysql-bin' 
17 2018-08-07T11:27:24.772279+01:00 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4363' --binlog '/data/mysql/mysql3308/logs/mysql-bin' : 2 (No such file or directory)
18 2018-08-07T11:27:24.772317+01:00 1 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
19 2018-08-07T11:27:24.772322+01:00 1 [ERROR] Aborting
20 
21 [root@zlm4 11:28:39 /data/mysql/mysql3308/data]
22 #innobackupex --help
23 -bash: innobackupex: command not found
24 
25 //It's due to absence of Xtrabackup tools on node zlm4.

 

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图