MySQL高可用Percona-XtraDB-Cluster环境的搭建-创新互联

本篇内容介绍了“MySQL高可用Percona-XtraDB-Cluster环境的搭建”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

数据库架构：三个节点PXC

node1：192.168.8.51

node2：192.168.8.52

node3：192.168.8.53

keepalived工具包

percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz

Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gz

一、关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

二、配置hosts

192.168.8.51  node1
192.168.8.52  node2
192.168.8.53  node3

三、安装依赖

yum remove mariadb-libs -y
yum install -y gcc
yum install -y gcc-c++
yum install -y ncurses-devel.x86_64
yum install -y cmake.x86_64
yum install -y libaio.x86_64
yum install -y libaio-devel
yum install -y bison.x86_64
yum install -y gcc-c++.x86_64
yum install -y bind-utils
yum install -y wget
yum install -y curl
yum install -y curl-devel
yum install -y perl
yum install -y openssh-clients
yum install -y setuptool
yum install -y sysstat
yum install -y make
yum install -y libev
yum install -y redhat-lsb*
yum install -y lrzsz.x86_64 -y
yum install -y perl-DBD-MySQL
yum install -y perl-IO-Socket-SSL.noarch
yum install -y git
yum install -y scons
yum install -y socat
yum install -y check
yum install -y boost-devel

四、删除安装包产生的mysql配置文件

rm -rf /etc/my.cnf*

五、添加mysql用户和组

groupadd -g 300 mysql
useradd -u 300 -g mysql mysql

六、安装PXC和xtrabackup

1、解压工具包

cd /mysql/app
tar zxvf
/software/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gz
mv percona-xtrabackup-2.4.11-Linux-x86_64 xtrabackup
cd /mysql/app
tar zxvf
/software/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.
tar.gz
mv Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101 mysql
cp /mysql/app/xtrabackup/bin/* /usr/sbin/

2、修改环境变量

 vi ~/.bash_profile

PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin:/usr/bin:/sbin:/bin

vi /etc/profile

PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin:/usr/bin:/sbin:/bin

source /etc/profile
source ~/.bash_profile
xtrabackup --version
mysql --version

3、创建相关目录

mkdir -p /mysql/data/3306/data
mkdir -p /mysql/log/3306/binlog
mkdir -p /mysql/log/3306/relaylog
mkdir -p /mysql/backup/backup-db
mkdir -p /mysql/backup/backup-tmp
mkdir -p /mysql/backup/backup-binlog
chown -R mysql:mysql /mysql/*

4、修改mysql.server

rm -rf /etc/my.cnf*
mv /mysql/app/mysql/support-files/mysql.server mysql.server.bak
cp /software/mysql.server.pxc /mysql/app/mysql/support-files/mysql.server
chown mysql:mysql /mysql/app/mysql/support-files/mysql.server
chmod +x /mysql/app/mysql/support-files/mysql.server
cp /mysql/app/mysql/support-files/mysql.server /mysql/app/mysql/bin/mysqlpxc
mysqlpxc status

5、配置my.cnf

192.168.8.51

vi /mysql/data/3306/my.cnf

[client]
port=3306
socket	= /mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@itpux \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=513306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket	= /mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/general.log
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/slow-query.log
long_query_time=10
log-error=/mysql/log/3306/mysql-error.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
log_bin=/mysql/log/3306/binlog/mysql-binlog
log_bin_index=/mysql/log/3306/binlog/mysql-binlog.index
binlog_format=ROW
#pxc parameter 
log-slave-updates = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name=steven_mysql
wsrep_slave_threads=2
wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.8.51,192.168.8.52,192.168.8.53
wsrep_node_address=192.168.8.51                   
wsrep_node_name=node1                             
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:sstuser123"
pxc_strict_mode=ENFORCING

192.168.8.52

vi /mysql/data/3306/my.cnf

[client]
port=3306
socket	= /mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@itpux \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=523306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket	= /mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/general.log
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/slow-query.log
long_query_time=10
log-error=/mysql/log/3306/mysql-error.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
log_bin=/mysql/log/3306/binlog/mysql-binlog
log_bin_index=/mysql/log/3306/binlog/mysql-binlog.index
binlog_format=ROW
#pxc parameter 
log-slave-updates = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name=steven_mysql
wsrep_slave_threads=2
wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.8.51,192.168.8.52,192.168.8.53
wsrep_node_address=192.168.8.52                   
wsrep_node_name=node2                             
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:sstuser123"
pxc_strict_mode=ENFORCING

192.168.8.53

vi /mysql/data/3306/my.cnf

[client]
port=3306
socket	= /mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@itpux \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=533306
port=3306
user = mysql
bind_address= 0.0.0.0
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket	= /mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
#event_scheduler =1
query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/general.log
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/slow-query.log
long_query_time=10
log-error=/mysql/log/3306/mysql-error.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
log_bin=/mysql/log/3306/binlog/mysql-binlog
log_bin_index=/mysql/log/3306/binlog/mysql-binlog.index
binlog_format=ROW
#pxc parameter 
log-slave-updates = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
wsrep_cluster_name=steven_mysql
wsrep_slave_threads=2
wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.8.51,192.168.8.52,192.168.8.53
wsrep_node_address=192.168.8.53                   
wsrep_node_name=node3                             
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:sstuser123"
pxc_strict_mode=ENFORCING

6、初始化node1

/mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data

ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock

mysqlpxc bootstrap-pxc

7、根据初始化密码登录mysql并修改root密码

[root@node1 tmp]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node1 tmp]# mysql -uroot --password='h0.=+GPp 
mysql> alter user 'root'@'localhost' identified by 'mysql';
Query OK, 0 rows affected (0.37 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> quit
Bye
[root@node1 tmp]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.21-20-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.21-29.26, Revision 1702aea, wsrep_29.26
Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'root'@'%' identified by 'mysql'; 
Query OK, 0 rows affected (0.02 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.30 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

8、创建复制用户

mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Query OK, 0 rows affected (0.30 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)
mysql> show status like '%wsrep%';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid           | c3d76c5e-e407-11e8-a2b4-36c797a7eab7 |
| wsrep_protocol_version           | 8                                    |
| wsrep_last_applied               | 8                                    |
| wsrep_last_committed             | 8                                    |
| wsrep_replicated                 | 8                                    |
| wsrep_replicated_bytes           | 1864                                 |
| wsrep_repl_keys                  | 8                                    |
| wsrep_repl_keys_bytes            | 256                                  |
| wsrep_repl_data_bytes            | 1066                                 |
| wsrep_repl_other_bytes           | 0                                    |
| wsrep_received                   | 2                                    |
| wsrep_received_bytes             | 141                                  |
| wsrep_local_commits              | 0                                    |
| wsrep_local_cert_failures        | 0                                    |
| wsrep_local_replays              | 0                                    |
| wsrep_local_send_queue           | 0                                    |
| wsrep_local_send_queue_max       | 1                                    |
| wsrep_local_send_queue_min       | 0                                    |
| wsrep_local_send_queue_avg       | 0.000000                             |
| wsrep_local_recv_queue           | 0                                    |
| wsrep_local_recv_queue_max       | 2                                    |
| wsrep_local_recv_queue_min       | 0                                    |
| wsrep_local_recv_queue_avg       | 0.500000                             |
| wsrep_local_cached_downto        | 1                                    |
| wsrep_flow_control_paused_ns     | 0                                    |
| wsrep_flow_control_paused        | 0.000000                             |
| wsrep_flow_control_sent          | 0                                    |
| wsrep_flow_control_recv          | 0                                    |
| wsrep_flow_control_interval      | [ 100, 100 ]                         |
| wsrep_flow_control_interval_low  | 100                                  |
| wsrep_flow_control_interval_high | 100                                  |
| wsrep_flow_control_status        | OFF                                  |
| wsrep_cert_deps_distance         | 1.000000                             |
| wsrep_apply_oooe                 | 0.000000                             |
| wsrep_apply_oool                 | 0.000000                             |
| wsrep_apply_window               | 1.000000                             |
| wsrep_commit_oooe                | 0.000000                             |
| wsrep_commit_oool                | 0.000000                             |
| wsrep_commit_window              | 1.000000                             |
| wsrep_local_state                | 4                                    |
| wsrep_local_state_comment        | Synced                               |
| wsrep_cert_index_size            | 1                                    |
| wsrep_cert_bucket_count          | 22                                   |
| wsrep_gcache_pool_size           | 3504                                 |
| wsrep_causal_reads               | 0                                    |
| wsrep_cert_interval              | 0.000000                             |
| wsrep_ist_receive_status         |                                      |
| wsrep_ist_receive_seqno_start    | 0                                    |
| wsrep_ist_receive_seqno_current  | 0                                    |
| wsrep_ist_receive_seqno_end      | 0                                    |
| wsrep_incoming_addresses         | 192.168.8.51:3306                    |
| wsrep_desync_count               | 0                                    |
| wsrep_evs_delayed                |                                      |
| wsrep_evs_evict_list             |                                      |
| wsrep_evs_repl_latency           | 0/0/0/0/0                            |
| wsrep_evs_state                  | OPERATIONAL                          |
| wsrep_gcomm_uuid                 | c3d4e212-e407-11e8-a5ad-cf65e64856f5 |
| wsrep_cluster_conf_id            | 1                                    |
| wsrep_cluster_size               | 1                                    |
| wsrep_cluster_state_uuid         | c3d76c5e-e407-11e8-a2b4-36c797a7eab7 |
| wsrep_cluster_status             | Primary                              |
| wsrep_connected                  | ON                                   |
| wsrep_local_bf_aborts            | 0                                    |
| wsrep_local_index                | 0                                    |
| wsrep_provider_name              | Galera                               |
| wsrep_provider_vendor            | Codership Oy     |
| wsrep_provider_version           | 3.26(r)                              |
| wsrep_ready                      | ON                                   |
+----------------------------------+--------------------------------------+

9、将node2加入到集群

rm -rf /mysql/data/3306/data/*
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock

mysqlpxc start
MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists[FAILED]
Initializing MySQL database:  [  OK  ]
Starting MySQL (Percona XtraDB Cluster)......State transfer in progress, setting sleep higher
.....[  OK  ]

ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock
tail -100f /mysql/log/3306/mysql-error.log

10、将node2加入到集群

rm -rf /mysql/data/3306/data/*
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock

mysqlpxc start
MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) exists[FAILED]
Initializing MySQL database:  [  OK  ]
Starting MySQL (Percona XtraDB Cluster)......State transfer in progress, setting sleep higher
.....[  OK  ]

ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sock
tail -100f /mysql/log/3306/mysql-error.log

七、数据验证

在三个节点查看数据库如下：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

测试一

在node1创建数据库test

mysql> create database test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

在node2和node3进行查看

mysql> create database test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

测试二

在node2创建测试表

mysql> use test;
Database changed
mysql> create table t1(id int(6));
Query OK, 0 rows affected (0.52 sec)
mysql> create table t2(id int(6));
Query OK, 0 rows affected (0.13 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

在node1和node2进程查看

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+

测试三

在node3删除t2

mysql> use test;
Database changed
mysql> drop table t2;
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

在node1和node2查看

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

到此，PXC搭建完成，三个节点均可进行读写操作。

“MySQL高可用Percona-XtraDB-Cluster环境的搭建”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联-成都网站建设公司网站，小编将为大家输出更多高质量的实用文章！