Getting MySQL Setup and Replicating - ksgh/dkrb-slave-check GitHub Wiki

Assuming you have Docker installed

I'm not going to go through all that as there's enough information out there. I used terraform to create 3 CentOS nodes: node-00, node-01, and node-02. Selinux disabled because it can be a PITA, and be sure you modify any security group you use to allow:

  • TCP 2377
  • TCP & UDP 7946
  • UDP 4789 .... traffic between these nodes.

MySQL/Docker Setup

First, we're going to pull an official mysql image from: https://hub.docker.com/_/mysql

docker pull mysql:5.7.27

Set labels on our nodes so mysql services land on expected nodes because I'm using bind-mounts here.

On node-00

docker node update --label-add mysqlnode=mysql-00 node-00

On node-01

docker node update --label-add mysqlnode=mysql-01 node-01

On node-02

docker node update --label-add mysqlnode=mysql-02 node-02

Setup mysql.yml docker stack file

Write this file somewhere, then we'll explain what some of this means and why it's here.

version: '3.1'

networks:
  net-generic:
    ipam:
      config:
        - subnet: 10.9.0.0/24

services:
  mysql-00:
    image: mysql:5.7.27
    networks:
      net-generic:
        aliases:
          - mysql-00
          - mysql-write
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - /home/centos/mysql/data:/var/lib/mysql
      - /home/centos/mysql/mysql.cnf:/etc/mysql/conf.d/m-00.cnf
    environment:
      MYSQL_ROOT_PASSWORD: stuffandthings
    deploy:
      placement:
        constraints:
          - node.labels.mysqlnode == mysql-00

  mysql-01:
    image: mysql:5.7.27
    networks:
      net-generic:
        aliases:
          - mysql-01
          - mysql-read
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - /home/centos/mysql/data:/var/lib/mysql
      - /home/centos/mysql/mysql.cnf:/etc/mysql/conf.d/m-01.cnf
    environment:
      MYSQL_ROOT_PASSWORD: stuffandthings
    deploy:
      placement:
        constraints:
          - node.labels.mysqlnode == mysql-01

  mysql-02:
    image: mysql:5.7.27
    networks:
      net-generic:
        aliases:
          - mysql-02
          - mysql-read
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - /home/centos/mysql/data:/var/lib/mysql
      - /home/centos/mysql/mysql.cnf:/etc/mysql/conf.d/m-02.cnf
    environment:
      MYSQL_ROOT_PASSWORD: stuffandthings
    deploy:
      placement:
        constraints:
          - node.labels.mysqlnode == mysql-02

The network

I'm just specifying a network so things are consistent... and I'll have a range of predictable IPs available for services to fall into, which will matter when we grant perms to mysql users. We'll use this same network to launch our redis and slave-check services into later

networks:
  net-generic:
    ipam:
      config:
        - subnet: 10.9.0.0/24

Volumes

There's a couple ways you could go about this (using a data volume for instance). I just choose to use a bind-mount to a local directory on my machine. You will obviously have to replace /home/centos to something that exists in your environment.

    volumes:
      - /home/centos/mysql/data:/var/lib/mysql
      - /home/centos/mysql/mysql.cnf:/etc/mysql/conf.d/m-00.cnf

/home/centos/mysql/data:/var/lib/mysql is for mysql's data. /home/centos/mysql/mysql.cnf:/etc/mysql/conf.d/m-00.cnf is going to be required to enable bin logging on the master and we're going to specify the server-id to ensure we're not fighting that later. These (custom *.cnf files will be included via mysql's configuration that's already setup in the image. So create these files accordingly.

m-00.cnf (master)

[mysqld]
log-bin = mysql-bin
server-id = 100

m-01.cnf (secondary/slave)

[mysqld]
server-id = 200

m-02.cnf (secondary/slave)

[mysqld]
server-id = 300

MYSQL_ROOT_PASSWORD: stuffandthings - super secret password here. This will be the password given to root on initial creation of the containers/services. We're now ready to fire these up. (At this point in time no replication is established - we're simply lighting up 3 mysql containers)

docker stack deploy -c mysql.yml mysql

You should see something like the following:

Creating network mysql_net-generic
Creating service mysql_mysql-01
Creating service mysql_mysql-02
Creating service mysql_mysql-00

Now that we have these running, we need to create some users and setup replication. I'm using mysql-00 (mysql_mysql-00) as the master.

Create the replication user

docker exec <mysql-00 container id> mysql -u root -pstuffandthings -e \
    "CREATE USER IF NOT EXISTS 'repl'@'10.9.%' IDENTIFIED BY 'rep1f0rt3st'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.9.%';"

Create the user for the slave-check process

docker exec <mysql-00 container id> mysql -u root -pstuffandthings -e \
    "CREATE USER IF NOT EXISTS 'slave-check'@'10.9.%' IDENTIFIED BY 'fermyg00ber'; GRANT REPLICATION CLIENT ON *.* TO 'slave-check'@'10.9.%';"

Establish replication with the mysql-00 master

mysql-01

docker exec stacks_mysql-01_1 mysql -u root -pstuffandthings -e \
    "change master to master_host='mysql-00', master_user='repl', master_password='rep1f0rt3st', master_log_file='mysql-bin.000001', master_log_pos=4; start slave; select sleep(3) from dual; show slave status\G"

mysql-02

docker exec stacks_mysql-02_1 mysql -u root -pstuffandthings -e \
    "change master to master_host='mysql-00', master_user='repl', master_password='rep1f0rt3st', master_log_file='mysql-bin.000001', master_log_pos=4; start slave; select sleep(3) from dual; show slave status\G"

If all has gone as expected you should see expected "show slave status" output with mysql-00 as the master host.

Next

Bring up the Redis Service