MySQL Replication (Docker)

Getting Started

Get Image

root@veronica:/home/blazingraptor/docker# docker pull mysql:8.0

Make network

root@veronica:/home/blazingraptor/docker# docker network create --subnet=172.16.1.0/29 hamysqlnet
c9357827cc79e3aa6ec77fcc9aaebc34a0576215c1d24b64f976952dde9fbebb

6 ip's

Run MySQL in Docker
https://hub.docker.com/_/mysql/

Make MySQL Slave

root@veronica:/home/blazingraptor/docker# docker run --net hamysqlnet --ip 172.16.1.3 -d --name hamysqlslave -e MYSQL_ROOT_PASSWORD=potatonoodlesoup mysql:8.0
1072cdfc2d841ad8dc9e8a512cafc283aedba0013c79d0542452c89f920b3785

Make MySQL Master

root@veronica:/home/blazingraptor/docker# docker run --net hamysqlnet --ip 172.16.1.2 -d --name hamysqlmaster -e MYSQL_ROOT_PASSWORD=potatonoodlesoup mysql:8.0
23ea541e6f02949e9f79e65fddd6690c077672499880f716b7f67a1b9b2a7b5c

On Both

Get in container

root@veronica:/home/blazingraptor/docker# docker exec -it hamysqlmaster /bin/bash
root@23ea541e6f02:/#

Install VIM

root@23ea541e6f02:/# apt update
root@23ea541e6f02:/# apt install apt-file
root@23ea541e6f02:/# apt install vim nano less
apt update;apt install apt-file;apt install vim nano less

Save Password

root@23ea541e6f02:/# nano /root/.my.cnf
[client]
user=root
password=potatonoodlesoup

The first step is to find the section that looks like this, binding the server to the local host:

Master

root@23ea541e6f02:/# nano /etc/mysql/mysql.cnf
log-bin=black-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1

root@23ea541e6f02:/# nano /etc/mysql/my.cnf
server-id=1

Slave

root@35fda98578b8:/# nano /etc/mysql/mysql.cnf
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

root@35fda98578b8:/# nano /etc/mysql/my.cnf
server-id=2

Master

mysql> CREATE USER repl_user@172.16.1.3;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@172.16.1.3;
mysql> ALTER USER repl_user@172.16.1.3 IDENTIFIED WITH mysql_native_password BY 'bucketof';
mysql> ALTER USER repl_user@172.16.1.3 IDENTIFIED WITH mysql_native_password BY 'billy';

Slave

mysql> CHANGE MASTER TO MASTER_HOST='172.16.1.2',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='billy',
    -> MASTER_LOG_FILE='',
    -> MASTER_LOG_POS=4;

mysql> start slave;

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
(truncated output)
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

root@veronica:~# docker ps
CONTAINER ID   IMAGE              COMMAND                  CREATED          STATUS          PORTS                  NAMES
aa5f47c4c5fb   mysql:8.0          "docker-entrypoint.s…"   11 minutes ago   Up 11 minutes   3306/tcp, 33060/tcp    hamysqlmaster
35fda98578b8   mysql:8.0          "docker-entrypoint.s…"   11 minutes ago   Up 11 minutes   3306/tcp, 33060/tcp    hamysqlslave
f13110710bd4   analyze-real-app   "docker-entrypoint.s…"   6 days ago       Up 6 days       0.0.0.0:3000->80/tcp   analyze-real-app-app
root@veronica:~# docker restart aa5f47c4c5fb
aa5f47c4c5fb
root@veronica:~# docker restart 35fda98578b8
35fda98578b8

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
(truncated output)