首页 > 编程笔记 > MySQL笔记阅读：16

Windows环境实现MySQL主从复制（非常详细）

MySQL Replication 是 MySQL 的一个非常重要的功能，主要用于主服务器和从服务器之间的数据复制操作。

MySQL 从 3.25 版本开始提供数据库复制（replication）功能。MySQL 复制是指从一个 MySQL 主服务器（Master）将数据复制到另一台或多台 MySQL 从服务器（Slave）的过程。

本节将主要通过实验讲解 MySQL Replication 在 Windows 环境下如何配置主从复制的功能。

复制前的准备工作

在 Windows 环境下，如果想要实现主从复制功能，需要准备操作环境，本示例操作环境如下表所示：

表：MySQL 主从复制所需的环境
角色	IP	操作系统	MySQL 版本
Master	192.168.1.208	Windows	MySQL-installer-community-8.0
Slave	192.168.1.206	Windows	MySQL-installer-community-8.0

读者在做实验的过程中，如果没有富余的计算机，可以使用 VirtualBox、VMware 虚拟机来模拟实现，操作系统可以选 Windows。这里 Master 可以由本机担任，Salve 再装一台虚拟机担任；IP地址可以自行按网络情况做相应的修改。

Windows环境下实现主从复制

准备好两台安装 MySQL 8.0 的计算机后，即可对主从复制备份进行配置，操作步骤如下：

1) 在 Windows 操作系统下安装好两台主机的 MySQL 服务器，一台 Master 主机，一台 Slave 从机，并配置好两台主机的 IP 地址，实现两台计算机可以网络连通。

2) 配置 Master 主机的相关信息，在 Master 主机上开启 binlog 日志。首先，确认一下 datadir 的具体路径。

mysql> show variables like '%datadir%';
+---------------+---------------------------------------------+
| Variable_name | Value                                       |
+---------------+---------------------------------------------+
| datadir       | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ |
+---------------+---------------------------------------------+
1 row in set (0.00 sec)

3) 此时到 C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 8.0 目录下，打开配置文件 my.ini，添加如下代码，开启 binlog 功能：

[mysqld]
log_bin="D:/mysqllog/binlog"
expire_logs_days = 10
max_binlog_size = 100M

我们在 D 盘下创建 mysqllog 文件夹，binlog 日志记录在该文件夹里面，该配置中 max_binlog_size 参数表示二进制日志文件最大的大小。

4) 登录 MySQL 之后，可以执行 show variables like '%log_bin%' 命令来测试 log_bin 是否成功开启：

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                | Value                 |
+---------------------------------+--------------------------+
| log_bin                     | ON                    |
| log_bin_basename              | D:\MySQLlog\binlog      |
| log_bin_index                | D:\MySQLlog\binlog.index |
| log_bin_trust_function_creators | OFF                   |
| log_bin_use_v1_row_events      | OFF                   |
| sql_log_bin                  | ON                    |
+---------------------------------+--------------------------+
6 rows in set (0.00 sec)

如果 log_bin 参数的值为 ON，那么表示二进制日志文件已经成功开启；如果为 OFF，那么表示二进制日志文件开启失败。

5) 在 Master 主机上配置复制所需要的账户，这里创建一个名为 repl 的用户，% 表示任何远程地址的 repl 用户都可以连接 Master 主机，语句执行如下所示：

mysql> grant replication slave on *.* to repl@'%' identified by '123';
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.09 sec)

6) 在 my.ini 配置文件中配置 Master 主机的相关信息，如下所示：

[mysqld]
log_bin="D:/MySQLlog/binlog"
expire_logs_days = 10
max_binlog_size = 100M

server-id = 1
binlog-do-db = test
binlog-ignore-db = mysql

这些配置语句的含义如下：

server-id：表示服务器标识 id 号，Master 和 Slave 上的 server-id 不能一样；
binlog-do-db：表示需要复制的数据库，这里以 test 数据库为例；
binlog-ignore-db：表示不需要复制的数据库。

7) 重启 Master 主机的 MySQL 服务，然后输入 show master status 命令查询 Master 主机的信息：

mysql> show master status \G;
*** 1. row ***
          File: binlog.000003
       Position: 120
    Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)

8) 将 Master 主机的数据备份出来，然后导入到 Slave 从机中去，具体执行语句如下：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump –u root -p –h localhost test >c:\a.txt
Enter password:

将 c:/a.txt 复制到 Slave 从机上面去，然后执行以下操作：
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump –u root –p root –h localhost
test <c:\a.txt
Warning: Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 8.0.28, for Win32 (x86)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       8.0.28-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on  2022-04-03 17:25:17

9) 在 Slave 从机（192.168.1.206）的 C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 8.0 目录下，配置 my.ini 配置文件，具体配置信息如下所示：

[mysql]
default-character-set=utf8
log_bin="D:/MySQLlog/binlog"
expire_logs_days=10
max_binlog_size = 100M

[mysqld]
server-id = 2

提示，配置 Slave 从机上的 my.ini 文件时，需要将 server-id=2 写到 [mysqld] 后面。另外，如果配置文件中还有 log_bin 的配置，可以将它注释掉。例如：

# Binary Logging.
# log-bin
# log_bin = "D:/MySQLlog/mysql-bin.log"

10) 重启 Slave 从机（192.168.1.206），在 Slave 从机的 MySQL 数据库中执行如下命令，关闭 slave 服务：

mysql> stop slave;
Query OK, 0 rows affected (0.05 sec)

11) 在 Slave 从机上设置实现复制功能的相关信息，命令如下：

mysql> change master to
   -> master_host='192.168.1.208',
   -> master_user='repl',
   -> master_password='123',
   -> master_log_file='binglog.000003',
   -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

各个参数所代表的具体含义如下：

master_host：表示实现复制的主机的 IP 地址；
master_user：表示实现复制的登录远程主机的用户；
master_password：表示实现复制的登录远程主机的密码；
master_log_file：表示实现复制的 binlog 日志文件；
master_log_pos：表示实现复制的 binlog 日志文件的偏移量。

继续执行操作，显示 Slave 从机的状况，如下所示：

mysql> start slave;
Query OK, 0 rows affected (0.11 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.208
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 120
Relay_Log_File: 2022-20190220JX-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: No
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: 120
Relay_Log_Space: 120
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_Crl:
Master_SSL_Crlpath:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_IO_Error_Timestamp: 190403 17:53:15
Last_SQL_Error:
Last_SQL_Error_Timestamp:
Master_Server_Id: 1
Master_UUID: a6bd1fa8-8d6b-11e2-97b4-001f3ca9bc3a
Master_Info_File: C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 8.0\data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190403 17:53:15
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

在上述执行 show slave status \G 命令中很显然存在一些问题：

Last_IO_Error: Got fatal error 1236 from master when reading dat
a from binary log: 'Could not find first log file name in binary log index file'

下面给出解决该问题的方法：
1) 重启 Master（192.168.1.208）主机，执行 show master status \G 命令，记下 File 和 Position 的值，后面 Slave 从机会用到。命令执行如下：

mysql> show master status \G;
*** 1. row ***
          File: binlog.000004
       Position: 120
    Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)

2) 在 Slave（192.168.1.206）从机上重新设置信息，命令执行如下所示：

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

mysql> change master to
    -> master_log_file='binlog.000004',
    -> master_log_pos = 120;
Query OK, 0 rows affected (0.16 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.208
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 120
Relay_Log_File: 2022-20190220JX-relay-bin.000002
Relay_Log_Pos: 280
Relay_Master_Log_File: binlog.000004
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: 120
Relay_Log_Space: 463
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_Crl:
Master_SSL_Crlpath:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Error:
Last_IO_Error_Timestamp:
Last_SQL_Error:
Last_SQL_Error_Timestamp:
Master_Server_Id: 1
Master_UUID: a6bd1fa8-8d6b-11e2-97b4-001f3ca9bc3a
Master_Info_File: C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 8.0\data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)

由此可见，问题完全解决，接下来可以进行 Window 环境下主从复制的测试。

Windows环境下主从复制测试

在 Windows 环境中测试主从复制操作，具体操作步骤如下：

1) 在 Master 主机的 MySQL 环境下，执行如下命令：

mysql> use test;
Database changed
mysql> create table rep_test(
   -> data integer
   -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into rep_test values(2);
Query OK, 1 row affected (0.06 sec)

2) 在 Slave 从机的 MySQL 环境下，查看主机刚才添加的表和数据是否成功同步过来，命令执行如下所示：

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t2           |
+----------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| rep_test       |
| t2           |
+----------------+
2 rows in set (0.00 sec)

mysql> select *from rep_test;
+------+
| data |
+------+
|    2  |
+------+
1 row in set (0.02 sec)

测试表明数据已经成功同步到 Slave 从机上了，本节试验只是一主一从的同步，在实际生产环境中 MySQL 架构可能会用到一主多从的架构，其配置方法类似，这里不再赘述。

Windows环境实现MySQL主从复制（非常详细）

复制前的准备工作

Windows环境下实现主从复制

Windows环境下主从复制测试

相关文章