本篇内容介绍了“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

一、关闭防火墙

systemctlstopfirewalldsystemctldisablefirewalld

二、配置hosts

192.168.8.51node1192.168.8.52node2192.168.8.53node3

三、安装依赖

yumremovemariadb-libs-yyuminstall-ygccyuminstall-ygcc-c++yuminstall-yncurses-devel.x86_64yuminstall-ycmake.x86_64yuminstall-ylibaio.x86_64yuminstall-ylibaio-develyuminstall-ybison.x86_64yuminstall-ygcc-c++.x86_64yuminstall-ybind-utilsyuminstall-ywgetyuminstall-ycurlyuminstall-ycurl-develyuminstall-yperlyuminstall-yopenssh-clientsyuminstall-ysetuptoolyuminstall-ysysstatyuminstall-ymakeyuminstall-ylibevyuminstall-yredhat-lsb*yuminstall-ylrzsz.x86_64-yyuminstall-yperl-DBD-MySQLyuminstall-yperl-IO-Socket-SSL.noarchyuminstall-ygityuminstall-ysconsyuminstall-ysocatyuminstall-ycheckyuminstall-yboost-devel

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

rm-rf/etc/my.cnf*

五、添加mysql用户和组

groupadd-g300mysqluseradd-u300-gmysqlmysql

六、安装PXC和xtrabackup

1、解压工具包

cd/mysql/apptarzxvf/software/percona-xtrabackup-2.4.11-Linux-x86_64.libgcrypt145.tar.gzmvpercona-xtrabackup-2.4.11-Linux-x86_64xtrabackupcd/mysql/apptarzxvf/software/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gzmvPercona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101mysqlcp/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-Rmysql:mysql/mysql/*

4、修改mysql.server

rm-rf/etc/my.cnf*mv/mysql/app/mysql/support-files/mysql.servermysql.server.bakcp/software/mysql.server.pxc/mysql/app/mysql/support-files/mysql.serverchownmysql: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/mysqlpxcmysqlpxcstatus

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]########basicsettings########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########logsettings########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#pxcparameterlog-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.51wsrep_node_name=node1wsrep_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]########basicsettings########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########logsettings########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#pxcparameterlog-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.52wsrep_node_name=node2wsrep_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]########basicsettings########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########logsettings########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#pxcparameterlog-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.53wsrep_node_name=node3wsrep_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

mysqlpxcbootstrap-pxc

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

[root@node1tmp]#mysql-uroot-pmysqlmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:YES)[root@node1tmp]#mysql-uroot--password='h0.=+GPp<v/R'mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis6Serverversion:5.7.21-20-29.26-logCopyright(c)2009-2018PerconaLLCand/oritsaffiliatesCopyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>mysql>alteruser'root'@'localhost'identifiedby'mysql';QueryOK,0rowsaffected(0.37sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.02sec)mysql>quitBye[root@node1tmp]#mysql-uroot-pmysqlmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis7Serverversion:5.7.21-20-29.26-logPerconaXtraDBClusterbinary(GPL)5.7.21-29.26,Revision1702aea,wsrep_29.26Copyright(c)2009-2018PerconaLLCand/oritsaffiliatesCopyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>createuser'root'@'%'identifiedby'mysql';QueryOK,0rowsaffected(0.02sec)mysql>grantallprivilegeson*.*to'root'@'%'identifiedby'mysql'withgrantoption;QueryOK,0rowsaffected,1warning(0.01sec)mysql>flushprivileges;QueryOK,0rowsaffected(0.30sec)mysql>selectuser,hostfrommysql.user;+---------------+-----------+|user|host|+---------------+-----------+|root|%||mysql.session|localhost||mysql.sys|localhost||root|localhost|+---------------+-----------+4rowsinset(0.00sec)

8、创建复制用户

mysql>CREATEUSER'sstuser'@'localhost'IDENTIFIEDBY'sstuser123';QueryOK,0rowsaffected(0.01sec)mysql>GRANTRELOAD,LOCKTABLES,PROCESS,REPLICATIONCLIENTON*.*TO'sstuser'@'localhost';QueryOK,0rowsaffected(0.30sec)mysql>FLUSHPRIVILEGES;QueryOK,0rowsaffected(0.04sec)mysql>showstatuslike'%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|CodershipOy<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.sock

mysqlpxcstartMySQL(PerconaXtraDBCluster)isnotrunning,butlockfile(/var/lock/subsys/mysql)exists[FAILED]InitializingMySQLdatabase:[OK]StartingMySQL(PerconaXtraDBCluster)......Statetransferinprogress,settingsleephigher.....[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

mysqlpxcstartMySQL(PerconaXtraDBCluster)isnotrunning,butlockfile(/var/lock/subsys/mysql)exists[FAILED]InitializingMySQLdatabase:[OK]StartingMySQL(PerconaXtraDBCluster)......Statetransferinprogress,settingsleephigher.....[OK]

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

七、数据验证

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

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys|+--------------------+4rowsinset(0.00sec)

测试一

在node1创建数据库test

mysql>createdatabasetestDEFAULTCHARSETutf8COLLATEutf8_general_ci;QueryOK,1rowaffected(0.03sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys||test|+--------------------+5rowsinset(0.01sec)

在node2和node3进行查看

mysql>createdatabasetestDEFAULTCHARSETutf8COLLATEutf8_general_ci;QueryOK,1rowaffected(0.03sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||sys||test|+--------------------+5rowsinset(0.01sec)

测试二

在node2创建测试表

mysql>usetest;Databasechangedmysql>createtablet1(idint(6));QueryOK,0rowsaffected(0.52sec)mysql>createtablet2(idint(6));QueryOK,0rowsaffected(0.13sec)mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2|+----------------+2rowsinset(0.00sec)

在node1和node2进程查看

mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1||t2|+----------------+

测试三

在node3删除t2

mysql>usetest;Databasechangedmysql>droptablet2;QueryOK,0rowsaffected(0.10sec)mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1|+----------------+

在node1和node2查看

mysql>showtables;+----------------+|Tables_in_test|+----------------+|t1|+----------------+

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

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