Mysql数据库的主从同步配置

Mysql主从同步配置

配置准备：

需要两个数据库
mysql 可视化工具，当然使用用命令行也可以
我这里演示使用 docker 启动两个 mysql 容器, 你也可以安装两个 mysql 前提版本一致

1、安装两个 mysql

				?

									[root@localhost /]# mkdir -p /opt/docker/mysql1/conf/

									[root@localhost /]# mkdir -p /opt/docker/mysql1/logs/

									[root@localhost /]# mkdir -p /opt/docker/mysql1/data/

启动第一个 mysql 挂载对应的文件目录 port: 6894

				?

									[root@localhost /]# docker run -d -p 6894:3306 --name mysql1 \

									                    -v /opt/docker/mysql1/conf:/etc/mysql/ \

									                    -v /opt/docker/mysql1/logs:/logs \

									                    -v /opt/docker/mysql1/data:/var/lib/mysql \

									                    --privileged=true \

									                    -e MYSQL_ROOT_PASSWORD=qtykGhC29eP4Smp mysql:5.7

通过拷贝第二个 mysql

需要注意复的 mysql /opt/docker/mysql2/data/auto.cnf 目录下有一个 auth.cnf 需要删除

				?

									[root@localhost docker]# cp -r /opt/docker/mysql1/ /opt/docker/mysql2/

删除 auth.cnf 文件

				?

									[root@localhost docker]# rm -f /opt/docker/mysql2/data/auto.cnf

启动第二个 mysql

				?

									[root@localhost docker]#  docker run -d -p 6895:3306 --name mysql2 \

									                    -v /opt/docker/mysql2/conf:/etc/mysql/ \

									                    -v /opt/docker/mysql2/logs:/logs \

									                    -v /opt/docker/mysql2/data:/var/lib/mysql \

									                    --privileged=true \

									                    -e MYSQL_ROOT_PASSWORD=qtykGhC29eP4Smp mysql:5.7

2、编写mysql配置文件

主库 my.cnf 文件

				?

									[root@localhost docker]# vim /opt/docker/mysql1/conf/my.cnf

my.cnf 文件内容

				?

									[mysqld]

									# 主库配置

									server-id=1 # 服务 id 唯一性

									log-bin=mysql1-log # 开启二进制日志 

									binlog-format=ROW # 日志记录模式

									replicate-do-db=db_docker # 要复制的数据名称

									# replicate-ignore-db=db_docker # 不需要复制的数据名称

从库 my.cnf 文件

				?

									[root@localhost docker]# vim /opt/docker/mysql2/conf/my.cnf

my.cnf 文件内容

				?

									[mysqld]

									# 从库配置

									server-id=2   # 服务 id 唯一性

									log-bin=mysql2-log  # 开启二进制日志 

									binlog-format=ROW # 日志记录模式 

									binlog-do-db=db_docker # 要复制的数据名称

									# binlog-ignore-db=db_docker # 不需要复制的数据名称

重启 docker mysql 容器

				?

									[root@localhost docker]# docker restart mysql1

									[root@localhost docker]# docker restart mysql2

3、初始化数据

两个 mysql 分别执行以下 sql 语句创建数据库创建表

				?

									-- 创建数据库

									CREATE DATABASE `db_docker`;

									USE db_docker;

									-- 创建表

									CREATE TABLE `t_docker` (

									    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,

									    `name` VARCHAR ( 255 ) DEFAULT NULL,

									     PRIMARY KEY ( `id` )

									) ENGINE = INNODB AUTO_INCREMENT =0 DEFAULT CHARSET = utf8;

查看主库二进制日志：

主库执行以下命令输出二进制日志文件的状态信息

				?

									mysql> SHOW MASTER STATUS ;

									+------------------+----------+--------------+------------------+-------------------+

									| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

									+------------------+----------+--------------+------------------+-------------------+

									| mysql-bin.000001 |     2223 |              |                  |                   |

									+------------------+----------+--------------+------------------+-------------------+

配置从库二进制日志

从库更改用于连接到复制主库、读取主库的二进制日志和读取从库的中继日志的参数

				?

									CHANGE MASTER TO

									MASTER_HOST="192.168.101.59", # 主机地址 你的主服务器 ip

									Master_Port=6894, # 端口

									MASTER_USER="root", # 账号

									MASTER_PASSWORD="qtykGhC29eP4Smp", # 密码

									MASTER_LOG_FILE="mysql-bin.000001", # 主库二进制文件名 根据实际情况填写

									MASTER_LOG_POS=377; # 主库二进制文件位置 根据实际情况填写

从库执行，启动复制

				?

									mysql> START SLAVE;

从库线程的基本参数的状态信息。从 MySQL 8.0.22 开始，使用 SHOW REPLICA STATUS代替 SHOW SLAVE STATUS，该版本已弃用。在 MySQL 8.0.22 之前的版本中，使用SHOW SLAVE STATUS. 该语句需要REPLICATION CLIENT特权（或已弃用的 SUPER特权）。

以下两参数为 yes 表示配置成功，否则配置有问题。 Slave_IO_Running: Yes Slave_SQL_Running: Yes

如果以上两个参数有一个未 No 说明有错误，请查看这个两个字段 Last_Errno Last_Error

错误内容会记录在 Last_Error 这个字段中，根据错误内容修改。

				?

									mysql>  SHOW SLAVE STATUS \G;

									*************************** 1. row ***************************

									               Slave_IO_State: Waiting for master to send event

									                  Master_Host: 192.168.101.59

									                  Master_User: root

									                  Master_Port: 6894

									                Connect_Retry: 60

									              Master_Log_File: mysql-bin.000001

									          Read_Master_Log_Pos: 2223

									               Relay_Log_File: 98394ee2fb48-relay-bin.000004

									                Relay_Log_Pos: 320

									        Relay_Master_Log_File: 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: 2223

									              Relay_Log_Space: 534

									              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: aa58ab20-f500-11eb-aa65-0242ac110002

									             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:

4、其他mysql 相关命令

停止主库的数据更新操作

				?

									mysql>flush tables with read lock;

主库解锁

				?

									mysql>unlock tables;

从库执行，停止复制; 当从库配置二进制日志出错时，需要停止复制或重置，再重新配置，让后启动复制

				?

									mysql> STOP SLAVE;

从库执行，重置复制

				?

									mysql> RESET SLAVE;

mysql 注意事项

版本不同对应的命令和配置文件可能存在差异
演示使用的是 mysql 5.7
如果命令或配置不起作用详情官网
在实际应用中尽量不要使用默认端口 3306 容易被攻击

mysql 容器：

进入容器

				?

									docker exec -it mysql2 /bin/sh

									# mysql2 容器名称 这里也可以是容器 id

登录 mysql

				?

									mysql -u root -pqtykGhC29eP4Smp

									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 9

									Server version: 5.7.35-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>

my.cnf 配置解释

				?

									[mysqld]

									# 主库配置

									# 指定一个唯一的服务器 ID,可以为0但是服务器会拒绝，所以有效值 1~4294967295 之间。默认值1

									# 官网 https://dev.mysql.com/doc/refman/5.7/en/replication-options.html

									server-id=1

									# 开启二进制日志，二进制文件名称，可以是路径 例如： /logs/mysql/log 但是需要注意给目录文件提权，否则mysql可以无权写入，导致报错。

									log-bin=mysql1-log

									# 日志记录模式 有三种 

									# STATEMENT 导致日志记录基于语句。

									# ROW 导致日志记录基于行。这是默认设置。

									# MIXED 导致日志记录使用混合格式。介于 前两种模式之间

									# 官网 https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

									binlog-format=ROW 

									# 要复制的数据名称，要指定多个数据库，您必须使用此选项的多个实例。

									# 由于数据库名称可以包含逗号，如果您提供逗号分隔列表，则该列表将被视为单个数据库的名称。

									# 多个实例：

									# replicate-do-db=db_docker1 

									# replicate-do-db=db_docker2 

									replicate-do-db=db_docker 

									# 不需要复制的数据名称，配置同上

									# replicate-ignore-db=db_docker # 不需要复制的数据名称

				?

									[mysqld]

									# 从库配置同上 举一反三

									server-id=2   # 服务 id 唯一性

									log-bin=mysql2-log  # 开启二进制日志 

									binlog-format=ROW # 日志记录模式 

									binlog-do-db=db_docker # 要复制的数据名称

									# binlog-ignore-db=db_docker # 不需要复制的数据名称

auth.cnf 文件

文件内容 server-uuid

这里 UUID 也是必须唯一，启动的时候会自动生成。如果您也是通过复制 mysql data 目录创建的数据库需要删除该文件

				?

									[auto]

									server-uuid=aa58ab20-f500-11eb-aa65-0242ac110002

UUID相同会报以下错误

				?

									Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.