Window平台MYSQL实现主从复制

1.MySQL主从复制介绍

MySQL数据库支持单向、双向、链式级联、环状等不同业务场景的复制。在复制过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL，重新更新到从数据库，使得主从服务器的数据达到一致。

如果设置了链式级联复制，那么从服务器本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器，链式级联复制类似A—>B—>C的复制形式。在这里，只介绍主—>从形式的配置方案。至于互为主从，链式级联的以后有机会再讨论。

MySQL的主从复制的主要场景有以下几个：

1）从服务器作为主服务器的实时数据备份

2）主从服务器实现读写分离（主写从读），从服务器实现负载均衡

3）把多个从服务器根据业务重要性进行拆分访问（从服务器根据业务进行拆分）

2.MySQL主从复制原理介绍

MySQL的主从复制是一个异步的复制过程（一般情况下感觉是实时的），数据将从一个MySQL数据库（Master）复制到另外一个MySQL数据库（Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和I/O线程）在Slave端，另外一个线程（I/O线程）在Master端。

要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave端从Master端获取binlog日志，然后再在Slave上以相同的顺序执行获取的binlog日志中所记录的各种SQL操作。

要打开MySQL的binlog记录功能，可以通过在MySQL的配置文件my.cnf中的mysqld模块（[mysqld]标识后的参数部分）增加"log-bin"参数来实现。

3.配置MySQL主从复制

本次实验的主从复制有单向的主从复制，一个Master和一个Slave。两个MySQL服务器都按照之前的方法搭建好了，并且也都开放了对应的端口。

在本机上有三个数据库

MySQL5.53306

MySQL5.73307

MySQL8.03308

使用MySQL5.5作为master，MySQL5.7作为slave1, MySQL8.0作为slave2

①配置Master信息

添加server_id和log-bin等参数，保存退出之后重启数据库

Master的my.ini配置文件信息如下

其他参数配置说明

检查主库配置信息，登陆主数据库，

使用show variables like 'server_id';命令检查参数的情况，看到log-bin已经打开。

使用show variables like 'log_bin';命令

②在主服务器的mysql命令行里为从机赋予权限

使用create user 'repl'@'127.0.0.1' identified by 'asdf';命令在主库上建立用于主从复制的账号

使用grant replication slave on *.* to 'repl'@'127.0.0.1';命令

使用flush privileges;

使用select user,host from mysql.user where user='repl';命令检查账号建立成功

#显示主服务器的状态信息，并且找到File和Position的值记录下来；

mysql>show master status \G;

（#此处加\G的意思是格式化输出，否则输出乱七八糟，看不清楚）

③保持主从mysql的test数据库初始状态一致

一般是先将所有的表加读锁，然后copy磁盘上的数据库文件夹。我这里直接停止服务，然后将数据文件拷贝过去。

④配置slave信息

slave1的my.ini配置文件信息

其他参数配置说明

注意：

1) server-id这一项需要认真检查，一定不能和主服务器冲突了，不然到时候会出现莫民其妙的问题，因为同步的时候会会根据server-id做判断，如果server-id一样就不进行同步了，不然可能会导致死循环(主主同步或者环状同步的时候)。

2)要使用replicate-wild-ignore-table参数，而不是用replicate-do-db或者replicate-ignore-db来过滤需要同步的数据库和不需要同步的数据库。这里有几个原因：

A. replicate-wild-ignore-table参数能同步所有跨数据库的更新，比如replicate-do-db或者replicate-ignore-db不会同步类似

use mysql;

UPDATE test.aaa SET amount=amount+10;

B. replicate-wild-ignore-table=mysql.%在以后需要添加同步数据库的时候能方便添加而不需要重新启动从服务器的数据库。因为以后很可能需要同步其他的数据库。

3) auto_increment_increment和auto_increment_offset参数，这两个参数一般用在主主同步中，用来错开自增值,防止键值冲突。

4) --slave-skip-errors参数，不要胡乱使用这些跳过错误的参数，除非你非常确定你在做什么。当你使用这些参数时候，MYSQL会忽略那些错误，这样会导致你的主从服务器数据不一致。

Mysql命令行登入命令

mysql -h localhost -u root -p 123456（命令行下当Mysql没设置端口为3306情况下使用）

mysql -h localhost -P端口–u用户名–p密码（注意-P为大写）

⑤进行slave其他关联配置

change master tomaster_host='127.0.0.1', master_port=3306, master_user='root',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=419;

配置值的对应关系

⑥重启mysql服务，查看从机配置

mysql>show slave status \G;查看状态

如果显示Slave_IO_Running:Yes

Slave_SQL_Running:Yes

以上两项都为Yes，那说明没问题了。

如果显示不能对slave进行修改时，需要先停止slave：stop slave，完成修改之后再启动slave。

使用start slave;命令启动slave

同理，Slave2同样相同的配置方法

配置master访问权限

create user 'repl2'@'127.0.0.1' identifiedby 'asdf';

grant replication slave on *.* to'repl2'@'127.0.0.1';

配置Slave2信息

使用mysql -h localhost -P3308 -uroot –proot命令登录Slave2

关联设置

change master tomaster_host='127.0.0.1',master_port=3306,master_user='repl2',master_password='asdf',master_log_file='mysql-bin.000002',master_log_pos=6368;

这样，一主两从配置完成。主库数据变化时，从库会相应变化。

4.管理MYSQL主从同步的命令

①停止MYSQL同步

STOP SLAVE IO_THREAD; #停止IO进程

STOP SLAVE SQL_THREAD; #停止SQL进程

STOP SLAVE; #停止IO和SQL进程

②启动MYSQL同步

START SLAVE IO_THREAD; #启动IO进程

START SLAVE SQL_THREAD; #启动SQL进程

START SLAVE; #启动IO和SQL进程

③重置MYSQL同步

RESET SLAVE;

用于让从属服务器忘记其在主服务器的二进制日志中的复制位置,它会删除master.info和relay-log.info文件，以及所有的中继日志，并启动一个新的中继日志,当你不需要主从的时候可以在从上执行这个操作。不然以后还会同步，可能会覆盖掉你的数据库，我以前就遇到过这样傻叉的事情。哈哈！

④查看MYSQL同步状态

SHOW SLAVE STATUS;

这个命令主要查看Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、Last_IO_Error、Last_SQL_Error这些值来把握复制的状态。

⑤临时跳过MYSQL同步错误

遇到主从同步遇到错误的时候，比如一个主键冲突等，那么我就需要在确保那一行数据一致的情况下临时的跳过这个错误，那就需要使用SQL_SLAVE_SKIP_COUNTER = n命令了，n是表示跳过后面的n个事件,比如我跳过一个事件的操作如下：

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

⑥从指定位置重新同步

有的时候主从同步有问题了以后，需要从log位置的下一个位置进行同步，相当于跳过那个错误，这时候也可以使用CHANGE MASTER命令来处理，只要找到对应的LOG位置就可以,比如：

CHANGE MASTER TOMASTER_HOST='10.1.1.75',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=106;

START SLAVE;

5.MYSQL主从同步的管理经验介绍

1.不要乱使用SQL_SLAVE_SKIP_COUNTER命令。

这个命令跳过之后很可能会导致你的主从数据不一致，一定要先将指定的错误记录下来，然后再去检查数据是否一致，尤其是核心的业务数据。

2.结合percona-toolkit工具pt-table-checksum定期查看数据是否一致。

3.使用replicate-wild-ignore-table选项而不要使用replicate-do-db或者replicate-ignore-db。

原因已经在上面做了说明。

4.将主服务器的日志模式调整成mixed。

5.每个表都加上主键，主键对数据库的同步会有影响尤其是居于ROW复制模式。