9~10.mysql数据库的主从复制深度经验&增量恢复知识实践

=============================
1.mysql主从复制原理画图

=============================
2.企业面试必胜绝技

=============================
3.mysql数据库主从复制故障多个案例

==============================
4.MYSQL主从同步配置步骤：
1、准备两台数据库环境，或者单台多实例环境，能否正常启动和登录。
2、配置my.cnf文件，主库配置log-bin和server-id参数，从库配置 server-id不能和主库及其他从库一样，一般不开启从库log-bin功能。
注意:配置参数后要重启生效。
3、登录主库增加用于从库连接主库同步的账户例如:rep，并授权replication slave同步的权限。
4、登录主库，整库锁表 flush table with read lock（窗口关闭后即失效,超时参数到了也失效）；然后show master status查看binlog的位置状态。
5、新开窗口，linux命令行备份或导出原有的数据库数据，并拷贝到从库所在的服务器目录。
如果数据量很大，并且允许停机，可以停机打包而不用 mysqldump。
6、解锁主库，unlock tables。
7、把主库导出的原有数据恢复到从库。
8、根据主库的show master status查看binlog的位置状态，在从库执行change master to...语句。
9、从库开启同步开关,，start slave。
10、从库show slave status\G，检查同步状态，并在主库进行更新测试。

=============================
5.mysql主从复制原理要点：
<1>异步方式同步；
<2>逻辑同步模式，多种模式，默认是通过SQL语句执行；
<3>主库通过记录binlog实现对从库的同步，binlog记录数据库的更新语句；
<4>主库1个IO线程，从库由1个IO线程和一个SQL线程来完成；
<5>从库关键文件master.info, relay-log, relay-info功能。
注释：
master.info记录了change master的所有信息；
relay-log是中继日志，记录了主库IO线程发过来的binlog日志；
relay-info是SQL线程把binlog日志应用到数据库里面的一个位置点。
<6>如果从库还想级联从库，需要打开log-bin和log-slave-updates参数。

==============================
6.生产场景快速配置mysq主从复制方案
1、安装好要配置从库的数据库,配置好log-bin和server-d参数。
2、无需配置主库mycnf文件,主库的log-bin和 server-id参数默认就是配置好的。
3、登录主库增加用于从库连接主库同步的账户例如:rep.并授权 replication slave同步的权限。
4、使用半夜 mysqldump带 Mastel-data=1备份的全备数据恢复到从库。
5、在从库执行 change master to..语句,无需 binlog文件及对应位置点。
6、从库开启同步开关, start slave
7、从库 show slave status g,检查同步状态,并在主库进行更新测试。

=============================
7.一键获取全备及binlog位置多种方案
（1）生产环境中主从库同步配置注意事项：
a.申请设备资源，用来做从库服务器；
b.撰写方案文档和实施步骤；
假如服务器只有主库而且已经跑了生产线上应用了，现在由于业务需要第一次做从库，此时可能需要和公司申请停机维护时间（要确定这个时间段内可以做一次全备），即在用户访问量最小且不影响内部其他业务运转的时间点来停机（包括锁表）配置主从复制，一般都是凌晨进行。
注意：停机（锁表，停库）的最小时间段，为锁表后备份的时间，也就是说无需等待主从配置好；当然也可以不申请停机时间，在定时任务备份时，每天的夜里的定时备份做一些措施即可实现。
（2）无需熬夜在工作时间轻松配置从库；
当然也可在不申请停机时间，在定时任务备份时，每天的夜里服务压力小时刻的定时备份时做一些措施即可。
如：模拟主从同步的步骤，获取到全备及全备过程中binlog位置的信息或者直接用--master-data参数解决。
脚本1,2,3方法：
[root@oldboyedu-01 script]# cat mysql-bak.sh
#!/bin/bash

MYUSER=root
MYPASS="oldboy123"
MYSOCK=/data/3306/mysql.sock
MAIN_PATH=/service/script
DATA_PATH=/service/script
LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
MYSQL_PATH=/application/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"
MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B --master-data=1 --single-transaction -e"
${MYSQL_DUMP}|gzip >$DATA_FILE;

有了mysql_backup_2012-04-17.sql.gz和mysqllogs_2012-04-17.log内容做从库指日可待。
次日早上恢复即可。

配置从库重点：
<1>导入全备
<2>change master

=============================
8.不停主库一键批量创建主库
脚本实现的几种方法。

mysql线程同步状态用途：
通过mysql线程同步状态查看数据库同步是否完成，用于主库宕机或者人工数据库主从迁移等；主库宕机选择最快的从库提升为主，就需要查看，当然也可以利用mysql的半同步功能，选择固定的库提升为主。
说明：及时查看，mysql线程同步状态，好些个状态说明在mysql手册有，视频也有。

=============================
10.mysql主从复制读写分离授权多种方案（生产场景）
当配置好mysql主从复制后，所有对数据库内容的更新就必须在主服务器上进行。
因为数据复制是单向的，只有在主库上更新，才能避免用户对主服务器上数据库内容的更新与从服务器上数据库内容的更新一致，而不会发生冲突！
（1）生产mysql复制环境用户授权方案

生产授权方案<1>：
主库：web oldboy123 10.0.0.1 3306 (select,insert,delete,update)
从库：主库的web用户同步到从库，然后回收insert,delete,update权限。
也可采用不回收从库权限，设置非root的my.cnf配置文件中的read-only参数确保从库只读。
开发：
web oldboy123 10.0.0.1 3306 (select,insert,delete,update)
web oldboy123 10.0.0.2 3306 (select)

生产授权方案<2>：
主库：web_w oldboy123 10.0.0.1 3306 (select,insert,delete,update)
从库：web_r oldboy123 10.0.0.2 3306 (select)
风险：web_w连接从库！设置非root的my.cnf配置文件中的read-only参数确保从库只读。
开发：多套用户密码不专业。

生产授权方案<3>：
不同步mysql库，主从库分别进行如下授权：
主库：web oldboy123 10.0.0.1 3306 (select,insert,delete,update)
从库：web oldboy123 10.0.0.2 3306 (select)
缺陷：从库切换主库时，连接用户权限问题。保留一个从库专门准备接替主库。

=============================
11.忽略mysql主从复制主库授权表同步
忽略授权表的方式：
[root@oldboyedu-01 ~]# ps -ef|grep mysql
[root@oldboyedu-01 ~]# netstat -lntup|grep 330
[root@oldboyedu-01 ~]# vim /data/3306/my.cnf
replicate-ignore-db=mysql
#20180208 add
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema

[root@oldboyedu-01 ~]# grep binlog /data/3306/my.cnf
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema

配置my.cnf文件后需要重启生效：
[root@oldboyedu-01 ~]# /data/3306/mysql stop
Stoping MySQL...
[root@oldboyedu-01 ~]# /data/3306/mysql start
Starting MySQL...
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
mysql> create user oldgirl111@'172.%' identified by 'oldgirl123';
mysql> flush privileges;
mysql> select user,host from mysql.user;
+------------+--------------+
| user | host |
+------------+--------------+
| rep | 10.0.0.% |
| root | 127.0.0.1 |
| oldgirl | 172.% |
| oldgirl111 | 172.% | //有了！

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock
mysql> select user,host from mysql.user; //没有啦！
+---------+--------------+
| user | host |
+---------+--------------+
| rep | 10.0.0.% |
| root | 127.0.0.1 |
| oldgirl | 172.% |
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.200
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000004
Read_Master_Log_Pos: 298
Relay_Log_File: relay-bin.000011
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql3306-bin.000004
Slave_IO_Running: Yes //
Slave_SQL_Running: Yes //

=============================
12.mysql主从复制指定不同库表同步参数说明

=============================
13.mysql主从复制从库只读案例实践及原理总结
通过read-only参数防止数据写从库方法：

[root@oldboyedu-01 ~]# vim /data/3307/my.cnf
[mysqld]
#20180208 add
read-only
[root@oldboyedu-01 ~]# /data/3307/mysql stop
[root@oldboyedu-01 ~]# /data/3307/mysql start
mysql> use oldboy
mysql> create table t(id int);
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| t |

mysql> grant select,insert,update,delete on *.* to 'ruirui'@'localhost' identified by 'ruirui123';
mysql> flush privileges;
mysql> select user,host from mysql.user;
| ruirui | localhost |
mysql> quit
Bye

[root@oldboyedu-01 ~]# mysql -uruirui -pruirui123 -S /data/3307/mysql.sock
mysql> select user();
+------------------+
| user() |
+------------------+
| ruirui@localhost |
+------------------+
1 row in set (0.00 sec)

mysql> use oldboy
Database changed
mysql> insert into t values(2);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

=============================
14.企业场景mysql主从复制故障原因及实践
模拟错误：模拟重现故障能力是运维人员最重要的能力！
　　
故障一：mysql从库数据库冲突导致同步停止
mysql> show slave status\G
Slave_IO_Running: Yes //
Slave_SQL_Running: No //

mysql> create database butongbu;
mysql> create database butongbu;
ERROR 1007 (HY000): Can't create database 'butongbu'; database exists
mysql> 尴尬了！
否则，根据错误代码可以：
法一：
mysql> show variables like "sql_slave_skip_counter";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| sql_slave_skip_counter | 0 |
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;

mysql> show slave status\G
　　
法二：根据错误号跳过指定的错误
[root@oldboyedu-01 ~]# grep "slave-skip-errors" /data/3307/my.cnf
slave-skip-errors = 1032,1062， 1007 <==一般由于入库重复导致的失败就可以忽略。

=============================
15~16.从库开启binlog原因及开启实践
让mysql从库记录binlog方法：
需要记录binlog的情况：
a.当前从库还要作为其他从库的主库（级联同步）。
b.把从库作为数据库备份服务器需要开启binlog。

[root@oldboyedu-01 ~]# vim /data/3307/my.cnf //添加
log-bin = /data/3307/mysql-bin
log-slave-updates
expire_logs_days = 7
重启服务生效！
[root@oldboyedu-01 ~]# ll /data/3307/

=============================
17~19.企业场景-一主多从宕机从库切换主库和从同步
场景：主库宕机（数据库宕机或者服务器宕机）
登录从库，查看两个线程的更新状态：
mysql> show slave status\G
Slave_IO_State: Waiting for master to send event
说明主从已经同步了。最新的。

通过master.info确定新主库，登录众多从库中分别查看：
[root@oldboyedu-01 ~]# cat /data/3306/data/master.info
[root@oldboyedu-01 ~]# cat /data/3307/data/master.info
18
mysql3306-bin.000004 //
381
10.0.0.200
rep
oldboy123
3306
60
0
说明：确保更新完毕，越大说明越新，看看从库中哪个最快，从库与主库一致性更高，丢的数据越少，甚至一致。选择POS最大的作为主库，例如：32 3306
或者选择利用半同步功能，直接选择做了实时同步的这个从库。太子位置。
授权同步用户和主库一样。

<1>确保所有relay log全部更新完毕。
在每个从库上执行stop slave io_thread;show processlist;
直到看到has read all relay log;表示从库更新都执行完毕。
<2>登录从库：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
stop slave;
reset master;
quit;
<3>进到数据库数据目录，删除master.info relay-log.info
检查授权表，类似read-only参数等。
<4>32 3306 提升从库为主库
[root@oldboyedu-01 ~]# vim /data/3306/my.cnf
开启：log-bin = /data/3306/mysql-bin
如果存在log-slave-updates read-only等一定注释掉它。
/data/3306/mysql restart
到此位置，提升主库完毕。
<5>如果主库宕机但是服务器还能起来，需要从主库拉取binlog补全提升主库的从库。

<6>其他从库操作：
stop slave;
change master to master_host = '192.168.1.32';//如果不同步，就指定位置点。
start slave;
mysql> show slave status\G
--------------------------主库宕机切换成功。

<7>修改程序配置文件从主数据库35指向32。
平时访问数据库用域名，则直接可以修改hosts解析。

<8>修理损坏的主库，完成后作为从库使用，或者切换。
提示：更多恢复信息，参考mysql手册，第六章的FAQ，本文档结尾附录有。

如果没有任何宕机，我们有计划的切换，怎么做？
a.主库锁表。
b.登录所有的库查看同步状态，是否完成。
其他与前面叙述一样。

mysql5.5以上支持半同步，一个插件。
一主多从主库宕机如何恢复，通过master.info确定新主库。
半同步下的一主多从恢复，直接对设置半同步的从库确定为主库。
让某一个稳定从库和主库完全一致，即主库和这个从库都更新数据完毕，在返回给用户更新成功。
优点：确保至少一个从库和主库数据一致。
缺点：主从之间网络延迟，或者从库有问题时候，用户体验很差，当然可以设置超时时间，10秒。

从库的slave宕机
恢复方法：重做slave
直接灌数据：
stop slave;
gzip -d oldboy_date_5.sql.gz //一个小时
mysql -uroot -p'oldboy123' -S /data/3307/mysql.sock < oldboy_data_5.sql & //约2小时30分钟
change master to master_host='192.168.1.35',master_user='repl',
master_password='slavepass',master_log_file='mysql-bin.001440',
master_log_pos=68824;

=============================
20.企业场景-双主及多主同步过程和企业选择方案
双主同步互为主从（不推荐）方式：
[root@oldboyedu-01 ~]# vim /data/3306/my.cnf
#添加：
auto_increment_increment = 2
auto_increment_offset = 1
log-sl
ave-updates
log-bin

[root@oldboyedu-01 ~]# vim /data/3307/my.cnf
#添加：
auto_increment_increment = 2
auto_increment_offset = 2
log-slave-updates
log-bin
库M1：1,3,5；M2：6,8,10。
说明：反向做主从同步，就可以双向写数据了。
使用主主前提：表的主键自增。
其他思路：通过程序指定ID写库M1：1,3,5；M2：2,4,6。

Heartbeat+DRBD+MySQL高可用架构方案与实施过程细节
http://blog.51cto.com/oldboy/1240412

=============================
21.全量及增量备份概念及优缺点

案例：某电商网站数据库特大故障解决
http://blog.51cto.com/oldboy/1431161

全量备份：
全量数据就是数据库中所有的数据；全量备份就是把数据库中所有的数据进行备份。
备份一个库：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -F -B oldboy|gzip >/opt/mysql_oldboy_bak_$(date +%F).sql.gz
备份所有库：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -F -B -A --events |gzip >/opt/mysql_all_bak_$(date +%F).sql.gz

增量备份：
增量数据是从上次全量备份之后到下次全备之前一个段的数据（即更新的新数据）。对于mysql来说，binlog日志就是mysql的增量数据。
　　

=============================
22.不同企业如何选择备份方案及备份需求恢复的场景分析
（1）
　　
（2）mysql的备份何时能派上用场？
<1>迁移或者升级数据库时。
<2>增加从库的时候。
<3>因为硬件或特殊异常情况，主库或从库宕机，主从可以互相切换，无需备份。
<4>人为的DDL,DML语句误删数据等操作，主从库没办法（所有库都会执行），此时需要备份。
<5>跨机房灾备时，需要备份到异地。

=============================
23.不同企业如何选择备份方案
什么情况下需要增量恢复？
我们在生产工作中一般常用一主多从的数据库架构，常见的备份方案是在某一个不对外服务的从库上开启binlog，然后实施定时全备份和实时增量备份。
增量恢复：利用二进制日志和全备进行的恢复过程。

（1）主或者从库宕机（硬件损坏）是否需要增量恢复？
不需要增量恢复，主库宕机只需要把其中一个同步最快的从库切换为主库即可。
主库宕机，只要选择更新最快的从库（master.info，或5.5半同步机制），提升为主库。
从库宕机，直接不用就好了（一般都会配置LVS负载均衡）。或者正常修复。
（2）人为操作数据库SQL语句破坏主库是否需要增量恢复？
在数据库主库内部命令行误操作，会导致所有的数据库（包括主从库）数据丢失，例如：在主库执行了drop database test;这样的删除语句，这时所有的从库也会执行这个drop database test;语句，从而导致所有的数据库上的oldboy库数据丢失。这样的场景是需要增量恢复的。
//企业里可以所有的delete语句替换为update语句处理防止删除。
（3）只有一个主库是否需要增量恢复？
如果公司只有一个主库的情况，首先应该做定时全量备份（一天每天一次）及增量备份（每隔1-10分钟对binlog日志做切割然后备份到其他服务器上，或者本地其他的硬盘里）或者写到网络文件系统（备份服务器）里。
如果不允许数据丢失，最好的办法就是做从库，通过drbd（基于硬盘块的）同步。

正常情况：
主从同步：除了分担读写分离压力外，还可以防止物理设备损坏数据丢失的恢复。
从库备份：在从库进行全量和增量备份方式的备份，可以防止人为对主库的误操作导致数据丢失。
确保备份的从库实时和主库是同步状态。

小结：一般由人为（或程序）逻辑的方式在数据库执行SQL语句等误操作才需要增量恢复，因为此时所有的从库也执行了误操作语句。

=============================
24.mysql数据库恢复的必要条件及恢复案例场景图解
mysql数据库恢复的必要条件：
<1>开启mysql log-bin日志功能：
[root@oldboyedu-01 ~]# grep log-bin /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
提示：主库和备份的从库都要开启binlog记录功能。
<2>存在mysql数据库全备。
小结：增量恢复的条件：存在一份全备加上全备后的时刻到出问题的所有增量binlog文件备份。
一般机械磁盘删除可以恢复，但是在数据库里删除就难办了。

　　
理想情况：用户方停止写入，锁表；或iptables；或暂停应用。

=============================
25.模拟企业数据丢失应用的案例场景

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
mysql> show variables like '%character_set%';

这里：
[root@oldboyedu-01 ~]# grep "log-bin" /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysql3306-bin
[root@oldboyedu-01 ~]# /data/3306/mysql stop
Stoping MySQL...
[root@oldboyedu-01 ~]# ls /data/3306/
data my.cnf mysql mysql_oldboy3306.err
[root@oldboyedu-01 ~]# /data/3306/mysql start
Starting MySQL...
[root@oldboyedu-01 ~]# ls /data/3306/
data my.cnf mysql mysql3306-bin.000001 mysql3306-bin.index mysqld.pid mysql_oldboy3306.err mysql.sock

[root@oldboyedu-01 ~]# mysqlbinlog /data/3306/mysql3306-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180209 0:03:22 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 180209 0:03:22 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ynR8Wg8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADKdHxaEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

半夜0:00全备：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -F -B oldboy|gzip >/opt/bak.sql.gz

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -F -B --master-data=2 oldboy|gzip >/opt/bak_$(date +%F).sql.gz
//一般备份之前也可锁表把master.info拷贝过去；加了--master-data=2就不用了。

模拟更新数据：半夜0:00到10:00写入数据：
mysql> insert into test(name) values('test4');
mysql> insert into test(name) values('test5');
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test2 |
| 4 | test4 |
| 5 | test5 |
+----+-------+

[root@oldboyedu-01 ~]# ls /data/3306/
data my.cnf mysql mysql3306-bin.000001 mysql3306-bin.index mysqld.pid mysql_oldboy3306.err mysql.sock
[root@oldboyedu-01 ~]# mysqlbinlog /data/3306/mysql3306-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180209 0:03:22 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 180209 0:03:22 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
ynR8Wg8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADKdHxaEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#180209 0:20:33 server id 1 end_log_pos 177 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1518106833/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 177
#180209 0:20:33 server id 1 end_log_pos 205 Intvar
SET INSERT_ID=4/*!*/;
# at 205
#180209 0:20:33 server id 1 end_log_pos 308 Query thread_id=3 exec_time=0 error_code=0
use `oldboy`/*!*/;
SET TIMESTAMP=1518106833/*!*/;
insert into test(name) values('test4')
/*!*/;
# at 308
#180209 0:20:33 server id 1 end_log_pos 335 Xid = 12
COMMIT/*!*/;
# at 335
#180209 0:20:35 server id 1 end_log_pos 405 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1518106835/*!*/;
BEGIN
/*!*/;
# at 405
#180209 0:20:35 server id 1 end_log_pos 433 Intvar
SET INSERT_ID=5/*!*/;
# at 433
#180209 0:20:35 server id 1 end_log_pos 536 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1518106835/*!*/;
insert into test(name) values('test5')
/*!*/;
# at 536
#180209 0:20:35 server id 1 end_log_pos 563 Xid = 13
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

提示：备份的同时binlog文件生成了新的文件名，增量恢复就是从这个mysql3306-bin.000001（或者mysql3306-bin.000002/3等）开始的。

删除数据库oldboy：
mysql> drop database oldboy;
mysql> show databases; //没了！
mysql> quit
Bye

发现故障并排查原因：
出问题10分钟后，公司运营人员报网站故障，联系我们运维DBA人员解决。此时应该查看网站报错或后台日志，可能连不上oldboy数据库的提示，然后登录数据库排查，可以发现数据库oldboy都没了，突然想起可能是删库的领导导致的，询问，问题原因找到。开始准备恢复。原因还可能是开发人员通过程序日志判断。
提示：
数据库的权限管理的重要性。未雨绸缪，得病容易祛病难，数据库的恢复也如此。
数据库的更新流程很重要。权限设置。例如：银行查询一个语句得有6、7次审批。

=============================
26~27.mysql数据库增量恢复详细实践
说明：只是做备份恢复，不做从库！
增量恢复全过程（重点）：
通过防火墙禁止web等应用向主库写数据或者锁表，让主库对外暂停更新，然后再进行恢复。
检查全备及binlog日志
[root@oldboyedu-01 ~]# ll /opt/bak.sql.gz
-rw-r--r-- 1 root root 20 Feb 9 00:17 /opt/bak.sql.gz
[root@oldboyedu-01 ~]# ll /opt/bak_2018-02-09.sql.gz
-rw-r--r-- 1 root root 20 Feb 9 00:17 /opt/bak_2018-02-09.sql.gz
[root@oldboyedu-01 ~]# ls /data/3306/
data my.cnf mysql mysql3306-bin.000001 mysql3306-bin.index mysqld.pid mysql_oldboy3306.err mysql.sock
[root@oldboyedu-01 opt]# gzip -d bak_2018-02-09.sql.gz
[root@oldboyedu-01 opt]# grep -i "change" bak_2018-02-09.sql
　　

特殊：领导说必须继续写数据：这个得丢一点数据！
刷新bin-log
[root@oldboyedu-01 ~]# mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-logs
[root@oldboyedu-01 ~]# ls /data/3306/
data my.cnf mysql mysql3306-bin.000001 mysql3306-bin.000002//新的 mysql3306-bin.index mysqld.pid mysql_oldboy3306.err mysql.sock

[root@oldboyedu-01 ~]# cp /data/3306/mysql3306-bin.000002 /opt/
[root@oldboyedu-01 ~]# cd /opt/
[root@oldboyedu-01 opt]# mysqlbinlog -d oldboy mysql3306-bin.000002
[root@oldboyedu-01 opt]# mysqlbinlog -d oldboy mysql3306-bin.000002 >bin.sql
[root@oldboyedu-01 opt]# vim bin.sql //删除drop database oldboy那条语句！

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show variables like '%log_bin%'"
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
现在不让写入数据了：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </opt/bak_2018-02-09.sql
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy </opt/bin.sql
注重流程控制！

思路小结：
　　

如果不是drop，而是update破坏数据，解决起来就复杂，一般需要停库或禁止库被应用服务写入，然后再恢复。
如何防止update破坏数据一例：
防止人为误操作MySQL数据库技巧一例
http://blog.51cto.com/oldboy/1321061

=============================
28.mysql增量恢复小结及核心思想
增量恢复小结：
<1>人为SQL造成的误操作。
<2>全备和增量。
<3>恢复时建议对外停止更新。
<4>恢复全量，然后把增量日志中有问题的SQL语句删除，恢复到数据库。

增量恢复核心思想：
<1>流程制度控制。如果不做，面临服务和数据，鱼与熊掌不可兼得。
<2>延迟备份来解决，信息做监控，黑名单，白名单机制。
<3>业务需求容忍度，可量化的目标，根据需求选择停库或锁表或者容忍丢失部分数据。

posted @ 2018-02-09 22:47 bkycrmn 阅读(384) 评论(0) 收藏举报

刷新页面返回顶部

9~10.mysql数据库的主从复制深度经验&增量恢复知识实践

公告