mysql pxc mysql5.7_centos7.4 安装 pxc5.7(mysql 版本为 5.7.21) 使用 xtrabackup-v2 数据同步

centos7.4 安装 pxc5.7(mysql 版本为 5.7.21)使用 xtrabackup-v2 数据同步

我们准备了 3 太服务器：

192.168.31.151(pxc1)，192.168.31.152(pxc2)，192.168.31.153(pxc3)

在 3 台机器上分别操作：

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

yum install Percona-XtraDB-Cluster-57 -y

已安装:

Percona-XtraDB-Cluster-57.x86_64 0:5.7.21-29.26.1.el7 Percona-XtraDB-Cluster-shared-57.x86_64 0:5.7.21-29.26.1.el7

Percona-XtraDB-Cluster-shared-compat-57.x86_64 0:5.7.21-29.26.1.el7

作为依赖被安装:

Percona-XtraDB-Cluster-client-57.x86_64 0:5.7.21-29.26.1.el7 Percona-XtraDB-Cluster-server-57.x86_64 0:5.7.21-29.26.1.el7 libev.x86_64 0:4.15-7.el7

lsof.x86_64 0:4.87-4.el7 percona-xtrabackup-24.x86_64 0:2.4.10-1.el7 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7

perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7 perl-DBI.x86_64 0:1.627-4.el7

perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7

perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 qpress.x86_64 0:11-1.el7

socat.x86_64 0:1.7.3.2-2.el7

替代:

mariadb-libs.x86_64 1:5.5.56-2.el7

启动服务

service mysql start

登录 MySQL 密码

登录 MySQL 密码在/var/log/mysqld.log 中

grep 'temporary password' /var/log/mysqld.log

mysql -uroot -p

修改添加数据库 root、同步账号

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO 'pxc_user'@'%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO 'pxc_user'@'localhost' IDENTIFIED BY '123456';

停止 MySQL，准备启动 pxc 集群

systemctl stop mysql

替换修改配置文件/etc/my.cnf

数据库 1(pxc1)-作为集群第一个启动的节点

[client]

default-character-set = utf8mb4

[mysqld]

skip_ssl

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

slow_query_log=ON

slow_query_log_file=/var/lib/mysql/mysql-slow.log

general_log=ON

general_log_file=/var/lib/mysql/log-mysql.log

long_query_time = 1

explicit_defaults_for_timestamp = 1

innodb_buffer_pool_instances = 8

innodb_buffer_pool_dump_at_shutdown = 1

log_timestamps=system

# Path to Galera library

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

# Cluster connection URL contains IPs of nodes

#If no IP is found, this implies that a new cluster needs to be created,

#in order to do that you need to bootstrap this node

wsrep_cluster_address=gcomm://

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

binlog_format=ROW

max_connections=10000

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# Slave thread to use

wsrep_slave_threads= 8

wsrep_log_conflicts

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

innodb_autoinc_lock_mode=2

# Node IP address

wsrep_node_address=192.168.31.151

# Cluster name

wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified, then system hostname will be used

wsrep_node_name=pxc1

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER

pxc_strict_mode=DISABLED

# SST method

wsrep_sst_method=xtrabackup-v2

#Authentication for SST method

wsrep_sst_auth="pxc_user:123456"

数据库 2(pxc2)

[client]

default-character-set = utf8mb4

[mysqld]

skip_ssl

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

slow_query_log=ON

slow_query_log_file=/var/lib/mysql/mysql-slow.log

general_log=ON

general_log_file=/var/lib/mysql/log-mysql.log

long_query_time = 1

explicit_defaults_for_timestamp = 1

innodb_buffer_pool_instances = 8

innodb_buffer_pool_dump_at_shutdown = 1

log_timestamps=system

# Path to Galera library

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

# Cluster connection URL contains IPs of nodes

#If no IP is found, this implies that a new cluster needs to be created,

#in order to do that you need to bootstrap this node

wsrep_cluster_address=gcomm://192.168.31.151,192.168.31.152,192.168.31.153

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

binlog_format=ROW

max_connections=10000

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# Slave thread to use

wsrep_slave_threads= 8

wsrep_log_conflicts

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

innodb_autoinc_lock_mode=2

# Node IP address

wsrep_node_address=192.168.31.152

# Cluster name

wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified, then system hostname will be used

wsrep_node_name=pxc2

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER

pxc_strict_mode=DISABLED

# SST method

wsrep_sst_method=xtrabackup-v2

#Authentication for SST method

wsrep_sst_auth="pxc_user:123456"

数据库 3(pxc3)

[client]

default-character-set = utf8mb4

[mysqld]

skip_ssl

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

slow_query_log=ON

slow_query_log_file=/var/lib/mysql/mysql-slow.log

general_log=ON

general_log_file=/var/lib/mysql/log-mysql.log

long_query_time = 1

explicit_defaults_for_timestamp = 1

innodb_buffer_pool_instances = 8

innodb_buffer_pool_dump_at_shutdown = 1

log_timestamps=system

# Path to Galera library

wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

# Cluster connection URL contains IPs of nodes

#If no IP is found, this implies that a new cluster needs to be created,

#in order to do that you need to bootstrap this node

wsrep_cluster_address=gcomm://192.168.31.151,192.168.31.152,192.168.31.153

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

binlog_format=ROW

max_connections=10000

# MyISAM storage engine has only experimental support

default_storage_engine=InnoDB

# Slave thread to use

wsrep_slave_threads= 8

wsrep_log_conflicts

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

innodb_autoinc_lock_mode=2

# Node IP address

wsrep_node_address=192.168.31.153

# Cluster name

wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified, then system hostname will be used

wsrep_node_name=pxc3

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER

pxc_strict_mode=DISABLED

# SST method

wsrep_sst_method=xtrabackup-v2

#Authentication for SST method

wsrep_sst_auth="pxc_user:123456"

启动 pxc 集群

在集群第一个节点上执行：

systemctl start mysql@bootstrap.service

在其他 2 个节点执行：

systemctl start MySQL

第一节点启动关闭命令

systemctl start mysql@bootstrap.service

systemctl stop mysql@bootstrap.service

其他节点启动关闭命令

systemctl start MySQL

systemctl stop MySQL