mysql 连表update_讲一讲 MySQL 数据备份杀手锏 binlog

我们都知道数据非常重要。

还有一些网络上经常看到的笑话，某公司程序员对工作不满，删除数据库跑路，让老板损失惨重，欲哭无泪。近日又爆出一起案件，京东一名程序员辞职当天，删除数据库跑路！

那么有没有什么解决办法呢？

即使真的删除了数据库，也有备份数据可以快速恢复，甚至可以实时恢复，即使内部数据库被炸毁，外部用户也无法察觉，一切从容。

MySQL 作为一款热门数据库，在数据备份和高可用性方面非常具有竞争力。今天我们重点介绍

MySQL 主备是什么

情况1：

案例 2：

那么，这里的核心数据同步是如何实现的呢？

主从同步原理

1.在备库执行命令绑定主库的信息

mysql> CHANGE MASTER TO MASTER_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; 

2、备库执行start slave命令，备库启动I/O、SQL两个线程

3. 当主数据库有数据更新时，更新的事件类型写入主数据库的文件中。

4.主库会创建日志转储线程，通知从库有数据更新

5. 从属服务器从节点的日志转储线程请求指定文件位置的副本，并将请求的副本存储在本地 Relay 日志中。

6、从站启动另外一个SQL线程，读取中继日志，解析日志中的命令并执行，保证主备数据库之间的数据同步。

有哪些格式？

现在，让我们仔细看看日志。

有三种格式：行、混合

接下来我们开始一个实验：

首先创建一个表

CREATE TABLE `person` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `income` bigint(20) NOT NULL COMMENT '收入',
  `expend` bigint(20) NOT NULL COMMENT '支出',
  PRIMARY KEY (`id`),
  KEY `idx_income` (`income`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='个人收支表';

插入4条记录：

insert into person values(50,500,500);
insert into person values(60,600,600);
insert into person values(70,700,700);
insert into person values(80,800,800);

查看模式：

查看当前正在写入的文件：

要查看内容，我们先来看一下行模式

show binlog events in 'mysql-bin.000001';

阐明：

找到文件的物理位置：

root@167bfa3785f1:/# find / -name mysql-bin.000001
/var/lib/mysql/mysql-bin.000001

使用命令查看具体内容：

mysqlbinlog -vv mysql-bin.000001 --start-position=2986;

红色框内的内容表示插入命令已经执行，into(80,800,800)；

其中@1、@2、@3表示表中的字段，为了节省空间，没有使用原来的名称。

修改格式，并设置为查看日志格式：

set global binlog_format='STATEMENT';

设置完之后需要退出MySQL再重新连接才能看到效果

show binlog events in 'mysql-bin.000001';

从图中我们可以看到，当=时，这里面记录的是SQL语句原文。

其中use tomge:表示先切换到对应的数据库

如果要从特定位置查看，可以添加 from 可选参数，如下所示：

show binlog events in 'mysql-bin.000001'  from 5168;

与行对比：

格式记录SQL语句；行格式记录事件（,,）

使用该格式时，会记录SQL语句，在主库执行时，可能会使用索引A；但同步到备库执行时，可能会使用索引B。

同一条SQL语句，由于索引不同，执行结果可能会有所不同。

对于这种情况，我们建议使用行格式。

即使我们使用带有where条件的语句（如：>720），但是该记录是需要删除的主键id（id=80），所以也不会出错。

什么是混合格式？

由于该格式可能造成主备数据库数据同步不一致，因此我们将采用行格式。

但是行格式占用很大空间，写入也会占用大量IO资源。

因此官方提出了融合两者优势的混合模式。

内容如下：

数据恢复

当然我们也建议将MySQL设置为行模式，因为它可以用于数据恢复。我们来看看如何使用三个DML操作来恢复数据：

1、：

当我们执行该命令时，如果设置了‘FULL’，那么它包含已删除行的所有字段的值。

如果误删除了，由于所有字段的值都记录了，所以执行反向命令就可以了。

设置为时，只记录关键信息，如id=80

2、：

以行格式，所有字段值都会被记录。

如果犯了错误，只需要根据这些值找到对应的行，然后执行操作即可。

3.

行格式会记录修改前后的整行数据。

若修改有误，只需覆盖修改前的数据即可。

使用命令恢复数据：

如果要执行数据恢复，可以使用以下命令

mysqlbinlog mysql-bin.000001  --start-position=1  --stop-position=3000 | mysql -h192.168.0.1 -P3306 -u$user -p$pwd;

在192.168.0.1机器的数据库上重放并恢复mysql-bin.文件位置从1到3000。

【新品上架】

  推荐阅读：
我是如何一步步让公司的MySQL支撑亿级流量的？
什么是 MySQL 的“回表”？
一切从MySQL的删除说起
热门：4 款专属极客卫衣，程序员秒懂！

每日打卡赢积分兑换书籍入口