1.开启二进制日志
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id
log_bin=/usr/local/mysql/data/mysql-bin
2.查看二进制日志
#物理查看
[root@db02 ~]# ll /usr/local/mysql/data/
-rw-rw---- 1 mysql mysql 120 Jul 21 19:24 mysql-bin.000001
-rw-rw---- 1 mysql mysql 39 Jul 21 19:24 mysql-bin.index
#数据库查看
mysql> show variables like '%log_bin%';
3.事件
1.什么是事件
1)在binlog中最小的记录单元为event
2)一个事务会被拆分成多个事件(event)
2.事件(event)特性
1)每个event都有一个开始位置(start position)和结束位置(stop position)。
2)所谓的位置就是event对整个二进制的文件的相对位置。
3)对于一个二进制日志中,前120个position是文件格式信息预留空间。
4)MySQL第一个记录的事件,都是从120开始的。
4.刷新binlog
1)flush logs;
2)重启数据库时会刷新
3)二进制日志上限,默认1G(max_binlog_size)
5.删除binlog
1.根据存在时间删除日志
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
2.使用purge命令删除
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
3.根据文件名删除
PURGE BINARY LOGS TO 'mysql-bin.000010';
4.使用reset master
mysql> reset master;
二进制日志作用
1.记录已提交的DML事务语句,并拆分为多个事件(event)来进行记录
2.记录所有DDL、DCL等语句,总之,二进制日志会记录所有对数据库发生修改的操作
3.如果我拥有数据库搭建开始所有的二进制日志,那么我可以把数据恢复到任意时刻
4.数据的备份与恢复
5.数据的复制
1.数据库的备份与恢复
1.添加数据
mysql> create database binlog;
mysql> use binlog
mysql> create table binlog(id int);
mysql> insert binlog values(1),(2),(3);
mysql> insert binlog values(4);
mysql> insert binlog values(5);
2.删除数据
#误删除两条数据
mysql> delete from binlog where id=5;
mysql> delete from binlog where id=4;
3.通过binlog恢复数据
#查看二进制日志找到位置点
[root@db02 data]# mysqlbinlog mysql-bin.000002
#将位置点之间的数据取出
[root@db02 data]# mysqlbinlog --start-position=631 --stop-position=978 mysql-bin.000002 > /tmp/hf.sql
#将数据导入回去
[root@db02 data]# mysql < /tmp/45.sql
2.使用binlog配合数据库升级
1.准备一台新的数据库,版本为5.6.38
2.旧数据库备份数据
[root@db03 ~]# mysqldump -uroot -p123 --triggers -R --master-data=2 -B ku linux9 myisam qiudao qiudaodsb tmp world xiangqing >/tmp/full.sql
3.将备份的数据库传到新数据库
[root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
4.修改sql中的存储引擎
[root@db02 data]# sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql
5.将修改后的sql文件导入新数据
[root@db02 data]# mysql < /tmp/full.sql
6.将代码中的数据库地址修改为新的数据库地址
7.通过binlog将数据迁移过程中新生成的数据取出
[root@db03 data]# mysqlbinlog -uroot -p123 --start-position=120 --stop-position=465 mysql-bin.000014 > /tmp/bu.sql
[root@db03 data]# scp /tmp/bu.sql 172.16.1.52:/tmp/
8.将新数据导入新库
[root@db02 data]# mysql < /tmp/bu.sql
二进制日志
1.作用
1.记录已提交的DML事务语句,并拆分为多个事件(event)来进行记录
2.记录所有DDL、DCL等语句
3.数据的备份恢复
4.数据的复制
#如果我拥有数据库搭建开始所有的二进制日志,那么我可以把数据恢复到任意时刻
2.二进制日志介绍
1.二进制日志默认是关闭的
2.查看二进制
mysql> show variables like '%log_bin%';
3.配置
[root@db03 ~]# vim /etc/my.cnf
server_id=1
log_bin=/service/mysql/data/mysql-bin
4.开启二进制一般存储子 $datadir/ 下面,以'mysql-bin.00000N'命名
3.二进制日志工作模式
1.工作模式种类
1.statement 语句模式
2.row 行级模式
3.mixed 混合模式
2.查看工作模式
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
3.STATEMENT语句模式
#语句模式,mysql5.6默认的模式
记录数据库中操作过得所有sql语句
#查看
[root@db03 data]# mysqlbinlog mysql-bin.000014
#优缺点:
1.易读
2.不安全
3.相对于行级模式占用磁盘空间小
4.row行级模式
#行级模式,mysql5.7默认的模式
记录的是数据的变化过程
#配置行级模式
[root@db03 data]# vim /etc/my.cnf
binlog_format=row
#查看方式
[root@db03 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000016
#优缺点:
1.安全
2.不易读
3.相对于语句模式占用磁盘大
5.mixed混合模式