何以搭建Percona XtraDB Cluster集群

什么样搭建Percona XtraDB Cluster集群

怎么搭建Percona XtraDB Cluster集群

一、景况谋算

     主机IP                     主机名               操作系统版本    
PXC

     192.168.244.146     node1              CentOS7.1    
 Percona-XtraDB-Cluster-56-5.6.30

     192.168.244.147     node2              CentOS7.1    
 Percona-XtraDB-Cluster-56-5.6.30

     192.168.244.148     node3              CentOS7.1    
 Percona-XtraDB-Cluster-56-5.6.30

     关闭防火墙或然允许3306, 4444,
4567和4568多个端口的接连

     关闭SElinux

二、下载PXC

     安装PXC yum源

     # yum install

     那样会在/etc/yum.repos.d下生成percona-release.repo文件

     安装PXC

     # yum install Percona-XtraDB-Cluster-56

     最后下载下来的版本是Percona-XtraDB-Cluster-56-5.6.30

     注意:八个节点上均要设置。

三、配置节点

     配置节点一

     修改node1的/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.244.146

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

     启动node1

     # systemctl start
[email protected]

    注意:这么些是CentOS 7下的运营格局,借使是CentOS 6,则运营格局为 #
/etc/init.d/mysql bootstrap-pxc

   
之所以选取bootstrap运维,其实是告诉数据库,那是首先个节点,不用进行多少的同步。

    利用这种措施运行,相当于wsrep_cluster_address格局设置为gcomm://。

    此时,可登入顾客端查看数据库的景况

    mysql> show status like ‘wsrep%’;

    首要关切之下参数的动静

+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid       | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c |
 ...
| wsrep_local_state            | 4   
| wsrep_local_state_comment    | Synced                               |
 ...
| wsrep_cluster_size           | 1
 ...
| wsrep_cluster_status         | Primary                              |
| wsrep_connected              | ON                                   |
 ...
| wsrep_ready                  | ON                                   |

   
在地点的安插文件中,有个wsrep_sst_auth参数。该参数是用于其余节点插手到该集群中,利用XtraBackup奉行State
Snapshot Transfer(类似于全量同步)的。

    所以,接下去是授权 

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

    配置节点二

    修改node2的/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.244.147

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"     

     启动node2

     # systemctl start mysql

     如若是CentOS 6,则运行方式为 # /etc/init.d/mysql start

   
 假诺在运行的经过中冒出难点,可查阅mysql的错误日志,即便是RPM安装,暗中同意是/var/lib/mysql/主机名.err 

     运行完成后,也可经过mysql> show status like
‘wsrep%’;命令查看集群的信息。

     配置节点三

     修改node3的/etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #3 address
wsrep_node_address=192.168.244.148

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

金莎娱乐手机版 1

   

     启动node3

     # systemctl start mysql

     登陆数据库,查看集群的动静

+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid       | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c |
 ...
| wsrep_local_state            | 4   
| wsrep_local_state_comment    | Synced                               |
 ...
| wsrep_cluster_size           | 3
 ...
| wsrep_cluster_status         | Primary                              |
| wsrep_connected              | ON                                   |
 ...
| wsrep_ready                  | ON        

    通过wsrep_cluster_size能够看看集群有3个节点。

四、
测试

   
 下边来测量检验一把,在node3中开创一张表,并插入记录,看node1和node2中是不是查询得到。

      node3中开创测验表并插入记录

root@node3 > create table test.test(id int,description varchar(10));
Query OK, 0 rows affected (0.18 sec)

root@node3 > insert into test.test values(1,'hello,pxc');
Query OK, 1 row affected (0.01 sec)

      node1和node2中查询

root@node1 > select * from test.test;
+------+-------------+
| id   | description |
+------+-------------+
|    1 | hello,pxc   |
+------+-------------+
1 row in set (0.00 sec)

root@node2 > select * from test.test;
+------+-------------+
| id   | description |
+------+-------------+
|    1 | hello,pxc   |
+------+-------------+
1 row in set (0.05 sec) 

迄今,Percona XtraDB Cluster搭建完毕~

总结:

  1. 刚开始起步node2的时候,运营失利,错误日志中报如下音讯:

    2016-06-15 20:06:09 4937 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)

          at gcomm/src/pc.cpp:connect():162
    

    2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
    2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1387: Failed to open channel ‘my_centos_cluster’ at ‘gcomm://192.168.244.146,192.168.244.147,192.168.244.148’: -110 (Connection timed out)
    2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs connect failed: Connection timed out
    2016-06-15 20:06:09 4937 [ERROR] WSREP: wsrep::connect(gcomm://192.168.244.146,192.168.244.147,192.168.244.148) failed: 7
    2016-06-15 20:06:09 4937 [ERROR] Aborting

    2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to read ‘ready ‘ from: wsrep_sst_xtrabackup-v2 –role ‘joiner’ –address ‘192.168.244.147’ –datadir ‘/var/lib/mysql/’ –defaults-file ‘/etc/my.cnf’ –defaults-group-suffix ” –parent ‘5870’ ”

         Read: '(null)'
    

    2016-06-15 20:27:03 5870 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 –role ‘joiner’ –address ‘192.168.244.147’ –datadir ‘/var/lib/mysql/’ –defaults-file ‘/etc/my.cnf’ –defaults-group-suffix ” –parent ‘5870’ ” : 2 (No such file or directory)
    2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to prepare for ‘xtrabackup-v2’ SST. Unrecoverable.
    2016-06-15 20:27:03 5870 [ERROR] Aborting

专程是上边的报错音讯,依照

新生才发现,是S埃利nux未有关闭。。。

另外,在节点参与集群的经过中,假如报关于xtrabackup-v2的一无所长,不要紧先将wsrep_sst_method的办法设置为rsync大概mysqldump,看是或不是得逞。

  1. 以systemctl
    start [email protected]运营的节点,必得以systemctl
    stop
    [email protected]关闭,假设以systemctl
    stop mysql关闭,则没意义。

仿照效法文书档案:

1. 

  1. PXC官方文档

3. 

Percona XtraDB Cluster
的详细介绍:请点这里
Percona XtraDB Cluster
的下载地址:请点这里

本文长久更新链接地址:

XtraDB Cluster集群
怎么样搭建Percona XtraDB Cluster集群 一、情形计划 主机IP 主机名
操作系统版本 PXC 192.168.244.146 node1 CentOS7.1 Perco…

什么样搭建Percona XtraDB Cluster集群,perconaxtradb

一、情况打算

     主机IP                     主机名               操作系统版本    
PXC

     192.168.244.146     node1              CentOS7.1    
 Percona-XtraDB-Cluster-56-5.6.30

     192.168.244.147     node2              CentOS7.1    
 Percona-XtraDB-Cluster-56-5.6.30

     192.168.244.148     node3              CentOS7.1    
 Percona-XtraDB-Cluster-56-5.6.30

     关闭防火墙只怕允许3306, 4444, 4567和4568七个端口的总是

     关闭SElinux

     

二、下载PXC

     安装PXC yum源

     # yum install

     那样会在/etc/yum.repos.d下生成percona-release.repo文件

     安装PXC

     # yum install Percona-XtraDB-Cluster-56

     最后下载下来的本子是Percona-XtraDB-Cluster-56-5.6.30

     注意:四个节点上均要安装。

 

三、配置节点

     配置节点一

     修改node1的/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.244.146

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

     

     启动node1

     # systemctl start
[email protected]

    注意:这几个是CentOS 7下的启航航空模型型式,如若是CentOS 6,则运营情势为 #
/etc/init.d/mysql bootstrap-pxc

   
之所以选拔bootstrap运营,其实是告诉数据库,那是率先个节点,不用举办数据的一块。

    利用这种格局运维,约等于wsrep_cluster_address格局设置为gcomm://。

 

    此时,可记名客商端查看数据库的图景

    mysql> show status like ‘wsrep%’;

    首要关切之下参数的景况

+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid       | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c |
 ...
| wsrep_local_state            | 4   
| wsrep_local_state_comment    | Synced                               |
 ...
| wsrep_cluster_size           | 1
 ...
| wsrep_cluster_status         | Primary                              |
| wsrep_connected              | ON                                   |
 ...
| wsrep_ready                  | ON                                   |

 

   
在地点的布署文件中,有个wsrep_sst_auth参数。该参数是用于另外节点参预到该集群中,利用XtraBackup推行State
Snapshot Transfer(类似于全量同步)的。

    所以,接下去是授权 

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

     

    配置节点二

    修改node2的/etc/my.cnf

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.244.147

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

     

     启动node2

     # systemctl start mysql

     假设是CentOS 6,则运营情势为 # /etc/init.d/mysql start

   
 假使在运营的进程中冒出难点,可查看mysql的一无是处日志,假诺是RPM安装,暗许是/var/lib/mysql/主机名.err 

     运行完结后,也可透过mysql> show status like
‘wsrep%’;命令查看集群的消息。

     

     配置节点三

     修改node3的/etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.244.146,192.168.244.147,192.168.244.148

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #3 address
wsrep_node_address=192.168.244.148

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

    

     启动node3

     # systemctl start mysql

     登入数据库,查看集群的景况

+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid       | 1fbb69e3-32a3-11e6-a571-aeaa962bae0c |
 ...
| wsrep_local_state            | 4   
| wsrep_local_state_comment    | Synced                               |
 ...
| wsrep_cluster_size           | 3
 ...
| wsrep_cluster_status         | Primary                              |
| wsrep_connected              | ON                                   |
 ...
| wsrep_ready                  | ON        

    通过wsrep_cluster_size可以看到集群有3个节点。

   

四、 测试

   
 上面来测量检验一把,在node3中创建一张表,并插入记录,看node1和node第22中学能或无法查询获得。

      node3中创建测量试验表并插入记录

root@node3 > create table test.test(id int,description varchar(10));
Query OK, 0 rows affected (0.18 sec)

root@node3 > insert into test.test values(1,'hello,pxc');
Query OK, 1 row affected (0.01 sec)

      node1和node2中查询

root@node1 > select * from test.test;
+------+-------------+
| id   | description |
+------+-------------+
|    1 | hello,pxc   |
+------+-------------+
1 row in set (0.00 sec)

root@node2 > select * from test.test;
+------+-------------+
| id   | description |
+------+-------------+
|    1 | hello,pxc   |
+------+-------------+
1 row in set (0.05 sec)

 

从那之后,Percona XtraDB Cluster搭建完成~

 

总结:

  1. 刚起先起步node2的时候,运行退步,错误日志中报如下新闻:

    2016-06-15 20:06:09 4937 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)

          at gcomm/src/pc.cpp:connect():162
    

    2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
    2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1387: Failed to open channel ‘my_centos_cluster’ at ‘gcomm://192.168.244.146,192.168.244.147,192.168.244.148’: -110 (Connection timed out)
    2016-06-15 20:06:09 4937 [ERROR] WSREP: gcs connect failed: Connection timed out
    2016-06-15 20:06:09 4937 [ERROR] WSREP: wsrep::connect(gcomm://192.168.244.146,192.168.244.147,192.168.244.148) failed: 7
    2016-06-15 20:06:09 4937 [ERROR] Aborting

    2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to read ‘ready ‘ from: wsrep_sst_xtrabackup-v2 –role ‘joiner’ –address ‘192.168.244.147’ –datadir ‘/var/lib/mysql/’ –defaults-file ‘/etc/my.cnf’ –defaults-group-suffix ” –parent ‘5870’ ”

         Read: '(null)'
    

    2016-06-15 20:27:03 5870 [ERROR] WSREP: Process completed with error: wsrep_sst金莎娱乐手机版,_xtrabackup-v2 –role ‘joiner’ –address ‘192.168.244.147’ –datadir ‘/var/lib/mysql/’ –defaults-file ‘/etc/my.cnf’ –defaults-group-suffix ” –parent ‘5870’ ” : 2 (No such file or directory)
    2016-06-15 20:27:03 5870 [ERROR] WSREP: Failed to prepare for ‘xtrabackup-v2’ SST. Unrecoverable.
    2016-06-15 20:27:03 5870 [ERROR] Aborting

特别是下面的报错消息,依照

新生才意识,是S埃利nux未有关闭。。。

别的,在节点参与集群的长河中,借使报有关xtrabackup-v2的荒谬,无妨先将wsrep_sst_method的格局设置为rsync只怕mysqldump,看是不是得逞。

  1. 以systemctl
    start [email protected]开发银行的节点,必得以systemctl
    stop
    [email protected]关门,即便以systemctl
    stop mysql关闭,则没效果。

 

参照文档:

1. 

  1. PXC官方文书档案

3. 

    

 

    

 

     

XtraDB Cluster集群,perconaxtradb
一、情形策画 主机IP 主机名 操作系统版本 PXC 192.168.244.146 node1
CentOS7.1 Percona-XtraDB-Cluster-56-5.6….

相关文章