笔记 Docker 安装 Mysql (四) （2）Docker版Mysq主从复制

1.运行master容器

docker run -d -p 3307:3306 --privileged=true -v /data/mysql-master/log:/var/log/mysql -v /data/mysql-master/data:/var/lib/mysql -v /data/mysql-master/conf:/etc/mysql/ --name mysql-master -e MYSQL_ROOT_PASSWORD=root mysql:5.7.40-debian

1.1 [/data/mysql-master/conf] 目录下新建my.cnf配置文件

[client] default_character_set=utf8 [mysqld] collation_server=utf8_general_ci character_set_server=utf8

##设置server_id, 同一局域网中需要唯一 server_id=101

##指定不需要同步的数据库名称 binlog-ignore-db=mysql

##开启二进制日志功能 log-bin=wbz-mysql-bin

##设置二进制日志使用内存大小(事务) binlog_cache_size=1M

##设置使用的二进制日志格式(mixed,statement,row) binlog_format=mixed

##二进制日志国企清理时间。默认值为0，表示不自动清理 expire_logs_days=7

##跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。 ##如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致 slave_skip_errors=1062

2重启master实例

docker restart mysql-master

进入mysql-master容器，并确认mysql-master容器中mysql是否可用

[root@hadoop104 conf]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 70107d91200d mysql:5.7.40-debian "docker-entrypoint.s…" 18 minutes ago Up 2 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master 3e3dbc6c45f1 redis:6.0.8 "docker-entrypoint.s…" About an hour ago Up About an hour 0.0.0.0:6379->6379/tcp, :::6379->6379/tcp redis1 [root@hadoop104 conf]# docker exec -it mysql-master /bin/bash root@70107d91200d:/# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.40-log MySQL Community Server (GPL)

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>

1.3.master容器实例内创建数据同步用户

CREATE USER 'slave'@'%' IDENTIFIED BY '123';

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO 'slave'@'%';

2.运行slave容器

docker run -d -p 3308:3306 --privileged=true -v /data/mysql-slave/log:/var/log/mysql -v /data/mysql-slave/data:/var/lib/mysql -v /data/mysql-slave/conf:/etc/mysql/ --name mysql-slave -e MYSQL_ROOT_PASSWORD=root mysql:5.7.40-debian

2.1 进入【/data/mysql-slave/conf】目录下新建my.cnf配置文件

[client] default_character_set=utf8 [mysqld] collation_server=utf8_general_ci character_set_server=utf8

##设置server_id, 同一局域网中需要唯一 server_id=102

##指定不需要同步的数据库名称 binlog-ignore-db=mysql

##开启二进制日志功能 log-bin=wbz-mysql-slave1-bin

##设置二进制日志使用内存大小(事务) binlog_cache_size=1M

##设置使用的二进制日志格式(mixed,statement,row) binlog_format=mixed

##二进制日志国企清理时间。默认值为0，表示不自动清理 expire_logs_days=7

##relay_log配置中继日志

relay_log=wbz-mysql-relay-bin

##log_slave_updates表示slave将复制事件写进自己的二进制日志

log_slave_updates=1

##slave设置为只读（具有super权限的用户除外）

read_only=1

2.2 重启slave容器

docker restart mysql-slave

3. 在主数据库中查看主从同步状态

show master status;

[root@hadoop104 conf]# docker exec -it mysql-master /bin/bash root@70107d91200d:/# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.40-log MySQL Community Server (GPL)

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>

4.在从数据库中配置主从复制

4.1 进入slave容器

docker exec -it mysql-slave /bin/bash

4.2 进入slave容器中的mysql数据库

mysql -uroot -p

4.3配置主从复制

change master to master_host='192.168.1.200', master_user='slave',master_password='123', master_port=3307,master_log_file='wbz-mysql-bin.000001', master_log_pos=617, master_connect_retry=30;

以上参数来源于步骤【3.在主数据库中查看主从同步状态】

参数说明：

master_host：主数据库的IP地址

master_port：主数据库的运行端口

master_user：在主数据库创建的用户同步数据的用户账号

master_password：在主数据库创建的用于同步数据的用户密码

master_log_file：指定从数据库要复制数据的日志文件，通过查看主数据库的主从同步状态，获取File参数

master_log_pos：指定从数据库从那个位置开始复制数据，通过查看主数据的状态，获取Position参数；

master_connect_retry：链接失败重试时间间隔，单位：秒

5. 在从数据库中查看主从同步状态

show slave status \G;

********* 1. row ********* Slave_IO_State: Master_Host: 192.168.1.200 Master_User: slave Master_Port: 3307 Connect_Retry: 30 Master_Log_File: wbz-mysql-bin.000001 Read_Master_Log_Pos: 617 Relay_Log_File: wbz-mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: wbz-mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 617 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

6.在从数据库中开启主从同步

mysql> start slave; Query OK, 0 rows affected (0.01 sec)

在从数据库中查看主从同步状态

如下状态值，则代表链路开通，主从同步开启成功

Slave_IO_Running: Yes Slave_SQL_Running: Yes

mysql> show slave status \G; ********* 1. row ********* Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.200 Master_User: slave Master_Port: 3307 Connect_Retry: 30 Master_Log_File: wbz-mysql-bin.000001 Read_Master_Log_Pos: 617 Relay_Log_File: wbz-mysql-relay-bin.000002 Relay_Log_Pos: 324 Relay_Master_Log_File: wbz-mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 617 Relay_Log_Space: 535 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101 Master_UUID: 11130d61-841e-11ed-a8c1-0242ac110004 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

7 .主从复制测试

7.1 在主机新建库，并在库中见表后插入数据

mysql> create database db01; Query OK, 1 row affected (0.00 sec)

mysql> use db01; Database changed mysql> create table t1(id int, name varchar(10)); Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1, 'li4'); Query OK, 1 row affected (0.13 sec)

mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | li4 | +------+------+ 1 row in set (0.00 sec)

7.2 在从机中使用数据库并查看记录

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 t1; +------+------+ | id | name | +------+------+ | 1 | li4 | +------+------+ 1 row in set (0.00 sec)