MySQL 的主从复制(windows版)
1.概要
1.1 配置过程
1.2 复制类型和半同步复制(可选)
1.3 简单测试
1.4主库故障及崩溃处理
1.5 从库故障及崩溃处理
- 配置过程
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,即先写入磁盘再同步。
-
- 重启主服务器
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
- 复制类型和半同步复制
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%';
- 简单测试
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
- 主库故障及崩溃处理
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是否同步更新