Mysql Slave Config With Docker

Server environment

1
2
3
4
5
6
7
8
9
10
11
12
$ cat /etc/issue
Ubuntu 14.04.5 LTS

$ docker -v
Docker version 1.6.2, build 7c8fca2

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.12-log |
+------------+

Get mysql basic docker

1
$ docker pull mysql

Mkdir the config file directory

1
2
3
$ mkdir -p ./config/mysql/master/1
$ mkdir -p ./config/mysql/slave/11
$ mkdir -p ./config/mysql/slave/12

The directory tree:

1
2
3
4
5
6
7
config/
└── mysql
├── master
│   └── 1
└── slave
├── 11
└── 12

New the config files

master 1

1
$ vim config/mysql/master/1/master.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[mysqld]
server_id = 1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-storage-engine=INNODB

#Optimize omit

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = /var/lib/mysql/binlog
log_bin_trust_function_creators=1
binlog_format = ROW
expire_logs_days = 99
sync_binlog = 0

slow-query-log=1
slow-query-log-file=/var/log/mysql/slow-queries.log
long_query_time = 3
log-queries-not-using-indexes
binlog-row-image=full

slave 11

1
$ vim config/mysql/slave/11/slave.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[mysqld]
server_id = 11

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-storage-engine=INNODB

#Optimize omit

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = /var/lib/mysql/binlog
log_bin_trust_function_creators=1
binlog_format = ROW
expire_logs_days = 99
sync_binlog = 0

relay_log=slave-relay-bin
log-slave-updates=1
slave-skip-errors=all

slow-query-log=1
slow-query-log-file=/var/log/mysql/slow-queries.log
long_query_time = 3
log-queries-not-using-indexes
binlog-row-image=full

slave 12

1
$ vim config/mysql/slave/12/slave.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[mysqld]
server_id = 12

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-storage-engine=INNODB

#Optimize omit

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = /var/lib/mysql/binlog
log_bin_trust_function_creators=1
binlog_format = ROW
expire_logs_days = 99
sync_binlog = 0

relay_log=slave-relay-bin
log-slave-updates=1
slave-skip-errors=all

slow-query-log=1
slow-query-log-file=/var/log/mysql/slow-queries.log
long_query_time = 3
log-queries-not-using-indexes
binlog-row-image=full

Now there will be master-1, slave1-1,slave1-2 three mysql config files

1
2
3
4
5
6
7
8
9
10
config
└── mysql
├── master
│   └── 1
│   └── master.cnf
└── slave
├── 11
│   └── slave.cnf
└── 12
└── slave.cnf

Build the mysql containers

1
2
3
$ sudo docker run -v $PWD/config/mysql/master/1:/etc/mysql/conf.d -v $PWD/data/mysql/master/1:/var/lib/mysql -v $PWD/log/mysql/master/1:/var/log/mysql -e MYSQL_ROOT_PASSWORD=mysqlpassword -d -p 4306:3306 --name=mysql-master-1 mysql
$ sudo docker run -v $PWD/config/mysql/slave/11:/etc/mysql/conf.d -v $PWD/data/mysql/slave/11:/var/lib/mysql -v $PWD/log/mysql/slave/11:/var/log/mysql -e MYSQL_ROOT_PASSWORD=mysqlpassword -d -p 4307:3306 --name=mysql-slave-11 --link mysql-master-1:master mysql
$ sudo docker run -v $PWD/config/mysql/slave/12:/etc/mysql/conf.d -v $PWD/data/mysql/slave/12:/var/lib/mysql -v $PWD/log/mysql/slave/12:/var/log/mysql -e MYSQL_ROOT_PASSWORD=mysqlpassword -d -p 4308:3306 --name=mysql-slave-12 --link mysql-master-1:master mysql
1
$ sudo docker ps
1
2
3
4
CONTAINER ID        IMAGE               COMMAND                CREATED             STATUS              PORTS                    NAMES
9716b7e177ad mysql:latest "docker-entrypoint.s 34 minutes ago Up 34 minutes 0.0.0.0:4308->3306/tcp mysql-slave-12
b731f3b7a8d0 mysql:latest "docker-entrypoint.s 34 minutes ago Up 34 minutes 0.0.0.0:4307->3306/tcp mysql-slave-11
6a3cbc3f50d4 mysql:latest "docker-entrypoint.s 34 minutes ago Up 34 minutes 0.0.0.0:4306->3306/tcp mysql-master-1

Entry the master database

1
$ sudo docker exec -it mysql-master-1 mysql -p

Then check the master status

1
mysql> show master status\G;

1
2
3
4
5
6
7
*************************** 1. row ***************************
File: binlog.000004
Position: 743
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Remember the binlog file name and position. Here the file is binlog.000004 and 743

Create master user

1
2
3
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;

username is ‘repl’ and password is ‘repl’

Entry the slave Mysql and config them

1
$ sudo docker exec -it mysql-slave-1 mysql -p
1
2
3
4
5
6
7
8
mysql> CHANGE MASTER TO \
-> MASTER_HOST='master',\
-> MASTER_PORT=3306,\
-> MASTER_USER='repl',\
-> MASTER_PASSWORD='repl',\
-> MASTER_LOG_FILE='binlog.000004',\
-> MASTER_LOG_POS=743;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
1
2
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
  • MASTER_HOST=’master’ // ‘master’ is the docker link alias

  • MASTER_LOG_FILE=’binlog.000004’ and MASTER_LOG_POS=743; //come from master status

Check Slave status

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 743
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 473
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

Ok slave mysql has connected to the master database.
You can change something in master then check the changes on slave machine.

Set Slave Readonly

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show global variables like "%read_only%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.06 sec)

mysql> show global variables like "%read_only%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)

Set Slave Read-Write

1
2
mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

Lock All Tables During Sync Data

To ensure that all user, include super user, can not write, look the table read only,

1
2
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

1
2
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
Share