Docker部署mysql双主双从
mysql多实例
概述
1.MySql多实例是指安装MySql之后，可以再一台linux服务器上同时启动多个MySql数据库（实例），不需要安装多个MySql；
2.如果是有多台Linux服务器，那么我们需要每台服务器分别安装；
3.在一台Linux服务器上启动多个MySql数据库（实例），通过为各个数据库实例配置独立的配置文件来实现，即每个数据库实例有自己单独的配置文件；
配置
1.在Mysql安装主目录下创建/data/3307,/data/3308,/data/3309,/data/3310,四个目录；
2.执行数据库初始化，在Mysql的/usr/local/mysql-5.7.18/bin目录下执行命令：
mysqld --initialize-insecure --basedir=/use/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3307 --user=mysql
mysqld --initialize-insecure --basedir=/use/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3308 --user=mysql
mysqld --initialize-insecure --basedir=/use/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3309 --user=mysql
mysqld --initialize-insecure --basedir=/use/local/mysql-5.7.18 --datadir=/usr/local/mysql-5.7.18/data/3310 --user=mysql
3.在/data/3307,/data/3308,/data/3309,/data/3310,四个目录下分别创建一个my.cnf文件；
4.配置四个Mysql数据库服务的my.cnf文件；
主从复制
原理
1.master主服务器上的数据发生改变时，则将其改变写入二进制日志文件中；
2.salve从服务器会在一定时间间隔内对master主服务器上的二进制日志进行探测，探测其是否发生过改变；
3.如果发生了改变，则开始一个I/O Thread请求master二进制事件；
4.同时master主服务器为每个I/O Thread启动一个dump Thread，用于向其发送二进制事件；
5.slave从服务器将接收到的二进制事件保存至自己本地的中继日志文件中；
6.salve从服务器将启动SQL Thread从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致；
7.最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒；
主从搭建
节点信息
| 主节点 | 端口 | 对应的从节点 | 端口 |
|---|---|---|---|
| master1 | 3306 | slave1 | 3307 |
| master2 | 3308 | slave2 | 3309 |
主节点配置文件
- master1
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 主服务器唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 设置需要复制的数据库(可设置多个)
binlog-do-db=test
# 设置logbin格式，默认STATEMENT记录sql语句，ROW：记录行中修改的列,MIXED:智能选择
binlog_format=MIXED
# 自增长字段从那个数开始
auto-increment-offset=1
# 自增长字段每次递增的量
auto-increment-increment=2
#调整时区
default-time_zone = '+8:00'
#设置最大连接数
max_connections=1000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# Custom config should go here
!includedir /etc/mysql/conf.d/
- master2
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 主服务器唯一ID
server-id=2
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 设置需要复制的数据库(可设置多个)
binlog-do-db=test
# 设置logbin格式，默认STATEMENT记录sql语句，ROW：记录行中修改的列,MIXED:智能选择
binlog_format=MIXED
# 自增长字段从那个数开始
auto-increment-offset=2
# 自增长字段每次递增的量
auto-increment-increment=2
#调整时区
default-time_zone = '+8:00'
#设置最大连接数
max_connections=1000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# Custom config should go here
!includedir /etc/mysql/conf.d/
注意点
binlog-do-db：指定mysql的binlog日志记录哪个db
Replicate_Do_DB：参数是在slave上配置，指定slave要复制哪个库
在master上设置binlog_do_db弊端：
1、过滤操作带来的负载都在master上
2、无法做基于时间点的复制（利用binlog）。
从节点配置文件
- slave1
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=3
##开启二进制日志功能，以备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
#调整时区
default-time_zone = '+8:00'
#设置最大连接数
max_connections=1000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# Custom config should go here
!includedir /etc/mysql/conf.d/
- slave2
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=4
##开启二进制日志功能，以备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
#调整时区
default-time_zone = '+8:00'
#设置最大连接数
max_connections=1000
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# Custom config should go here
!includedir /etc/mysql/conf.d/
docker启动mysql容器
- master1
docker run --name mysql-master1 -p 3306:3306 --privileged=true \
-v /docker/mysql/master1/conf/my.cnf:/etc/mysql/my.cnf \
-v /docker/mysql/master1/logs:/var/log/mysql \
-v /docker/mysql/master1/data:/var/lib/mysql\
-e MYSQL_ROOT_PASSWORD=root -d mysql:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --default-authentication-plugin=mysql_native_password
- slave1
docker run --name mysql-slave1 -p 3307:3306 --privileged=true \
-v /docker/mysql/slave1/conf/my.cnf:/etc/mysql/my.cnf \
-v /docker/mysql/slave1/logs:/var/log/mysql \
-v /docker/mysql/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root -d mysql:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --default-authentication-plugin=mysql_native_password
- master2
docker run --name mysql-master2 -p 3308:3306 --privileged=true \
-v /docker/mysql/master2/conf/my.cnf:/etc/mysql/my.cnf \
-v /docker/mysql/master2/logs:/var/log/mysql \
-v /docker/mysql/master2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root -d mysql:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --default-authentication-plugin=mysql_native_password
- slave2
docker run --name mysql-slave2 -p 3309:3306 --privileged=true \
-v /docker/mysql/slave2/conf/my.cnf:/etc/mysql/my.cnf \
-v /docker/mysql/slave2/logs:/var/log/mysql \
-v /docker/mysql/slave2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root -d mysql:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --default-authentication-plugin=mysql_native_password
docker-compose编排文件启动容器
[root@localhost mysql]# cat docker-compose.yml
version: '3'
services:
mysql-master1:
image: mysql:latest
container_name: mysql-master1
restart: always
privileged: true
ports:
- "3306:3306"
environment:
- MYSQL_ROOT_PASSWORD=root
- character-set-server=utf8mb4
- collation-server=utf8mb4_unicode_ci
- default-authentication-plugin=mysql_native_password
volumes:
- /docker/mysql/master1/conf/my.cnf:/etc/mysql/my.cnf
- /docker/mysql/master1/logs:/var/log/mysql
- /docker/mysql/master1/data:/var/lib/mysql
mysql-master2:
image: mysql:latest
container_name: mysql-master2
restart: always
privileged: true
ports:
- "3308:3306"
environment:
- MYSQL_ROOT_PASSWORD=root
- character-set-server=utf8mb4
- collation-server=utf8mb4_unicode_ci
- default-authentication-plugin=mysql_native_password
volumes:
- /docker/mysql/master2/conf/my.cnf:/etc/mysql/my.cnf
- /docker/mysql/master2/logs:/var/log/mysql
- /docker/mysql/master2/data:/var/lib/mysql
mysql-slave1:
image: mysql:latest
container_name: mysql-slave1
restart: always
privileged: true
ports:
- "3307:3306"
environment:
- MYSQL_ROOT_PASSWORD=root
- character-set-server=utf8mb4
- collation-server=utf8mb4_unicode_ci
- default-authentication-plugin=mysql_native_password
volumes:
- /docker/mysql/slave1/conf/my.cnf:/etc/mysql/my.cnf
- /docker/mysql/slave1/logs:/var/log/mysql
- /docker/mysql/slave1/data:/var/lib/mysql
mysql-slave2:
image: mysql:latest
container_name: mysql-slave2
restart: always
privileged: true
ports:
- "3309:3306"
environment:
- MYSQL_ROOT_PASSWORD=root
- character-set-server=utf8mb4
- collation-server=utf8mb4_unicode_ci
- default-authentication-plugin=mysql_native_password
volumes:
- /docker/mysql/slave2/conf/my.cnf:/etc/mysql/my.cnf
- /docker/mysql/slave2/logs:/var/log/mysql
- /docker/mysql/slave2/data:/var/lib/mysql
主节点创建用于复制操作的用户并授权
- master1
# 进入mysql-master1容器
[root@localhost mysql]# docker exec -it mysql-master1 bash
# 登录mysql
root@2929f5c20467:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> create user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.03 sec)
# 授权
mysql> grant replication slave on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
# 刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 验证用户创建成功
mysql> use mysql
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 User,Host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| User | Host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| root | % | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| slave | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> exit
Bye
root@2929f5c20467:/# exit
exit
- master2
[root@localhost mysql]# docker exec -it mysql-master2 bash
root@7b32e9b09f14:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> create user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql
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 User,Host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| User | Host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| root | % | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| slave | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)
查看master状态
记录File和Position
show master status;
- master1
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000010 | 156 | test | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.01 sec)
mysql> exit
Bye
root@8d669cfbb9e4:/# exit
exit
- master2
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000009 | 346 | test | mysql,information_schema,performance_schema,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
1 row in set (0.00 sec)
mysql> exit
Bye
root@8d669cfbb9e4:/# exit
exit
从机配置需要复制的主机
- slave1
[root@localhost mysql]# docker exec -it mysql-slave1 bash
root@79800b1e74f1:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> CHANGE MASTER TO \
-> MASTER_HOST='192.168.1.234',\
-> MASTER_PORT=3306,\
-> MASTER_USER='slave',\
-> MASTER_PASSWORD='123456',\
-> MASTER_LOG_FILE='mysql-bin.000010',\
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.234
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 156
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000010
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: 156
Relay_Log_Space: 529
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: 1
Master_UUID: 1096c5ee-ac77-11ea-b30b-0242ac110002
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
- slave2
[root@localhost mysql]# docker exec -it mysql-slave2 bash
root@ebdbb5a83652:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> CHANGE MASTER TO \
-> MASTER_HOST='192.168.1.234',\
-> MASTER_PORT=3308,\
-> MASTER_USER='slave',\
-> MASTER_PASSWORD='123456',\
-> MASTER_LOG_FILE='mysql-bin.000009',\
-> MASTER_LOG_POS=346;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.234
Master_User: slave
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 346
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000009
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: 346
Relay_Log_Space: 529
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: 2
Master_UUID: 59e8cd49-ac79-11ea-b7e3-0242ac160003
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
注意点
- Slave_IO_Running和Slave_SQL_Running：必须都为yes；
- Slave_IO_Running表示slave与主机的IO通讯 I/O thread，不为yes时可能的原因：
1.网络不通
2.账户密码错误
3.防火墙
4.mysql配置文件问题(server-id重复)
5.连接服务器时语法
6.主服务器mysql权限 - Slave_SQL_Running表示slave从中继日志中读取二进制日志SQL Thread，不为yes时可能的原因：
1.在从机上进行了写操作（非双机热备情况下）。
2.slave机器重启，事务回滚。
3.各种原因导致的数据不同步。
解决方案1:因事务的原因导致的sql进程停止。
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;
解决方案2:因binlog不一致导致的不同步。
从机重新配置主从同步。
测试主从复制成功
在master1上创建test数据库(因为maser1和master2设置的binlog-to-db=test)，创建表并插入一条记录，然后登录slave1上查看数据是否同步。
[root@localhost mysql]# docker exec -it mysql-master1 bash
root@95f938b82a6c:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table user (user_name VARCHAR(32));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into user(user_name) value('admin');
Query OK, 1 row affected (0.01 sec)
mysql> exit
Bye
root@95f938b82a6c:/# exit
exit
[root@localhost mysql]# docker exec -it mysql-slave1 bash
root@79800b1e74f1:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> use test;
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 user;
+-----------+
| user_name |
+-----------+
| admin |
+-----------+
1 row in set (0.00 sec)
2800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
