登录 注册

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

本贴最后更新于 2246 天前，其中的信息可能已经事过境迁

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

  • CentOS

    CentOS（Community Enterprise Operating System）是 Linux 发行版之一，它是来自于 Red Hat Enterprise Linux 依照开放源代码规定释出的源代码所编译而成。由于出自同样的源代码，因此有些要求高度稳定的服务器以 CentOS 替代商业版的 Red Hat Enterprise Linux 使用。两者的不同在于 CentOS 并不包含封闭源代码软件。

    238 引用 • 224 回帖 • 1 关注
  • pxc
    1 引用
  • MySQL

    MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    676 引用 • 535 回帖
2.0k 6 421 210 1 277 46 42 63 899

相关帖子

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

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
centrexzj
回帖
0
 帖子
38
 积分
525