主从复制

2015年5月28日11时,12小时后恢复,损失:平均每小时106.48W$
1)高可用
2)辅助备份
3)分担负载
复制是 MySQL 的一项功能,允许服务器将更改从一个实例复制到另一个实例。
1)主服务器将所有数据和结构更改记录到二进制日志中。
2)从属服务器从主服务器请求该二进制日志并在本地应用其内容。
3)IO:请求主库,获取上一次执行过的新的事件,并存放到relaylog
4)SQL:从relaylog中将sql语句翻译给从库执行
二.主从复制原理
主从复制的前提
1)两台或两台以上的数据库实例
2)主库要开启二进制日志
3)主库要有复制用户
4)主库的server_id和从库不同
5)从库需要在开启复制功能前,要获取到主库之前的数据(主库备份,并且记录binlog当时位置)
6)从库在第一次开启主从复制时,时必须获知主库:ip,port,user,password,logfile,pos
IP:10.0.0.51 Port:3306 User:rep Password:oldboy123 logFile:mysql-bin.000002 Pos:120
7)从库要开启相关线程:IO、SQL
8)从库需要记录复制相关用户信息,还应该记录到上次已经从主库请求到哪个二进制日志
9)从库请求过来的binlog,首先要存下来,并且执行binlog,执行过的信息保存下来
主从复制涉及到的文件和线程
主库:
1)主库binlog:记录主库发生过的修改事件
2)dump thread:给从库传送(TP)二进制日志线程
从库:
1)relay-log(中继日志):存储所有主库TP过来的binlog事件
2)master.info:存储复制用户信息,上次请求到的主库binlog位置点
3)IO thread:接收主库发来的binlog日志,也是从库请求主库的线程
4)SQL thread:执行主库TP过来的日志
原理
1)通过change master to语句告诉从库主库的ip,port,user,password,file,pos
2)从库通过start slave命令开启复制必要的IO线程和SQL线程
3)从库通过IO线程拿着change master to用户密码相关信息,连接主库,验证合法性
4)从库连接成功后,会根据binlog的pos问主库,有没有比这个更新的
5)主库接收到从库请求后,比较一下binlog信息,如果有就将最新数据通过dump线程给从库IO线程
6)从库通过IO线程接收到主库发来的binlog事件,存储到TCP/IP缓存中,并返回ACK更新master.info
7)将TCP/IP缓存中的内容存到relay-log中
8)SQL线程读取relay-log.info,读取到上次已经执行过的relay-log位置点,继续执行后续的relay-log日志,执行完成后,更新relay-log.info
主从复制搭建实战
主库操作:
1)修改配置文件
#编辑mysql配置文件 [root@db01 ~]# vim /etc/my.cnf #在mysqld标签下配置 [mysqld] #主库server-id为1,从库不等于1 server_id =1 #开启binlog日志 log_bin=mysql-bin
2)创建主从复制用户
#登录数据库 [root@db01 ~]# mysql -uroot -poldboy123 #创建rep用户 mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';
从库操作:
1)修改配置文件
#修改db02配置文件 [root@db02 ~]# vim /etc/my.cnf #在mysqld标签下配置 [mysqld] #主库server-id为1,从库不等于1 server_id =5 #重启mysql [root@db02 ~]# /etc/init.d/mysqld restart #记录主库binlog及位置点 mysql> show master status; #登陆数据库 [root@db02 ~]# mysql -uroot -poldboy123 #执行change master to 语句 mysql> change master to -> master_host='10.0.0.51', -> master_user='rep', -> master_password='oldboy123', -> master_auto_position=1;
四.主从复制基本故障处理
IO线程
连接主库
1)user password ip port
2)网络:不通,延时高,防火墙
请求binlog
1)binlog不存在或者损坏
更新relay-log和master.info
SQL线程 1)relay-log出现问题 2)从库做写入了
- 操作对象已存在(create)
- 操作对象不存在(insert update delete drop truncate alter)
- 约束问题、数据类型、列属性
处理方法一:
#临时停止同步 mysql> stop slave; #将同步指针向下移动一个(可重复操作) mysql> set global sql_slave_skip_counter=1; #开启同步 mysql> start slave;
处理方法二:
#编辑配置文件 [root@db01 ~]# vim /etc/my.cnf #在[mysqld]标签下添加以下参数 slave-skip-errors=1032,1062,1007
但是以上操作都是有风险存在的
处理方法三:
1)重新备份数据库,恢复到从库
2)给从库设置为只读
#在命令行临时设置 set global read_only=1; #在配置文件中永久生效 read_only=1
五.延时从库
普通的主从复制可能存在不足
1)逻辑损坏怎么办?
2)不能保证主库的操作,从库一定能做
3)高可用?自动failover?
4)过滤复制
企业中一般会延时3-6小时
延时从库配置方法
#停止主从 mysql>stop slave; #设置延时为180秒 mysql>CHANGE MASTER TO MASTER_DELAY = 180; #开启主从 mysql>start slave; #查看状态 mysql> show slave status \G SQL_Delay: 60 3.延时从库停止方法 #停止主从 mysql> stop slave; #设置延时为0 mysql> CHANGE MASTER TO MASTER_DELAY = 0; #开启主从 mysql> start slave;
思考问题:
总数据量级500G,正常备份去恢复需要1.5-2小时
1)配置延时3600秒
mysql>CHANGE MASTER TO MASTER_DELAY = 3600;
2)主库
drop database db;
3)怎么利用延时从库,恢复数据?
提示:
- 1、从库relaylog存放在datadir目录下
- 2、mysqlbinlog 可以截取relaylog内容
- 3、show relay log events in 'db01-relay-bin.000001';
处理的思路:
1)停止SQL线程
mysql> stop slave sql_thread;
2)截取relaylog到误删除之前点
- relay-log.info 获取到上次运行到的位置点,作为恢复起点
- 分析relay-log的文件内容,获取到误删除之前position
模拟故障处:
1)关闭延时
mysql -S /data/3308/mysql.sock mysql> stop slave; mysql> CHANGE MASTER TO MASTER_DELAY = 0; mysql> start slave;
2)模拟数据
mysql -S /data/3307/mysql.sock source /root/world.sql use world; create table c1 select * from city; create table c2 select * from city;
3)开启从库延时5分钟
mysql -S /data/3308/mysql.sock show slave status \G mysql>stop slave; mysql>CHANGE MASTER TO MASTER_DELAY = 300; mysql>start slave; mysql -S /data/3307/mysql.sock use world; create table c3 select * from city; create table c4 select * from city;
4)破坏,模拟删库故障。(以下步骤在5分钟内操作完成。)
mysql -S /data/3307/mysql.sock
drop database world;
5)从库,关闭SQL线程
mysql -S /data/3308/mysql.sock
stop slave sql_thread;
6)截取relay-log
起点: cd /data/3308/data/ cat relay-log.info ./db01-relay-bin.000002 283 终点: mysql -S /data/3308/mysql.sock show relaylog events in 'db01-relay-bin.000002' db01-relay-bin.000002 | 268047 mysqlbinlog --start-position=283 --stop-position=268047 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
恢复relay.sql
1)取消从库身份
mysql> stop slave;
mysql> reset slave all;
2)恢复数据
mysql> set sql_log_bin=0; mysql> source /tmp/relay.sql mysql> use world mysql> show tables;
六.半同步复制
从MYSQL5.5开始,支持半自动复制。之前版本的MySQL Replication都是异步(asynchronous)的,主库在执行完一些事务后,是不会管备库的进度的。如果备库不幸落后,而更不幸的是主库此时又出现Crash(例如宕机),这时备库中的数据就是不完整的。简而言之,在主库发生故障的时候,我们无法使用备库来继续提供数据一致的服务了。
半同步复制(Semi synchronous Replication)则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题,安全的考虑。
5.5 出现概念,但是不建议使用,性能太差 5.6出现group commit 组提交功能,来提升开启半同步复制的性能 5.7更加完善了,在group commit基础上出现了MGR 5.7的增强半同步复制的新特性:after commit; after sync;
半同步复制开启方法
1)安装(主库)
#登录数据库 [root@db01 ~]# mysql -uroot -poldboy123 #查看是否有动态支持 mysql> show global variables like 'have_dynamic_loading'; #安装自带插件 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so'; #启动插件 mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; #设置超时 mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000; #修改配置文件 [root@db01 ~]# vim /etc/my.cnf #在[mysqld]标签下添加如下内容(不用重启库) [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000 检查安装: mysql> show variables like'rpl%'; mysql> show global status like 'rpl_semi%';
2)安装(从库)
#登录数据库 [root@mysql-db02 ~]# mysql -uroot -poldboy123 #安装slave半同步插件 mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so'; #启动插件 mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1; #重启io线程使其生效 mysql> stop slave io_thread; mysql> start slave io_thread; #编辑配置文件(不需要重启数据库) [root@mysql-db02 ~]# vim /etc/my.cnf #在[mysqld]标签下添加如下内容 [mysqld] rpl_semi_sync_slave_enabled =1
注:相关参数说明 rpl_semi_sync_master_timeout=milliseconds 设置此参数值(ms),为了防止半同步复制在没有收到确认的情况下发生堵塞,如果Master在超时之前没有收到任何确认,将恢复到正常的异步复制,并继续执行没有半同步的复制操作。 rpl_semi_sync_master_wait_no_slave={ON|OFF} 如果一个事务被提交,但Master没有任何Slave的连接,这时不可能将事务发送到其它地方保护起来。默认情况下,Master会在时间限制范围内继续等待Slave的连接,并确认该事务已经被正确的写到磁盘上。 可以使用此参数选项关闭这种行为,在这种情况下,如果没有Slave连接,Master就会恢复到异步复制。
测试半同步
#创建两个数据库,test1和test2 mysql> create database test1; Query OK, 1 row affected (0.04 sec) mysql> create database test2; Query OK, 1 row affected (0.00 sec) #查看复制状态 mysql> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 768 | | Rpl_semi_sync_master_net_wait_time | 1497 | | Rpl_semi_sync_master_net_waits | 2 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 884 | | Rpl_semi_sync_master_tx_wait_time | 1769 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | #此行显示2,表示刚才创建的两个库执行了半同步 | Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+ 14 rows in set (0.06 sec)
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test1 | | test2 | +--------------------+ #关闭半同步(1:开启 0:关闭) mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0; #查看半同步状态 mysql> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 768 | | Rpl_semi_sync_master_net_wait_time | 1497 | | Rpl_semi_sync_master_net_waits | 2 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | OFF | #状态为关闭 | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 884 | | Rpl_semi_sync_master_tx_wait_time | 1769 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec) #再一次创建两个库 mysql> create database test3; Query OK, 1 row affected (0.00 sec) mysql> create database test4; Query OK, 1 row affected (0.00 sec) #再一次查看半同步状态 mysql> show global status like 'rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 768 | | Rpl_semi_sync_master_net_wait_time | 1497 | | Rpl_semi_sync_master_net_waits | 2 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 884 | | Rpl_semi_sync_master_tx_wait_time | 1769 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | #此行还是显示2,则证明,刚才的那两条并没有执行半同步否则应该是4 | Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
注:不难发现,在查询半同步状态是,开启半同步,查询会有延迟时间,关闭之后则没有
七.过滤复制
主库:
白名单:只记录白名单中列出的库的二进制日志
- binlog-do-db
黑名单:不记录黑名单列出的库的二进制日志
- binlog-ignore-db
从库:
白名单:只执行白名单中列出的库或者表的中继日志
- --replicate-do-db=test
- --replicate-do-table=test.t1
- --replicate-wild-do-table=test.t2
黑名单:不执行黑名单中列出的库或者表的中继日志
- --replicate-ignore-db
- --replicate-ignore-table
- --replicate-wild-ignore-table
复制过滤配置:
[root@db01 data]# vim /data/3307/my.cnf #在[mysqld]标签下添加 replicate-do-db=world #关闭MySQL mysqladmin -S /data/3307/mysql.sock shutdown #启动MySQL mysqld_safe --defaults-file=/data/3307/my.cnf &
测试复制过滤:
第一次测试:
1)主库:
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock mysql> use world mysql> create table t1(id int);
2)从库查看结果:
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> use world mysql> show tables;
第二次测试:
1)主库:
[root@db02 ~]# mysql -uroot -p123 -S /data/3308/mysql.sock mysql> use test mysql> create table tb1(id int);
2)从库查看结果:
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> use test mysql> show tables;
实例
主从复制
开胃菜:
主从复制: -提高并发 -减轻主库压力 -负载均衡 主库开启binlog: binlog3种工作模式: 1、语句模式 2、行级模式 3、混合模式:mixed 主从复制的先决条件: 1、要有两个以上的实例 2、开启server_id 主库server_id = 5 从库server_id = 5 从库之间server_id可以相同吗? 可以 3、主库上要有主从复制用户 4、主库要开启bing binlog3种工作模式: 1、语句模式:statment (默认) 2、行级模式 :row (企业常用) 3、混合模式:mixed 5、两个实例数据保持一致 -在binlog之前来一次全备 PS:从库机必须手动开启同步 - start slave; -这样才能时时同步主库 命令 ecgo $? 查看上一条命令是否执行成功 -成功 0 -失败: 不为 0
一、主从复制操作--(数据不一致)
1、开启两个实例 分别为: a1 、a2 2、设a1为主库,a2为从库 3、修改配置文件 -在a1中 -1、[root@oldboy ~]# cd /etc -2、[root@oldboy etc]# vim my.cnf -[mysqld]下: log-bin=mysql-bin binlog_format=row server_id=51 -3、[root@oldboy ~]# /etc/init.d/mysqld restart -在a2中 -1、[root@oldboy ~]# cd /etc -2、[root@oldboy etc]# vim /etc/my.cnf -[mysqld]下: -server_id =52 -3、[root@oldboy ~]# /etc/init.d/mysqld restart 4、记录位置点: -在a1中 -1、记录为支点 mysql> show master status; -2、创建主库用户 - mysql> grant replication slave on *.* to rep@'%' identified by '123'; 5、在从库中: 登录mysql数据库 mysql #先执行 change master to #再一个个执行: #主库IP master_host='10.0.0.51', #主库用户名 master_user='rep', #主库用户密码 master_password='123', #主库位置点 master_log_file='mysql-bin.000003', #主库位置点 起始位置 master_log_pos=120; ------------快捷--------------- change master to master_host='10.0.0.51', master_user='rep', master_password='123', master_log_file='mysql-bin.000001', master_log_pos=120; ------------------------------- PS:可能会碰到问题---主库位置点不正确 -解决: -stop slave; -reset slave; #更改主库位置点 ,主库位置点 起始位置 -change master to master_log_file='mysql-bin.000005(正确的)',master_log_pos=686; -start slave; -show slave status\G 开启slave 6、mysql> start slave; 开启IO线程 和SQL线程 查看slave 7、mysql> show slave status\G 可能碰到的错误: 1、IO线程是No(Slave_IO_Running: No) Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. 这种情况有两种解决方法: 1、 在主从库中: 删除 主从库 auth.cnf #切换目录 -[root@oldboy ~]# cd /usr/local/mysql/data #删除 -[root@oldboy data]# rm -fr auto.cnf #重启mysql -[root@oldboy data]# /etc/init.d/mysqld stop -[root@oldboy data]# /etc/init.d/mysqld start #重新连接mysql -[root@oldboy data]# mysql -uroot -p1 在从库中: 重启slave: -mysql> stop slave; -mysql> start slave; -mysql> show slave status\G 2、修改/usr/local/mysql/data/auto.cnf 下的 UUID -只要UUID不一样就行了。 -本质上和 第1中方法差不多,删除auto.cnf 后 重启mysql 会自动生成以个auto.cnf且里面UUID不一样。 2、IO线程是 Connecting Last_IO_Error: Last_IO_Error: error connecting to master 'rep@10.0.0.11:3306' - retry-time: 60 retries: 17 下面三个方法只要有一个报错都会导致线程IO是NO 解决方法: 一个个的试: 1.ping 主库IP 2.telnet 主库ip 主库端口 3.mysql -urep -p123 -h10.0.0.11 -可能碰到的错误: #用户@主机名 而不是IP -rep@'10.0.0.11'(password:YES) 这是 反向解析错误 -解决方法: 在配置文件中: -vim /etc/my.cnf -在[mysqld]下加上: -skip-name-reseolve 跳过反向解析 -全部ping通之后重新执行第5步,不过 参数 的变成比 ping通 的数据 -输完改正获得数据后执行 先关闭slave -stop slave; 在开启slave -start slave; 查看 -mysql> show slave status\G -完成。
PS:配置多个从库只需在新的从库中修改server-id就可以了
之后从库连主库使用 主从库用户:
[root@oldboy ~]# mysql -urep -p123 -h 10.0.0.11
二、主从库的数据一致
PS:从库机必须手动开启同步 - start slave; -这样才能时时同步主库
-1、在binlog之前 做一次全备!!! ------解决主库数据多余从库的问题 -全备操作: #在主库中 -[root@oldboy ~]# mysqldump -uroot -p1 -A --master-data=2 > /tmp/full.sql -[root@oldboy ~]# scp /tmp/full.sql 172.16.1.13:/tmp -确认:yes -密码:1 #在从库中: -1、mysql> stop slave; -2、mysql> source /tmp/full.sql -3、[root@oldboy ~]# vim /tmp/full.sql #拿到位置点 -CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=120; -4、mysql> reset slave; -5、mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=120; -6、mysql> start slave; -7、mysql> show slave staus\G -成功后可以看到: Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000013 Slave_IO_Running: Yes 这个时候主库不管写什么从库都会同步过去!!!(完成一个非常常见的主从复制)
解决从库数据多余主库数据
-解决思路: -把多余的数据删掉!!! -想彻底删除可以 初始化数据库 -初始化数据库: #停止数据库 -/etc/init.d/mysqld stop 删除data目录 -cd /usr/local/mysql -rm -fr data/ -cd scripts/ #初始化数据库 -./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data #重启mysql -/etc/init.d/mysqld start 完成。。。 查看新库 -mysql -show databases; -现在想做主从 -source /tem/full.sql 查看 -show databases; 这个时候和主库数据一模一样 -开启change -change master to #主库IP master_host='10.0.0.11', #主库用户名 master_user='rep', #主库用户密码 master_password='123', #主库位置点 master_log_file='mysql-bin.000003', #主库位置点 起始位置 master_log_pos=120; -start slave; -show slave status\G 完成。。。。
延迟从库
在从库中: -开启mysql -stop slave; #设置延迟180 -mysql> change master to master_Delay=180; -mysql> start slave; -mysql> show slave status\G -关闭延迟: -stop slave; #设置延迟0 -mysql> change master to master_Delay=0; -mysql> start slave; -mysql> show slave status\G