Catalog
-
MySQL highly available Galera Cluster
- Galera Cluster
- Galera Cluster features
- Disadvantages of Galera Cluster
- Galera Cluster working process
- Official document of Galera Cluster
- Galera Cluster consists of two components
- WSREP replication implementation
- PXC principle
- Practical case: Percona XtraDB Cluster(PXC 5.7)
- Add nodes to PXC cluster
- Repairing failed nodes in PXC cluster
- Implementation of MariaDB Galera Cluster
- Replication issues and Solutions
- Performance metrics
- Pressure test tools
- my.cnf configuration case of production environment
- MySQL configuration best practices
MySQL highly available Galera Cluster
Galera Cluster
Galera Cluster: MySQL Cluster integrated with Galera plug-ins is a new type of highly redundant and highly available solution with no data sharing. At present, Galera Cluster has two versions, percona xtrab cluster and MariaDB Cluster. Galera itself has multi main characteristics, that is, it adopts multi master cluster architecture, which is robust, consistent and complete in data High availability solutions with outstanding performance in terms of performance and performance
Galera Cluster features
• multi master architecture: the real multi-point read-write cluster is up-to-date in reading and writing data at any time • synchronous replication: data synchronization between different nodes of the cluster, no delay, data will not be lost after the database is hung up • concurrent replication: support parallel execution and better performance when applying data from node • failover: in case of database failure, it is easy to switch because it supports multi-point write Hot plug: during service, if the database hangs, as long as the monitor finds it fast enough, the non service time will be very little. During node failure, the impact of the node itself on the cluster is very small Automatic node cloning: incremental data or basic data do not need to be manually backed up when adding nodes or shutting down for maintenance. Galera Cluster will automatically pull online node data, and eventually the cluster will become consistent • application transparency: cluster maintenance, application transparency
Only innodb storage engine tables are supported
Disadvantages of Galera Cluster
• as DDL needs to pass global verification, cluster performance is determined by the worst performance node in the cluster (generally, the configuration of cluster nodes is the same) • when a new node joins or a large node is delayed to join again, it needs to copy data in full (SST, State Snapshot Transfer). As a donor (contributor, such as the provider when synchronizing data), the node cannot provide read and write during synchronization • only innodb storage engine tables are supported
Galera Cluster working process
Official document of 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 consists of two components
• Galera replication library (galera-3) • WSREP: MySQL extended with the Write Set Replication
WSREP replication implementation
- PXC: Percona XtraDB Cluster, which is Percona's implementation of Galera
Reference warehouse:
https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
- MariaDB Galera Cluster:
Reference warehouse:
https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/
Note: both require at least three nodes. You cannot install mysql server or MariaDB server
PXC principle
PXC most commonly uses the following four port numbers:
3306: port number of database external service • 4444: port number of the request SST • 4567: port number for communication between group members • 4568: port number used to transmit IST
Important concepts and core parameters involved in PXC:
- Number of nodes in the cluster: the number of nodes in the whole cluster should be controlled within the range of at least 3 and at most 8. At least 3 nodes are used to prevent brain crack, because only 2 nodes can cause this phenomenon. The sign of cleft brain phenomenon is to input any command, and the returned result is unknown command. When a node is in a cluster, it will switch state due to new node joining or failure, synchronization failure and other reasons.
- Phase of node state change:
• open: the status when the node starts successfully and attempts to connect to the cluster • primary: the node is already in the cluster. When the new node joins and selects donor for data synchronization • joiner: the node is in the state waiting to receive the synchronization file • joined: the state when the node completes data synchronization and tries to keep consistent with the progress of the cluster • synced: the status of the node when it provides services normally, indicating that it has completed synchronization and kept consistent with the progress of the cluster • donor: the state when the node is in the state of providing full data for the newly added node
Note: the donor node is the data contributor. If a new node joins the cluster and needs the SST data transmission of a large amount of data at this time, the performance of the whole cluster may be dragged down. Therefore, in the production environment, if the data volume is small, you can also use the full amount of SST data transmission, but if the data volume is large, it is not recommended to use this method. You can consider building it first Establish the master-slave relationship, and then join the cluster.
- Data transmission mode of node:
• SST: State Snapshot Transfer, full data transfer • IST: Incremental State Transfer
There are three ways of SST data transmission: xtrabackup, mysqldump and rsync, while there is only one way of incremental data transmission: xtrabackup. However, in the production environment, the amount of data is generally small, and full data transmission of SST can be used, but only xtrabackup can be used.
- GCache module:
In PXC, a very important module, its core function is to cache the latest write set for each node. If a new node is added, the increment of the new data can be transferred to the new node without using the SST transmission mode, so that the node can join the cluster faster
- The following parameters are involved:
• gcache.size: the size of the incremental information of the cache write set. Its default size is 128MB. By setting the wsrep provider options parameter, it is recommended to adjust it to the range of 2GB to 4GB, with enough space for more incremental information. • gcache.mem_size: the size of the memory cache in GCache can be adjusted appropriately to improve the performance of the whole cluster • gcache.page_size: if the memory is not enough (GCache is not enough), write the write set directly to the disk file
Practical case: Percona XtraDB Cluster(PXC 5.7)
-
Environmental preparation
Four hosts:
pxc1:192.168.39.7 pxc1:192.168.39.27 pxc1:192.168.39.37 pxc4:192.168.39.47
OS version
Turn off firewall and SELinux to ensure time synchronization[root@pxc1 ~]#cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core)
Note: if MySQL is already installed, you must uninstall - Install Percona XtraDB Cluster 5.7
- Configure yum source
[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
- The other three virtual machines are also configured with yum source
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/
- Install PXC 5.7 on all three nodes
[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
- Configure mysql and cluster configuration files on each node
/etc/my.cnf is the main configuration file. In the current version, the rest of the configuration files are placed in the directory / etc / percona xtradb-cluster.conf.d, including mysqld.cnf, mysqld_safe.cnf and wsrep.cnf
#Master profile does not need to be modified [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/ # Look at the three files mysqld.cnf mysqld_safe.cnf wsrep.cnf #The following configuration file does not need to be modified [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 # Different nodes are recommended 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 # Recommended, not required log_slave_updates expire_logs_days=7 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #The following configuration file does not need to be modified [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 #The configuration file of PXC must be modified [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 of three nodes 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 #Each node, specify its own IP wsrep_cluster_name=pxc-cluster # Cluster name wsrep_node_name=pxc-cluster-node-1 #Each node, specify its own node name pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:centos7" #You can change your own password if you cancel the bank note # According to the above modification information, all hosts are modified [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 # View the configuration file with this command [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"
Note: Although the Galera Cluster no longer needs to be synchronized in the form of binlog, it is recommended to enable the binary log function in the configuration file. The reason is that if new nodes need to be added in the later stage, the old node will transfer data to the new node in the way of full SST transmission, which will probably drag down the cluster performance. Therefore, let the new node complete the synchronization in the way of binlog before adding Entering the cluster is a better choice
- Configuration meaning of configuration files
[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 :::* #Starting the first person node (the first node is different from other nodes) belongs to the server that creates the cluster. At this stage, it can be understood as the main server, but after the cluster is completed, there is no master-slave relationship [root@pxc1 ~]#systemctl start mysql@bootstrap.service [root@pxc1 ~]#ss -ntul # Start the following two ports (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 :::*
- Find the temporary password of MySQL, and modify it for the first login or the management operation cannot be performed.
[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:' # Need quotation marks 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; # To perform the operation of viewing the library, you must reset the password ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # You must use the ALTER USER statement to reset the password before executing this statement mysql> alter user root@'localhost' identified by 'magedu'; # Modify the password (other nodes will automatically synchronize the password after joining the cluster without modification) Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@centos7 ~]#mysql -uroot -pmagedu # Log in again after modification 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>
- View variables
# View write set replication related variables
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)
# View related status variables
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 <info@codership.com>
*************************** 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)
#Key contents
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 <info@codership.com> |
| wsrep_provider_version | 3.39(rb3295e6) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
71 rows in set (0.00 sec)Explain: Wsrep? Cluster? Size indicates that there is only one node in the Galera cluster The status of wsrep local state comment is synchronized (4), indicating that the data has been synchronized (because it is the first boot node, no data needs to be synchronized). If the status is Joiner, it means that the SST is not completed. Only when all nodes are synchronized can new nodes be added Wsrep cluster status is Primary and is fully connected and ready
Start all other nodes in PXC cluster
- Start all other nodes in PXC cluster
# Create an authorization account on the first node before startup (otherwise, other nodes may not be able to get up without authorization database) mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost' identified by 'centos7'; # The authorized password here must correspond to the password set in the configuration file, and all servers must be the same. Query OK, 0 rows affected, 1 warning (0.00 sec) [root@pxc2 etc]#ss -ntl # Port condition before startup 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 # Start database [root@pxc2 etc]#ss -ntl # After startup, ports 3306 and 4567 appear 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 # Log in to the database 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> # Start as another node [root@pxc3 etc]#systemctl start mysql # Start database [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>
- Check the cluster status and verify whether the cluster is successful
# At any node, view the cluster status 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) # Create database test at any node (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) # View on 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) # View on 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) # Both nodes can see the synchronization successful
- Test for conflicts
#Using X shell software, at the same time in three node database, in one of the nodes successfully mysql> create database db2; # Only one node will succeed. Other nodes report errors and do not create 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 disadvantages (write data at the same time will affect performance)
# Using a command loop to create 10w records
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) # It took 39s to close to 40s to add
# Adding records in a transactional way is fast
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)
- Add id change for each node when adding records
# Each node can add records repeatedly. (automatic growth)
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 node addition
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)Add nodes to PXC cluster
There are two situations when a node joins the Galera cluster: the new node joins the cluster and the members who are temporarily out of the group join the cluster again 1) new nodes join Galera cluster When a new node joins the cluster, it needs to select a Donor node from the current cluster to synchronize data, that is, the so-called state snapshot tranfer (SST) process. The way SST synchronizes data is determined by the option wsrep? SST? Method, and xtrabackup is generally selected. It must be noted that when the new node is added to Galera, all existing data on the new node will be deleted, and then all data will be fully backed up from Donor through xtrabackup (assuming this method is used) for recovery. Therefore, if the amount of data is large, the process of adding new nodes will be slow. Moreover, before a new node becomes synchronized, do not add other new nodes at the same time, otherwise it will easily crush the cluster. If this is the case, you can consider using wsrep? SST? Method = Rsync to do incremental synchronization. Since it is incremental synchronization, it is better to ensure that there is a part of the data base on the new node, otherwise it is no different from full synchronization, and this will add a global readonly lock to the Donor node. 2) old nodes join Galera cluster If the old node joins the Galera cluster, it means that this node has been in the Galera cluster before. There is a part of the data base, but what is missing is the data when it leaves the cluster. When joining the cluster, IST(incremental snapshot transfer) transmission mechanism will be adopted, that is, incremental transmission will be used.
# Also configure the yum source first
# Installing database on
[root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
# Modify profile
[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.7192.168.39.27192.168.39.37192.168.39.47 ා add the ip address of the new node in this line (although other node configuration files can not be added, it is better to add them all, for example, the server shuts down to the newly added node in turn (the newly added node remains as the last guide server), but the next time you turn on the other node, the newly added server and data will not be found Library can't get up)
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"
# View the number of connections to 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)Repairing failed nodes in PXC cluster
- Stop service at other nodes
[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.
- View the wsrep cluster size variable on any other node
[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 # One node is missing
1 row in set (0.00 sec)- Add a database to view synchronization
mysql> create database db4; Query OK, 1 row affected (0.01 sec) #You can see that the data is synchronized at any other node mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) # Normal synchronization of other nodes
- Recovery services, data synchronization
[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) # Data can still be synchronized after service recovery mysql> SHOW STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 4 | +--------------------+-------+ 1 row in set (0.00 sec)
Implementation of MariaDB Galera Cluster
#Implemented on all three nodes [root@centos8 ~]#dnf install mariadb-server-galera -y [root@centos8 ~]#vim /etc/my.cnf.d/galera.cnf # Modify profile #wsrep_cluster_address="dummy://" wsrep_cluster_address="gcomm://192.168.39.7,192.168.39.27,192.168.39.37" #Start the first node [root@centos8 ~]#galera_new_cluster #Restart other nodes [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_%';
Replication issues and Solutions
Replication issues and solutions:
(1) data damage or loss
Master: MHA + semi repl
Slave: re copy
(2) hybrid storage engine
MyISAM: transaction not supported
InnoDB: transaction support
(3) non unique server id
Re replication
(4) replication delay
Additional monitoring tools are required
One slave multi master: support after MariaDB version 10
Multithreaded replication: replication to multiple databasesPerformance metrics
Database service metrics:
Qps: query per second Tps: transaction per second
Pressure test tools
Common MySQl stress testing tools
mysqlslap Sysbench: Powerful https://github.com/akopytov/sysbench tpcc-mysql MySQL Benchmark Suite MySQL super-smack MyBench MYSQL Pressure test
mysqlslap
Mysqlsnap: from mariadb package, the test process generates a mysqlsnap schema by default, generates test table t1, queries and inserts test data, and the mysqlsnap library is generated automatically. If it already exists, delete it first. Use -- only print to print the actual test process. After the test is completed, no trace will be left in the database.
- Use format:
mysqlslap [options]
Common parameter [options] Description:
--Auto generate SQL, - a ා automatically generate test table and data, which means to test with SQL script generated by mysqlslap tool itself Concurrent pressure --Auto generate SQL load type = type the type of the test statement. Represents whether the environment to be tested is read operation or write operation It's a mixture of the two. Values include: read, key, write, update and mixed (default) --Auto generate SQL add auto increment stands for automatically adding auto increment column to the generated table, which is supported from version 5.1.18 --Number char cols = n, - x n ා how many character type columns are included in the automatically generated test table, default 1 --Number int cols = n, - y n ා how many number type columns are included in the automatically generated test table, default 1 --Number of queries = n × total number of test queries (concurrent customers × queries per customer) --query=name,-q ා use custom script to execute test, such as calling custom stored procedure or sql statement to execute test --Create schema represents the customized test library name and test schema --Commit once after how many DML S = n ා --compress, -C ා if both the server and the client support compression, compress the information --concurrency=N, -c N = concurrency, that is, how many clients are simulated to execute select at the same time. Multiple values can be specified with commas Or -- delimiter parameter specifies the value as separator, for example: -- concurrency = 100200500 --Engine = engine? Name, - e engine? Name? Represents the engine to be tested. There can be multiple engines separated by separators. For example: - engines=myisam,innodb --iterations=N, -i N ා the number of iterations for test execution, which represents how many times to run tests in different concurrent environments --Only print only prints the test statement without actually executing it. --detach=N ා disconnect and reconnect after executing N statements --Debug info, - t ා print memory and CP
mysqlslap example
#Single thread test mysqlslap -a -uroot -pmagedu #Multithreaded testing. Use -- concurrency to simulate concurrent connections mysqlslap -a -c 100 -uroot -pmagedu #Iterative testing. Used to average tests that need to be executed multiple times 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 #Test and compare the performance of different storage engines at the same time mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 -- engine=myisam,innodb --debug-info -uroot -pmagedu #Perform one test, 50 and 100 concurrent queries respectively, and perform 1000 total queries mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot - pmagedu #50 and 100 concurrencies get one test result (Benchmark) respectively. The more concurrency, the longer time to complete all queries. For accuracy //As a matter of fact, you can test several times more iteratively mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 -- debug-info -uroot -pmagedu
my.cnf configuration case of production environment
- Hardware: memory 32G
#Open independent tablespace innodb_file_per_table = 1 #The maximum number of simultaneous sessions allowed by MySQL service. If there is often an error prompt for Too Many Connections, you need to increase this value max_connections = 8000 #Number of tables opened by all threads open_files_limit = 10240 #Back log is the number of connections that the operating system can maintain in the listening queue back_log = 300 #The maximum number of error allowed per client connection. When the number is exceeded, MYSQL server will disable the connection request of this host until MYSQL //Information about restarting the server or clearing the host through the flush hosts command max_connect_errors = 1000 #The data size of each connection is 1G at most, which must be a multiple of 1024. Generally, it is set to the value of the largest BLOB max_allowed_packet = 32M #Specify the maximum connection time for a request wait_timeout = 10 # The sort buffer is used to handle sorting caused by queues like ORDER BY and GROUP BY sort_buffer_size = 16M #Full table scan without index. Minimum buffer used join_buffer_size = 16M #Query buffer size query_cache_size = 128M #Specifies the buffer size that a single query can use. The default is 1M query_cache_limit = 4M # Set default transaction isolation level transaction_isolation = REPEATABLE-READ # The heap size used by the thread. This value limits the recursion depth of stored procedures that can be processed in memory and the complexity of SQL statements. The memory with this capacity is //Reserved for connection thread_stack = 512K # Binary log function log-bin #Binary log format binlog_format=row #InnoDB uses a buffer pool to store the index and raw data. This variable can be set to 80% of the physical memory size innodb_buffer_pool_size = 24G #Number of IO threads used to synchronize IO operations innodb_file_io_threads = 4 #The recommended setting for the number of threads allowed in the InnoDb core is twice the number of CPU s plus the number of disks innodb_thread_concurrency = 16 # The size of the buffer used to buffer log data innodb_log_buffer_size = 16M //Size of each log file in the log group innodb_log_file_size = 512M # Total number of files in the log group innodb_log_files_in_group = 3 # Time for InnoDB transaction to wait for InnoDB row lock before SQL statement is rolled back innodb_lock_wait_timeout = 120 #Slow query duration long_query_time = 2 #Record queries that are not indexed log-queries-not-using-indexes
MySQL configuration best practices
For the Internet business with high concurrency and big data, the idea of architecture design is to "liberate the CPU of database and transfer the calculation to the service layer". In the case of large concurrency, these functions are likely to drag the database to death, and the business logic to the service layer has better expansibility, which can easily realize "add machine and performance"
- Reference resources:
Alibaba Java development manual
58 home database 30 military regulations interpretation
http://zhuanlan.51cto.com/art/201702/531364.htm