Open in app

Sign in

Write

Sign in

Live Migrate your MySQL workload to Kubernetes

Paul Czarkowski
7 min readApr 2, 2018

Recently I’ve been working with a bunch of folks in the Kubernetes community led by Kris Nova on a demonstration of modernizing a legacy Java app to run on Kubernetes (join us in #monolith on the Kubernetes Slack).

Not being a “developer” means its hard for me to contribute to building the app itself (especially being Java) but I can help figure out some of the infrastructure bits. For example if we want to be able to demonstrate a live cutover from the legacy deployment to the Kubernetes deployment we need to be able to live migrate the database.

Chances are if you’re from an Operations background like myself you’ve done a few MySQL migrations in the past. If you can do them with downtime you’d use mysqldump or xtradbackup on the existing server and restore to the new. If you need to do it live then you usually set up a leader/follower relationship with the old and new servers and then turn off the replication at cutover time.

I volunteered to figure out what it would look like to migrate data live from the old database to the new database and found it to be an interesting problem. Helm charts exist for MySQL in several forms (mysql, mysqlha) and its forks (percona, percona-xtradb-cluster).

To keep it simple (for now) I decided to use the basic MySQL chart and figure out how to replicate from one to another. Turned out it was actually pretty simple, not dissimilar to setting up MySQL replication normally.

You can find my original documentation for the process here, but I wanted to write it out a bit more thoroughly using a GCP based instance combined with a GKE cluster. I used GKE for simplicity, but you could do this with any Kubernetes cluster.

Following is instructions that you should be able to follow in order to step through the process and see for yourself how it works. I will use a GCP instance for the legacy MySQL server and a GKE cluster for the new one.

Create instance and GKE cluster in Google Cloud

This walkthrough assumes you have already installed gcloud, kubectl and helm and that you know their basic usage.

Create a compute instance called legacy-mysql:

$ gcloud compute instances create legacy-mysql
Created [https://www.googleapis.com/compute/v1/projects/pgtm-pczarkowski/zones/us-central1-c/instances/legacy-mysql].
NAME          ZONE           MACHINE_TYPE   PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP    STATUS
legacy-mysql  us-central1-c  n1-standard-1               10.128.0.2   35.188.97.245  RUNNING

Create a GKE cluster called mysql-demo:

$ gcloud container clusters create mysql-demo
Creating cluster mysql-demo...done.                                            
NAME        LOCATION       MASTER_VERSION  MASTER_IP     MACHINE_TYPE   NODE_VERSION  NUM_NODES  STATUS
mysql-demo  us-central1-c  1.8.8-gke.0     35.226.24.75  n1-standard-1  1.8.8-gke.0   3          RUNNING
$ gcloud container clusters get-credentials mysql-demo
Fetching cluster endpoint and auth data.
kubeconfig entry generated for mysql-demo.
$ kubectl get nodes
NAME                                        STATUS    ROLES     AGE       VERSION
gke-mysql-demo-default-pool-23f42837-0zkv   Ready     <none>    1m        v1.8.8-gke.0
gke-mysql-demo-default-pool-23f42837-f28b   Ready     <none>    1m        v1.8.8-gke.0
gke-mysql-demo-default-pool-23f42837-jhkp   Ready     <none>    1m        v1.8.8-gke.0

Set up Legacy MySQL server

SSH into new instance and install MySQL:

$ gcloud compute ssh legacy-mysql
username@legacy-mysql:~$ sudo su -
root@legacy-mysql:~# apt-get update && apt-get -y install mysql-server
...
...

Harden MySQL by running mysql_secure_installation (I accepted all the defaults except for leaving root password blank just to save typing):

root@legacy-mysql:~# mysql_secure_installation
...
...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Install and use sysbench to create a bunch of tables with data in them:

root@legacy-mysql:~# mysqladmin create data1
root@legacy-mysql:~# sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=data1 --mysql-user=root prepare 
sysbench 0.4.12:  multi-threaded system evaluation benchmarkCreating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
root@legacy-mysql:~#

Create a replication user:

root@legacy-mysql:~# mysql -e "GRANT LOCK TABLES, SELECT,RELOAD,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO \"repl\"@\"%\" IDENTIFIED BY \"replpass\";"

Note: ordinarily you’d use a stronger password and set an IP address (or range) instead of % but since this is running in gcloud with default firewalls restricting access this is safe enough for the sake of the demo.

Finally we need to set this database up to be able to act as a Leader.

root@legacy-mysql:~# cat <<EOF > /etc/mysql/conf.d/leader.cnf
> [mysqld]
> server-id=1
> binlog-format=mixed
> log-bin=mysql-bin
> datadir=/var/lib/mysql
> innodb_flush_log_at_trx_commit=1
> sync_binlog=1
> EOF

Often MySQL/MariaDB defaults to bind only to localhost, run the following command to comment that out:

$ sed -i 's/^bind-address/#bind-address/' \
    /etc/mysql/mariadb.conf.d/50-server.cnf

Restart MySQL for the changes to take effect:

$ systemctl restart mariadb

Deploy MySQL to Kubernetes

We’re going to use the MySQL Helm chart to deploy MySQL on kubernetes.

Create a file called values.yaml on your local workstation that we’ll use to configure the helm chart:

$ cat <<EOF > values.yaml
persistence:
  enabled: true
  size: 20GiconfigurationFiles:
  mysql.cnf: |
    [mysqld]
    server-id=2
    binlog-format=mixed
    log-bin=mysql-bin
    innodb_flush_log_at_trx_commit=1S
    sync_binlog=1
    relay-log=mysql-relay-bin
    log-slave-updates=1
EOF

Next install Tiller on your cluster:

$ kubectl -n kube-system create serviceaccount tiller
serviceaccount "tiller" created$ kubectl create clusterrolebinding tiller --clusterrole cluster-admin --serviceaccount=kube-system:tiller
clusterrolebinding "tiller" created$ helm init --service-account=tiller
Tiller (the Helm server-side component) has been installed into your Kubernetes Cluster.
Happy Helming!

After a few minutes Tiller should be installed and ready and will respond to a version request:

$ helm version
Client: &version.Version{SemVer:"v2.8.1", GitCommit:"6af75a8fd72e2aa18a2b278cfe5c7a1c5feca7f2", GitTreeState:"clean"}
Server: &version.Version{SemVer:"v2.8.1", GitCommit:"6af75a8fd72e2aa18a2b278cfe5c7a1c5feca7f2", GitTreeState:"clean"}

Install MySQL via Helm:

$ helm install --namespace mysql-demo -n demo stable/mysql -f values.yaml 
NAME:   demo
LAST DEPLOYED: Fri Mar 30 14:49:44 2018
NAMESPACE: mysql-demo
STATUS: DEPLOYED
...
...
$

After a few minutes MySQL should be online. Export a few environment variables to make it easier to work with:

$ export MYSQL_PASS=$(kubectl get secret  --namespace mysql-demo demo-mysql -o jsonpath="{.data.mysql-root-password}" | base64 --decode; echo) $ export MYSQL_POD=$(kubectl get pod -n mysql-demo --selector=app=demo-mysql -o jsonpath='{.items..metadata.name}')$ export MYSQL_LEGACY=$(gcloud compute instances list --filter="name=('legacy-mysql')" --format 'value(networkInterfaces[0].networkIP)')

Check that MySQL is working:

$ kubectl exec -ti -n mysql-demo $MYSQL_POD -- mysql -p$MYSQL_PASS -e "select now();"
+---------------------+
| now()               |
+---------------------+
| 2018-03-30 19:58:41 |
+---------------------+

Set up replication

Next we need to create a firewall rule to allow pods running on the GKE cluster to access the legacy MySQL server. You could YOLO it by allowing anything to access it on 3306, but we’ll try and be a bit smarter than that. Google Cloud lets you firewall based on tags.

Create a tag for the legacy mysql server:

$ gcloud compute instances add-tags legacy-mysql --tags=legacy-mysql

Create a firewall rule to allow 3306 to that tag from the local network ( usually 10.0.0.0/8 ).

$ gcloud compute firewall-rules create legacy-mysql-replication \                                         
    --action allow \
    --direction ingress \
    --rules tcp:3306 \
    --source-ranges 10.0.0.0/8 \
    --priority 100 \
    --target-tags legacy-mysql

This next command runs amysqldump of the legacy mysql server on the new kubernetes based mysql server and loads the resultant data:

$ kubectl exec -n mysql-demo -ti $MYSQL_POD -- sh -c \
  "mysqldump --host=$MYSQL_LEGACY --user=repl --password=replpass \
  --skip-lock-tables --single-transaction --flush-logs --hex-blob \
  --master-data=2 -A | tee /tmp/dump.sql | mysql -p$MYSQL_PASS"

Set the new server to be read-only to ensure it is only updated by the leader:

$ kubectl exec -n mysql-demo -ti $MYSQL_POD -- \
  mysql -p$MYSQL_PASS -e "SET GLOBAL read_only = ON;"

Next we need to get the master log file and position from the mysqldump from earlier:

$ kubectl exec -n mysql-demo -ti $MYSQL_POD -- \
  head /tmp/dump.sql -n80 | grep "MASTER_LOG_POS"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=366;

Use these values to enable replication:

$ kubectl exec -n mysql-demo -ti $MYSQL_POD -- \
  mysql -p$MYSQL_PASS -e "
  CHANGE MASTER TO
    MASTER_HOST='$MYSQL_LEGACY',
    MASTER_USER='repl',
    MASTER_PASSWORD='replpass',
    MASTER_LOG_FILE='<master-log-file-from-above>',
    MASTER_LOG_POS=<master-log-pos-from-above>;
    START SLAVE;"

Wait a few minutes then check the replication status:

$ kubectl exec -n mysql-demo -ti $MYSQL_POD -- \
  mysql -p$MYSQL_PASS -e "SHOW SLAVE STATUS \G";

If everything looks okay we can create more data on the leader and see if it replicates.

Create some more data on the legacy server:

root@legacy-mysql:~# mysqladmin create data2
root@legacy-mysql:~# sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=data2 --mysql-user=root prepare 
sysbench 0.4.12:  multi-threaded system evaluation benchmarkCreating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...
root@legacy-mysql:~#

We should see the new table with 1000000 records in it on the new server:

$ kubectl exec -n mysql-demo -ti $MYSQL_POD -- \
  mysql -p$MYSQL_PASS data2 -e "select count(*) from sbtest;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+

Final Cut-over

Once you’re ready to cutover your application you’ll want to do several things in quick succession. The order you run them in may depend on your particular situation.

Set the original MySQL server to Read Only:

$ root@legacy-mysql:~# mysql -e "SET GLOBAL read_only = ON;"

Disable replication and set the new MySQL server to be writable:

$ kubectl exec -n mysql-demo -ti $MYSQL_POD -- \
  mysql -p$MYSQL_PASS -e "STOP SLAVE; SET GLOBAL read_only = OFF;"

Stop MySQL on the original server:

root@legacy-mysql:~# systemctl stop mariadb

Set up MySQL on the original server to only listen on localhost (just in case you need the data, or if somebody comes along and starts it again):

$ sed -i 's/^#bind-address.*$/bind-address = 127.0.0.1/' \
    /etc/mysql/mariadb.conf.d/50-server.cnf

Update your application to point to the new MySQL server, or update your load balancer or DNS to point to your application if you migrated it to Kubernetes at the same time.

Conclusion

With Persistent Volumes, StatefulSets and well written Helm Charts its possible to consider running databases and other stateful application in Kubernetes, and the lessons we learned running these application can be applied to running them in Kubernetes with some tweaks and changes in how we do things.

I don’t believe it would be too difficult to update the helm chart itself, or have some extra Kubernetes Jobs to help perform the migration steps and eliminate the need for a lot of the manual work (kubectl exec is kind of an antipattern).

Next steps would be to figure out how to also incorporate the mysqlha or percona-xtradb-cluster Helm Charts to host multiple replicating MySQL servers in Kubernetes itself.

Kubernetes
MySQL
Monolith
Data Migration
DevOps

--

--

Paul Czarkowski

Written by Paul Czarkowski

187 Followers

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams