MYSQL一个设备上的主从复制实现-windows

只记录一次在一个设备上实现mysql主从复制的过程，很详细，建议收藏，用到的时候照着步骤做就可以，会记录所有需要注意到的细节和一些容易遇到的坑以及解决办法！

如果需要在同一台电脑（服务器）上实现mysql的主从复制备份，需要一个主数据库和一个从数据库。

本文使用的是mysql-5.6.39-winx64免安装版。安装版的也可以，需要自行找到安装生成的data所在目录以及my.ini文件位置。

因为在这之前我已经安装好了主数据库，本文只记录从数据库的安装过程，（主数据库也是个全新的，会记录所有的配置）。

主数据库端口号 port = 3307。

从数据库端口号 port = 3308 。

安装第二个从mysql：

找到第一个安装的mysql目录，完整的复制到另一个磁盘里（因为我的是免安装的，如果有安装包或者压缩包可以重新安装一个，我懒。）（我这里放到了一起，好操作，实际看情况为了好区分，我在后面加了后缀-copy

编辑copy数据库的my.ini:

basedir = E:\MYSQL\mysql-5.6.39-winx64-copy
datadir = E:\MYSQL\mysql-5.6.39-winx64-copy\data

port = 3308

添加环境变量：1、右击我的电脑-2、点击属性-3、点击高级系统设置-4、点击环境变量

在系统变量里添加两个数据库的变量，不添加的话两个mysql容易乱（两个mysql后面分别加了个1、2区分） 

 然后在系统变量里的PATH里追加两个变量值：

%MYSQL_HOME1%\bin     %MYSQL_HOME2%\bin

将两个mysql注册成windows服务：

使用管理员命令窗口进入mysql安装目录下的bin目录里：

 

两个数据库分别操作输入服务安装命令：

（MYSQL2_copy 是服务名字，可以随意自定义）

E:\MYSQL\mysql-5.6.39-winx64-copy\bin\mysqld install MYSQL2_copy --defaults-file="E:\MYSQL\mysql-5.6.39-winx64-copy\my.ini"

（补充：移除服务的命令：mysqld remove MYSQL2_copy）

运行提示下面内容表示服务安装成功：

Service successfully installed.

可以进入服务页面查看（服务页面进入方法：右击我的电脑，点击管理，然后点击服务和应用下的服务即可）：

启动mysql服务：

一：命令启动：

net start MYSQL1

二：服务列表启动（右击服务，点击启动）

修改主数据库的my.ini配置：

在[mysqld]标签下添加：

# 服务器id 可自定义
server-id = 3307
# 开启二进制日志
log-bin=mysql-bin
log-bin-index=master-bin.index
relay_log=mysql-relay-bin
# 需要备份的数据库
binlog-do-db=test
# 不需要备份的数据库
binlog-ignore-db=mysql

保存退出，重启主数据库服务

binlog-do-db用于指定需要同步的数据库，binlog-ignore-db指定不需要同步的数据库，如果这两个参数都不设置，则从服务器会复制主服务器的所有数据库

一般同步的话会新建一个专门用在该场景的用户，所以我们需要在主服务器上创建一个新的用户（如user_cp,密码123456）

方法如下：

打开管理员命令窗口，切换至主数据库目录下的bin目录下，用 root 账户连接MySQL主服务器(-u账号 -p密码)

mysql -uroot -proot --protocol=tcp --host=localhost --port=3307

创建新用户（@后面的ip地址为允许连接的客户端的ip地址。）： 

create user 'user_cp'@'127.0.0.1' identified by '123456';

给新用户配置主从复制的权限（@后面的ip地址为允许连接的客户端的ip地址，如果改为 '%'，就表示客户端没有ip地址的限制）：

grant replication slave on *.* to 'user_cp'@'127.0.0.1' identified by '123456';

刷新一下权限：

flush privileges;

如果主服务器中的数据库“test”中，已经有数据，我们需要先手动把主服务器中的数据复制到从服务器中，为了防止我们复制数据的时候，数据库中的test里的数据发生更新变化，我们可以先锁定数据库。

方法：

使用全局锁

flush tables with read lock;

这个命令是全局读锁定，它会给主服务器中的所有数据库都加上读锁，这里顺便说一下读锁和写锁的区别：
read lock（读锁）：也叫共享锁，允许所有的读操作，但阻塞写操作，即所有连接只可以读数据，但不允许写数据。
write lock（写锁）：也叫排它锁、独占锁，只允许当前连接的读和写，不允许其他并发的读操作和写操作。
锁定主服务器的数据库后，我们在从服务器中，也创建一个数据库test，并将所有的表（包括表结构和表数据）都导入。
然后，我们执行下面的命令，解锁：

unlock tables;  

查看主服务器的master状态（记住列表中的File 和 Position的值，等会配置从服务器的时候需要用到）：

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |     497  |     test     | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+

从服务器配置：

修改从服务器的数据库配置文件E:\MYSQL\mysql-5.6.39-winx64-copy\my.ini：

[mysqld]标签下添加：

# 端口号
port = 3308
# 服务id 可自定义
server_id = 3308
# 开启二进制日志（从服务器不是必须要开启二进制日志）
log-bin=mysql-bin 

保存退出，重启MySQL服务。

使用管理员命令窗口登录（常用的登录方式是mysql -u用户名 -p密码，但是因为一个设备有多个mysql需要指定端口号来登录，否则只会进入默认的3306端口服务器）:

mysql -uroot -proot --protocol=tcp --host=localhost --port=3308

然后复制以下内容运行（需要修改里面的参数为自己服务器的信息）：

change master to master_host='127.0.0.1',master_user='user_cp',master_password='123456',master_port=3307,master_log_file='mysql-bin.000013',master_log_pos=497;

参数详解：

master_host： 主服务器的IP

master_user： 主服务器上新创建的用户名

master_password： 用户的密码

master_port： 主服务器的端口，如果未曾修改，默认即可。

master_log_file： 主服务器二进制日志文件的名称，填写查看主服务器的master状态时显示的File的值

master_log_pos： 日志的位置，填写查看主服务器的master状态时显示的Position的值

 

启动从服务器的slave复制功能：

start slave;

停止主从复制功能：

stop slave;

查看从服务器的slave状态：

show slave status \G;

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: user_cp
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 819
Relay_Log_File: DESKTOP-IDKR9K2-relay-bin.000003
Relay_Log_Pos: 605
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes，则说明主从复制的所有配置已成功，即从服务器已经可以自动与主服务器的数据库数据实现同步了。

(如果是测试数据库的话可以自己测试一下，往主服务器执行几条添加修改等命令，看一下从服务器是否同步)

此后，只要主服务器的数据有更新（比如：在 test数据库中新建了一张表或者表中的数据发生了变化），从服务器都会自动与主服务器保持一致。但如果有人刻意改变了从服务器的数据，主服务器中的数据并不会同步更新，除非我们把这两个MySQL服务器设置为互为主从。