本篇内容介绍了“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<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.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环境的搭建”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
