大家好，我是Tom哥~我们都知道数据很重要，也经常在网上看到一些笑话，欲哭无泪。这不是最近发生的吗？京东到家程序员离职当天删库跑路！那么有什么解决办法吗？即使数据库真的被删除了，也还有备份数据，可以快速恢复。甚至可以实现实时热备份，即使内部用户炸了，外部用户也察觉不到，一切风平浪静。MySQL作为目前流行的数据库，在数据备份和高可用方面都非常有竞争力。今天重点介绍什么是MySQL主备情况1：客户端的业务操作，读写权限是主库。一定的机制将数据实时同步到备库。由于某些原因，主库无法正常响应客户端的请求。情况二：主备切换完成后客户端读写，访问备库（此时备库升级为新的主库。）那么，这里核心数据同步是如何实现的呢？主从同步原理1、在备库上执行changemaster命令，绑定主库信息mysql>CHANGEMASTERTOMASTER_HOST='192.168.1.1',MASTER_USER='repl',MASTER_PASSWORD='replpassword',MASTER_PORT=3306，MASTER_AUTO_POSITION=1，MASTER_RETRY_COUNT=0，MASTER_HEARTBEAT_PERIOD=10000；MASTER_HOST：master主机名（或IP地址）MASTER_PORT：mysql实例端口号MASTER_USER_password_MASSIWORDS：用户名：如果master改成MASTER_AUTO_POSITION=1，slave连接master将使用基于GTID的复制协议MASTER_RETRY_COUNT：重连次数MASTER_HEARTBEAT_PERIOD：复制心跳的周期https://www.docs4dev.com/docs/zh/mysql/5.7/reference/change-master-to.html2。备库执行startslave命令，备库启动两个线程：I/O线程和SQL线程3。master主库有数据更新，将本次更新的事件类型写入4.主库会创建一个logdump线程通知slave有数据更新5.slave会请求一份指定的binlog文件location从master节点的logdump线程中获取，并将返回的binlog保存到本地Relaylogrelaylog6.slave打开一个SQL线程读取Relay日志，解析出日志中的命令并执行，从而保证主备数据库的数据同步。binlog有哪些格式？现在，让我们仔细看看binlog日志。binlog有三种格式：row,statement,mixed接下来我们开始一个实验：先建表CREATETABLE`person`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键',`income`bigint(20)NOTNULLCOMMENT'income',`expend`bigint(20)NOTNULLCOMMENT'expenditure',PRIMARYKEY(`id`),KEY`idx_income`(`income`))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8COMMENT='个人收支表';插入4条记录：insertintopersonvalues(50,500,500);insertintopersonvalues(60,600,600);insertintopersonvalues(70,700,700);insertintopersonvalues(80,800,800);查看binlog方式：查看当前写入的binlog文件：查看binlog中的内容，我们先来看行模式showbinlogeventsin'mysql-bin.000001'；说明：SET@@SESSION.GTID_NEXT='ANONYMOUS'BEGIN启动一个事务Table_map记录哪个库更新了，哪个表Write_rows记录了做了什么操作，需要使用mysqlbinlog工具详细查看binlog。COMMIT/*xid=157*/结束一个事务找到binlog文件的物理位置：root@167bfa3785f1:/#find/-namemysql-bin.000001/var/lib/mysql/mysql-bin.000001使用mysqlbinlog命令查看具体内容：mysqlbinlog-vvmysql-bin.000001--start-position=2986;红框内的内容表示执行了insert命令，insertintopersonvalues(80,800,800)；其中，@1、@2、@3表示表中person的前几个字段，为了节省空间，不用原来的名字。修改binlog格式，设置为STATEMENT，查看日志格式：setglobalbinlog_format='STATEMENT';设置完成后，需要退出mysql，重新连接才能看到有效showbinlogeventsin'mysql-bin.000001'；从图中我们可以看出，当binlog_format=statement时，binlog中记录的是SQL语句的原文。其中，usetomge:表示先切换到对应的数据库。如果想查看指定位置的binlog，可以加上可选参数from，如下：showbinlogeventsin'mysql-bin.000001'from5168;statement和row比较：statement行格式的binlog记录sql语句；行格式的binlog记录事件（Table_map，Write_rows，Delete_rows）。binlog为statement格式时，记录sql语句，主库执行时可能会用到索引A；但是在同步到备库的时候，可能会用到索引B。索引不同，同一条SQL语句运行结果可能不同。对于这种场景，我们建议使用行格式的binlog。即使我们使用带where条件的delete语句（如：income>720），binlog记录的是要删除的主键id（id=80），所以不会报错。混合格式的binlog是什么？由于statement格式的binlog可能会导致主备数据库数据同步不一致，所以我们会使用row格式。但是行格式占用空间大，写binlog也占用大量IO资源。因此，官方提出了一种综合了两者优点的混动混合模式。内容如下：mysql会自动判断语句格式，是否会造成主备不一致的问题。如果语句格式会造成主备不一致的问题，会自动使用行格式。如果语句格式不会导致主备不一致，则使用语句格式恢复数据。当然，我们也推荐将MySQL的binlog设置为行模式，因为可以用于数据恢复。我们来看看insert、update、delete这三个DML操作是如何恢复数据的。1.delete：当我们执行删除命令时，如果binlog_row_image设置为'FULL'，那么Delete_rows包含被删除行的所有字段的值。如果误删了，因为binlog记录了所有字段的值，反向执行insert就可以了。当binlog_row_image设置为MINIMAL时，只记录key信息，比如id=802，insert:row格式，binlog会记录insert的所有字段值。如果出错，只需要根据这些值找到对应的行，然后执行删除操作即可。3、在update:row格式下，binlog会记录update修改前后的整行数据。如果出错，只需用修改前的数据覆盖即可。通过命令恢复数据：如果要进行数据恢复，可以使用如下命令mysqlbinlogmysql-bin.000001--start-position=1--stop-position=3000|mysql-h192.168.0.1-P3306-u$user-p$pwd;在192.168.0.1机器的数据库上回放恢复mysql-bin.000001文件位置1到3000的binlog。
