MySQL高可用Percona-XtraDB-Cluster环境的搭建
短信预约 -IT技能 免费直播动态提醒
本篇内容介绍了“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:/binvi /etc/profile
PATH=\$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin:/usr/bin:/sbin:/binsource /etc/profile
source ~/.bash_profile
xtrabackup --version
mysql --version3、创建相关目录
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 status5、配置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=ENFORCING192.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=ENFORCING192.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=ENFORCING6、初始化node1
/mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/dataln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sockmysqlpxc bootstrap-pxc7、根据初始化密码登录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<v/R'
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 6
Server version: 5.7.21-20-29.26-log
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>
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 <info@codership.com> |
| 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.sockmysqlpxc 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.log10、将node2加入到集群
rm -rf /mysql/data/3306/data/*
ln -sf /mysql/data/3306/mysql.sock /tmp/mysql.sockmysqlpxc 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环境的搭建”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/43.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
