# MySQL master-slave replication based on dockerfile

**Repository Path**: zonkidd/mysql-master-slave-based-on-dockerfile

## Basic Information

- **Project Name**: MySQL master-slave replication based on dockerfile
- **Description**: 在一台主机上基于dockerfile搭建mysql主从复制
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2023-05-20
- **Last Updated**: 2024-04-02

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

实验环境：

仅有一台机器：pjj-18

IP：192.168.115.80



主Mysql的配置：

1、创建/mysql文件夹

```shell
mkdir /mysql
```





2、进入/mysql文件夹

```shell
cd /mysql
```





3、创建dockerfile

\# vim dockerfile

```shell
From mysql:5.7.26
LABEL author="tigerfive"
LABEL co-author="zonkidd"

ENV MYSQL_ALLOW_EMPTY_PASSWORD yes
COPY conf.sh /mysql/conf.sh
COPY privileges.sql /mysql/privileges.sql

CMD ["sh", "/mysql/conf.sh"]
```



4、创建conf.sh

\# vim conf.sh

```shell
#!/bin/bash

set -e

echo '1. set server_id....'
sed -i '/\[mysqld\]/a server-id=1\nlog-bin=/var/log/mysql/mysql-bin\ngtid-mode=ON\nenforce-gtid-consistency=ON' /etc/mysql/mysql.conf.d/mysqld.cnf

echo '2. start mysql...'
service mysql start

echo '3. setting password...'
sed -i 's/MYSQLROOTPASSWORD/'$MYSQL_ROOT_PASSWORD'/' /mysql/privileges.sql
sed -i 's/MYSQLREPLICATIONUSER/'$MYSQL_REPLICATION_USER'/' /mysql/privileges.sql
sed -i 's/MYSQLREPLICATIONPASSWORD/'$MYSQL_REPLICATION_PASSWORD'/' /mysql/privileges.sql
mysql < /mysql/privileges.sql


echo '4. service mysql status'
echo 'mysql for tigerfive if ready...'

tail -f /dev/null
```

5、创建privileges.sql

\# vim privileges.sql

```sql
use mysql;
set password for root@'localhost' = password("1234567");
grant all on *.* to "repl"@'%' identified by "1234567" with grant option;
flush privileges;
```



6、构建mysql5.7.26镜像

```shell
# docker build -t zonkidd/mysql5.7.26-master:v1
```





7、测试容器配置是否成功

```shell
# docker run -p 3306:3306 --name zonkiddsql -e MYSQL_ROOT_PASSWORD="1234567" -e MYSQL_REPLICATION_USER="repl"  -e MYSQL_REPLICATION_PASSWORD="1234567"  -d zonkidd/mysql5.7.26-master:v1
```



8、容器登录测试

```shell
# docker exec -it zonkiddsql bash
root@756ef34048f5:/# mysql -uroot -p1234567
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>
```



从Mysql的配置：

1、创建/mysql2文件夹

```
mkdir /mysql2
```





2、进入/mysql2文件夹

```
cd /mysql2
```





3、创建dockerfile

\# vim dockerfile

```shell
From mysql:5.7.26
LABEL author="tigerfive"
LABEL co-author="zonkidd"

ENV MYSQL_ALLOW_EMPTY_PASSWORD yes
COPY conf.sh /mysql2/conf.sh
COPY privileges.sql /mysql2/privileges.sql

CMD ["sh", "/mysql2/conf.sh"]
```



4、创建conf.sh

\# vim conf.sh

```shell
#!/bin/bash

set -e

echo '1. set server_id...'
RAND="$(date +%s | rev | cut -c 1-2)$(echo ${RANDOM})"
sed -i '/\[mysqld\]/a server-id='$RAND'\nlog-bin=/var/log/mysql/mysql-bin\ngtid-mode=ON\nenforce-gtid-consistency=ON' /etc/mysql/mysql.conf.d/mysqld.cnf


echo '2. start mysql...'
service mysql start

echo '3. setting password...'
sed -i 's/MYSQLROOTPASSWORD/'$MYSQL_ROOT_PASSWORD'/' /mysql2/privileges.sql
sed -i 's/MYSQLMASTERSERVICEHOST/'$MYSQL_MASTER_SERVICE_HOST'/' /mysql2/privileges.sql
sed -i 's/MYSQLREPLICATIONUSER/'$MYSQL_REPLICATION_USER'/' /mysql2/privileges.sql
sed -i 's/MYSQLREPLICATIONPASSWORD/'$MYSQL_REPLICATION_PASSWORD'/' /mysql2/privileges.sql


mysql < /mysql2/privileges.sql

echo '4. service mysql status'
echo 'mysql for tigerfive if ready...'

tail -f /dev/null
```



5、创建privileges.sql

\# vim privileges.sql

```sql
use mysql;
set password for root@'localhost' = password('1234567');
flush privileges;
CHANGE MASTER TO master_host='192.168.115.80', master_user='repl', master_password='1234567' ;
START SLAVE;
```



6、构建mysql5.7.26镜像

```shell
# docker build -t zonkidd/mysql5.7.26-slave:v1
```





7、容器创建，测试容器配置是否成功

```shell
# docker run -p 3307:3306  --name zonkiddsql2 -e MYSQL_ROOT_PASSWORD="1234567" -e MYSQL_MASTER_SERVICE_HOST="172.17.0.4" -e MYSQL_REPLICATION_USER='repl' -e MYSQL_REPLICATION_PASSWORD="1234567"   -d zonkidd/mysql5.7.26-slave:v1

# docker exec -it zonkiddsql2 bash
root@c2feb4582891:/# mysql -uroot -p1234567

```

若想创建一主多从架构，则更改映射的端口和把容器名称更改





8、检测主从同步链接情况

```mysql
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.115.80
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 852
               Relay_Log_File: c2feb4582891-relay-bin.000004
                Relay_Log_Pos: 1065
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
```



9、测试主从同步

主Mysql：

```mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
```



Slave1：

```mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
```



Slave2:

```mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
```



成功!