MySQL高可用 Galera Cluster
目錄- MySQL高可用 Galera Cluster
- Galera Cluster
- Galera Cluster特點
- Galera Cluster 缺點
- Galera Cluster作業程序
- Galera Cluster官方檔案
- Galera Cluster包括兩個組件
- WSREP復制實作
- PXC 原理
- PXC最常使用如下4個埠號：
- PXC中涉及到的重要概念和核心引數：
- 實戰案例：Percona XtraDB Cluster(PXC 5.7）
- 在PXC集群中加入節點
- 在PXC集群中修復故障節點
- 實作MariaDB Galera Cluster
- 復制的問題和解決方案
- 性能衡量指標
- 資料庫服務衡量指標：
- 壓力測驗工具
- 常見MySQl壓力測驗工具
- mysqlslap
- 常用引數 [options] 說明：
- mysqlslap示例
- 生產環境 my.cnf 配置案例
- MySQL配置最佳實踐
Galera Cluster
Galera Cluster：集成了Galera插件的MySQL集群，是一種新型的，資料不共享的，高度冗余的高可用方案，目前Galera Cluster有兩個版本，分別是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架構，是一個既穩健，又在資料一致性、完整性及高性能方面有出色表現的高可用解決方案
Galera Cluster特點
? 多主架構：真正的多點讀寫的集群，在任何時候讀寫資料，都是最新的
? 同步復制：集群不同節點之間資料同步，沒有延遲，在資料庫掛掉之后，資料不會丟失
? 并發復制：從節點APPLY資料時，支持并行執行，更好的性能
? 故障切換：在出現資料庫故障時，因支持多點寫入，切換容易
? 熱插拔：在服務期間，如果資料庫掛了，只要監控程式發現的夠快，不可服務時間就會非常少，在節點故障期間，節點本身對集群的影響非常小
? 自動節點克隆：在新增節點，或者停機維護時，增量資料或者基礎資料不需要人工手動備份提供，Galera Cluster會自動拉取在線節點資料，最終集群會變為一致
? 對應用透明：集群的維護，對應用程式是透明的
只支持innodb存盤引擎的表
Galera Cluster 缺點
? 由于DDL 需全域驗證通過，則集群性能由集群中最差性能節點決定（一般集群節點配置都是一樣的）
? 新節點加入或延后較大的節點重新加入需全量拷貝資料(SST，State Snapshot Transfer),作為donor( 貢獻者，如： 同步資料時的提供者)的節點在同步程序中無法提供讀寫
? 只支持innodb存盤引擎的表
Galera Cluster作業程序
Galera Cluster官方檔案
http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://www.percona.com/doc/percona-xtradb-cluster/LATEST/index.html
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
Galera Cluster包括兩個組件
? Galera replication library (galera-3)
? WSREP：MySQL extended with the Write Set Replication
WSREP復制實作
- PXC：Percona XtraDB Cluster，是Percona對Galera的實作
參考倉庫：
https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
- MariaDB Galera Cluster：
參考倉庫：
https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/
注意：兩者都需要至少三個節點，不能安裝mysql server 或 mariadb-server
PXC 原理
PXC最常使用如下4個埠號：
? 3306：資料庫對外服務的埠號
? 4444：請求SST的埠號
? 4567：組成員之間進行溝通的埠號
? 4568：用于傳輸IST的埠號
PXC中涉及到的重要概念和核心引數：
- 集群中節點的數量：整個集群中節點數量應該控制在最少3個、最多8個的范圍內，最少3個節點是為了防止出現腦裂現象，因為只有在2個節點下才會出現此現象，腦裂現象的標志就是輸入任何命令，回傳的結果都是unknown command，節點在集群中，會因新節點的加入或故障、同步失效等原因發生狀態的切換，
- 節點狀態的變化階段：
? open：節點啟動成功，嘗試連接到集群時的狀態
? primary：節點已處于集群中，在新節點加入并選取donor進行資料同步時的狀態
? joiner：節點處于等待接收同步檔案時的狀態
? joined：節點完成資料同步作業，嘗試保持和集群進度一致時的狀態
? synced：節點正常提供服務時的狀態，表示已經同步完成并和集群進度保持一致
? donor：節點處于為新加入的節點提供全量資料時的狀態
備注：donor節點就是資料的貢獻者，如果一個新節點加入集群，此時又需要大量資料的SST資料傳輸，就有可能因此而拖垮整個集群的性能，所以在生產環境中，如果資料量較小，還可以使用SST全量資料傳輸，但如果資料量很大就不建議使用這種方式，可以考慮先建立主從關系，然后再加入集群，
- 節點的資料傳輸方式：
? SST：State Snapshot Transfer，全量資料傳輸
? IST：Incremental State Transfer，增量資料傳輸
SST資料傳輸有xtrabackup、mysqldump和rsync三種方式，而增量資料傳輸就只有一種方式xtrabackup，但生產環境中一般資料量較小時，可以使用SST全量資料傳輸，但也只使用xtrabackup方法，
- GCache模塊：
在PXC中一個特別重要的模塊，它的核心功能就是為每個節點快取當前最新的寫集，如果有新節點加入進來，就可以把新資料的增量傳遞給新節點，而不需要再使用SST傳輸方式，這樣可以讓節點更快地加入集群中
- 涉及如下引數：
? gcache.size：快取寫集增量資訊的大小，它的默認大小是128MB，通過wsrep_provider_options引數設定，建議調整為2GB~4GB范圍，足夠的空間便于快取更多的增量資訊，
? gcache.mem_size：GCache中記憶體快取的大小，適度調大可以提高整個集群的性能
? gcache.page_size：如果記憶體不夠用（GCache不足），就直接將寫集寫入磁盤檔案中
實戰案例：Percona XtraDB Cluster(PXC 5.7）
-
環境準備
四臺主機：
pxc1:192.168.39.7
pxc1:192.168.39.27
pxc1:192.168.39.37
pxc4:192.168.39.47
???OS版本
[root@pxc1 ~]#cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
關閉防火墻和SELinux，保證時間同步
注意：如果已經安裝MySQL，必須卸載
- 安裝 Percona XtraDB Cluster 5.7
- 配置yum源
[root@pxc1 ~]#vim /etc/yum.repos.d/percona_pxc.repo
[percona]
name=percona_repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled=1
gpgcheck=0
- 其他三臺虛擬機也配置yum源
scp /etc/yum.repos.d/percona_pxc.repo 192.168.39.27:/etc/yum.repos.d/
scp /etc/yum.repos.d/percona_pxc.repo 192.168.39.37:/etc/yum.repos.d/
scp /etc/yum.repos.d/percona_pxc.repo 192.168.39.47:/etc/yum.repos.d/
- 在三個節點都安裝好PXC 5.7
[root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
- 在各個節點上分別配置mysql及集群組態檔
/etc/my.cnf為主組態檔，當前版本中，其余的組態檔都放在/etc/percona-xtradb-cluster.conf.d目錄里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三個檔案
#主組態檔不需要修改
[root@pxc1 ~]#cat /etc/my.cnf
#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
# Please make any edits and changes to the appropriate sectional files
# included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
[root@pxc1 ~]#ls /etc/percona-xtradb-cluster.conf.d/ # 查看一下三個檔案
mysqld.cnf mysqld_safe.cnf wsrep.cnf
#下面組態檔不需要修改
[root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=1 # 建議各個節點不同
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin # 建議啟用，非必須項
log_slave_updates
expire_logs_days=7
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#下面組態檔不需要修改
[root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
#
# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
nice = 0
#PXC的組態檔必須修改
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27 #三個節點的IP
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.7 #各個節點，指定自已的IP
wsrep_cluster_name=pxc-cluster # 集群名稱
wsrep_node_name=pxc-cluster-node-1 #各個節點，指定自已節點名稱
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:centos7" #取消本行注釋，可以改一個自己的密碼
# 根據以上修改資訊所有主機都修改
[root@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf # 通過這個命令查看組態檔
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.39.7,192.168.39.27,192.168.39.37
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.39.7
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:centos7"
注意：盡管Galera Cluster不再需要通過binlog的形式進行同步，但還是建議在組態檔中開啟二進制日志功能，原因是后期如果有新節點需要加入，老節點通過SST全量傳輸的方式向新節點傳輸資料，很可能會拖垮集群性能，所以讓新節點先通過binlog方式完成同步后再加入集群會是一種更好的選擇
- 組態檔各項配置意義
[root@pxc1 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
udp UNCONN 0 0 192.168.122.1:53 *:*
udp UNCONN 0 0 *%virbr0:67 *:*
udp UNCONN 0 0 *:111 *:*
udp UNCONN 0 0 *:5353 *:*
udp UNCONN 0 0 *:58153 *:*
udp UNCONN 0 0 *:812 *:*
udp UNCONN 0 0 :::111 :::*
udp UNCONN 0 0 :::812 :::*
tcp LISTEN 0 128 *:111 *:*
tcp LISTEN 0 128 *:6000 *:*
tcp LISTEN 0 5 192.168.122.1:53 *:*
tcp LISTEN 0 128 *:22 *:*
tcp LISTEN 0 128 127.0.0.1:631 *:*
tcp LISTEN 0 128 127.0.0.1:6010 *:*
tcp LISTEN 0 128 :::111 :::*
tcp LISTEN 0 128 :::6000 :::*
tcp LISTEN 0 128 :::22 :::*
tcp LISTEN 0 128 ::1:631 :::*
tcp LISTEN 0 128 ::1:6010 :::*
#啟動第一人節點（第一個節點啟動和別的節點啟動都不一樣）屬于創建集群的服務器現在階段可以理解為主服務器，但是集群做完之后就沒有主從之分了都是主
[root@pxc1 ~]#systemctl start [email protected]
[root@pxc1 ~]#ss -ntul # 以下兩個埠啟動起來就行了 （3306、4567）
，，，，，，，，，，，，，，，，，，，，，，，
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port
udp UNCONN 0 0 192.168.122.1:53 *:*
udp UNCONN 0 0 *%virbr0:67 *:*
udp UNCONN 0 0 *:111 *:*
udp UNCONN 0 0 *:5353 *:*
udp UNCONN 0 0 *:58153 *:*
udp UNCONN 0 0 *:812 *:*
udp UNCONN 0 0 :::111 :::*
udp UNCONN 0 0 :::812 :::*
tcp LISTEN 0 128 *:111 *:*
tcp LISTEN 0 128 *:6000 *:*
tcp LISTEN 0 5 192.168.122.1:53 *:*
tcp LISTEN 0 128 *:22 *:*
tcp LISTEN 0 128 *:4567 *:*
tcp LISTEN 0 128 127.0.0.1:631 *:*
tcp LISTEN 0 128 127.0.0.1:6010 *:*
tcp LISTEN 0 80 :::3306 :::*
tcp LISTEN 0 128 :::111 :::*
tcp LISTEN 0 128 :::6000 :::*
tcp LISTEN 0 128 :::22 :::*
tcp LISTEN 0 128 ::1:631 :::*
tcp LISTEN 0 128 ::1:6010 :::*
- 找到MySQL的臨時密碼，第一次登錄要修改要不執行不了管理操作，
[root@centos7 ~]#grep "password" /var/log/mysqld.log
2019-12-03T06:13:39.187929Z 1 [Note] A temporary password is generated for root@localhost: a>#;L(Sm4Ln:
[root@centos7 ~]#mysql -uroot -p'a>#;L(Sm4Ln:' # 需要加引號引起來
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 11
Server version: 5.7.27-30-57-log
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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> show databases; # 執行查看庫操作提示必須重置密碼
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # 在執行此陳述句之前，必須使用ALTER USER陳述句重置密碼.
mysql> alter user root@'localhost' identified by 'magedu'; # 修改密碼（其他節點不用修改加入集群以后會自動同步密碼）
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@centos7 ~]#mysql -uroot -pmagedu # 修改以后重新登錄
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 12
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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> SHOW VARIABLES LIKE 'wsrep%'\G
*************************** 1. row ***************************
Variable_name: wsrep_OSU_method
Value: TOI
*************************** 2. row ***************************
Variable_name: wsrep_RSU_commit_timeout
Value: 5000
*************************** 3. row ***************************
Variable_name: wsrep_auto_increment_control
Value: ON
*************************** 4. row ***************************
Variable_name: wsrep_causal_reads
Value: OFF
*************************** 5. row ***************************
Variable_name: wsrep_certification_rules
Value: strict
*************************** 6. row ***************************
Variable_name: wsrep_certify_nonPK
Value: ON
*************************** 7. row ***************************
Variable_name: wsrep_cluster_address
Value: gcomm://192.168.39.7,192.168.39.27,192.168.39.37
*************************** 8. row ***************************
Variable_name: wsrep_cluster_name
Value: pxc-cluster
*************************** 9. row ***************************
Variable_name: wsrep_convert_LOCK_to_trx
Value: OFF
*************************** 10. row ***************************
Variable_name: wsrep_data_home_dir
Value: /var/lib/mysql/
*************************** 11. row ***************************
Variable_name: wsrep_dbug_option
Value:
*************************** 12. row ***************************
Variable_name: wsrep_debug
Value: OFF
*************************** 13. row ***************************
Variable_name: wsrep_desync
Value: OFF
*************************** 14. row ***************************
Variable_name: wsrep_dirty_reads
Value: OFF
*************************** 15. row ***************************
Variable_name: wsrep_drupal_282555_workaround
Value: OFF
*************************** 16. row ***************************
Variable_name: wsrep_forced_binlog_format
Value: NONE
*************************** 17. row ***************************
Variable_name: wsrep_load_data_splitting
Value: ON
*************************** 18. row ***************************
Variable_name: wsrep_log_conflicts
Value: ON
*************************** 19. row ***************************
Variable_name: wsrep_max_ws_rows
Value: 0
*************************** 20. row ***************************
Variable_name: wsrep_max_ws_size
Value: 2147483647
*************************** 21. row ***************************
Variable_name: wsrep_node_address
Value: 192.168.39.7
*************************** 22. row ***************************
Variable_name: wsrep_node_incoming_address
Value: AUTO
*************************** 23. row ***************************
Variable_name: wsrep_node_name
Value: pxc-cluster-node-1
*************************** 24. row ***************************
Variable_name: wsrep_notify_cmd
Value:
*************************** 25. row ***************************
Variable_name: wsrep_on
Value: ON
*************************** 26. row ***************************
Variable_name: wsrep_preordered
Value: OFF
*************************** 27. row ***************************
Variable_name: wsrep_provider
Value: /usr/lib64/galera3/libgalera_smm.so
*************************** 28. row ***************************
Variable_name: wsrep_provider_options
Value: base_dir = /var/lib/mysql/; base_host = 192.168.39.7; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no;
*************************** 29. row ***************************
Variable_name: wsrep_recover
Value: OFF
*************************** 30. row ***************************
Variable_name: wsrep_reject_queries
Value: NONE
*************************** 31. row ***************************
Variable_name: wsrep_replicate_myisam
Value: OFF
*************************** 32. row ***************************
Variable_name: wsrep_restart_slave
Value: OFF
*************************** 33. row ***************************
Variable_name: wsrep_retry_autocommit
Value: 1
*************************** 34. row ***************************
Variable_name: wsrep_slave_FK_checks
Value: ON
*************************** 35. row ***************************
Variable_name: wsrep_slave_UK_checks
Value: OFF
*************************** 36. row ***************************
Variable_name: wsrep_slave_threads
Value: 8
*************************** 37. row ***************************
Variable_name: wsrep_sst_auth
Value: ********
*************************** 38. row ***************************
Variable_name: wsrep_sst_donor
Value:
*************************** 39. row ***************************
Variable_name: wsrep_sst_donor_rejects_queries
Value: OFF
*************************** 40. row ***************************
Variable_name: wsrep_sst_method
Value: xtrabackup-v2
*************************** 41. row ***************************
Variable_name: wsrep_sst_receive_address
Value: AUTO
*************************** 42. row ***************************
Variable_name: wsrep_start_position
Value: 00000000-0000-0000-0000-000000000000:-1
*************************** 43. row ***************************
Variable_name: wsrep_sync_wait
Value: 0
43 rows in set (0.01 sec)
# 查看相關狀態變數
mysql> SHOW STATUS LIKE 'wsrep%'\G
*************************** 1. row ***************************
Variable_name: wsrep_local_state_uuid
Value: 10c3398e-1594-11ea-9f84-c2cae1c33555
*************************** 2. row ***************************
Variable_name: wsrep_protocol_version
Value: 9
*************************** 3. row ***************************
Variable_name: wsrep_last_applied
Value: 1
*************************** 4. row ***************************
Variable_name: wsrep_last_committed
Value: 1
*************************** 5. row ***************************
Variable_name: wsrep_replicated
Value: 1
*************************** 6. row ***************************
Variable_name: wsrep_replicated_bytes
Value: 232
*************************** 7. row ***************************
Variable_name: wsrep_repl_keys
Value: 1
*************************** 8. row ***************************
Variable_name: wsrep_repl_keys_bytes
Value: 32
*************************** 9. row ***************************
Variable_name: wsrep_repl_data_bytes
Value: 133
*************************** 10. row ***************************
Variable_name: wsrep_repl_other_bytes
Value: 0
*************************** 11. row ***************************
Variable_name: wsrep_received
Value: 2
*************************** 12. row ***************************
Variable_name: wsrep_received_bytes
Value: 154
*************************** 13. row ***************************
Variable_name: wsrep_local_commits
Value: 0
*************************** 14. row ***************************
Variable_name: wsrep_local_cert_failures
Value: 0
*************************** 15. row ***************************
Variable_name: wsrep_local_replays
Value: 0
*************************** 16. row ***************************
Variable_name: wsrep_local_send_queue
Value: 0
*************************** 17. row ***************************
Variable_name: wsrep_local_send_queue_max
Value: 1
*************************** 18. row ***************************
Variable_name: wsrep_local_send_queue_min
Value: 0
*************************** 19. row ***************************
Variable_name: wsrep_local_send_queue_avg
Value: 0.000000
*************************** 20. row ***************************
Variable_name: wsrep_local_recv_queue
Value: 0
*************************** 21. row ***************************
Variable_name: wsrep_local_recv_queue_max
Value: 2
*************************** 22. row ***************************
Variable_name: wsrep_local_recv_queue_min
Value: 0
*************************** 23. row ***************************
Variable_name: wsrep_local_recv_queue_avg
Value: 0.500000
*************************** 24. row ***************************
Variable_name: wsrep_local_cached_downto
Value: 1
*************************** 25. row ***************************
Variable_name: wsrep_flow_control_paused_ns
Value: 0
*************************** 26. row ***************************
Variable_name: wsrep_flow_control_paused
Value: 0.000000
*************************** 27. row ***************************
Variable_name: wsrep_flow_control_sent
Value: 0
*************************** 28. row ***************************
Variable_name: wsrep_flow_control_recv
Value: 0
*************************** 29. row ***************************
Variable_name: wsrep_flow_control_interval
Value: [ 100, 100 ]
*************************** 30. row ***************************
Variable_name: wsrep_flow_control_interval_low
Value: 100
*************************** 31. row ***************************
Variable_name: wsrep_flow_control_interval_high
Value: 100
*************************** 32. row ***************************
Variable_name: wsrep_flow_control_status
Value: OFF
*************************** 33. row ***************************
Variable_name: wsrep_cert_deps_distance
Value: 1.000000
*************************** 34. row ***************************
Variable_name: wsrep_apply_oooe
Value: 0.000000
*************************** 35. row ***************************
Variable_name: wsrep_apply_oool
Value: 0.000000
*************************** 36. row ***************************
Variable_name: wsrep_apply_window
Value: 1.000000
*************************** 37. row ***************************
Variable_name: wsrep_commit_oooe
Value: 0.000000
*************************** 38. row ***************************
Variable_name: wsrep_commit_oool
Value: 0.000000
*************************** 39. row ***************************
Variable_name: wsrep_commit_window
Value: 1.000000
*************************** 40. row ***************************
Variable_name: wsrep_local_state
Value: 4
*************************** 41. row ***************************
Variable_name: wsrep_local_state_comment
Value: Synced
*************************** 42. row ***************************
Variable_name: wsrep_cert_index_size
Value: 1
*************************** 43. row ***************************
Variable_name: wsrep_cert_bucket_count
Value: 22
*************************** 44. row ***************************
Variable_name: wsrep_gcache_pool_size
Value: 1592
*************************** 45. row ***************************
Variable_name: wsrep_causal_reads
Value: 0
*************************** 46. row ***************************
Variable_name: wsrep_cert_interval
Value: 0.000000
*************************** 47. row ***************************
Variable_name: wsrep_open_transactions
Value: 0
*************************** 48. row ***************************
Variable_name: wsrep_open_connections
Value: 0
*************************** 49. row ***************************
Variable_name: wsrep_ist_receive_status
Value:
*************************** 50. row ***************************
Variable_name: wsrep_ist_receive_seqno_start
Value: 0
*************************** 51. row ***************************
Variable_name: wsrep_ist_receive_seqno_current
Value: 0
*************************** 52. row ***************************
Variable_name: wsrep_ist_receive_seqno_end
Value: 0
*************************** 53. row ***************************
Variable_name: wsrep_incoming_addresses
Value: 192.168.39.7:3306
*************************** 54. row ***************************
Variable_name: wsrep_cluster_weight
Value: 1
*************************** 55. row ***************************
Variable_name: wsrep_desync_count
Value: 0
*************************** 56. row ***************************
Variable_name: wsrep_evs_delayed
Value:
*************************** 57. row ***************************
Variable_name: wsrep_evs_evict_list
Value:
*************************** 58. row ***************************
Variable_name: wsrep_evs_repl_latency
Value: 0/0/0/0/0
*************************** 59. row ***************************
Variable_name: wsrep_evs_state
Value: OPERATIONAL
*************************** 60. row ***************************
Variable_name: wsrep_gcomm_uuid
Value: 10c2c674-1594-11ea-83fc-6e0df986585c
*************************** 61. row ***************************
Variable_name: wsrep_cluster_conf_id
Value: 1
*************************** 62. row ***************************
Variable_name: wsrep_cluster_size
Value: 1
*************************** 63. row ***************************
Variable_name: wsrep_cluster_state_uuid
Value: 10c3398e-1594-11ea-9f84-c2cae1c33555
*************************** 64. row ***************************
Variable_name: wsrep_cluster_status
Value: Primary
*************************** 65. row ***************************
Variable_name: wsrep_connected
Value: ON
*************************** 66. row ***************************
Variable_name: wsrep_local_bf_aborts
Value: 0
*************************** 67. row ***************************
Variable_name: wsrep_local_index
Value: 0
*************************** 68. row ***************************
Variable_name: wsrep_provider_name
Value: Galera
*************************** 69. row ***************************
Variable_name: wsrep_provider_vendor
Value: Codership Oy <[email protected]>
*************************** 70. row ***************************
Variable_name: wsrep_provider_version
Value: 3.39(rb3295e6)
*************************** 71. row ***************************
Variable_name: wsrep_ready
Value: ON
71 rows in set (0.00 sec)
#重點內容
mysql> SHOW STATUS like 'wsrep%';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 10c3398e-1594-11ea-9f84-c2cae1c33555 |
| wsrep_protocol_version | 9 |
| wsrep_last_applied | 1 |
| wsrep_last_committed | 1 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 232 |
| wsrep_repl_keys | 1 |
| wsrep_repl_keys_bytes | 32 |
| wsrep_repl_data_bytes | 133 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 154 |
| 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 | 1592 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| 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.39.7:3306 |
| wsrep_cluster_weight | 1 |
| 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 | 10c2c674-1594-11ea-83fc-6e0df986585c |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 10c3398e-1594-11ea-9f84-c2cae1c33555 |
| 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 <[email protected]> |
| wsrep_provider_version | 3.39(rb3295e6) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
71 rows in set (0.00 sec)
說明：
wsrep_cluster_size表示，該Galera集群中只有一個節點
wsrep_local_state_comment 狀態為Synced(4)，表示資料已同步完成(因為是第一個引導節點，無資料需要同步)， 如果狀態是Joiner, 意味著 SST 沒有完成. 只有所有節點狀態是Synced，才可以加新節點
wsrep_cluster_status為Primary，且已經完全連接并準備好
啟動PXC集群中其它所有節點
7. 啟動PXC集群中其它所有節點
# 啟動前在第一個節點上創建一個授權賬戶（要不然可能別的節點沒有授權資料庫會起不來）
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' identified by 'centos7'; # 這里授權的密碼必須和組態檔里設定的密碼對應，所有服務器都要一樣，
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@pxc2 etc]#ss -ntl # 啟動前埠情況
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 128 ::1:6010 :::*
[root@pxc2 etc]#systemctl start mysql # 啟動資料庫
[root@pxc2 etc]#ss -ntl # 啟動后 3306、4567 埠都出現了
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 *:4567 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 80 :::3306 :::*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 128 ::1:6010 :::*
LISTEN 0 128 ::1:6011 :::*
[root@pxc2 etc]#mysql -uroot -pmagedu # 登錄資料庫
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 11
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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>
# 另一個節點一樣啟動
[root@pxc3 etc]#systemctl start mysql # 啟動資料庫
[root@pxc3 ~]#mysql -uroot -pmagedu
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 11
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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> SHOW VARIABLES LIKE 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name | Value |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-1 |
+-----------------+--------------------+
1 row in set (0.00 sec)
mysql> show variables like 'wsrep_node_address';
+--------------------+--------------+
| Variable_name | Value |
+--------------------+--------------+
| wsrep_node_address | 192.168.39.7 |
+--------------------+--------------+
1 row in set (0.01 sec)
mysql> show variables like 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)
# 在任意節點創建資料庫測驗（pxc2）
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+---------------+
| Variable_name | Value |
+--------------------+---------------+
| wsrep_node_address | 192.168.39.27 |
+--------------------+---------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# 在pxc1上查看
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+--------------+
| Variable_name | Value |
+--------------------+--------------+
| wsrep_node_address | 192.168.39.7 |
+--------------------+--------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# 在pxc2上查看
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+---------------+
| Variable_name | Value |
+--------------------+---------------+
| wsrep_node_address | 192.168.39.37 |
+--------------------+---------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
# 兩個節點都可以看到同步成功
- 測驗是否有沖突問題
#利用Xshell軟體，同時在三個節點資料庫，在其中一個節點成功
mysql> create database db2; # 只有一個節點會成功其他節點報錯不創建
Query OK, 1 row affected (0.00 sec)
mysql> create database db2;
ERROR 1007 (HY000): Can't create database 'db2'; database exists
mysql> create database db2;
ERROR 1007 (HY000): Can't create database 'db2'; database exists
- PXC缺點（同時寫入資料性能會有影響）
# 使用一個命令回圈創建10w條記錄
mysql> create table test (id int auto_increment primary key,name char(10));
delimiter ;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> delimiter $$
mysql>
mysql> create procedure proc_test()
-> begin
-> declare i int;
-> set i = 1;
-> while i < 100000
-> do insert into test(name) values (concat('wang',i));
-> set i = i +1;
-> end while;
-> end$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> delimiter ;
mysql> call proc_test();
Query OK, 1 row affected (3 min 39.51 sec) # 用了39s接近40s時間才添加完成
# 使用事務方式添加記錄還是很快的
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> call proc_test();
Query OK, 1 row affected (2.44 sec)
mysql> commit;
Query OK, 0 rows affected (0.25 sec)
- 添加記錄的話每個節點添加id變化
# 每個節點添加記錄可重復，（是自動增長的）
mysql> insert t1(name)values('mage');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | mage |
| 4 | magea |
| 7 | mageb |
| 10 | mage |
+----+-------+
4 rows in set (0.00 sec)
# 2 節點添加
mysql> insert t1(name)values('mage');
Query OK, 1 row affected (0.00 sec)
mysql> insert t1(name)values('mage');
Query OK, 1 row affected (0.00 sec)
mysql> insert t1(name)values('mage');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | mage |
| 4 | magea |
| 7 | mageb |
| 10 | mage |
| 11 | mage |
| 14 | mage |
| 17 | mage |
+----+-------+
7 rows in set (0.00 sec)
在PXC集群中加入節點
一個節點加入到Galera集群有兩種情況：新節點加入集群、暫時離組的成員再次加入集群
1）新節點加入Galera集群
新節點加入集群時，需要從當前集群中選擇一個Donor節點來同步資料，也就是所謂state_snapshot_tranfer(SST)程序，SST同步資料的方式由選項wsrep_sst_method決定，一般選擇的是xtrabackup，
必須注意，新節點加入Galera時，會洗掉新節點上所有已有資料，再通過xtrabackup(假設使用的是該方式)從Donor處完整備份所有資料進行恢復，所以，如果資料量很大，新節點加入程序會很慢，而且，在一個新節點成為Synced狀態之前，不要同時加入其它新節點，否則很容易將集群壓垮，如果是這種情況，可以考慮使用wsrep_sst_method=rsync來做增量同步，既然是增量同步，最好保證新節點上已經有一部分資料基礎，否則和全量同步沒什么區別，且這樣會對Donor節點加上全域readonly鎖，
2）舊節點加入Galera集群
如果舊節點加入Galera集群，說明這個節點在之前已經在Galera集群中呆過，有一部分資料基礎，缺少的只是它離開集群時的資料，這時加入集群時，會采用IST(incremental snapshot transfer)傳輸機制，即使用增量傳輸，
# 同樣先配置yum源
# 在安裝資料庫
[root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
# 修改組態檔
[root@pxc4 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.39.7,192.168.39.27,192.168.39.37,192.168.39.47 # 這一行添加上新節點的ip（雖然其他節點組態檔不添加也可以，但是最好都加上，比如說服務器依次關機到新添加的節點（新添加的節點留為最后的引導服務器），但是下次開機別的節點會找不到新添加的服務器，資料庫就起不來了）
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.39.47
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-4
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:centos7"
# 查看連接數以變為4個
mysql> show status like 'wsrep_cluster_size'\G
*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
Value: 4
1 row in set (0.00 sec)
在PXC集群中修復故障節點
- 在其他節點停止服務
[root@pxc2 ~]#systemctl stop mysql
[root@pxc2 ~]#systemctl status mysql
● mysql.service - Percona XtraDB Cluster
Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Tue 2019-12-03 16:52:51 CST; 3min 21s ago
Process: 29259 ExecStopPost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/SUCCESS)
Process: 29221 ExecStop=/usr/bin/mysql-systemd stop (code=exited, status=0/SUCCESS)
Process: 26928 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS)
Process: 26925 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
Process: 26853 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
Main PID: 26925 (code=exited, status=0/SUCCESS)
Dec 03 15:12:54 pxc2 mysqld_safe[26925]: 2019-12-03T07:12:54.344288Z mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position
Dec 03 15:12:56 pxc2 mysql-systemd[26928]: State transfer in progress, setting sleep higher
Dec 03 15:13:16 pxc2 mysql-systemd[26928]: SUCCESS!
Dec 03 15:13:16 pxc2 systemd[1]: Started Percona XtraDB Cluster.
Dec 03 16:52:39 centos7.localdomain systemd[1]: Stopping Percona XtraDB Cluster...
Dec 03 16:52:39 centos7.localdomain mysql-systemd[29221]: SUCCESS! Stopping Percona XtraDB Cluster......
Dec 03 16:52:51 centos7.localdomain mysqld_safe[26925]: 2019-12-03T08:52:51.720383Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Dec 03 16:52:51 centos7.localdomain mysql-systemd[29259]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable
Dec 03 16:52:51 centos7.localdomain mysql-systemd[29259]: WARNING: mysql may be already dead
Dec 03 16:52:51 centos7.localdomain systemd[1]: Stopped Percona XtraDB Cluster.
- 在其它任意節點查看wsrep_cluster_size變數
[root@centos7 ~]#mysql -uroot -pmagedu
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 21
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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> show status like 'wsrep_cluster_size'\G
*************************** 1. row ***************************
Variable_name: wsrep_cluster_size
Value: 3 # 少了一個節點
1 row in set (0.00 sec)
- 添加一個資料庫查看同步情況
mysql> create database db4;
Query OK, 1 row affected (0.01 sec)
#在其它任意節點可看到資料已同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| db4 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
# 其他節點正常同步
- 恢復服務，資料同步
[root@pxc2 ~]#systemctl start mysql
[root@pxc2 ~]#mysql -uroot -pmagedu
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 11
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, Revision 64987d4, WSREP version 31.39, wsrep_31.39
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| db4 |
| mysql |
| performance_schema |
| sys |
+--------------------+
8 rows in set (0.00 sec)
# 恢復服務之后資料還是能同步的
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+
1 row in set (0.00 sec)
實作MariaDB Galera Cluster
#在三個節點上都實作
[root@centos8 ~]#dnf install mariadb-server-galera -y
[root@centos8 ~]#vim /etc/my.cnf.d/galera.cnf # 修改組態檔
#wsrep_cluster_address="dummy://"
wsrep_cluster_address="gcomm://192.168.39.7,192.168.39.27,192.168.39.37"
#啟動第一節點
[root@centos8 ~]#galera_new_cluster
#再啟動其它節點
[root@centos8 ~]#systemctl start mariadb
[root@centos8 ~]#ss -ntul
Netid State Recv-Q Send-Q
Local Address:Port Peer Address:Port
tcp LISTEN 0 128
0.0.0.0:22 0.0.0.0:*
tcp LISTEN 0 128
0.0.0.0:4567 0.0.0.0:*
tcp LISTEN 0 80
0.0.0.0:3306 0.0.0.0:*
tcp LISTEN 0 128
[::]:22 [::]:*
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show status like "wsrep_ready";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready | ON |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_%'\G
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';
復制的問題和解決方案
復制的問題和解決方案：
(1) 資料損壞或丟失
Master： MHA + semi repl
Slave： 重新復制
(2) 混合使用存盤引擎
MyISAM：不支持事務
InnoDB： 支持事務
(3) 不惟一的server id
重新復制
(4) 復制延遲
需要額外的監控工具的輔助
一從多主：mariadb10版后支持
多執行緒復制：對多個資料庫復制
性能衡量指標
資料庫服務衡量指標：
Qps：query per second
Tps：transaction per second
壓力測驗工具
常見MySQl壓力測驗工具
mysqlslap
Sysbench：功能強大
https://github.com/akopytov/sysbench
tpcc-mysql
MySQL Benchmark Suite
MySQL super-smack
MyBench
MYSQL壓力測驗
mysqlslap
Mysqlslap：來自于mariadb包，測驗的程序默認生成一個mysqlslap的schema,生成測驗表t1，查詢和插入測驗資料，mysqlslap庫自動生成，如果已經存在則先洗掉，用--only-print來列印實際的測驗程序，整個測驗完成后不會在資料庫中留下痕跡，
- 使用格式：
mysqlslap [options]
常用引數 [options] 說明：
--auto-generate-sql, -a #自動生成測驗表和資料，表示用mysqlslap工具自己生成的SQL腳本來測驗
并發壓力
--auto-generate-sql-load-type=type #測驗陳述句的型別，代表要測驗的環境是讀操作還是寫操作還
是兩者混合的，取值包括：read，key，write，update和mixed(默認)
--auto-generate-sql-add-auto-increment #代表對生成的表自動添加auto_increment列，從 5.1.18版本開始支持
--number-char-cols=N, -x N #自動生成的測驗表中包含多少個字符型別的列，默認1
--number-int-cols=N, -y N #自動生成的測驗表中包含多少個數字型別的列，默認1
--number-of-queries=N #總的測驗查詢次數(并發客戶數×每客戶查詢次數)
--query=name,-q #使用自定義腳本執行測驗，例如可以呼叫自定義的存盤程序或者sql陳述句來執行測驗
--create-schema #代表自定義的測驗庫名稱，測驗的schema
--commint=N #多少條DML后提交一次
--compress, -C #如服務器和客戶端都支持壓縮，則壓縮資訊
--concurrency=N, -c N #表示并發量，即模擬多少個客戶端同時執行select，可指定多個值，以逗號
或者--delimiter引數指定值做為分隔符,如：--concurrency=100,200,500
--engine=engine_name, -e engine_name #代表要測驗的引擎，可以有多個，用分隔符隔開，例 如：--engines=myisam,innodb
--iterations=N, -i N #測驗執行的迭代次數，代表要在不同并發環境下，各自運行測驗多少次
--only-print #只列印測驗陳述句而不實際執行，
--detach=N #執行N條陳述句后斷開重連
--debug-info, -T #列印記憶體和CP
mysqlslap示例
#單執行緒測驗
mysqlslap -a -uroot -pmagedu
#多執行緒測驗，使用--concurrency來模擬并發連接
mysqlslap -a -c 100 -uroot -pmagedu
#迭代測驗，用于需要多次執行測驗得到平均值
mysqlslap -a -i 10 -uroot -pmagedu
mysqlslap ---auto-generate-sql-add-autoincrement -a
mysqlslap -a --auto-generate-sql-load-type=read
mysqlslap -a --auto-generate-secondary-indexes=3
mysqlslap -a --auto-generate-sql-write-number=1000
mysqlslap --create-schema world -q "select count(*) from City"
mysqlslap -a -e innodb -uroot -pmagedu
mysqlslap -a --number-of-queries=10 -uroot -pmagedu
#測驗同時不同的存盤引擎的性能進行對比
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --
engine=myisam,innodb --debug-info -uroot -pmagedu
#執行一次測驗，分別50和100個并發，執行1000次總查詢
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -
pmagedu
#50和100個并發分別得到一次測驗結果(Benchmark)，并發數越多，執行完所有查詢的時間越長，為了準確
起見，可以多迭代測驗幾次
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --
debug-info -uroot -pmagedu
生產環境 my.cnf 配置案例
- 硬體：記憶體32G
#打開獨立表空間
innodb_file_per_table = 1
#MySQL 服務所允許的同時會話數的上限，經常出現Too Many Connections的錯誤提示，則需要增大此值
max_connections = 8000
#所有執行緒所打開表的數量
open_files_limit = 10240
#back_log 是作業系統在監聽佇列中所能保持的連接數
back_log = 300
#每個客戶端連接最大的錯誤允許數量，當超過該次數，MYSQL服務器將禁止此主機的連接請求，直到MYSQL
服務器重啟或通過flush hosts命令清空此主機的相關資訊
max_connect_errors = 1000
#每個連接傳輸資料大小.最大1G，須是1024的倍數，一般設為最大的BLOB的值
max_allowed_packet = 32M
#指定一個請求的最大連接時間
wait_timeout = 10
# 排序緩沖被用來處理類似ORDER BY以及GROUP BY佇列所引起的排序
sort_buffer_size = 16M
#不帶索引的全表掃描.使用的buffer的最小值
join_buffer_size = 16M
#查詢緩沖大小
query_cache_size = 128M
#指定單個查詢能夠使用的緩沖區大小，預設為1M
query_cache_limit = 4M
# 設定默認的事務隔離級別
transaction_isolation = REPEATABLE-READ
# 執行緒使用的堆大小. 此值限制記憶體中能處理的存盤程序的遞回深度和SQL陳述句復雜性，此容量的記憶體在每次
連接時被預留.
thread_stack = 512K
# 二進制日志功能
log-bin
#二進制日志格式
binlog_format=row
#InnoDB使用一個緩沖池來保存索引和原始資料, 可設定這個變數到物理記憶體大小的80%
innodb_buffer_pool_size = 24G
#用來同步IO操作的IO執行緒的數量
innodb_file_io_threads = 4
#在InnoDb核心內的允許執行緒數量，建議的設定是CPU數量加上磁盤數量的兩倍
innodb_thread_concurrency = 16
# 用來緩沖日志資料的緩沖區的大小
innodb_log_buffer_size = 16M
在日志組中每個日志檔案的大小
innodb_log_file_size = 512M
# 在日志組中的檔案總數
innodb_log_files_in_group = 3
# SQL陳述句在被回滾前,InnoDB事務等待InnoDB行鎖的時間
innodb_lock_wait_timeout = 120
#慢查詢時長
long_query_time = 2
#將沒有使用索引的查詢也記錄下來
log-queries-not-using-indexes
MySQL配置最佳實踐
高并發大資料的互聯網業務，架構設計思路是“解放資料庫CPU，將計算轉移到服務層”，并發量大的情況下，這些功能很可能將資料庫拖死，業務邏輯放到服務層具備更好的擴展性，能夠輕易實作“增機器就加性能”
- 參考：
阿里巴巴Java開發手冊
58到家資料庫30條軍規解讀
http://zhuanlan.51cto.com/art/201702/531364.htm
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/99392.html
標籤：MySQL
下一篇：請教關于自動排崗的問題