Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "building a high-availability Percona-XtraDB-Cluster environment for MySQL". In the operation of practical cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Database architecture: three-node PXC

Node1:192.168.8.51

Node2:192.168.8.52

Node3:192.168.8.53

Keepalived toolkit

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

First, turn off the firewall

Systemctl stop firewalldsystemctl disable firewalld

2. Configure hosts

192.168.8.51 node1192.168.8.52 node2192.168.8.53 node3

Third, installation dependence

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

Delete the mysql configuration file generated by the installation package

Rm-rf / etc/my.cnf*

Add mysql users and groups

Groupadd-g 300 mysqluseradd-u 300-g mysql mysql

VI. Install PXC and xtrabackup

1. Decompression kit

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. Modify environment variables

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:/binsource / etc/profilesource ~ / .bash_profilextrabackup-versionmysql-version

3. Create related directories

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. Modify 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. Configure my.cnf

192.168.8.51

Vi / mysql/data/3306/my.cnf

[client] port=3306socket = / mysql/data/3306/ MySQL. Socks [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 = 1 log throttlequeriesNot _ 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. 51192.168.8.52192.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. Socks [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 = 1 log throttlequeriesNot _ 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. 51192.168.8.52192.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. Socks [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 = 1 log throttlequeriesNot _ 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. 51192.168.8.52192.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. Initialize 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-pxc

7. Log in to mysql according to the initialization password and modify the root password

[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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.