Percona集群制定的服务器节点如下:
node #1 hostname: pzsd01 IP: 10.1.11.14 node #2 hostname: pzsd02 IP: 10.1.11.15 node #3 hostname: pzsd03 IP: 10.1.11.16
*所有节点都是安装了Linux CentOS 6.4
*防火墙关闭
*selinux disabled
*安装
-
部署percona和epel知识库
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
rpm -Uvh
http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum install Percona-XtraDB-Cluster-56
*配置三个服务器节点
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.1.11.15,10.1.11.16,10.1.11.17
# 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=10.1.11.15
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=my_centos_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
配置完之后,第一个节点启动,使用如下命令:
[root@percona1 ~]# /etc/init.d/mysql bootstrap-pxc
这条命令可以通过这个wsrep_cluster_address=gcomm://10.1.11.15,10.1.11.16,10.1.11.17。启动集群,percona集群将会被引导。
第一个节点,启动之后,可以通过下列命令来验证
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------+--------------------------------------+ | wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec | ... | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | ... | wsrep_cluster_size | 3 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | ... | wsrep_ready | ON | +----------------------------+--------------------------------------+有如上的输出,说明集群已经被正确的引导
设置root账号的密码如下:
mysql@percona1>UPDATE mysql.user SET password=PASSWORD("Passw0rd") where user='root';
mysql@percona1>FLUSH PRIVILEGES;为了可以正确的执行 State Snapshot Transfer using XtraBackup,我们需要如下的权限用户:
mysql@percona1>CREATE USER'sstuser'@'localhost' IDENTIFIED BY 's3cret';
mysql@percona1>GRANTR ELOAD,LOCKTABLES,REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@percona1>FLUSH PRIVILEGES;*****SST的解释
State Snapshot Transfer 是可以让one node (donor) 通过数据拷贝升级至joining node (joiner)。
一个新节点加入至集群中,需要同步数据,那么SST可以传输数据,让新节点成为集群的一部分。
SST的三个有效方法:mysqldump, rsync andxtrabackup。不过多解释,目前最好用的是xtrabackup
配置第二个节点,/etc/my.cnf 文件配置如下:
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.1.11.15,10.1.11.16,10.1.11.17
# 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=10.1.11.16
# Cluster name
wsrep_cluster_name=my_centos_cluster
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
[root@percona2 ~]# /etc/init.d/mysql start这个节点会自动同步数据基于SST,ROOT用户名和密码都同步了第一个节点。
配置第三个节点,/etc/my.cnf 文件配置如下:
[mysqld]
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.1.11.15,10.1.11.16,10.1.11.17
# 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=10.1.11.17
# Cluster name
wsrep_cluster_name=my_centos_cluster
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
第三个节点,通过下列命令启动服务
[root@percona2 ~]# /etc/init.d/mysql start
*测试REPLICATION
创建数据库在第二个节点上面。
mysql@percona2>CREATE DATABASE percona;
Query OK,1 row affected(0.01sec)
创建example表在第三个节点上面:
mysql@percona3>USE percona;
Database changed
mysql@percona3>CREATE TABLE example( node_id INT PRIMARY KEY,node_name VARCHAR(30));
QueryOK, 0 rows affected(0.05sec)
mysql@percona1>INSERT INTO percona.example VALUES(1,'percona1');
QueryOK,1 row affected(0.02sec)
查询数据在第二节点上面:
mysql@percona2>SELECT * FROM percona.example;
+---------+-----------+ | node_id | node_name | +---------+-----------+ | 1 | percona1 | +---------+-----------+ 1 row in set (0.00 sec)