189 8069 5689
本篇内容介绍了“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.=+GPpmysql> 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、创建复制用户