运维公开课管理 - docker版实现MySQL主从架构 - 《运维专题笔记》

测试版
- 创建MySQL容器
实用版
问题：删除容器后，MySQL库还在吗
实现MySQL主从架构-docker版
读写分离
MySQL架构

测试版

创建MySQL容器

1. 下载MySQL镜像

[root@k8s-master ~]# docker search mysql --limit 3
NAME      DESCRIPTION                                     STARS     OFFICIAL   AUTOMATED
mysql     MySQL is a widely used, open-source relation…   12286     [OK]       
mariadb   MariaDB Server is a high performing open sou…   4726      [OK]       
percona   Percona Server is a fork of the MySQL relati…   572       [OK]       
[root@k8s-master ~]# docker pull mysql:5.7
5.7: Pulling from library/mysql
72a69066d2fe: Pull complete 
93619dbc5b36: Pull complete 
Digest: sha256:f2ad209efe9c67104167fc609cca6973c8422939491c9345270175a300419f94
Status: Downloaded newer image for mysql:5.7
docker.io/library/mysql:5.7

2. 运行MySQL容器

[root@k8s-master ~]# docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
9d00ca3b344832a1c648da874fbcb531cd0ef1a4cc75aa8940d532d1d3933fc7
[root@k8s-master ~]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
9d00ca3b3448   mysql:5.7   "docker-entrypoint.s…"   4 seconds ago   Up 2 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   boring_haslett

3. 登录MySQL容器，并创建测试数据

[root@k8s-master ~]# docker exec -it 9d00ca3 /bin/bash
root@9d00ca3b3448:/# mysql -p
Enter password: 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)
### 创建数据库及表
mysql> use db01;
Database changed
mysql> create table zmedu(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
### 插入数据
mysql> insert into zmedu values(1,'itlaoxin');
Query OK, 1 row affected (0.05 sec)
mysql> select * from zmedu;
+------+----------+
| id   | name     |
+------+----------+
|    1 | itlaoxin |
+------+----------+
1 row in set (0.00 sec)
mysql>

测试远程链接

[root@k8s-master ~]# mysql -uroot -p123456 -h 192.168.3.71
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.36 MySQL Community Server (GPL)
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> use db01
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [db01]> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| zmedu          |
+----------------+
1 row in set (0.00 sec)
MySQL [db01]>

4. 注意事项：

注意事项：默认情况下，docker中字符集都是latin1 插入中文容易报错

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

插入中文一定会报错

MySQL [db01]> insert into zmedu values(2,'老辛');
ERROR 1366 (HY000): Incorrect string value: '\xE8\x80\x81\xE8\xBE\x9B' for column 'name' at row 1
MySQL [db01]>

实用版

创建实例

[root@k8s-master ~]# docker pull mysql:5.7
5.7: Pulling from library/mysql
72a69066d2fe: Pull complete 
Digest: sha256:f2ad209efe9c67104167fc609cca6973c8422939491c9345270175a300419f94
Status: Downloaded newer image for mysql:5.7
docker.io/library/mysql:5.7
[root@k8s-master ~]# docker run -d -p 3306:3306 --privileged=true -v /zmedu/mysql/log:/var/log/mysql -v /zmedu/mysql/data:/var/lib/mysql -v /zmedu/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456  --name mysql mysql:5.7
ce47c9c630ffead3d398a56c40f394960c6d778be967f7aad4f508c035325352

修改配置文件

新建my.cnf

[root@k8s-master ~]# cat !$
cat /zmedu/mysql/conf/my.cnf
[client]
default_character_set=utf8
[mysqld]
collation_server =utf8_general_ci
character_set_server = utf8
[root@k8s-master ~]#

重新启动容器并进入查看字符集

[root@k8s-master ~]# docker restart mysql
mysql
[root@k8s-master ~]# docker exec -it mysql bash
root@ce47c9c630ff:/# mysql -uroot -p123456
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 2
Server version: 5.7.36 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql>

测试是否可以插入中文

[root@k8s-master ~]# mysql -uroot -p123456 -h 192.168.3.71
[root@k8s-master ~]# create databese db01;
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [db01]> insert into zmedu values(2,'老辛');
Query OK, 1 row affected (0.00 sec)
MySQL [db01]> select *from zmedu;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 老辛   |
+------+--------+
1 row in set (0.00 sec)

可以看到已经可以插入中文了

建议：创建好数据库后，一定要先设置字符集，再去创建库和表

问题：删除容器后，MySQL库还在吗

实现方法：把MySQL容器删掉，查看数据是否还在？

[root@k8s-master mysql]# docker rm -f mysql
mysql
[root@k8s-master mysql]# docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
[root@k8s-master mysql]# docker run -d -p 3306:3306 --privileged=true -v /zmedu/mysql/log:/var/log/mysql -v /zmedu/mysql/data:/var/lib/mysql -v /zmedu/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456  --name mysql mysql:5.7
193de1aa288ee23b2b5648d3be5c99726945a8115e5b84c604b83a23a7dffdff
[root@k8s-master mysql]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
193de1aa288e   mysql:5.7   "docker-entrypoint.s…"   15 seconds ago   Up 14 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql
[root@k8s-master mysql]# docker exec -it 193de1 bash
root@193de1aa288e:/# mysql -p123456
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 2
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db01               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from zmedu;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 老辛   |
+------+--------+
1 row in set (0.01 sec)

可以看到，即使文件被删除了，重建后，依然存在，说明数据卷的重要性

实现MySQL主从架构-docker版

1. 下载MySQL镜像

[root@k8s-master ~]# docker pull mysql:5.7
5.7: Pulling from library/mysql
72a69066d2fe: Pull complete 
93619dbc5b36: Pull complete 
70deed891d42: Pull complete 
Digest: sha256:f2ad209efe9c67104167fc609cca6973c8422939491c9345270175a300419f94
Status: Downloaded newer image for mysql:5.7
docker.io/library/mysql:5.7

2. 创建MySQL-master并创建配置文件

[root@k8s-master ~]# docker run -p 3307:3306 --name mysql-master \
> -v /mydata/mysql-master/log:/var/log/mysql \
> -v /mydata/mysql-master/data:/var/lib/mysql \
> -v /mydata/mysql-master/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=root \
> -d mysql:5.7
cc88521bb0baea9f75c86fefb41dfa44cb3c4398f7e11885c9b9d873be6b93f9
[root@k8s-master ~]# cd /mydata/mysql-master/conf/
[root@k8s-master conf]# vim my.cnf
[root@k8s-master conf]# grep -v ^# my.cnf 
[mysqld]
server_id=101 
binlog-ignore-db=mysql  
log-bin=mall-mysql-bin  
binlog_cache_size=1M  
binlog_format=mixed  
expire_logs_days=7  
slave_skip_errors=1062

关于配置文件的注释：

[root@k8s-master conf]# cat my.cnf 
[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=101 
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能
log-bin=mall-mysql-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

3. 重启MySQL并配置需要同步的数据和用户

重启MySQL

[root@k8s-master conf]# docker restart mysql-master
mysql-master
[root@k8s-master conf]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
cc88521bb0ba   mysql:5.7   "docker-entrypoint.s…"   4 minutes ago   Up 3 seconds   33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   mysql-master
[root@k8s-master conf]#

创建需要同步的数据及数据库

[root@k8s-master conf]# docker exec -it mysql-master /bin/bash
root@cc88521bb0ba:/# mysql -proot
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 2
Server version: 5.7.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

4. 新建从服务器

a/mysql-slave/log:/var/log/mysql \
> -v /mydata/mysql-slave/data:/var/lib/mysql \
> -v /mydata/mysql-slave/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=root  \
> -d mysql:5.7
15514af9443fce00c9c6f3cc182ad210440a0052e54123a9516503d23a6bc7bb
[root@k8s-master conf]# cd /mydata/mysql-slave/conf/
[root@k8s-master conf]# vim my.cnf
[root@k8s-master conf]# grep -v ^# my.cnf 
[mysqld]
server_id=102
binlog-ignore-db=mysql  
log-bin=mall-mysql-slave1-bin  
binlog_cache_size=1M  
binlog_format=mixed  
expire_logs_days=7  
slave_skip_errors=1062  
relay_log=mall-mysql-relay-bin  
log_slave_updates=1  
read_only=1

配置文件解析：

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=102
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062  
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin  
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1  
## slave设置为只读（具有super权限的用户除外）
read_only=1

5. 重启从服务器

[root@k8s-master conf]# docker restart mysql-slave
mysql-slave

6. 查看同步状态

在主服务器查看同步状态

[root@k8s-master conf]# docker exec -it mysql-master bash
root@cc88521bb0ba:/# mysql -proot
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.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mall-mysql-bin.000001 |      617 |              | mysql            |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

进入从服务器配置主从同步

[root@k8s-master conf]# docker exec -it mysql-slave /bin/bash
root@15514af9443f:/# mysql -proot
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 2
Server version: 5.7.36-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.3.71', master_user='slave', master_password='123456', master_port=3307, master_log_file='mall-mysql-bin.000001', master_log_pos=617, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>

查看主从状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.3.71
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 617
               Relay_Log_File: mall-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mall-mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

7. 主从同步测试

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.3.71
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 617
               Relay_Log_File: mall-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mall-mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

开启主从同步

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.3.71
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 617
               Relay_Log_File: mall-mysql-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Master_Log_File: mall-mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

插入数据测试：

在主服务上插入数据，在从服务器上查看

主服务器上创建数据

mysql> create database db02;
Query OK, 1 row affected (0.00 sec)
mysql> use db02
Database changed
mysql> create table zmedu(id int,name varchar(28));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into zmedu values(1,'zhang');
Query OK, 1 row affected (0.02 sec)
mysql> select * from zmedu;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
+------+-------+
1 row in set (0.00 sec)
mysql>

从服务器查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db02               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db02
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from zmedu;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
+------+-------+
1 row in set (0.00 sec)
mysql>

docker版实现MySQL主从架构

测试版

创建MySQL容器

1. 下载MySQL镜像

2. 运行MySQL容器

3. 登录MySQL容器，并创建测试数据

4. 注意事项：

实用版

创建实例

修改配置文件

重新启动容器并进入查看字符集

测试是否可以插入中文

问题： 删除容器后，MySQL库还在吗

实现MySQL主从架构-docker版

1. 下载MySQL镜像

2. 创建MySQL-master并创建配置文件

3. 重启MySQL并配置需要同步的数据和用户

4. 新建从服务器

5. 重启从服务器

6. 查看同步状态

7. 主从同步测试

读写分离

MySQL架构

问题：删除容器后，MySQL库还在吗