0.架构图:
1.下载并上传安装包:
下载地址:https://www.percona.com/downloads/
1.0 操作系统上操作(三主机):
需要关闭防火墙、selinux.
添加mysql账号:
groupadd mysql
useradd -g mysql mysql -d /home/mysql -s /sbin/nologin
mkdir /u01/pxc-mysql;
chown mysql:mysql /u01/pxc-mysql -R;
chmod 775 /u01/pxc-mysql -R
1.1解压安装包:
先安装xtraBackup:
yum -y install percona-xtrabackup-24-2.4.20-1.el6.x86_64.rpm
(或yum -y localinstall percona-xtrabackup-24-2.4.20-1.el6.x86_64.rpm)
1.2安装Percona-XtraDB-Cluster
需要安装包:
qpress
socat
yum -y install perl-IO-Socket-SSL.noarch perl-DBD-MySQL.x86_64 perl-Time-HiRes openssl openssl-devel socat
https://pkgs.org/download/qpress
wget https://repo.percona.com/yum/release/6/RPMS/x86_64/qpress-11-1.el6.x86_64.rpm
再执行安装,即成功:
rpm包安装配置文件位置/etc/percona-xtradb-cluster.conf/wsrep.cnf:
注意事项:
1).旧有主机上安装pxc包,/etc/my.cnf并不会做好软链接,可以自己手动做软链接
cd/etc/alternatives
ln -s /etc/alternatives/my.cnf my.cnf;
cd /etc;
ln -s /etc/alternatives/my.cnf my.cnf ;
这样就用到了/etc/percona-xtradb-cluster.conf.d/wsrep.cnf真正配置文件 ,或者直接拷贝/etc/percona-xtradb-cluster.conf.d/wsrep.cnf配置文件到/etc/my.cnf即可。
2).socat本地安装:
wget http://www.dest-unreach.org/socat/download/socat-1.7.3.2.tar.gz
tar -zxf socat-1.7.3.2.tar.gz
cd socat-1.7.3.2
./configure --disable-fips
make && make install
cd ..
ln -s /usr/local/bin/socat /usr/bin/
1.3配置pxc配置文件参数:
cp /etc/percona-xtradb-cluster.conf.d/wsrep.cnf /etc/my.cnf
节点1配置:
[mysqld]
user=mysql
innodb_buffer_pool_size=1024M
#basedir=/usr
datadir=/u01/pxc-mysql/
port=3306
server_id=330690
socket=/tmp/mysql.sock
pid-file=/u01/pxc-mysql/mysql.pid
character-set-server=utf8mb4
log-bin=/u01/pxc-mysql/mysql-binlog
log-error=/u01/pxc-mysql/error.log
log_warnings=2
slow_query_log_file=/u01/pxc-mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://10.161.170.90,10.161.170.91,10.161.170.95
wsrep_node_name=pxc1
wsrep_node_address=10.161.170.90
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:root123"
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
节点2和3配置同上,只是需要修改wsrep_node_name、wsrep_node_address、server_id此三个参数值。
socket=/tmp/mysql.sock 这个值最好等于 /var/lib/mysql/mysql.sock;防止改完root密码需要做ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock 否则直接 mysql -uroot -proot123时会找/var/lib/mysql/mysql.sock而不是我们指定的/tmp/mysql.sock位置(即为mysql -S /tmp/mysql.sock -uroot -p连接原因)。
3.启动节点顺序:
3.1第一个节点(/etc/init.d/mysql bootstrap-pxc,只在第一个节点使用bootstrap-pxc启动):
查看初始化时临时密码:
不能直接连接:即mysql -uroot -p'ge9n7QO-a>sp'
也不能mysql -S /tmp/mysql.sock -uroot -p'ge9n7QO-a>sp'
只能mysql -S /tmp/mysql.sock -uroot -p回车后,输入密码ge9n7QO-a>sp登录:
mysql -S /tmp/mysql.sock -uroot -p ;
修改密码:
set password='root123';
alter user 'root'@'localhost' identified by 'root123';
alter user 'root'@'localhost' password expire never;
flush privileges;
验证root登录:
创建pxc传输账号(配置文件中账号及密码对应wsrep_sst_auth="sstuser:root123"):
create user 'sstuser'@'localhost' identified by 'root123';
grant reload,lock tables,process,replication client on *.* to 'sstuser'@'localhost';
flush privileges;
验证sstuser登录:
启动第二个节点,配置文件都修改到位后:
/etc/init.d/mysql start;
检查第二节点状态:
mysql -usstuser -proot123 -S /tmp/mysql.sock -e 'show status like "wsrep_cluster%"'
启动第三个节点,配置文件都修改到位后:
/etc/init.d/mysql start ;
mysql -usstuser -proot123 -S /tmp/mysql.sock -e 'show status like "wsrep_cluster%"'
第三节点启动报错解决:
原因:因为主机之前有安装过mysql造成 ; chmod 755 /etc/init.d/mysql ;
错误如下:
检查节点3的错误日志如下vi /u01/pxc-mysql/error.log:
对比三主机/etc/my.cnf(644) 、/etc/init.d/mysql (755)、/etc/percona-xtradb-cluster.conf.d/wsrep.cnf 、/u01/pxc-mysql(775)目录权限.
4.测试:
4.1授予root登录权限:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;
flush privileges; select user,host from user;
在任意节点上授权即可。
测试连接如下:
4.2创建一个管理账号(root登录任意窗口):
create user 'admin'@'%' identified by 'root123';
grant all privileges on *.* to 'admin'@'%' ;
flush privileges;
测试连接用户admin:
4.3创建数据库及表:
create database test CHARACTER SET 'utf8mb4';
刷新各连接,已经同步:
在任意节点上test库上创建表:
CREATE TABLE `tb_ad` (
`id` bigint(20) NOT NULL,
`type` int(10) DEFAULT NULL ,
`titile` varchar(100) DEFAULT NULL ,
`url` varchar(200) DEFAULT NULL ,
`create` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
检查同步状态;
到此,pxc测试完成。
5. 官方文档安装笔记:
https://www.percona.com/doc/percona-xtradb-cluster/5.7/install/yum.html#yum
5.1官网三主机:
192.168.70.61
192.168.70.62
192.168.70.63
5.2.pxc需要的端口不能占用:vi /etc/services检查(netstat -tunlp |egrep "3306|4444|4567|4568")
3306
4444
4567
4568
5.3.selinux关闭和防火墙关闭
setenforce 0 (vi /etc/selinux/conf 改为SELINUX=disable)
5.4.安装如下:yum install Percona-XtraDB-Cluster-57
wget https://repo.percona.com/yum/release/6/RPMS/x86_64/qpress-11-1.el6.x86_64.rpm
包安装如下:
Percona-XtraDB-Cluster-devel-57
Percona-XtraDB-Cluster-test-57
Percona-XtraDB-Cluster-debuginfo-57
Percona-XtraDB-Cluster-galera-3-debuginfo
Percona-XtraDB-Cluster-shared-57
(备注:service mysql start
grep 'temporary password' /var/log/mysqld.log
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass';)
下载好了本地安装:
rpm -ivh qress
yum localinstall Percona-XtraDB*rpm
cd /etc/percona-xtradb-cluster.conf.d
cp wsrep.cnf wsrep.cnf-bak
5.5.配置文件:redhat或centos:
节点1:/etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.70.61,192.168.70.62,192.168.70.63
wsrep_node_name=pxc1
wsrep_node_address=192.168.70.61
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
第二节点:
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.70.61,192.168.70.62,192.168.70.63
wsrep_node_name=pxc2
wsrep_node_address=192.168.70.62
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
第三节点:
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.70.61,192.168.70.62,192.168.70.63
wsrep_node_name=pxc3
wsrep_node_address=192.168.70.63
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
备注: Percona XtraDB Cluster uses Percona XtraBackup for State Snapshot Transfer (SST).
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=<sst_user>:<sst_pass>
第一节点初始化:
/etc/init.d/mysql bootstrap-pxc
只在第一个节点执行(redhat7或centos7:systemctl start mysql@bootstrap.service)
第一节点创建用户和权限:
show status like 'wsrep%';
CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;
xtrabackup备份:
$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup \
--target-dir=/data/bkps/
$ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/
$ innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -
备份用户举例:
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;
第二节点添加:
/etc/init.d/mysql start
show status like 'wsrep%';