Skip to main content

Windows Server 下实现 MySQL 主从复制

MySQL 5.5 主从复制官方文档:Chapter 16. Replication

概述

名词解释

  • binlog:主库用来记录所有数据库发生的所有更改的日志文件。
  • relay log:从库拿到主库 binlog 的更新后,保存在本地的 relay log 文件中,然后从该文件读取主库的变化,并在从库上同步执行。
  • 主库 binlog dump 线程:主库用来向从库发送 binlog 更新的线程。
  • 从库 I/O 线程:从库用来接收主库更新的 binlog 的线程,接收到的更新就保存在从库本地的 relay log 文件中。
  • 从库 SQL 线程:从库用来从本地的 relay log 文件中读取 binlog 的更新并执行的线程。

复制方案对比

服务器环境说明

主数据库位于主服务器 ( 192.168.8.28 ) 上,MySQL 版本为 5.5.37 32位。

从数据库位于备用服务器 ( 192.168.8.27 ) 上,MySQL 版本与主服务器相同。

编辑主库配置文件

停止主库的服务。

备份主服务器上的 my.ini 之后,编辑该文件,在 [mysqld] 区块中增加以下内容。

第一行用于设置 binlog 文件的路径,第二行用于设置主库的 ID。主库和所有从库的 ID 应当互不相同。

log-bin="D:/Logs/MySQL/BinLogs/main-bin.log"
server-id=1

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions,建议再添加以下两行配置。

innodb_flush_log_at_trx_commit=1
sync_binlog=1

确保 my.ini 文件中没有启用 skip-networking 字段。

启动主库的服务。

编辑从库配置文件

停止从库的服务。

备份备用服务器上的 my.ini 之后,编辑该文件,在 [mysqld] 区块中增加以下内容。

server-id=2

# 添加以下两行之后,可以利用 binlog
# 实现从库上的数据备份和崩溃恢复功能
# 如果从库之后被提升为主库,下面两行
# 也可以让其他从库基于新的主库来创建
innodb_flush_log_at_trx_commit=1
sync-binlog=1

# 设置从库为只读,按需开启
# 如果被提升为主库,这一条一定要删掉
read-only=1

# 从库启动时,不自动开始主从复制
# 只有手动执行 start slave 才行
# 配置完主从复制后禁用该项即可
skip-slave-start

确保 my.ini 文件中没有启用 skip-networking 字段。

主库创建专门用于主从复制的用户

进入主库的 MySQL 命令行,输入如下命令,每行命令输入完成后按回车。

命令中的 srv-repl 为专门用于主从复制的 MySQL 用户,slavepass 为密码(尽量用字母 + 数字格式的简单密码,以免出问题)。

语句中的 % 表示所有服务器都可以使用这个用户(建议用此模式),如果想限定只能由从库所在服务器的 IP 使用该用户,则将其改为从库所在服务器的 IP 即可(可能出问题)。

mysql > CREATE USER 'srv-repl'@'%' IDENTIFIED BY 'slavepass';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'srv-repl'@'%';

锁定主库

进入主数据库的 MySQL 命令行,输入如下命令,每行命令输入完成后按回车。

# 加一个全局读的锁,保证数据无法被更改
mysql > FLUSH TABLES WITH READ LOCK;
# 新开一个进程,再输入如下命令
mysql > SHOW MASTER STATUS;

记下这里 File 字段 ( main-bin.000001 ) 和 Position 字段的值 ( 1325 ),后面要用。

如果这两个字段为空,则在后面用到这两个字段的地方,前者用空字符串 '',后者用 4。

从库导入主库数据

记得先停止从库的服务,然后将主库的数据库文件,包括 data 目录下以数据库为名的文件夹,以及 ibdata1 文件,复制到从库的 data 目录下。

由于主库的数据体积很大,所以没有采用从主库导出整个数据库、再导入到从库的方式。

不过为了备忘,还是在这里记录一下主库导出、从库导入的流程。

# 用 CMD 执行导入数据的命令(不要用 PowerShell,因为符号 `<` 是 PowerShell 的保留关键字)。
# 进入 MySQL 命令行
mysql -u root
# 创建数据库
mysql> CREATE DATABASE db1;
# 完成后退出
mysql> exit
# 向数据库导入数据,不创建数据库的话,导入时会报错
mysql -u root db1 < db1.sql

从库配置到主库的连接

执行如下命令进行配置。

# 登录 MySQL Shell
mysql -uroot -p
# 输入 root 用户的密码
……
# 配置主从复制相关参数
mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.28', MASTER_USER='srv-repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='main-bin.000001', MASTER_LOG_POS=1325;
# 启动从库
mysql> START SLAVE;
# 查看主从复制状态
mysql> SHOW SLAVE STATUS\G

如果 Slave_IO_RunningSlave_SQL_Running 这两个字段的值是 YES,则说明主从复制已经配置成功。

如果还不放心,可以手动执行命令查询主库和从库上,各库各表的行数。

相关关键词及文章