Let’s build MariaDB Galera cluster with three nodes on CentOS x64 6.7 Minimal.
On all nodes static IP adresses have been configured.
Node1- 10.20.20.11
Node2 – 10.20.20.12
Node3 – 10.20.20.13
On all nodes we need to install necessary packages:
1 |
# yum -y install wget ntp lsof |
On all nodes it’s necessary to configure and syncronize clock. In other case there might be a situation when a node that is being syncronized is just waiting something without any further actions.
Let’s start ntpd service an add it to autostart:
1 2 |
# service ntpd start # chkconfig ntpd on |
Disable Selinux:
1 2 |
# sed -i.bak 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config # setenforce 0 |
Now open ports 4567,4568 (Galera), 3308 (MariaDB), 4444 (rsync_sst) in iptables:
1 2 3 4 |
iptables -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 3308 -j ACCEPT iptables -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 4567 -j ACCEPT iptables -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 4568 -j ACCEPT iptables -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 4444 -j ACCEPT |
An example of iptables configuration:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
# cat /etc/sysconfig/iptables # Generated by iptables-save v1.4.7 on Tue Sep 29 18:43:45 2015 *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [21247:2382963] -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT #Galera Cluster replication traffic -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 4567 -j ACCEPT #Galera Cluster Incremental State Transfer -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 4568 -j ACCEPT #Rsync_SST -A INPUT -s 10.20.0.0/16 -i eth0 -p tcp -m tcp --dport 4444 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT # Completed on Tue Sep 29 18:43:45 2015 |
Install Percona and MariaDB repositories:
1 2 3 |
# yum -y install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm # vi /etc/yum.repos.d/MariaDB.repo |
Insert into a file:
1 2 3 4 5 |
[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.1/rhel6-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 |
Now we can install MariaDB and Percona packages:
1 |
# yum -y install MariaDB-server MariaDB-Galera-server MariaDB-client galera rsync |
On all three nodes execute commands:
1 2 3 4 5 6 |
# service mysql start Starting MySQL.... SUCCESS! # mysql_secure_installation ... ... # service mysql stop |
Save a backup copy of configuration file and fill in the new file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 |
# mkdir /var/log/mysql # cd /etc/my.cnf.d/ # mv server.cnf server.cnf.bak # vi server.cnf [mysqld] log_error=/var/log/mysql/mysql_error.log general_log_file=/var/log/mysql/mysql.log general_log = 0 #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes user=mysql port=3308 # (This must be substituted by wsrep_format) binlog_format=ROW # Currently only InnoDB storage engine is supported default-storage-engine=innodb # to avoid issues with 'bulk mode inserts' using autoinc innodb_autoinc_lock_mode=2 # This is a must for paralell applying transaction-isolation = REPEATABLE-READ # Query Cache is not supported with wsrep query_cache_size=0 query_cache_type=0 # Override bind-address # In some systems bind-address defaults to 127.0.0.1, and with mysqldump SST # it will have (most likely) disastrous consequences on donor node bind-address=0.0.0.0 ## ## WSREP options ## wsrep_on=on # Full path to wsrep provider library or 'none' wsrep_provider=/usr/lib64/galera/libgalera_smm.so # Provider specific configuration options #wsrep_provider_options= # Logical cluster name. Should be the same for all nodes. wsrep_cluster_name="mariadb_cluster" # Group communication system handle wsrep_cluster_address=gcomm://10.20.20.11,10.20.20.12,10.20.20.13 # Human-readable node name (non-unique). Hostname by default. wsrep_node_name=node1 # Base replication <address|hostname>[:port] of the node. # The values supplied will be used as defaults for state transfer receiving, # listening ports and so on. Default: address of the first network interface. wsrep_node_address=10.20.20.11 # Address for incoming client connections. Autodetect by default. #wsrep_node_incoming_address=192.168.56.221 # How many threads will process writesets from other nodes wsrep_slave_threads=1 # DBUG options for wsrep provider #wsrep_dbug_option # Generate fake primary keys for non-PK tables (required for multi-master # and parallel applying operation) wsrep_certify_nonPK=1 # Maximum number of rows in write set wsrep_max_ws_rows=131072 # Maximum size of write set wsrep_max_ws_size=1073741824 # to enable debug level logging, set this to 1 wsrep_debug=0 # convert locking sessions into transactions wsrep_convert_LOCK_to_trx=0 # how many times to retry deadlocked autocommits wsrep_retry_autocommit=1 # change auto_increment_increment and auto_increment_offset automatically wsrep_auto_increment_control=1 # retry autoinc insert, which failed for duplicate key error wsrep_drupal_282555_workaround=0 # enable "strictly synchronous" semantics for read operations wsrep_causal_reads=0 # Command to call when node status or cluster membership changes. # Will be passed all or some of the following options: # --status - new status of this node # --uuid - UUID of the cluster # --primary - whether the component is primary or not ("yes"/"no") # --members - comma-separated list of members # --index - index of this node in the list wsrep_notify_cmd= ## ## WSREP State Transfer options ## # State Snapshot Transfer method wsrep_sst_method=rsync # Address which donor should send State Snapshot to. # Should be the address of THIS node. DON'T SET IT TO DONOR ADDRESS!!! # (SST method dependent. Defaults to the first IP of the first interface) #wsrep_sst_receive_address= # SST authentication string. This will be used to send SST to joining nodes. # Depends on SST method. For mysqldump method it is root: wsrep_sst_auth= # Desired SST donor name. #wsrep_sst_donor= # Reject client queries when donating SST (false) #wsrep_sst_donor_rejects_queries=0 # Protocol version to use # wsrep_protocol_version= |
Copy the file to other two nodes and make sure that for all three nodes the next options have been set correctly:
wsrep_cluster_name = the name of cluster. It’s the same for all nodes.
wsrep_cluster_address = string with IP adresses of all nodes in the cluster gcomm://IP1,IP2,..,IPn. It’s the same for all nodes.
wsrep_node_address = IP address of the node with this configuration file. If there is only one network adapter we can leave this option commented.
wsrep_node_incoming_address = IP address of the node with this configuration file. If there is only one network adapter we can leave this option commented.
Now we are ready to start the cluster!
Let’s initialize cluster on the first node:
1 |
[root@mariadbcluster1 my.cnf.d]# mysqld --wsrep-new-cluster & |
…and start other two:
1 2 3 4 5 |
[root@mariadbcluster2 ~]# service mysql start Starting MySQL..... SUCCESS! [root@mariadbcluster3 ~]# service mysql start Starting MySQL..... SUCCESS! |
Now let’s check cluster’s status and make sure that all nodes are online. Execute (on any node):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# mysql -u root -p Enter password: The Welcome to the MariaDB monitor. The Commands end with ; or \ g. MariaDB Your connection id is 8 Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961 Copyright (c) 2000, 2014, the Oracle, the Ab Monty Program and : others. The Type 'to help;' or '\ h' for to help. The Type '\ c' to the clear the current input the statement The. MariaDB [(none)]> show For the status like '% wsrep_cluster_size%' ; + -------------------- + ------- + | variable_name | Value | + -------------------- + ------- + | wsrep_cluster_size | 3 | + -------------------- + ------- + 1 row in the set (0.00 sec) |
All three nodes are online.
In order to check other cluster-related info execute:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
MariaDB [(none)]> show status like '%wsrep_%'; +------------------------------+-------------------------------------------------------------+ | Variable_name | Value | +------------------------------+-------------------------------------------------------------+ | wsrep_local_state_uuid | 31f07ce3-b44a-11e3-b945-f72a90507c82 | | wsrep_protocol_version | 5 | | wsrep_last_committed | 0 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 10 | | wsrep_received_bytes | 2447 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 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_local_cached_downto | 18446744073700051615 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_cert_deps_distance | 0.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 0 | | wsrep_causal_reads | 0 | | wsrep_incoming_addresses | 10.20.20.11:3306,10.20.20.12:3306,10.20.20.13:3306 | | wsrep_cluster_conf_id | 23 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 31f07ce3-baaa-11e3-b945-f72a90507c82 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 1 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 25.3.2(r170) | | wsrep_ready | ON | +------------------------------+-------------------------------------------------------------+ 46 rows in set (0.00 sec) |
In order to make sure that replication is working let’s create some test database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> create database can_you_see_me_now; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | can_you_see_me_now | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) |
We should immediately see it on other nodes:
1 2 |
[root@mariadbcluster2 ~]$ mysql -u root -p Enter password: |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961 Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | can_you_see_me_now | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) |
1 2 |
[root@mariadbcluster3 ~]$ mysql -u root -p Enter password: |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 5.5.36-MariaDB-wsrep MariaDB Server, wsrep_25.9.r3961 Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | can_you_see_me_now | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) |