Собираем кластер MariaDB из трёх нод на CentOS x64 6.7 Minimal.
На всех нодах настроены статические IP адреса.
Нода1 — 10.20.20.11
Нода2 — 10.20.20.12
Нода3 — 10.20.20.13
На всех трёх нодах устанавливаем необходимые пакеты:
|
1 |
# yum -y install wget ntp lsof |
На всех нодах, должно быть правильно установленно время, это обязательно. Иначе вы столкнётесь с тем что, при передаче состояния ноды с донора, синхронизируемая нода будет просто чего-то ждать, без каких бы то ни было признаков активности.
Запускаем службу ntpd и добавляем ее в автозагрузку:
|
1 2 |
# service ntpd start # chkconfig ntpd on |
Отключаем Selinux:
|
1 2 |
# sed -i.bak 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config # setenforce 0 |
Открываем порты 4567,4568 (Galera), 3308 (MariaDB), 4444 (rsync_sst) в 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 |
Пример конфигурации iptables:
|
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 |
Устанавливаем репозитории Percona и MariaDB:
|
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 |
Вставляем в файл:
|
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 |
Устанавливаем пакеты MariaDB и Percona:
|
1 |
# yum -y install MariaDB-server MariaDB-Galera-server MariaDB-client galera rsync |
На всех трёх нодах выполняем:
|
1 2 3 4 5 6 |
# service mysql start Starting MySQL.... SUCCESS! # mysql_secure_installation ... ... # service mysql stop |
Сохраняем копию файла конфигурации и заполняем новый файл:
|
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= |
Скопируйте файл на две другие ноды и убедитесь что для всех трёх нод следующие опции заданы правильно:
wsrep_cluster_name = имя кластера. одинаковое для всех нод
wsrep_cluster_address = строка с адресами всех нод кластера вида gcomm://IP1,IP2,..,IPn. одиноково для всех нод в кластере
wsrep_node_address = IP адрес ноды с этим файлом конфигурации. Если сетевой интерфейс один, можно не заполнять.
wsrep_node_incoming_address = IP адрес ноды с этим файлом конфигурации. Если сетевой интерфейс один, можно не заполнять.
Теперь можно запускать кластер!
Инициализируем кластер на первой ноде:
|
1 |
[root@mariadbcluster1 my.cnf.d]# mysqld --wsrep-new-cluster & |
…и запускаем две остальные ноды:
|
1 2 3 4 5 |
[root@mariadbcluster2 ~]# service mysql start Starting MySQL..... SUCCESS! [root@mariadbcluster3 ~]# service mysql start Starting MySQL..... SUCCESS! |
Чтобы проверить состояние кластера и убедиться, что все ноды онлайн, выполним следующую команду (с любой ноды):
|
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) |
Все три ноды онлайн.
Посмотреть остальную информацию по кластеру можно командой:
|
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) |
Чтобы убедиться что репликация работает создадим базу данных:
|
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) |
Мы сразу же должны увидеть её на остальных нодах:
|
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) |
