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

This article focuses on "MySQL multi-master replication and MySQL Galera installation and deployment methods", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor to take you to learn "MySQL multi-master replication and MySQL Galera installation and deployment method"!

MySQL Galera introduction

Introduction to featur

MySQL/Galera is a multi-master cluster of MySQL/InnoDB and has the following features:

-synchronous replication

Multi-master topology of Active-active

-any node in the cluster can read and write

-automatic identity control, and the failed node automatically leaves the cluster

-automatic node access

-Real parallel replication based on the "row" level and ID check

-No single point of failure, easy to expand

MySQL Galera installation

Pre-installation preparation

Machine preparation

G221: 192.168.1.221 (Centos 6.4)

G222: 192.168.1.222 (Centos 6.4)

G223: 192.168.1.223 (Centos 6.4)

Installation dependency

-make sure that the version with gcc and gcc-c++ installed is at least 4.4

# yum install gcc gcc-c++

-make sure that the version of boost-devel installed is at least 1.4.1

# yum install boost-devel

-install scons check-devel openssl-devel

# yum install scons check-devel openssl-devel

MySQL Galera installation

Install MySQL 5.5.29 with wsrep Patch

# yum install libaio# wget https://launchpad.net/codership-mysql/5.5/5.5.29-23.7.3/+download/mysql-5.5.29_wsrep_23.7.3-linux-x86_64.tar.gz# tar zxvf mysql-5.5.29_wsrep_23.7.3-linux-x86_64.tar.gz # # mv mysql-5.5.29_wsrep_23.7.3-linux-x86_64 / usr/local/mysql # cd / usr/local/mysql/# groupadd mysql# useradd-r-g mysql mysql# chown-R mysql:mysql. # / scripts/mysql_install_db-- no-defaults-- datadir=/usr/local/mysql/data-- user=mysql# chown-R root. # chown-R mysql data

Install the Galera replication plug-in

# wget https://launchpad.net/galera/2.x/23.2.4/+download/galera-23.2.4-src.tar.gz# tar zxvf galera-23.2.4-src.tar.gz# cd galera-23.2.4-src# scons# cp garb/garbd / usr/local/mysql/bin/# cp libgalera_smm.so / usr/local/mysql/lib/plugin/

MySQL Galera configuration

Example of MySQL Galera configuration:

# cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysql# mkdir-p / var/lib/mysql# chown mysql:mysql / var/lib/mysql# vi / etc/my.cnf# cat / etc/ my.cnf [client] port = 3306socket = / var/lib/mysql/ mysql.sock [mysqld _ safe] log-error = / var/lib/mysql/mysql.logpid-file = / var/lib/mysql/ mysql.pid [mysqld] wsrep_node_name = node1wsrep _ provider = / usr/local/mysql/lib/plugin/libgalera_smm.so#wsrep_provider_options = 'gcache.size=1G Socket.ssl_key=my_key Socket.ssl_cert=my_cert'#wsrep_slave_threads=16wsrep_sst_method = rsync#wsrep_sst_auth=root:port = 3306socket = / var/lib/mysql/mysql.sockuser = mysqlbasedir = / usr/local/mysqldatadir = / usr/local/mysql/datadefault_storage_engine=InnoDB#innodb_buffer_pool_size=1G#innodb_log_file_size=256Minnodb_autoinc_lock_mode=2innodb_locks_unsafe_for_binlog=1innodb_flush_log_at_trx_commit=0innodb_doublewrite=0innodb_file_per_table=1binlog_format=ROWlog-bin=mysql-binserver-id=101relay- Log=mysql-relay-bin#read_only=1log-slave-updates=1

Note: please refer to MySQL wsrep parameters.

MySQL Galera startup and shutdown

First launch:

[root@G221] # / usr/local/mysql/bin/mysqld_safe-- wsrep_cluster_address=gcomm:// > / dev/null &

Or

[root@G221] # service mysql start-- wsrep_cluster_address=gcomm://

View the port on which mysqld is listening:

[root@G221] # netstat-plantu | grep mysqldtcp 0 0 0.0.0. 0 plantu 4567 0.0.0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0 of the LISTEN 3656/mysqld tcp 3306 0. 0. 0. 0. 0 of the LISTEN 3656/mysqld.

Note:

1) "gcomm://" is a special address, which is only used when galera cluster initialization starts. When starting again, you need to use a specific IP address.

2) Port 4567 is the default port used by wsrep. The firewall setting rules for this port should be the same as those for 3306.

Close:

[root@G221 ~] # / usr/local/mysql/bin/mysqladmin-uroot-p shutdownMySQL Galera new node

Add a new node

Node access

When you add a new node, the new access node is called Joiner, and the node that provides replication to joiner is called Donor. New node access requires:

1) install the MySQL version with wsrep patch

2) install the Galera replication plug-in

3) configure the MySQL of the new node (refer to Donnor's my.cnf)

4) the address of the configured or started gcomm:// is the IP that needs to use donnor.

Access node G222:

[root@G222 data] # / usr/local/mysql/bin/mysqld_safe-wsrep_cluster_address= "gcomm://192.168.1.221:4567192.168.1.223:4567" > / dev/null &

Access node G223:

[root@G223 data] # service mysql start-- wsrep_cluster_address= "gcomm://192.168.1.221:4567192.168.1.222:4567"

There are two ways to modify the wsrep_cluster_address of a node: 1) restart the node with the new wsrep_cluster_address:

[root@G221 data] # service mysql restart-- wsrep_cluster_address= "gcomm://192.168.1.222:4567192.168.1.223:4567" Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS!

2) modify the MySQL global variable directly

Mysql > SHOW VARIABLES LIKE 'wsrep_cluster_address' +-+ | Variable_name | Value | + -+ | wsrep_cluster_address | gcomm://192.168.1.222:4567 | +-+-- + 1 row in set (0.00 sec) mysql > set global wsrep_cluster_address= "gcomm://192.168.1.222:4567192.168.1.223:4567" Query OK, 0 rows affected (2.20 sec) mysql > SHOW VARIABLES LIKE 'wsrep_cluster_address' +-+ | Variable_name | Value | +- -+-+ | wsrep_cluster_address | gcomm://192.168.1.222:4567192.168.1.223:4567 | +- -+-+ 1 row in set (0.00 sec) MySQL Galera monitoring

View related variables

View the MySQL version:

Mysql > SHOW GLOBAL VARIABLES LIKE 'version';+-+-+ | Variable_name | Value | +-+-+ | version | 5.5.29-log | +-+-+ 1 row in set (0.00 sec)

View the wsrep version:

Mysql > SHOW GLOBAL STATUS LIKE 'wsrep_provider_version' +-+-+ | Variable_name | Value | +-+-+ | wsrep_provider_version | 2.4 (rXXXX) | +- -+-+ 1 row in set (0.00 sec)

View all variables related to wsrep:

Mysql > SHOW VARIABLES LIKE 'wsrep%'\ Variable_name * 1. Row * * Variable_name: wsrep_OSU_method Value: TOI** 2. Row * * Variable_name: wsrep_auto_increment_control Value: ON** 3. Row * * Variable_name: wsrep_causal_reads Value: OFF* * 4. Row * * Variable_name: wsrep_certify_nonPK Value: ON** 5. Row * * Variable_ Name: wsrep_cluster_address Value: gcomm://192.168.1.222:4567192.168.1.223:4567** 6. Row * * Variable_name: wsrep_cluster_name Value: my_wsrep_cluster* * 7. Row * * Variable_name: wsrep_convert_LOCK_to_trx Value: OFF** 8.row * * * Variable_name: wsrep_data_home_dir Value: / usr/local/mysql/data/** 9. Row * * Variable_name: wsrep_dbug_option Value: * * 10. Row * * Variable_name: wsrep_debug Value: OFF** 11. Row * * Variable_name: wsrep_drupal_282555_workaround Value: OFF** 12. Row * * Variable_name: wsrep_forced_binlog_format Value: NONE** 13. Row * * Variable_name: wsrep_log_conflicts Value: OFF** 14. Row * * Variable_name: wsrep_max_ws_rows Value: 131072 * * 15. Row * * Variable_name: wsrep_max_ws_size Value: 1073741824 * 16. Row * * Variable_name: wsrep _ mysql_replication_bundle Value: 0floor * 17. Row * * Variable_name: wsrep_node_address Value: * * 18. Row * * Variable_name: wsrep_node_incoming_address Value: AUTO** 19. Row * * Variable_name: wsrep_node_name Value: node1* * 20. Row * * Variable_name: wsrep_notify_cmd Value: * 21. Row * * Variable_name: wsrep_on Value: ON** 22. Row * * Variable_name: wsrep_provider Value: / usr/local/mysql/lib/plugin/libgalera_smm.so** 23. Row * * Variable_name: wsrep_provider_options Value: base_host = 192.168.1.221; base_port = 4567; cert.log_conflicts = no; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT15S Evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 1; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = PT5M; gcache.dir = / usr/local/mysql/data/; gcache.keep_pages_size = 0; gcache.mem_size = 0 Gcache.name = / usr/local/mysql/data//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 16; gcs.fc_master_slave = NO; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = NO Gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr =; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 192.168.1.221; pc.checksum = true; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.version = 0; pc.weight = 1; protonet.backend = asio; protonet.version = 0 Replicator.causal_read_timeout = PT30S; replicator.commit_order = 3 times * 24. Row * * Variable_name: wsrep_recover Value: OFF** 25. Row * * Variable_name: wsrep_replicate_myisam Value: OFF** 26. Row * * Variable_name: wsrep_retry_autocommit Value: 1 million * 27. Row * * Variable_name: wsrep_slave_threads Value: 2 hours * 28. Row * * Variable_name: wsrep_sst_auth Value: * * 29. Row * * Variable_name: wsrep_sst_donor Value: * * 30. Row * * Variable_name: wsrep_sst_donor_rejects_queries Value: OFF** 31. Row * * Variable_name: wsrep_sst_method Value: rsync** 32. Row * * Variable_name: wsrep_sst_receive_address Value: AUTO** 33. Row * * Variable_name: wsrep_start_position Value: 80cdd13d-8cf2-11e2-0800-e0817023b754:033 rows in set (0.00 sec)

Status monitoring

Check the status of the Galera cluster:

Mysql > show status like 'wsrep%' +-- +-- + | Variable_name | Value | | +-- +-- + | wsrep_local_state_uuid | 80cdd13d-8cf2-11e2-0800-e0817023b754 | | | wsrep_protocol_version | 4 | | wsrep_last_committed | 3 | | wsrep_replicated | 3 | | wsrep_replicated_bytes | 522 | | | wsrep_received | 6 | wsrep_received_bytes | 1134 | | wsrep_local_commits | 1 | wsrep_local_cert_failures | 0 | wsrep_local_bf_aborts | 0 | | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.000000 | | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_flow_control_paused | 0.000000 | | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | 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 | 5 | wsrep_causal_reads | 0 | | | wsrep_incoming_addresses | 192.168.1.221 wsrep_cluster_size 3306192.168.1.222Vera 3306192.168.1.223Vera 3306 | | wsrep_cluster_conf_id | 13 | wsrep_cluster_size | 3 | | | wsrep_cluster_state_uuid | 80cdd13d-8cf2-11e2-0800-e0817023b754 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_ | Local_index | 0 | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy | | wsrep_provider_version | 2.4 (rXXXX) | | wsrep_ready | ON | +-- +-+ 40 rows in set (0.00 sec) |

Monitoring status description

Wsrep_last_committed: number of transactions last committed

Wsrep_local_cert_failures and wsrep_local_bf_aborts: rollback, number of conflicts detected (www.51itstudy.com)

Wsrep_local_send_queue_avg: a harbinger of network bottleneck. If this value is high, there may be a cyber bottle.

Wsrep_flow_control_paused: indicates how long replication has been stopped. That is to say, the extent to which the cluster is slow due to Slave latency. The value is 0-1, which is as close to 0 as possible, and a value of 1 means that replication stops completely. The value of wsrep_slave_threads can be optimized to improve.

Wsrep_cert_deps_distance: how many transactions can be processed in parallel. The wsrep _ slave_threads setting should not be too much higher than this value.

Wsrep_flow_control_sent: indicates how many times the node has stopped copying.

Wsrep_local_recv_queue_avg: indicates the average length of the slave transaction queue. A sign of bottleneck.

Wsrep_ready: if the value is ON, the SQL load is acceptable. If Off, you need to check wsrep_connected.

Wsrep_connected: if the value is Off and the value of wsrep_ready is also Off, the node is not connected to the cluster. It may be caused by misconfiguration such as wsrep_cluster_address or wsrep_cluster_name. You need to check the error log for specific errors)

Wsrep_local_state_comment: if wsrep_connected is On, but wsrep_ready is OFF, you can check the reason from this item.

Wsrep_cluster_state_uuid: the values of all nodes in the cluster should be the same. Nodes with different values indicate that they are not connected to the cluster.

Wsrep_cluster_conf_id: normally this value is the same on all nodes. If the value is different, the node is temporarily partitioned. The same value should be restored when the network connection between nodes is restored.

Wsrep_cluster_size: if this value matches the expected number of nodes, then all cluster nodes are connected.

Wsrep_cluster_status: the state of the cluster composition. If it is not Primary, it means that there is a partition or split-brain condition.

Cluster integrity check:

Node status check:

Copy the health check:

The slowest node has the highest wsrep_flow_control_sent and wsrep_local_recv_queue_avg values. If these two values are lower, they are relatively better.

Detect slow network problems:

Number of conflicts or deadlocks:

Other features of MySQL Galera

Galera arbitrator

Garbd can be run as a data-free Galera node, which can help detect and handle network splits (network splits) in an optimized way.

A good garbd node can prevent "split-brain" conditions from happening.

SSL support

Galera supports SSL communication, which is important for increasing data security (especially cloud deployment). It can be defined by parameters socket.ssl_cert and socket.ssl_key.

SST mode selection

SST allows newly accessed nodes to use custom methods to obtain the initial data. SST methods are mysqldump (default, slow), rsync, and xtrabackup (soon).

Wsrep_sst_method can be used to define the recommended use of rsync or xtrabackup in a production environment.

At this point, I believe you have a deeper understanding of "MySQL multi-master replication and MySQL Galera installation and deployment methods". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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