Article directory
- Ansible deploys MariaDB galera cluster (multi-master)
-
- introduce
- Node planning
- Basic environment preparation
- Write script file
- Execute script file
- View cluster status
- test
Ansible deploys MariaDB galera cluster (multi-master)
Introduction
MariaDB Galera cluster is a multi-master MySQL cluster solution based on synchronous replication. The nodes used have no single point of failure, high availability, high read and write performance, and good scalability.
Main Features:
-
Synchronous replication, master and backup without delay
-
The multi-master architecture allows multiple nodes to become master nodes in the cluster, and all master nodes can handle write requests, which means you can write data on any node, not just on a single node.
-
No single point of failure If a node fails, other nodes can still continue to work, and when the failed node recovers, it will automatically rejoin the cluster
Node planning
| IP | hostname | node |
|---|---|---|
| 192.168.200.10 | ansible | Ansible node |
| 192.168.200.20 | node1 | Node1 node |
| 192.168.200.30 | node2 | Node2 node |
| 192.168.200.40 | node3 | Node3 |
Basic environment preparation
(1) Modify the host name
[root@localhost ~]# hostnamectl set-hostname ansible [root@localhost ~]# hostnamectl set-hostname node1 [root@localhost ~]# hostnamectl set-hostname node2 [root@localhost ~]# hostnamectl set-hostname node3
(2) Install ansible
[root@ansible ~]# yum install -y epel-release [root@ansible ~]# yum install -y ansible
(3) Configure the connection between the Ansible node and the remote host
[root@ansible ~]# ssh-keygen [root@node1~]# ssh-keygen [root@node2~]# ssh-keygen [root@node3~]# ssh-keygen
(4) Configure the host group
[root@ansible ~]# vim /etc/ansible/hosts [node1] 192.168.200.20 [node2] 192.168.200.30 [node3] 192.168.200.40
(5) Configure Yum file
Mariadb10.3 (10.3 comes with galera software)
[root@ansible ~]# vim mariadb.repo [mariadb] name=MariaDB baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64/ gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB gpgcheck=1
(6) Configure the server-node(1~3).conf file for copying to remote nodes
[root@ansible ~]# vim server-node1.cnf [server] [mysqld] [galera] wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.200.20,192.168.200.30,192.168.200.40" binlog_format=row default_storage_engine=InnoDB wsrep_node_name=node1 [embedded] [mariadb] [mariadb-10.3] [root@node1 ~]# cp server-node1.cnf server-node2.cnf [root@node1 ~]# cp server-node1.cnf server-node3.cnf [root@node1 ~]# sed -i 's/wsrep_node_name=node1/wsrep_node_name=node2/g' server-node2.cnf [root@node1 ~]# sed -i 's/wsrep_node_name=node1/wsrep_node_name=node3/g' server-node3.cnf
Detailed explanation of configuration file server.conf parameters wsrep_on=ON # Whether to enable the plugin wsrep_provider=/usr/lib64/galera/libgalera_smm.so # Specify the address of the library file of galera wsrep_cluster_address="gcomm://192.168.200.20,192.168.200.30,192.168.200.40" # cluster IP binlog_format=row # The format of the binary log is row default_storage_engine=InnoDB # The default storage engine is InnoDB wsrep_node_name=node3 # specifies the current node name
(7) Test host connectivity
[root@ansible ~]# ansible all -m ping
192.168.200.20 | SUCCESS => {<!-- -->
"ansible_facts": {<!-- -->
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
192.168.200.30 | SUCCESS => {<!-- -->
"ansible_facts": {<!-- -->
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
192.168.200.40 | SUCCESS => {<!-- -->
"ansible_facts": {<!-- -->
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
Writing script files
[root@ansible ~]# vim install_galera_cluster.yaml
- hosts: all
remote_user: root
tasks:
- name: stop firewalld setenforce 0
shell: systemctl stop firewalld & amp; & amp; setenforce 0
-name: copy hosts
copy: src=/etc/hosts dest=/etc/hosts
- name: copy repo
copy: src=mariadb.repo dest=/etc/yum.repos.d/
- name: install mariadb
yum: name=mariadb-server state=installed
-name: start mariadb
service: name=mariadb state=started enabled=yes
- name: init_mysql
shell: mysqladmin -uroot password 000000
- name: stop mariadb
service: name=mariadb state=stopped
- hosts: node1
remote_user: root
tasks:
- name: copy server-node1.cnf
copy: src=server-node1.cnf dest=/etc/my.cnf.d/server.cnf
-name: chushihua
shell: galera_new_cluster
- hosts: node2
remote_user: root
tasks:
- name: copy server-node2.cnf
copy: src=server-node2.cnf dest=/etc/my.cnf.d/server.cnf
- hosts: node3
remote_user: root
tasks:
- name: copy server-node3.cnf
copy: src=server-node3.cnf dest=/etc/my.cnf.d/server.cnf
- hosts: node2, node3
remote_user: root
tasks:
-name: start mariadb
shell: systemctl start mariadb
Execute the script file
[root@ansible ~]# ansible-playbook install_galera_cluster.yaml PLAY [all] ************************************************* ***************************************************** *************************************************** TASK [Gathering Facts] ************************************************* ***************************************************** ************************************* ok: [192.168.200.20] ok: [192.168.200.30] ok: [192.168.200.40] TASK [stop firewalld setenforce 0] *********************************************** ***************************************************** *************************** changed: [192.168.200.20] changed: [192.168.200.30] changed: [192.168.200.40] TASK [copy hosts] ************************************************* ***************************************************** ******************************************* ok: [192.168.200.40] ok: [192.168.200.30] ok: [192.168.200.20] TASK [copy repo] ************************************************* ***************************************************** ********************************************* changed: [192.168.200.20] changed: [192.168.200.30] changed: [192.168.200.40] TASK [install mariadb] *********************************************** ***************************************************** ************************************* changed: [192.168.200.20] changed: [192.168.200.30] changed: [192.168.200.40] TASK [start mariadb] ************************************************* ***************************************************** ***************************************** changed: [192.168.200.40] changed: [192.168.200.20] changed: [192.168.200.30] TASK [init_mysql] ************************************************* ***************************************************** ***************************************** changed: [192.168.200.20] changed: [192.168.200.40] changed: [192.168.200.30] TASK [stop mariadb] ************************************************* ***************************************************** ***************************************** changed: [192.168.200.40] changed: [192.168.200.20] changed: [192.168.200.30] PLAY [node1] ************************************************* ***************************************************** *********************************************** TASK [Gathering Facts] ************************************************* ***************************************************** ************************************* ok: [192.168.200.20] TASK [copy server-node1.cnf] ******************************************* ***************************************************** *********************************** changed: [192.168.200.20] TASK [chushihua] ************************************************* ***************************************************** ******************************************* changed: [192.168.200.20] PLAY [node2] ************************************************* ***************************************************** *********************************************** TASK [Gathering Facts] ************************************************* ***************************************************** ************************************* ok: [192.168.200.30] TASK [copy server-node2.cnf] ******************************************* ***************************************************** *********************************** changed: [192.168.200.30] PLAY [node3] ************************************************* ***************************************************** *********************************************** TASK [Gathering Facts] ************************************************* ***************************************************** ************************************* ok: [192.168.200.40] TASK [copy server-node3.cnf] ******************************************* ***************************************************** *********************************** changed: [192.168.200.40] PLAY [node2,node3] *********************************************** ***************************************************** ******************************************* TASK [Gathering Facts] ************************************************* ***************************************************** ************************************* ok: [192.168.200.30] ok: [192.168.200.40] TASK [start mariadb] ************************************************* ***************************************************** ***************************************** changed: [192.168.200.40] changed: [192.168.200.30] PLAY RECAP *************************************************** ***************************************************** *********************************************** 192.168.200.20 : ok=11 changed=8 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 192.168.200.30 : ok=12 changed=8 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 192.168.200.40 : ok=12 changed=8 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
View cluster status
(node1, node2, node3 can all be viewed)
[root@node1 ~]# mysql -uroot -p000000 MariaDB [(none)]> show status like '%wsrep%'; + ------------------------------- + ----------------- -----------------------------------------------+ | Variable_name | Value | + ------------------------------- + ----------------- -----------------------------------------------+ | wsrep_applier_thread_count | 1 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_waits | 0 | | wsrep_apply_window | 0.000000 | | wsrep_causal_reads | 0 | | wsrep_cert_deps_distance | 0.000000 | | wsrep_cert_index_size | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_cluster_conf_id | 2 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 08f31069-2ec0-11ee-b090-fff9379da121 | | wsrep_cluster_status | Primary | | wsrep_cluster_weight | 3 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_connected | ON | | 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_flow_control_active | false | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_requested | false | | wsrep_flow_control_sent | 0 | | wsrep_gcomm_uuid | 08f1dd06-2ec0-11ee-829f-06675c76d425 | | wsrep_gmcast_segment | 0 | | wsrep_incoming_addresses | 192.168.200.20:3306,192.168.200.40:3306,192.168.200.30:3306| | wsrep_last_committed | 0 | | wsrep_local_bf_aborts | 0 | | wsrep_local_cached_downto | 18446744073709551615 | | wsrep_local_cert_failures | 0 | | wsrep_local_commits | 0 | | wsrep_local_index | 0 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_avg | 0.111111 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_local_state_uuid | 08f31069-2ec0-11ee-b090-fff9379da121 | | wsrep_open_connections | 0 | | wsrep_open_transactions | 0 | | wsrep_protocol_version | 9 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <[email protected]> | | wsrep_provider_version | 25.3.37(rd0a7bd7) | | wsrep_ready | ON | | wsrep_received | 9 | | wsrep_received_bytes | 552 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_rollbacker_thread_count | 1 | | wsrep_thread_count | 2 | + ------------------------------- + ----------------- ----------------------------------------------- +
-
wsrep_cluster_size 3 : There are 3 cluster members
-
wsrep_cluster_status Primary: primary server
-
wsrep_connected ON: whether it is connected
-
wsrep_incoming_addresses 192.168.200.20:3306,192.168.200.40:3306,192.168.200.30:3306 : connecting hosts
-
wsrep_ready ON : Whether the plugin is being applied
Test
Write data using node1
[root@node1 ~]# mysql -uroot -p000000
MariaDB [(none)]> create database node1;
MariaDB [(none)]> use node1;
MariaDB [node1]> CREATE TABLE employees (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50),
-> age INT,
-> department VARCHAR(50)
-> );
MariaDB [node1]> INSERT INTO employees (name, age, department) VALUES
-> ('John Doe', 30, 'Sales'),
-> ('Jane Smith', 25, 'Marketing'),
-> ('David Johnson', 35, 'HR');
node2 checks whether it is synchronized
[root@node2 ~]# mysql -uroot -p000000 MariaDB [(none)]> show databases; + --------------------+ | Database | + --------------------+ | information_schema | |mysql| | node1 | | performance_schema | | test | + --------------------+ MariaDB [node1]> select * from employees; + ---- + --------------- + ------ + ------------+ | id | name | age | department | + ---- + --------------- + ------ + ------------+ | 1 | John Doe | 30 | Sales | | 4 | Jane Smith | 25 | Marketing | | 7 | David Johnson | 35 | HR | + ---- + --------------- + ------ + ------------+
If node3 is down at this time
[root@node3 ~]# systemctl stop mariadb
View cluster information
MariaDB [node1]> show status like 'wsrep_cluster_size'; + -------------------- + ------- + | Variable_name | Value | + -------------------- + ------- + | wsrep_cluster_size | 2 | + -------------------- + ------- + # Immediately remove the down host
How to restore him? Is the data still there
MariaDB [node1]> select * from employees; + ---- + --------------- + ------ + ------------+ | id | name | age | department | + ---- + --------------- + ------ + ------------+ | 1 | John Doe | 30 | Sales | | 4 | Jane Smith | 25 | Marketing | | 7 | David Johnson | 35 | HR | + ---- + --------------- + ------ + ------------+ # The data is still there