MySQL 的主从复制（windows版）

1.概要

1.1 配置过程

1.2 复制类型和半同步复制（可选）

1.3 简单测试

1.4主库故障及崩溃处理

1.5 从库故障及崩溃处理

1. 配置过程

步骤归纳（共2步）：

（1）配置主机（二进制日志(my.cnf)，创建并授权复制用户）

（2）配置从机（中继日志（my.cnf），连接（change master to ）并开启服务（start slave））

2.1修改主服务器my.ini

   [mysqld]
  log-bin=mysql-bin   //[必须]启用二进制日志
  server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

sync_binlog=1         //[可选]在主库写入数据的时候，事务已经提交，但是数据在binlog cache中，没有写入到磁盘，并且数据已经同步到从库，此刻主库宕机，binlog cache中数据丢失，导致从库的日志比主库多。重新配置主从之后，并将sync_binlog设置为1，即先写入磁盘再同步。

 

1. 1. 重启主服务器

net stop mysql,net start mysql

2.3  在主服务器上建立帐户并授权slave:

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';

账号：slave

密码：123456

2.4  登录主服务器的mysql，查询master的状态

show master status;

2.5  修改从服务器my.ini

log_bin          = mysql-bin
server_id         = 2
relay_log         = mysql-relay-bin  //[必须]从库中继日志，从库根据它进行变更操作
log_slave_updates = 1
read_only         = 1

log_slave_updates表示slave将复制事件写进自己的二进制日志(测试后可行，很有用)。有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并是很实用，特别是那些需要在slave上创建表的应用。

2.6重启从服务器

net stop mysql,net start mysql

2.7配置从服务器Slave

CHANGE MASTER TO MASTER_HOST='192.168.1.59',

     MASTER_USER='slave',

      MASTER_PASSWORD='123456',

      MASTER_LOG_FILE='mysql-bin.000001',

      MASTER_LOG_POS=0;

注：MASTER_LOG_POS的值为0，是日志的开始位置。本例中偏移量应该为720.

2.8启动从服务器

start slave;    //启动从服务器复制功能

2.9显示从服务器状态

show slave status\G

1. 复制类型和半同步复制

3.1复制类型

-- 查看binlog的复制类型

SHOW VARIABLES LIKE '%binlog%format%'

-- binlog的复制类型设置

SET binlog_format = 'MIXED'    --三种类型 'STATEMENT' 'MIXED' 'ROW'

使用‘statement’, binlog日志中显示语句；而’row’显示表id。

3.2半同步复制

 

3.2.1判断主和从数据库是否支持动态添加插件

SELECT  @@have_dynamic_loading（应该返回yes）

3.2.2在主库安装semisync_master插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.dll'; //windows

3.2.3主库配置my.ini

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

3.2.4重启主库

3.2.5 查看结果

show variables like '%rpl_semi%';

show status like '%semi%';

3.2.6在备库安装semisync_slave插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.dll';//windows

3.2.7从库配置my.ini

rpl_semi_sync_slave_enabled=1

3.2.8重启从库

3.2.9 查看结果

show variables like '%rpl_semi%';

show status like '%semi%';

 

1. 简单测试

4.1主库操作语句

CREATE DATABASE hi_db;

USE hi_db;

CREATE TABLE hi_tb(id INT(3),NAME CHAR(10));

INSERT INTO hi_tb VALUES(002,'哈哈哈');

SHOW DATABASES;

4.2从库查询语句

USE hi_db;

Select  * from  hi_db

1. 主库故障及崩溃处理

5.1主库宕机处理

5.1.1  等待从库更新完毕

确保所有的relay log全部更新完毕，在每个从库上执行stop slave io_thread; show processlist;直到看到Has read all relay log,则表示从库更新都执行完毕了

5.1.2 查看从库relay-log最新更新位置

如下最新的relay-log已经更新主库bin-log的mysql-bin.000004 的position900的位置。

 

5.1.3关闭从服务器

stop slave;    //关闭服务器

5.1.4重新配置从服务器Slave

CHANGE MASTER TO MASTER_HOST='192.168.1.59',

     MASTER_USER='slave',

      MASTER_PASSWORD='123456',

      MASTER_LOG_FILE='mysql-bin.000004',

      MASTER_LOG_POS=990;

5.1.5启动从服务器

start slave;   

5.2主库-主库切换

将主主(3307写--3308读)切换 
前提：3307宕机 

5.2.1 修改3308配置文件。my.ini

# 在[mysqld]中删除 
# read_only 

5.2.2重新启动mysqld3308。
 net stop mysql,net start mysql

5.2.3检查主库的sql是否在从库上都执行完。mysql客户端操作 
#3308执行:SHOW PROCESSLIST; 
其中一个user='system user'的线程 
如果State值为"Reading event from the relay log",代表正在执行主库同步的中继日志sql 
如果State值为"Slave has read all relay log; waiting for the slave I/O thread to update it"，代表已经执行完主库同步的中继日志sql 
5.2.4业务连接3308 
5.2.5、3307修复完成后 
# 修改3307配置文件 my.ini 增加 
read_only 
5.2.6重新启动服务 

 net stop mysql,net start mysql

5.3主从切换

master服务器：192.168.1.112

slave服务器1:192.168.1.102

slave服务器2:192.168.1.104

 

5.3.1  确保所有的relay log全部更新完毕，在每个从库上执行stop slave io_thread; show processlist;直到看到Has read all relay log,则表示从库更新都执行完毕了

5.3.2登陆所有从库，查看relay-log文件，对比选择pos最大的作为新的主库，这里我们选择192.168.1.102为新的主库

5.3.3 登陆192.168.1.102，执行stop slave; 并进入数据库目录，删除relay-log.info文件, 配置my.ini文件，开启log-bin,如果有

relay_log,log-slaves-updates和read-only则要注释掉，

执行reset master。

5.3.4.创建用于同步的用户并授权slave，同2.3

5.3.5.登录另外一台从库，执行stop slave停止同步

5.3.6.连接到新的主库,同2.7

5.3.7.执行start slave;

5.3.8.修改新的master数据，测试slave是否同步更新