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 firewalldsystemctl disable firewalld

二、配置hosts

192.168.8.51  node1192.168.8.52  node2192.168.8.53  node3

三、安装依赖

yum remove mariadb-libs -yyum install -y gccyum install -y gcc-c++yum install -y ncurses-devel.x86_64yum install -y cmake.x86_64yum install -y libaio.x86_64yum install -y libaio-develyum install -y bison.x86_64yum install -y gcc-c++.x86_64yum install -y bind-utilsyum install -y wgetyum install -y curlyum install -y curl-develyum install -y perlyum install -y openssh-clientsyum install -y setuptoolyum install -y sysstatyum install -y makeyum install -y libevyum install -y redhat-lsb*yum install -y lrzsz.x86_64 -yyum install -y perl-DBD-MySQLyum install -y perl-IO-Socket-SSL.noarchyum install -y gityum install -y sconsyum install -y socatyum install -y checkyum install -y boost-devel

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

rm -rf /etc/my.cnf*

五、添加mysql用户和组

groupadd -g 300 mysqluseradd -u 300 -g mysql mysql

六、安装PXC和xtrabackup

1、解压工具包

cd /mysql/apptar zxvf/software/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gzmv percona-xtrabackup-2.4.11-Linux-x86_64 xtrabackupcd /mysql/apptar zxvf/software/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gzmv Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101 mysqlcp /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/profilesource ~/.bash_profilextrabackup --versionmysql --version

3、创建相关目录

mkdir -p /mysql/data/3306/datamkdir -p /mysql/log/3306/binlogmkdir -p /mysql/log/3306/relaylogmkdir -p /mysql/backup/backup-dbmkdir -p /mysql/backup/backup-tmpmkdir -p /mysql/backup/backup-binlogchown -R mysql:mysql /mysql/*

4、修改mysql.server

rm -rf /etc/my.cnf*mv /mysql/app/mysql/support-files/mysql.server mysql.server.bakcp /software/mysql.server.pxc /mysql/app/mysql/support-files/mysql.serverchown mysql:mysql /mysql/app/mysql/support-files/mysql.serverchmod +x /mysql/app/mysql/support-files/mysql.servercp /mysql/app/mysql/support-files/mysql.server /mysql/app/mysql/bin/mysqlpxcmysqlpxc status

5、配置my.cnf

192.168.8.51

vi /mysql/data/3306/my.cnf

[client]port=3306socket  = /mysql/data/3306/mysql.sock[mysql]no-beepprompt="\u@itpux \R:\m:\s [\d]> "#no-auto-rehashauto-rehashdefault-character-set=utf8[mysqld]########basic settings########server-id=513306port=3306user = mysqlbind_address= 0.0.0.0basedir=/mysql/app/mysqldatadir=/mysql/data/3306/datasocket  = /mysql/data/3306/mysql.sockpid-file=/mysql/data/3306/mysql.pidcharacter-set-server=utf8skip-character-set-client-handshake=1autocommit = 0#skip_name_resolve = 1max_connections = 800max_connect_errors = 1000default-storage-engine=INNODBtransaction_isolation = READ-COMMITTEDexplicit_defaults_for_timestamp = 1sort_buffer_size = 32Mjoin_buffer_size = 128Mtmp_table_size = 72Mmax_allowed_packet = 16Msql_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 = 1800wait_timeout = 1800read_buffer_size = 16Mread_rnd_buffer_size = 32M#event_scheduler =1query_cache_type = 1query_cache_size=1Mtable_open_cache=2000thread_cache_size=768myisam_max_sort_file_size=10Gmyisam_sort_buffer_size=135Mkey_buffer_size=32Mread_buffer_size=8Mread_rnd_buffer_size=4Mback_log=1024#flush_time=0open_files_limit=65536table_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=FILEgeneral_log = 0general_log_file=/mysql/log/3306/general.logslow_query_log = ONslow_query_log_file=/mysql/log/3306/slow-query.loglong_query_time=10log-error=/mysql/log/3306/mysql-error.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90min_examined_row_limit = 100log_bin=/mysql/log/3306/binlog/mysql-binloglog_bin_index=/mysql/log/3306/binlog/mysql-binlog.indexbinlog_format=ROW#pxc parameter log-slave-updates = 1innodb_locks_unsafe_for_binlog = 1innodb_autoinc_lock_mode = 2wsrep_cluster_name=steven_mysqlwsrep_slave_threads=2wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.8.51,192.168.8.52,192.168.8.53wsrep_node_address=192.168.8.51                   wsrep_node_name=node1                             wsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:sstuser123"pxc_strict_mode=ENFORCING

192.168.8.52

vi /mysql/data/3306/my.cnf

[client]port=3306socket  = /mysql/data/3306/mysql.sock[mysql]no-beepprompt="\u@itpux \R:\m:\s [\d]> "#no-auto-rehashauto-rehashdefault-character-set=utf8[mysqld]########basic settings########server-id=523306port=3306user = mysqlbind_address= 0.0.0.0basedir=/mysql/app/mysqldatadir=/mysql/data/3306/datasocket  = /mysql/data/3306/mysql.sockpid-file=/mysql/data/3306/mysql.pidcharacter-set-server=utf8skip-character-set-client-handshake=1autocommit = 0#skip_name_resolve = 1max_connections = 800max_connect_errors = 1000default-storage-engine=INNODBtransaction_isolation = READ-COMMITTEDexplicit_defaults_for_timestamp = 1sort_buffer_size = 32Mjoin_buffer_size = 128Mtmp_table_size = 72Mmax_allowed_packet = 16Msql_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 = 1800wait_timeout = 1800read_buffer_size = 16Mread_rnd_buffer_size = 32M#event_scheduler =1query_cache_type = 1query_cache_size=1Mtable_open_cache=2000thread_cache_size=768myisam_max_sort_file_size=10Gmyisam_sort_buffer_size=135Mkey_buffer_size=32Mread_buffer_size=8Mread_rnd_buffer_size=4Mback_log=1024#flush_time=0open_files_limit=65536table_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=FILEgeneral_log = 0general_log_file=/mysql/log/3306/general.logslow_query_log = ONslow_query_log_file=/mysql/log/3306/slow-query.loglong_query_time=10log-error=/mysql/log/3306/mysql-error.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90min_examined_row_limit = 100log_bin=/mysql/log/3306/binlog/mysql-binloglog_bin_index=/mysql/log/3306/binlog/mysql-binlog.indexbinlog_format=ROW#pxc parameter log-slave-updates = 1innodb_locks_unsafe_for_binlog = 1innodb_autoinc_lock_mode = 2wsrep_cluster_name=steven_mysqlwsrep_slave_threads=2wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.8.51,192.168.8.52,192.168.8.53wsrep_node_address=192.168.8.52                   wsrep_node_name=node2                             wsrep_sst_method=xtrabackup-v2wsrep_sst_auth="sstuser:sstuser123"pxc_strict_mode=ENFORCING

192.168.8.53

vi /mysql/data/3306/my.cnf

[client]port=3306socket  = /mysql/data/3306/mysql.sock[mysql]no-beepprompt="\u@itpux \R:\m:\s [\d]> "#no-auto-rehashauto-rehashdefault-character-set=utf8[mysqld]########basic settings########server-id=533306port=3306user = mysqlbind_address= 0.0.0.0basedir=/mysql/app/mysqldatadir=/mysql/data/3306/datasocket  = /mysql/data/3306/mysql.sockpid-file=/mysql/data/3306/mysql.pidcharacter-set-server=utf8skip-character-set-client-handshake=1autocommit = 0#skip_name_resolve = 1max_connections = 800max_connect_errors = 1000default-storage-engine=INNODBtransaction_isolation = READ-COMMITTEDexplicit_defaults_for_timestamp = 1sort_buffer_size = 32Mjoin_buffer_size = 128Mtmp_table_size = 72Mmax_allowed_packet = 16Msql_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 = 1800wait_timeout = 1800read_buffer_size = 16Mread_rnd_buffer_size = 32M#event_scheduler =1query_cache_type = 1query_cache_size=1Mtable_open_cache=2000thread_cache_size=768myisam_max_sort_file_size=10Gmyisam_sort_buffer_size=135Mkey_buffer_size=32Mread_buffer_size=8Mread_rnd_buffer_size=4Mback_log=1024#flush_time=0open_files_limit=65536table_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=FILEgeneral_log = 0general_log_file=/mysql/log/3306/general.logslow_query_log = ONslow_query_log_file=/mysql/log/3306/slow-query.loglong_query_time=10log-error=/mysql/log/3306/mysql-error.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90min_examined_row_limit = 100log_bin=/mysql/log/3306/binlog/mysql-binloglog_bin_index=/mysql/log/3306/binlog/mysql-binlog.indexbinlog_format=ROW#pxc parameter log-slave-updates = 1innodb_locks_unsafe_for_binlog = 1innodb_autoinc_lock_mode = 2wsrep_cluster_name=steven_mysqlwsrep_slave_threads=2wsrep_provider=/mysql/app/mysql/lib/libgalera_smm.sowsrep_cluster_address=gcomm://192.168.8.51,192.168.8.52,192.168.8.53wsrep_node_address=192.168.8.53                   wsrep_node_name=node3                             wsrep_sst_method=xtrabackup-v2wsrep_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 -pmysqlmysql: [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> quitBye[root@node1 tmp]# mysql -uroot -pmysqlmysql: [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 7Server version: 5.7.21-20-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.21-29.26, Revision 1702aea, wsrep_29.26Copyright (c) 2009-2018 Percona LLC and/or its affiliatesCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 startMySQL (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.socktail -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 startMySQL (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.socktail -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 changedmysql> 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 changedmysql> 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环境的搭建"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！