部署xtradb cluster时,建议使用3台及以上服务器。原因有二:
1、默认情况下,如果一个同伴死去或者两个节点之间的通信不稳定,两个节点都将不接受查询。当然这个可以通过添加忽略仲裁来解决:
set globalwsrep_provider_options=”pc.ignore_quorum=true”;
2、当宕掉的那台启动时,会进行同步,负责提供数据的节点角色变为Donor, Donor会有一定的时间无法写入。断开情况如下:
Mysqldump 适合小库
Rsync 复制时间内断开,速度快
XtraBackup 短时间内断开,速度略慢
节点信息node #1 hostname:percona1 IP:192.168.70.71
node #2 hostname:percona2 IP:192.168.70.72
node #3 hostname:percona3 IP:192.168.70.73
前提条件:三个节点都安装了centos系统 防火墙放通了端口:3306,4444,4567和4568
关闭selinux
安装percona xtradb集群先安装percona官方源
安装epel源,以便安装依赖包socat
安装xtradb
yum installPercona-XtraDB-Cluster-56
官网安装参考地址:
https://www.percona.com/doc/percona-xtradb-cluster/5.6/installation/yum_repo.html
配置节点官网参考地址:
https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/virt_sandbox.html
配置第一个节点第一个节点的/etc/my.cnf配置内容如下:
[mysqld]
datadir=/var/lib/mysql user=mysql
# Path to Galeralibrary wsrep_provider=/usr/lib64/libgalera_smm.so
# Clusterconnection URL contains the IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
# In order forGalera to work correctly binlog format should be ROW binlog_format=ROW
# MyISAM storageengine has only experimental support default_storage_engine=InnoDB
# This changeshow InnoDB autoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2
# Node #1address wsrep_node_address=192.168.70.71
# SST method wsrep_sst_method=xtrabackup-v2
# Cluster name wsrep_cluster_name=my_centos_cluster
# Authenticationfor SST method wsrep_sst_auth="sstuser:s3cret"
在此之后,第一个节点可以启动以下命令:
[iyunv@percona1 ~]#/etc/init.d/mysql bootstrap-pxc 如果在centos7上运行本教程,系统服务如下:
此命令将启动集群初步wsrep_cluster_address设置为gcomm://。这样的集群将自举以防后面的节点或MySQL重新开始,这不需要改变该配置文件。
第一个节点启动后,集群状态检测:
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 | 1 | | wsrep_cluster_status | Primary | | wsrep_connected |ON | ... | wsrep_ready |ON | +----------------------------+--------------------------------------+ 40 rows in set (0.01 sec) 该输出显示集群已成功自举
修改root密码,以及删除空用户
UPDATE mysql.user SET password=PASSWORD("Passw0rd")where user='root'; delete from mysql.user whereuser=''; FLUSH PRIVILEGES;
为了使xtrabackup成功建立快照,需要给新用户建立适当的权限。
mysql@percona1> CREATE USER'sstuser'@'localhost' IDENTIFIED BY 's3cret'; mysql@percona1> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; mysql@percona1> FLUSH PRIVILEGES; 注:MySQL的root账户也可用于建立PerconaXtrabackup的SST,但建议使用不同用户做这一点。
配置第二个节点第二个节点的/etc/my.cnf配置内容如下:
[mysqld]
datadir=/var/lib/mysql user=mysql
# Path to Galera library wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URLcontains IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
# In order for Galera to workcorrectly binlog format should be ROW binlog_format=ROW
# MyISAM storage engine hasonly experimental support default_storage_engine=InnoDB
# This changes how InnoDBautoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2
# Node #2 address wsrep_node_address=192.168.70.72
# Cluster name wsrep_cluster_name=my_centos_cluster
# SST method wsrep_sst_method=xtrabackup-v2
#Authentication for SSTmethod wsrep_sst_auth="sstuser:s3cret"
第二个节点的启动命令
/etc/init.d/mysql start
服务器已经启动后,它会自动接收状态快照传送。因此,第二个节点不会有空root密码了。
这是第二个节点的状态
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 | 2 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | ... | wsrep_ready | ON | +----------------------------+--------------------------------------+ 40 rows in set (0.01 sec)
配置第三个节点第三个节点的/etc/my.cnf配置文件
[mysqld]
datadir=/var/lib/mysql user=mysql
# Path to Galera library wsrep_provider=/usr/lib64/libgalera_smm.so
# Cluster connection URLcontains IPs of node#1, node#2 and node#3 wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73
# In order for Galera to workcorrectly binlog format should be ROW binlog_format=ROW
# MyISAM storage engine hasonly experimental support default_storage_engine=InnoDB
# This changes how InnoDBautoincrement locks are managed and is a requirement for Galera innodb_autoinc_lock_mode=2
# Node #3 address wsrep_node_address=192.168.70.73
# Cluster name wsrep_cluster_name=my_centos_cluster
# SST method wsrep_sst_method=xtrabackup-v2
#Authentication for SSTmethod wsrep_sst_auth="sstuser:s3cret"
然后启动节点
/etc/init.d/mysql start
服务器已经启动后,它会自动接收状态快照传送。
集群状态查看:
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 | +----------------------------+--------------------------------------+ 40 rows in set (0.01 sec) 该输出证实了第三个节点加入集群。
测试复制
在节点2上创建数据库
mysql@percona2> CREATE DATABASE percona; Query OK, 1row affected(0.01 sec)
在节点3上创建表
mysql@percona3> USE percona; Database changed
mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30)); Query OK, 0rows affected(0.05 sec)
在节点1上插入数据
mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1'); QueryOK, 1 rowaffected (0.02 sec)
在第二个节点上查看数据
mysql@percona2> SELECT * FROM percona.example; +---------+-----------+ | node_id | node_name | +---------+-----------+ | 1 | percona1 | +---------+-----------+ 1 row in set (0.00 sec)
集群搭建完成
|