Skip to content

Ansible playbook for deploying mysql 5.7 galera cluster with HAproxy as load balancer

Notifications You must be signed in to change notification settings

shyamjos/Ansible-mysql-galera-cluster

Repository files navigation

Ansible playbook for deploying mysql-server 5.7 galera cluster with HAproxy as load balancer

Features of Mysql 5.7 Galera Cluster

  • True Multi-master, Active-Active Cluster Read and write to any node at any time.
  • Synchronous Replication No slave lag, no data is lost at node crash.
  • Tightly Coupled All nodes hold the same state. No diverged data between nodes allowed.
  • Multi-threaded Slave For better performance. For any workload.
  • No Master-Slave Failover Operations or Use of VIP.
  • Hot Standby No downtime during failover (since there is no failover).
  • Automatic Node Provisioning No need to manually back up the database and copy it to the new node.
  • Supports InnoDB.
  • Transparent to Applications Required no (or minimal changes) to the application.
  • No Read and Write Splitting Needed.
  • Easy to Use and Deploy

Requirements

This playbook requires minimum of 3 ubuntu 16.04 nodes (you can add more nodes as needed) for installing mysql 5.7 galera cluster and 1 ubuntu 16.04 node for installing HAproxy. cluster nodes and proxy node must be defined inside the included inventory.yml file.

mysql galera cluster

Running Playbook

add hosts to inventory.yml

Note: Do not modify the host group names [mysql_cluster] and [load_balancer] when adding hosts to inventory.yml file since it has dependencies inside the playbook.

[mysql_cluster]
mysql_node_1 ansible_host=192.168.10.2 ansible_port=22 ansible_user=ubuntu
mysql_node_2 ansible_host=192.168.10.3 ansible_port=22 ansible_user=ubuntu
mysql_node_3 ansible_host=192.168.10.4 ansible_port=22 ansible_user=ubuntu
[load_balancer]
haproxy_load_balancer ansible_host=192.168.10.5 ansible_port=22 ansible_user=ubuntu

Before running the playbook you have to configure below variables inside the playbook : deploy-mysql-cluster.yml

mysql_cluster_name: demo_mysql_cluster <= name of your mysql cluster without spaces

mysql_root_password: your_mysql_root_password <= a strong password for mysql root user

To run the playbook

ansible-playbook --fork=1 deploy-mysql-cluster.yml -i inventory.yml

Note: The ansible option --fork=1 is used to disable parallel execution of tasks, which helps in data consistency when stopping/bootstrapping the cluster.

After successful completion of playbook, you can access the cluster by mysql -h load_balancer_IP -P 3306 -u root -p your_mysql_root_password

Verifying cluster status

check the last part of playbook output

ok: [haproxy_load_balancer] => {
    "msg": [
        " Test connection successfull", 
        " Total number of active mysql nodes in cluster: '3 '", 
        " Setup Completed!"
    ]
}


You can also verfiy the status of mysql cluster by manually running below query

`mysql -h load_balancer_IP -P 3306 -u root -p your_mysql_root_password`

`mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)`

Which means we have 3 active nodes

Stop Mysql Cluster

You can use the --tag option "stop_cluster" to safely stop the mysql cluster

ansible-playbook --fork=1 deploy-mysql-cluster.yml -i inventory.yml --tags "stop_cluster"

Start Mysql Clsuter

You can use the --tag option "start_cluster" to safely start/bootstrap the mysql cluster

ansible-playbook --fork=1 deploy-mysql-cluster.yml -i inventory.yml --tags "start_cluster"

To-DO List

  • Convert this playbook into an ansible role

About

Ansible playbook for deploying mysql 5.7 galera cluster with HAproxy as load balancer

Topics

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published