Windows环境实现MySQL主从复制(非常详细)
MySQL Replication 是 MySQL 的一个非常重要的功能,主要用于主服务器和从服务器之间的数据复制操作。
MySQL 从 3.25 版本开始提供数据库复制(replication)功能。MySQL 复制是指从一个 MySQL 主服务器(Master)将数据复制到另一台或多台 MySQL 从服务器(Slave)的过程。
本节将主要通过实验讲解 MySQL Replication 在 Windows 环境下如何配置主从复制的功能。
读者在做实验的过程中,如果没有富余的计算机,可以使用 VirtualBox、VMware 虚拟机来模拟实现,操作系统可以选 Windows。这里 Master 可以由本机担任,Salve 再装一台虚拟机担任;IP地址可以自行按网络情况做相应的修改。
1) 在 Windows 操作系统下安装好两台主机的 MySQL 服务器,一台 Master 主机,一台 Slave 从机,并配置好两台主机的 IP 地址,实现两台计算机可以网络连通。
2) 配置 Master 主机的相关信息,在 Master 主机上开启 binlog 日志。首先,确认一下 datadir 的具体路径。
3) 此时到 C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 8.0 目录下,打开配置文件 my.ini,添加如下代码,开启 binlog 功能:
4) 登录 MySQL 之后,可以执行 show variables like '%log_bin%' 命令来测试 log_bin 是否成功开启:
5) 在 Master 主机上配置复制所需要的账户,这里创建一个名为 repl 的用户,% 表示任何远程地址的 repl 用户都可以连接 Master 主机,语句执行如下所示:
6) 在 my.ini 配置文件中配置 Master 主机的相关信息,如下所示:
7) 重启 Master 主机的 MySQL 服务,然后输入 show master status 命令查询 Master 主机的信息:
8) 将 Master 主机的数据备份出来,然后导入到 Slave 从机中去,具体执行语句如下:
9) 在 Slave 从机(192.168.1.206)的 C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 8.0 目录下,配置 my.ini 配置文件,具体配置信息如下所示:
10) 重启 Slave 从机(192.168.1.206),在 Slave 从机的 MySQL 数据库中执行如下命令,关闭 slave 服务:
11) 在 Slave 从机上设置实现复制功能的相关信息,命令如下:
继续执行操作,显示 Slave 从机的状况,如下所示:
1) 重启 Master(192.168.1.208)主机,执行 show master status \G 命令,记下 File 和 Position 的值,后面 Slave 从机会用到。命令执行如下:
2) 在 Slave(192.168.1.206)从机上重新设置信息,命令执行如下所示:
1) 在 Master 主机的 MySQL 环境下,执行如下命令:
2) 在 Slave 从机的 MySQL 环境下,查看主机刚才添加的表和数据是否成功同步过来,命令执行如下所示:
MySQL 从 3.25 版本开始提供数据库复制(replication)功能。MySQL 复制是指从一个 MySQL 主服务器(Master)将数据复制到另一台或多台 MySQL 从服务器(Slave)的过程。
本节将主要通过实验讲解 MySQL Replication 在 Windows 环境下如何配置主从复制的功能。
复制前的准备工作
在 Windows 环境下,如果想要实现主从复制功能,需要准备操作环境,本示例操作环境如下表所示:角色 | 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 架构可能会用到一主多从的架构,其配置方法类似,这里不再赘述。