{"id":7369,"date":"2018-05-03T10:07:12","date_gmt":"2018-05-03T02:07:12","guid":{"rendered":"http:\/\/rmohan.com\/?p=7369"},"modified":"2018-05-03T21:19:26","modified_gmt":"2018-05-03T13:19:26","slug":"centos-7-4-mariadb-galera-cluster","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7369","title":{"rendered":"CentOS 7.4 MariaDB Galera Cluster"},"content":{"rendered":"
\n
<\/div>\n<\/div>\n
\n

Mariadb galera cluster installation:
\nOperating system:\u00a0CentOS<\/a>\u00a07.4 version
\nCluster number: 3 nodes
\nHost information: 192.168.153.142 node1 selinux=disabled firewalld Shutdown
\n192.168.153.143 node2 selinux=disabled firewalld Shut down
\n192.168.153.144 node3 selinux=disabled firewalld Shut down<\/p>\n

Build steps<\/p>\n

1. Hosts resolve each other: all three nodes must execute
\nvim \/etc\/hosts
\n192.168.153.142 node1
\n192.168.153.143 node2
\n192.168.153.144 node3<\/p>\n

2. Install the software package<\/p>\n

The first method: (yum install -y MariaDB-server MariaDB-client galera)
\nConfigure yum installation source and configure mariadb galera installation source
\nyum source configuration hang iso
\nSet up mariadb yum source and install (all nodes are required)
\nModify yum source file<\/p>\n

vi \/etc\/yum.repos.d\/mariadb.repo<\/p>\n

[mariadb]
\nname = MariaDB
\nbaseurl = http:\/\/yum.mariadb.org\/10.3.5\/centos74-amd64
\ngpgkey=https:\/\/yum.mariadb.org\/RPM-GPG-KEY-MariaDB
\ngpgcheck=1
\nenabled=0
\nWhen installing galera software, it needs to resolve its dependencies: boost-program-options.x86_64 (direct yum source installation)<\/p>\n

The second method: (rpm package installation) all three nodes need to be installed
\nto download the rpm package from the web: galera-25.3.23-1.rhel7.el7.centos.x86_64.rpm
\nMariaDB-10.3.5-centos74-x86_64-client .rpm
\nMariaDB-10.3.5-centos74-x86_64-compat.rpm
\nMariaDB-10.3.5-centos74-x86_64-common.rpm
\nMariaDB-10.3.5-centos74-x86_64-server.rpm
\nrpm -ivh MariaDB-10.3.5- Centos74-x86_64-compat.rpm –nodeps
\nrpm -ivh MariaDB-10.3.5-centos74-x86_64-common.rpm
\nrpm -ivh MariaDB-10.3.5-centos74-x86_64-client.rpm
\nyum install -y boost-program- Options.x86_64 (resolve to install galera dependencies)
\nrpm -ivh galera-25.3.23-1.rhel7.el7.centos.x86_64.rpm
\nrpm -ivh MariaDB-10.3.5-centos74-x86_64-server.rpm<\/p>\n

3.mariadb initialization (the three nodes need to be executed) After the
\ninstallation is complete, it will prompt the need to initialize mariadb (set the password)
\nsystemctl start mariadb
\nmysql_secure_installation (set the mysql password as prompted)
\nsystemctl stop mariadb<\/p>\n

4. Configure the galera
\nmaster node configuration file server.cnf
\nvim \/etc\/my.cnf.d\/server.cnf
\n[galera]
\nwsrep_on=ON
\nwsrep_provider=\/usr\/lib64\/galera\/libgalera_smm.so
\nwsrep_cluster_address=”gcomm:\/\/192.168 .153.142,192.168.153.143,192.168.153.144 ”
\nwsrep_node_name = node1
\nwsrep_node_address = 192.168.153.142
\nbinlog_format = Row
\ndefault_storage_engine = the InnoDB
\ninnodb_autoinc_lock_mode = 2
\nwsrep_slave_threads. 1 =
\nthe innodb_flush_log_at_trx_commit = 0
\ninnodb_buffer_pool_size = 120M
\nwsrep_sst_method = the rsync
\nwsrep_causal_reads the ON =
\ncopy of this file mariadb- 2, mariadb-3, and attention should wsrep_node_name wsrep_node_address into the corresponding node hostname and ip.<\/p>\n

5. Start the cluster service:
\nStart the MariaDB Galera Cluster service:
\n[root@node1 ~]# \/bin\/galera_new_cluster The
\nremaining two nodes are started by:
\n[root@node1 ~]# systemctl start mariadb
\nCheck the cluster status: (The cluster service uses 4567. And 3306 ports))
\n[root@node1 ~]# netstat -tulpn | grep -e 4567 -e 3306
\ntcp 0 0 0.0.0.0:4567 0.0.0.0: LISTEN 3557\/mysqld
\ntcp6 0 0 :::3306 ::: LISTEN 3557\/mysqld<\/p>\n

6. Verify the cluster status:
\nExecute on node1:
\n[root@node1 ~]# mysql -uroot -p ##Enter the database to
\nsee if galera plug-in is enabled to
\nconnect to mariadb and check if galera plug-in
\nMariaDB\u00a0is enabled\u00a0[(none)]> show status like “wsrep_ready”;
\n+—————+——-+
\n| Variable_name | Value |
\n+————— +——-+
\n| wsrep_ready | ON |
\n+—————+——-+
\n1 row in set (0.004 sec)
\npresent cluster machine Number
\nMariaDB [(none)]> show status like “wsrep_cluster_size”;
\n+——————–+——-+
\n| Variable_name | Value |
\n+——————–+——-+
\n| wsrep_cluster_size | 3 |
\n+————– ——+——-+
\n1 row in set (0.001 sec)
\ncheck the cluster status
\nMariaDB [(none)]> show status like “wsrep%”;
\n+——————————+—————— ———————————————-+
\n| Variable_name | Value |
\n+——————————+—————- ————————————————+
\n| wsrep_apply_oooe | 0.000000 |
\n| wsrep_apply_oool | 0.000000 |
\n| wsrep_apply_window | 1.000000 |
\n| wsrep_causal_reads | 14 |
\n| wsrep_cert_deps_distance | 1.200000 |
\n| wsrep_cert_index_size | 3 |
\n| wsrep_cert_interval | 0.000000 |
\n| wsrep_cluster_conf_id | 22 |
\n| wsrep_cluster_size | 3 | ## cluster members
\n| wsrep_cluster_state_uuid | b8ecf355-233a-11e8-825e-bb38179b0eb4 | ##UUID cluster unique tag
\n| wsrep_cluster_status | Primary | ##primary server
\n| wsrep_commit_oooe | 0.000000 |
\n| Wsrep_commit_oool | 0.000000 |
\n| wsrep_commit_window | 1.000000 |
\n| wsrep_connected | ON | ## currently connected in
\n| wsrep_desync_count | 0 |
\n| wsrep_evs_delayed | |
\n| wsrep_evs_evict_list | |
\n| wsrep_evs_repl_latency | 0\/0\/0\/0\/0 |
\n| wsrep_evs_state | the OPERATIONAL |
\n| wsrep_flow_control_paused | 0.000000 |
\n| wsrep_flow_control_paused_ns | 0 |
\n| wsrep_flow_control_recv | 0 |
\n| wsrep_flow_control_sent | 0 |
\n| wsrep_gcomm_uuid | 0eba3aff-2341-11e8-b45a-f277db2349d5 |
\n| wsrep_incoming_addresses | 192.168.153.142:3306,192.168.153.143:3306, 192.168.153.144:3306 | ## database in connection
\n| wsrep_last_committed | 9 | ##sql commit record
\n| wsrep_local_bf_aborts | 0 | ## is interrupted locally by the executing transaction process
\n| Wsrep_local_cached_downto | 5 |
\n| wsrep_local_cert_failures | 0 | ## local failed transaction
\n| wsrep_local_commits | 4 | sql ## local execution
\n| wsrep_local_index | 0 |
\n| wsrep_local_recv_queue | 0 |
\n| wsrep_local_recv_queue_avg | .057143 |
\n| wsrep_local_recv_queue_max | 2 |
\n| wsrep_local_recv_queue_min | 0 |
\n| wsrep_local_replays | 0 |
\n| wsrep_local_send_queue | 0 | local queue ## emitted
\n| wsrep_local_send_queue_avg | 0.000000 | ## queues averaging interval
\n| wsrep_local_send_queue_max |. 1 |
\n| wsrep_local_send_queue_min | 0 |
\n| wsrep_local_state |. 4 |
\n| wsrep_local_state_comment | Synced |
\n| wsrep_local_state_uuid | b8ecf355-233a-11e8-825e-bb38179b0eb4 | ##Cluster ID
\n| wsrep_protocol_version | 8 |
\n| wsrep_provider_name | Galera |
\n| wsrep_provider_vendor | Codership Oy <info@codership.com> |
\n| wsrep_provider_version | 25.3.23(r3789) |
\n| wsrep_ready | ON | ## Plug-In
\nWsrep_received | 35 | ##Data Copy Recipients
\n| wsrep_received_bytes | 5050 |
\n| wsrep_repl_data_bytes | 1022 |
\n| wsrep_repl_keys | 14 |
\n| wsrep_repl_keys_bytes | 232 |
\n| wsrep_repl_other_bytes | 0 |
\n| wsrep_replicated |. 5 | ## as the number of copy emitted
\n| wsrep_replicated_bytes | 1600 | sent replication data word ## The number of sections
\n| wsrep_thread_count | 2 |
\n+——————————+———– ————————————————– —+
\n58 rows in set (0.003 sec)
\nView connected hosts
\nMariaDB [(none)]> show status like “wsrep_incoming_addresses”;
\n+————————–+—————————————————————-+
\n| Variable_name | Value |
\n+————————–+—————————————————————-+
\n| wsrep_incoming_addresses | 192.168.153.142:3306,192.168.153.143:3306,192.168.153.144:3306 |
\n+————————–+—————————————————————-+
\n1 row in set (0.002 sec)<\/p>\n

7. Test whether the cluster mariad data is synchronized
\nMariaDB [(none)] create database lizk;
\nQuery OK, 1 row affected (0.010 sec)<\/p>\n

MariaDB [(none)]> show databases;
\n+——————–+
\n| Database |
\n+————– ——+
\n| china |
\n| hello |
\n| hi |
\n| information_schema |
\n| lizk |
\n| mysql |
\n| performance_schema |
\n| test |
\n+—————— –+
\n8 rows in set (0.001 sec)
\nYou can see that the lizk library is synchronized on the other two nodes.<\/p>\n

8. Simulated Brain Fissure After Treatment
\nThe following simulations show that in the case of packet loss in network jitter, the two nodes are disconnected and cause brain split.\u00a0It was performed on 192.168.153.143 192.168.153.144 and two nodes:
\niptables -A the INPUT -p TCP -j 4567 –sport the DROP
\niptables -A the INPUT -p TCP -j 4567 –dport the DROP
\nmore commands to disable the whole wsrep replication communication port 4567
\nto see node on 192.168.153.142:
\nMariaDB [(none)]> Show Status like “WS%”;
\n+ ——————— ———+—————————————- —-+
\n| Variable_name | Value |
\n+——————————+——– + ————————————
\n| wsrep_apply_oooe | 0.000000 |
\n| wsrep_apply_oool | 0.000000 |
\n| wsrep_apply_window | 1.000000 |
\n| wsrep_causal_reads | 16 |
\n| wsrep_cert_deps_distance | 1.125000 |
\n| wsrep_cert_index_size | 3 |
\n| Wsrep_cert_interval | 0.000000 |
\n| wsrep_cluster_conf_id | 18446744073709551615 |
\n| wsrep_cluster_size | 1 |
\n| wsrep_cluster_state_uuid | b8ecf355-233a-11e8-825e-bb38179b0eb4 |
\n| wsrep_cluster_status | non-Primary |
\nnow split brain situation has occurred, and the cluster can not execute any commands.
\nIn order to solve this problem, you can execute
\nset global wsrep_provider_options=”pc.bootstrap=true”;
\nThis command is used to forcibly recover nodes that have split brain.
\nVerify:
\nMariaDB [(none)]> = wsrep_provider_options Global SET “to true pc.bootstrap =”;
\nQuery the OK, 0 rows affected (0.015 sec)<\/p>\n

MariaDB [(none)]> select @@wsrep_node_name;
\n+——————-+
\n| @@wsrep_node_name |
\n+———– ——–+
\n| node1 |
\n+——————-+
\n1 row in set (0.478 sec)
\nFinally we will node 192.168.153.143 and 192.168 .153.144 Recover, just clean up the iptables table (because my test environment, the production environment needs to delete the above rules can be):
\n[root@node3 mysql]# iptables-F
\nafter the restoration to verify:
\nMariaDB [(none ]]> show status like “wsrep_cluster_size”;
\n+——————–+——-+
\n| Variable_name | Value |
\n+—- —————-+——-+
\n| wsrep_cluster_size | 3 |
\n+——————- -+——-+
\n1 row in set (0.001 sec)<\/p>\n

9. Because of the fault, it is necessary to check the two nodes of the cluster and check whether the data can be synchronized after restarting the service.
\nTo stop the operations of mariadb on 192.168.153.143 and 192.168.153.144:
\n[root@node2 mysql]# systemctl stop mariadb is
\nat 192.168. Insert data on node 153.142:
\nMariaDB [test]> select * from test1;
\n+——+
\n| id |
\n+——+
\n| 2 |
\n| 2 |
\n| 1 |
\n| 3 |
\n+– —-+
\n4 rows in set (0.007 sec)
\nNow restart the other two nodes in the cluster and see the data consistency, as with the master node.<\/p>\n

10. Abnormal processing: When the room suddenly loses power, all galera hosts are shut down abnormally, and the galera cluster service cannot start properly when the phone is switched on.\u00a0How to deal with?
\nStep 1: Open the mariadb service of the master host of the galera cluster.
\nStep 2: Start the mariadb service on the member host of the galera cluster.
\nException handling: The mysql service of the master host and member host of the galera cluster cannot be started. What should I do?
\nSolution one: Step 1. Delete the \/var\/lib\/mysql\/grastate.dat status file of
\n\/\u00a0garlera master host\u00a0\/bin\/galera_new_cluster to start the service.\u00a0Start normally.\u00a0Log in and check the wsrep status.
\nStep 2: Remove the \/var\/lib\/mysql\/grastate.dat status file from the galera member host
\nsystemctl restart mariadb Restart the service.\u00a0Start normally.\u00a0Log in and check the wsrep status.
\nSolution two: Step 1, modify the \/var\/lib\/mysql\/grastate.dat status file in the main host of the garlera group to
\nstart the service\u00a0with 0 as 1\u00a0\/bin\/galera_new_cluster.\u00a0Start normally.\u00a0Log in and check the wsrep status.
\nStep 2: Modify the 0 in the \/var\/lib\/mysql\/grastate.dat state file in the galera member host to 1
\nsystemctl restart mariadb to restart the service.\u00a0Start normally.\u00a0Log in and check the wsrep status.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"\n

Mariadb galera cluster installation: Operating system: CentOS 7.4 version Cluster number: 3 nodes Host information: 192.168.153.142 node1 selinux=disabled firewalld Shutdown 192.168.153.143 node2 selinux=disabled firewalld Shut down 192.168.153.144 node3 selinux=disabled firewalld Shut down<\/p>\n

Build steps<\/p>\n

1. Hosts resolve each other: all three nodes must execute vim \/etc\/hosts 192.168.153.142 node1 192.168.153.143 node2 192.168.153.144 node3<\/p>\n

2. Install […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[72],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7369"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7369"}],"version-history":[{"count":2,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7369\/revisions"}],"predecessor-version":[{"id":7371,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7369\/revisions\/7371"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7369"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7369"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7369"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}