MySQL主从复制的实现

MySQL主从复制的理解图：

 

MySQL Replication原理

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。

复制是异步的 从站不需要永久连接以接收来自主站的更新。

根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。

MySQL中复制的优点包括：

• 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
• 数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
• 分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
• 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

前提是作为主服务器角色的数据库服务器必须开启二进制日志

1. 主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。

2. 从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。

3. 从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

每个从服务器都会收到主服务器二进制日志的全部内容的副本。

从服务器设备负责决定应该执行二进制日志中的哪些语句。

除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。

如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。

重要: 您无法将主服务器配置为仅记录特定事件。

每个从站(从服务器)都会记录二进制日志坐标：

• 文件名
• 文件中它已经从主站读取和处理的位置。

由于每个从服务器都分别记录了自己当前处理二进制日志中的位置，因此可以断开从服务器的连接，重新连接然后恢复继续处理。

一主多从

如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整，将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。工作原理图如下：

（上面的都来自于Mysql 主从复制，这种原理类的内容，懒得自己再敲一遍了）

 

实现篇

首先需要创建MySQL多实例，这里我创建的是一主一从，如果需要多从，上面写有思路。
1、复制第一个安装的MySQL Server，直接整个文件夹复制，如下图。

在别的路径下粘贴之后，记得删除data文件夹，否则新的MySQL实例的配置文件会一直找第一个MySQL服务，然后新的MySQL实例的服务就会一直无法启动！我的主库为MySQL2，从库为MySQL3。因为我不想操作我第一个安装的MySQL服务。如下图：

2、修改配置文件my.ini：两个端口、两个dir路径、server-id和主库的二进制日志位置

关于server-id：

    ①mysql的同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定不同

    ② 每一个同步中的slave在master上都对应一个master线程，该线程就是通过slave的server-id来标识的；每个slave在master端最多有一个master线程，如果两个slave的server-id 相同，则后一个连接成功时，前一个将被踢掉。 这里至少有这么一种考虑：

     slave主动连接master之后，如果slave上面执行了slave stop；则连接断开，但是master上对应的线程并没有退出；当slave start之后，master不能再创建一个线程而保留原来的线程，那样同步就可能有问题；

    ③ 在mysql做主主同步时，多个主需要构成一个环状，但是同步的时候有要保证一条数据不会陷入死循环，这里就是靠server-id来实现的

[client]

port=3307

[mysql]
no-beep

default-character-set=utf8


# server_type=3
[mysqld]



# The TCP/IP Port the MySQL Server will listen on
port=3307

# 设置mysql的安装目录

basedir="C:/MySQL2"

# 设置mysql数据库的数据的存放目录，这个data目录如果不存在，需要自己手动在创建

datadir="C:/MySQL2/data/"


# Server Id.
server-id=2
#主库二进制日志的位置
log-bin=C:/MySQL2/mysql-bin.log

以下所有命令建议在管理员权限下执行

3、初始化：打开CMD窗口，跳转到MySQL2/bin路径下，执行命令初始化MySQL，这里初始化成功，就会自动生成data文件夹，而且CMD也会显示一个随机生成的MySQL密码，这里可以记住它，也可以在MySQL2/data/xxx.err这个err文件中查看。MySQL5.7以前的版本在安装时不需要执行这个初始化命令也可以。

mysqld --initialize --user=mysql --console

4、安装mysql：指定服务的名称MySQL2和配置文件my.ini的路径

mysqld install MySQL2 --defaults-file="C:\MySQL2\my.ini"

5、修改服务：win+r 输入regedit命令打开注册表，找到HKEY_LOCAL_MACHINE -> SYSTEM -> CurrentControlSet -> services -> MySQL2，可以看到imagePath参数值

"C:\MySQL Server 5.7\bin\mysqld" --defaults-file=C:\MySQL2\my.ini MySQL2

第一个mysqld的位置需要修改成你的新MySQL2服务的mysqld位置，如下

"C:\MySQL2\bin\mysqld" --defaults-file=C:\MySQL2\my.ini MySQL2

这里如果不修改，你的新MySQL服务也会一直无法启动！打开data文件夹下的xxx-slow.log文件
可以看到执行的还是原来的MySQL的mysqld这个配置文件

6、启动主库服务：

net start MySQL2

这时候如果启动MySQL服务失败，请先删除已安装的MySQL2服务：sc delete MySQL2（服务名称）。然后再看看上面的步骤，是哪一个配置没有按照步骤来修改。启动成功，就算是生成MySQL多实例了。这时候包括原来的MySQL Server服务，已经有两个了，端口号分别为3306、3307。然后我们就需要修改密码，这时候MySQL2的密码有3种可能：
1、密码丢失：使用工具连接mysql服务的时候账号名是root，密码就不用填。如果要新密码就先登录mysql，然后执行命令

set password=password(“新密码”);

2、随机生成了新密码：在MySQL安装目录下的data文件夹的err文件最后几行就可以看到新密码，这是在前面执行MySQL初始化命令的时候随机生成的，如果你没有清空cmd，也可以在CMD记录中看到，然后修改密码即可
3、密码过期：在MySQL安装目录下的data文件夹的err文件最后几行可以看到密码过期的信息，这时候只能重新设置密码
重设密码原理：跳过数据库的用户验证，直接Update更新mysql数据库的user表里的密码字段，具体步骤：
①管理员模式打开cmd窗口到MySQL2/bin目录下先停止服务net stop MySQL2
②输入命令  mysqld --skip-grant-tables  回车，此时就跳过了mysql的用户验证。注意输入此命令之后命令行就无法操作了，此时可以再打开一个新的cmd窗口（记为cmd2）
③cmd2切换到MySQL2/bin目录下，然后直接输入mysql，不需要带任何登录参数直接回车就可以登陆上数据库
④输入 use mysql;   选择mysql数据库。
⑤更改root密码
MySQL5.7版本以前：输入

update user set password=password('你的新密码') where user='root' and host='localhost';

MySQL5.7版本以后：输入

update mysql.user set authentication_string=PASSWORD("你的新密码") where user="root" and host="localhost"；

因为MySQL新版本用于存用户密码的字段名为authentication_string而不是 password，且新密码必须使用PASSWORD("xxxx")函数进行加密。以上所有请在管理员模式下执行

7、配置从库，记为MySQL3，它的my.ini配置如下：

[client]

port=3308

[mysql]
no-beep

default-character-set=utf8


# server_type=3
[mysqld]



# The TCP/IP Port the MySQL Server will listen on
port=3308

# 设置mysql的安装目录

basedir="C:/MySQL3"

# 设置mysql数据库的数据的存放目录，这个data目录如果不存在，需要自己手动在创建

datadir="C:/MySQL3/data/"


# Server Id.
server-id=2
#从库不需要这个配置文件
#log-bin=C:/MySQL2/mysql-bin.log

然后其他安装步骤跟主库一样。

8、连接MySQL主从库：通过Navicat Premium等工具连接了MySQL2、MySQL3，3307端口的记为3307master，3308的记为3308slave，test是我用来测试主从复制功能是否正常的测试数据库。如下图：

要想主从复制，就要数据库同构，即主从数据库3307master、3308slave所有的数据库名称一致。如果你发现从库中不包含主库同名数据库，这时候就直接新建同名数据库就可以了。

关于在主库中配置binlog-do-db=dbname（要同步的数据库名字） ：我并没有在主库的my.ini中配置这一项，只是手动在主从库创建了相同的数据库（即我要让它主从复制的数据库），然后MySQL也可以正确执行主从复制，它默认应该是从库全部复制（未验证）

9、设置主库：3307master主库中test数据库（第8步创建的测试库），新建查询，完成对从库的授权功能，SQL语句如下

#REPLICATION复制的权限  给  localhost（IP），@左边的是用户名，右边是密码
GRANT REPLICATION SLAVE ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root'

查看主库的状态

show MASTER STATUS

10、设置从库：3308slave从库中test数据库（第8步说了这个是测试库），新建查询，完成对主库的跟从功能，SQL语句如下
配置主库的相关信息，log_file和log_pos是show master status（第9步图中标有）显示出来的

CHANGE MASTER TO
master_host='localhost',master_port=3307,master_user='root',master_password='root',
master_log_file='mysql-bin.000004',master_log_pos=5475

启动从库：

start slave

查看从库的状态

show slave status

主要看几点：slave_sql_running是否YES，slave_io_running是否YES，log_file、log_pos、master_port是否和主库对应

11、查看主库3307master目前拥有的从库

show slave hosts;

主要看显示出来的数据是否和从库对应

12、如果这时候全部正常，那说明主从复制成功了

但是，因为每个人机器环境不一样，可能主从复制仍然失败。即主库新建表更新数据之后，从库没有实时跟着更新数据。经测试，等待几分钟，从库有概率跟着更新部分数据，具体我也不知道为什么。
解决办法：电脑重启。讲道理只要重启MySQL2、MySQL3服务（主从服务）就可以了。可是经过测试，我重启电脑才解决问题。

13、切记不要在从库进行写操作，会导致slave_sql_running变成了NO，主从复制失败。原因：主库的postion变了，从库没有跟着变

解决办法：
（1）、3308slave执行以下命令

slave stop; 
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
slave start;

通过show slave status命令发现，slave_sql_Runing=NO,还是没有解决，这时候就需要重新连接配置从服务。
（2）、重新配置：
3308slave从库中执行：stop slave;
3307master主库中执行：show MASTER STATUS，记录postion和log-file
最后3308slave从库中执行：

CHANGE MASTER TO
master_host='localhost',master_port=3307,master_user='root',master_password='root',
master_log_file='主库的文件名',master_log_pos=主库显示的数字

再次通过show slave status;命令发现，slave_sql_Runing=YES,这时候主从复制应该成功了，但还是有概率失败，这时候请看第三步
（3）、再次配置：第一次主从复制，需要主从库达到同构状态（即所拥有的数据库和数据都一样），所以先stop slave;然后手动同步主库数据到从库。再次执行配置2，即可成功。

建议：在确保主从复制成功之后，断开从库数据库连接，这样子就不会不小心操作到了，而且从库也会自动更新数据。