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 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環境的搭建”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注創新互聯-成都網站建設公司網站，小編將為大家輸出更多高質量的實用文章！
網頁題目：MySQL高可用Percona-XtraDB-Cluster環境的搭建-創新互聯
本文路徑：http://m.newbst.com/article6/cogsig.html
成都網站建設公司_創新互聯，為您提供全網營銷推廣、網站建設、App開發、軟件開發、虛擬主機、網頁設計公司
聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯
- 讓網站頁面快速收錄的方法? 2014-10-25
- 網站制作后期推廣您需要簡單的了解搜索引擎收錄的原理 2016-10-28
- 網站收錄正常卻沒有排名是什么原因造成的？ 2015-12-25
- 如何找出網站收錄異常的罪魁禍首? 2022-06-05
- 如何讓谷歌收錄網頁？ 2014-04-15
- 如何使網站更容易被搜索引擎收錄 2015-06-11
- 百度秒殺收錄站內文章的方法 2017-02-28
- 百度不收錄網站文章的原因以及解決辦法？ 2014-08-01
- 搜索引擎不收錄文章的原因有哪些？ 2013-10-26
- 提高網站收錄的優化技巧 2020-10-31
- SEO優化網站不收錄的解救方法 2014-05-15
- seo優化常識：怎樣提升網站百度收錄 2016-11-15