MySQL replication is a valuable feature and asset in Kubernetes (K8S), allowing for redundancy, increased availability and potential performance improvements. Follow this guide to learn how to setup your MySQL replication cluster.
We will use a traditional slave/master set up with asynchronous replication, configurable replication, depending on user configuration, and no requirement for a constant connection. For the official line on MySQL and more information plus technical details around replication, check out the MySQL documentation.
1. First Step
In this example, we are using Vagrant as a configuration manager on Mac OS X to create the Kubernetes environment. To follow this guide, please install Vagrant on your host machine. You will also need a virtualization tool, for example Oracle’s Virtual Box or VMware Fusion.
2. Setting up the K8S Environment
With a variety of ways to configure and setup your Kubernetes cluster, your existing system may vary. In this instance, we are using Vagrant (the Vagrantfile
is provided by the CoreOS setup guide), to set up the environment, making use of a single controller node and a set of three worker (slave) nodes.
After setting up Vagrant, check the state of the environment by running these commands…
➜ vagrant git:(master) ✗ vagrant status Current machine states: e1 running (virtualbox) c1 running (virtualbox) w1 running (virtualbox) w2 running (virtualbox) w3 running (virtualbox)
This environment represents multiple VMs. The VMs are all listed
above with their current state. For more information about a specific
VM, run`vagrant status NAME’.
➜ vagrant git:(master) ✗ kubectl get nodes NAME STATUS AGE 172.17.4.101 Ready,SchedulingDisabled 23m 172.17.4.201 Ready 23m 172.17.4.202 Ready 23m 172.17.4.203 Ready 21m ➜ vagrant git:(master) ✗
e1
is etcd node, c1
is controller node, and w1
, w2
, w3
are worker nodes.
3. Prepare Your MySQL Docker Image
Build the Docker Image
Having setup the replication cluster, we’ll modify the Docker file and build a Docker image with MySQL. Again, we follow the official guidance based on the MySQL Docker image.
For the master:
Edit the Dockerfile
, and add the following lines:
RUN sed -i '/\[mysqld\]/a server-id=1\nlog-bin' /etc/mysql/mysql.conf.d/mysqld.cnf
Edit docker-entrypoint.sh file
to create a user and password for replication:
echo "CREATE USER '$MYSQL_REPLICATION_USER'@'%' IDENTIFIED BY '$MYSQL_REPLICATION_PASSWORD' ;" | "${mysql[@]}" echo "GRANT REPLICATION SLAVE ON *.* TO '$MYSQL_REPLICATION_USER'@'%' IDENTIFIED BY '$MYSQL_REPLICATION_PASSWORD' ;" | "${mysql[@]}" echo 'FLUSH PRIVILEGES ;' | "${mysql[@]}"
We use environment variables MYSQL_REPLICATION_USER
and MYSQL_REPLICATION_PASSWORD
for user and password configuration. These environment variables will be set while the pod is created in Kubernetes.
Click the link to view the dockerfile and docker-entrypoint.sh
for building the master.
For the slave:
Edit the Dockerfile
, and add the following lines:
RUN RAND="$(date +%s | rev | cut -c 1-2)$(echo ${RANDOM})" && sed -i '/\[mysqld\]/a server-id='$RAND'\nlog-bin' /etc/mysql/mysql.conf.d/mysqld.cnf
The server-id
use random number.
Edit docker-entrypoint.sh
to add the master.
echo "STOP SLAVE;" | "${mysql[@]}" echo "CHANGE MASTER TO master_host='$MYSQL_MASTER_SERVICE_HOST', master_user='$MYSQL_REPLICATION_USER', master_password='$MYSQL_REPLICATION_PASSWORD' ;" | "${mysql[@]}" echo "START SLAVE;" | "${mysql[@]}"
The master host is MYSQL_MASTER_SERVICE_HOST
which will be the service name of the master pod in Kubernetes. For more information visit Kubernetes’ container lifecycle hooks guide.
Click the link to view the docker-entrypoint.sh for building the slave.
Finally, we can build the MySQL Master image and the MySQL Slave images, based on their Dockerfile.
docker build -t mysql-master:0.1 . docker build -t mysql-slave:0.1 .
4. Deploy to Kubernetes
We now deploy the MySQL replication cluster to kubernetes using the kubectl
command.
4.1 Deploy MySQL Master
Create a replication controller and service for the MySQL Master node. The yaml file we use to create replication controller and service are:
$ more mysql-master-rc.yaml apiVersion: v1 kind: ReplicationController metadata: name: mysql-master labels: name: mysql-master spec: replicas: 1 selector: name: mysql-master template: metadata: labels: name: mysql-master spec: containers: - name: master image: mysql-master:0.1 ports: - containerPort: 3306 env: - name: MYSQL_ROOT_PASSWORD value: "test" - name: MYSQL_REPLICATION_USER value: 'demo' - name: MYSQL_REPLICATION_PASSWORD value: 'demo' $ more mysql-master-service.yaml apiVersion: v1 kind: Service metadata: name: mysql-master labels: name: mysql-master spec: ports: - port: 3306 targetPort: 3306 selector: name: mysql-master
Now, we will use kubectl
to ar the controller and service:
$ kubectl create -f mysql-master-rc.yaml $ kubectl create -f mysql-master-service.yaml
It will take some time to create the pod because it needs to download the docker image.
$ kubectl get pods NAME READY STATUS RESTARTS AGE mysql-master-95j7d 1/1 Running 0 29m $ kubectl get svc NAME CLUSTER-IP EXTERNAL-IP PORT(S) AGE kubernetes 10.3.0.1 <none> 443/TCP 23h mysql-master 10.3.0.29 <none> 3306/TCP 25m
4.2 Deploy MySQL Slave
As with the master node, we will use two yaml files to create the replication controller and service for the MySQL slave.
$ more mysql-slave-rc.yaml apiVersion: v1 kind: ReplicationController metadata: name: mysql-slave labels: name: mysql-slave spec: replicas: 1 selector: name: mysql-slave template: metadata: labels: name: mysql-slave spec: containers: - name: slave image: mysql-slave:0.1 ports: - containerPort: 3306 env: - name: MYSQL_ROOT_PASSWORD value: "test" - name: MYSQL_REPLICATION_USER value: 'demo' - name: MYSQL_REPLICATION_PASSWORD value: 'demo' $ more mysql-slave-service.yaml apiVersion: v1 kind: Service metadata: name: mysql-slave labels: name: mysql-slave spec: ports: - port: 3306 targetPort: 3306 selector: name: mysql-slave
Now, we will use kubectl
to add the controller and service:
$ kubectl create -f mysql-slave-rc.yaml $ kubectl create -f mysql-slave-service.yaml
After these tasks have been performed, we should check the status through kubectl
.
$ kubectl get pods -o wide NAME READY STATUS RESTARTS AGE IP NODE mysql-master-95j7d 1/1 Running 0 33m 10.2.64.5 172.17.4.201 mysql-slave-gr41w 1/1 Running 0 23m 10.2.45.3 172.17.4.202 $ kubectl get svc NAME CLUSTER-IP EXTERNAL-IP PORT(S) AGE kubernetes 10.3.0.1 <none> 443/TCP 23h mysql-master 10.3.0.29 <none> 3306/TCP 28m mysql-slave 10.3.0.5 <none> 3306/TCP 22m
5. Test Your Setup
5.1 Create Your Database on Master
First, we will check the MySQL status both on master and slave. Go to the master pod and execute the following commands to check the MySQL status (follow the same steps for the slave node).
$ kubectl exec -it mysql-master-95j7d /bin/bash root@mysql-master-95j7d:/# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.0-dmr-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
Note
mysql-master-95j7d
is the name of master pod and the root password of MySQL is test
.
Then, create a database and add one table with a several rows.
On the master node, we do:
mysql> create database demo; Query OK, 1 row affected (0.02 sec) mysql> use demo; Database changed mysql> create table user(id int(10), name char(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into user values(100, 'user1'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +------+-------+ | id | name | +------+-------+ | 100 | user1 | +------+-------+ 1 row in set (0.00 sec) mysql>
5.1 Check Synchronization on Slave
Go to the slave node kubectl exec -it mysql-slave-gr41w /bin/bash
and ensure the table has been replicated:
mysql> show slave status\G; Empty set (0.0 sec) ERROR: No query specified mysql> mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | demo | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use demo; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from user; +------+-------+ | id | name | +------+-------+ | 100 | user1 | +------+-------+ 1 row in set (0.00 sec) mysql>
All data should now be synchronized.
5.3 Replication Controller Scaling
Now that we have one MySQL master pod and one MySQL slave pod, we can do some scaling. For example, set MySQL slave node to three.
kubectl get pods -o wide NAME READY STATUS RESTARTS AGE IP NODE mysql-master-95j7d 1/1 Running 0 1h 10.2.64.5 172.17.4.201 mysql-slave-4rk62 0/1 ContainerCreating 0 2s <none> 172.17.4.203 mysql-slave-9fjkl 0/1 ContainerCreating 0 2s <none> 172.17.4.201 mysql-slave-gr41w 1/1 Running 0 50m 10.2.45.3 172.17.4.202
You can see it’s creating now, after few time, the nodes will be ready and we can enter one of them to check the MySQL data synchronization.
5.4 Making MySQL Data Persistent
In your production environment, configure the volume mount for MySQL data persistent, the yaml file for creating replication controller is as below:
apiVersion: v1 kind: ReplicationController metadata: name: mysql-master labels: name: mysql-master spec: replicas: 1 selector: name: mysql-master template: metadata: labels: name: mysql-master spec: containers: - name: master image: paulliu/mysql-master:0.1 ports: - containerPort: 3306 volumeMounts: - name: mysql-data mountPath: /var/lib/mysql env: - name: MYSQL_ROOT_PASSWORD value: "test" - name: MYSQL_REPLICATION_USER value: 'demo' - name: MYSQL_REPLICATION_PASSWORD value: 'demo' volumes: - name: mysql-data hostPath: path: /var/lib/mysql
The Mount path /var/lib/mysql
is just the same as defined in Dockerfile
.