首页 > 编程笔记 > MySQL笔记 阅读:25

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
这些配置语句的含义如下:
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)
各个参数所代表的具体含义如下:
继续执行操作,显示 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 架构可能会用到一主多从的架构,其配置方法类似,这里不再赘述。

相关文章