系列学习 MySQL 之第 2 篇 —— Windows 实现两台 MySQL 服务器的主从复制

接着上一篇博客。我们已经安装好了两台 MySQL 服务器。

 

MySQL主从复制介绍

在 MySQL 集群环境中，可以分为主节点与从节点，通过主从复制可以实现数据备份、故障转移（其中一台宕机了可以访问另一台）、MySQL集群、高可用、读写分离等。

MySQL的主从复制原理：主从复制是 MySQL本身自带的一个功能，不需要额外的第三方软件就可以实现，其复制功能并不是copy文件来实现的，而是借助 binlog 日志文件里面的SQL命令实现的主从复制，可以理解为在 Master（主数据库） 端执行了一条SQL命令，那么在 Salve （从数据库）端同样会执行一遍，从而达到主从复制的效果。从库生成两个线程，一个I/O线程，一个SQL线程；  I/O线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log（中继日志） 文件中； 主库会生成一个 log dump 线程，用来给从库I/O线程传 binlog； 从数据库的 SQL 线程，会读取 relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。

补充：

binlog 日志文件：MySql 中有一种日志叫做 bin 日志（二进制日志），这个日志会记录下所有修改了数据库的SQL语句（DML 语句，如：insert，update，delete，ALTER TABLE，grant等）。主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

DML（data manipulation language）数据操纵语言：就是我们最经常用到的 insert、update、delete。 主要用来对数据库的数据进行一些操作。注意：select 语句属于DQL(Data Query Language)。

DDL（data definition language）数据库定义语言：其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上

DCL（Data Control Language）数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant，deny，revoke等）语句。比较少用到。

 

我们来实现两台 MySQL 的主从复制。我们让 5.7 版本的为主服务器，5.5版本的为从服务器。

首先找到 5.7 版本的 my.ini 配置文件：（说明，这个 data 目录就是安装的时候自己选择的。）

找到[mysqld]，默认配置的 server-id=1（在123行左右，这个要注意！否则会看到 server-id=1 的错误） ，我们修改成200，同时开启日志文件。

server-id=200
log-bin=mysql-bin

 

保存后，重启一下主服务器：（先停止，再启动）

然后在 MySQL 客户端 Navicat 进行查询：

输入以下查询：

show variables like '%server_id%';

结果为200，即为配置正确。

然后再查询：

show master status;

查询结果能看到 binlog 文件，即为正确，binlog 的版本是一直在叠加的。154是它的行数。

 

 

接下来，配置从服务器。5.6版本的，也是打开 my.ini 配置文件。（在安装目录下可以找到）

 

找到[mysqld]，默认配置的 server-id=1（在123行左右，这个要注意！否则会看到 server-id=1 的错误） ，修改服务ID为201，以及日志文件同步方式等。还有个重要的说明是要同步的数据库名。这里需要根据实际情况修改，比如我的是 my_db_test，等下要创建这个数据库，否则就没法同步了，也不知道要同步啥了。

# 从服务器的服务器ID
server_id=201
# 日志文件同步方式
log-bin=mysql-bin
# 要同步数据库，根据实际情况修改
binlog_do_db=my_db_test

我们重启一下5.6的服务器：

 

然后验证从服务器：

show variables like '%server_id%';

show master status;

 

 

 

 

然后，我们需要将【从服务器】同步【主服务器】的配置：在【从服务器】的查询上执行下面的句子（这里的 master_log_file 和 pos 需要根据你的实际情况配置）：

change master to master_host='127.0.0.1',master_port=3306,
master_user='root',master_password='root',
master_log_file='mysql-bin.000007',master_log_pos=154;

结果：

 

然后，【从服务器】开始同步，执行下面的句子：

-- 开始同步
start slave;

执行结果： 

最后，检查【从服务器】的状态：

-- 检查从服务器复制功能状态
SHOW SLAVE STATUS;

结果（必须 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes）：

 

如果是报错：

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.

是因为两台 MySQL 服务器的 UUID 相同了（如果是拷贝、克隆的就有这个问题），处理办法是把其中一台的 auto.cnf 文件删掉即可，然后重启那台的 mysql 服务器。

 

OK，两台服务器的主从复制已经安装完毕，我们接下来验证一下。

1、新建数据库：数据库名为：my_db_test

2、刷新【从服务器】即可看到已经创建好：my_db_test 数据库了

 

3、【主服务器】上创建一个 t_user 表，里面存放2个字段，验证【从服务器】也能同步：

-- 建表
create table t_user(
	`id` INTEGER(11) PRIMARY key not null auto_increment,
	`user_name` varchar(30)
);


-- 插入数据
insert into t_user values
(null,'biandan'),
(null,'流放深圳');

结果：

4、在【从服务器】验证：

 

OK，MySQL 主从复制搞定。

思考：如果【从服务器】修改了某个表的某些数据，那么【主服务器】是否会同步？

答案：不会同步。因为配置的信息里是【从服务器】获取【主服务器】的 binlog 日志文件进行同步的，主服务器压根不知道有【从服务器】存在。

为了解决这个问题，我们引入【读写分离】的概念：【主服务器】负责写，【从服务器】负责读，不能修改。

下一篇博客讲解。