目录
1、statement：基于SQL语句的复制（statement-based replication，SBR）
2、row：基于行的复制（row-based replication，RBR）
3、mixed：混合模式复制（mixed-based replication，MBR）
1、在mysql中使用show binlog events查看
一、bin log的三种格式
1、statement：基于SQL语句的复制（statement-based replication，SBR）
- 每一条会修改数据的sql都会记录在binlog中。
- 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。但是注意statement相比于row能节约多少性能与日志量，取决于应用的SQL情况。正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。
- 缺点：由于记录的只是执行语句，为了这些语句在slave上正确运行，我们还必须记录每条语句在执行时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时相同的结果。另外，一些特定的函数功能如果要在slave和master上保持一致会有很多相关问题。
2、row：基于行的复制（row-based replication，RBR）
- 5.1.5版本的MySQL才开始支持row level的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
- 优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row level的日志会非常清楚的记下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
- 缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。但是新版本的MySQL对row level模式进行了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。
3、mixed：混合模式复制（mixed-based replication，MBR）
- 从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
- 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如果一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
4、查看模式和更改模式
mysql> show variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | MIXED |+---------------+-------+1 row in set, 1 warning (0.00 sec)mysql> set binlog_format=mixed;Query OK, 0 rows affected (0.00 sec)
二、配置bin log策略
在配置文件中添加
[mysqld]# 指定 binary log 的路径和名称log-bin="/var/lib/mysql/binlog"# 存活时间binlog_expire_logs_seconds=60000# 单个 binlog 文件的最大大小max_binlog_size=100M# binlog的日志策略binlog_format=mixed;
三、获取bin log文件列表
mysql> show binary logs;+------------------+-----------+-----------+| Log_name | File_size | Encrypted |+------------------+-----------+-----------+| IU077-bin.000038 | 157 | No || IU077-bin.000039 | 1400 | No || IU077-bin.000040 | 157 | No || IU077-bin.000041 | 333 | No || IU077-bin.000042 | 157 | No || IU077-bin.000043 | 157 | No || IU077-bin.000044 | 157 | No || IU077-bin.000045 | 157 | No || IU077-bin.000046 | 157 | No || IU077-bin.000047 | 157 | No || IU077-bin.000048 | 180 | No || IU077-bin.000049 | 180 | No || IU077-bin.000050 | 157 | No || IU077-bin.000051 | 157 | No |+------------------+-----------+-----------+
四、生成新的bin log文件
以下三种情况均可生成新的bin log
- 每当我们停止或重启服务器时，服务器会把日志文件记入下一个日志文件，MySQL会在重启时生成一个新的日志文件，文件序号递增。
- 如果日志文件超过max_binlog_size（默认值1G）系统变量配置的上限时，也会生成新的日志文件（在这里需要注意的是，如果你正使用大的事务，二进制日志还会超过max_binlog_size，不会生成新的日志文件，事务全写入一个二进制日志中,这种情况主要是为了保证事务的完整性）
- 手动的flush logs刷新日志，会生成一个新的日志文件;
mysql> flush logs;Query OK, 0 rows affected (0.07 sec)
五、查看日志中的内容
1、在mysql中使用show binlog events查看
查看具体某个日志中的内容：
mysql> show binlog events in 'IU077-bin.000052';+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+| IU077-bin.000052 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 || IU077-bin.000052 | 126 | Previous_gtids | 1 | 157 | || IU077-bin.000052 | 157 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' || IU077-bin.000052 | 234 | Query | 1 | 345 | create database db_16 || IU077-bin.000052 | 345 | Anonymous_Gtid | 1 | 422 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' || IU077-bin.000052 | 422 | Query | 1 | 555 | use `db_16`; create table tb1(id int, lname varchar(20)) || IU077-bin.000052 | 555 | Anonymous_Gtid | 1 | 634 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' || IU077-bin.000052 | 634 | Query | 1 | 710 | BEGIN || IU077-bin.000052 | 710 | Table_map | 1 | 768 | table_id: 92 (db_16.tb1) || IU077-bin.000052 | 768 | Write_rows | 1 | 821 | table_id: 92 flags: STMT_END_F || IU077-bin.000052 | 821 | Xid | 1 | 852 | COMMIT || IU077-bin.000052 | 852 | Rotate | 1 | 899 | IU077-bin.000053;pos=4 |+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------+12 rows in set (0.00 sec)指定从某个pos开始查看
mysql> show binlog events in 'IU077-bin.000052' from 710;+------------------+-----+------------+-----------+-------------+--------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+------------+-----------+-------------+--------------------------------+| IU077-bin.000052 | 710 | Table_map | 1 | 768 | table_id: 92 (db_16.tb1) || IU077-bin.000052 | 768 | Write_rows | 1 | 821 | table_id: 92 flags: STMT_END_F || IU077-bin.000052 | 821 | Xid | 1 | 852 | COMMIT || IU077-bin.000052 | 852 | Rotate | 1 | 899 | IU077-bin.000053;pos=4 |+------------------+-----+------------+-----------+-------------+--------------------------------+4 rows in set (0.00 sec)查看当前正在写入的日志状态：
mysql> show master status\G*************************** 1. row *************************** File: IU077-bin.000053 Position: 157 Binlog_Do_DB: Binlog_Ignore_DB:Executed_Gtid_Set:1 row in set (0.00 sec)2、在shell中使用mysqlbinlog来查看
mysqlbinlog -v 日志文件的路径例如：mysqlbinlog -v /usr/local/mysql/data/binlog.000010
六、利用bin log 来恢复数据
注意：
bin log不是通过回滚来恢复数据的，而是重新执行SQL语句来恢复的。
1、通过pos来恢复
方式一：在shell中执行mysql登录和切换
mysqlbinlog --start-position=100 --stop-position=300 --database=数据库名 binlog文件 | mysql -uroot -p密码 -v 数据库名方式二：生成.sql后缀的脚本，然后在mysql中执行该脚本
# 第一步：先生成sql脚本mysqlbinlog --start-position=100 --stop-position=300 --database=数据库名 binlog文件 > resume.sql# 第二步：在sql中执行该脚本mysql> source 脚本路径/脚本名称2、通过时间来恢复
把position换成datetime或timestramp
一、mysqldump工具实现逻辑备份
基础操作：
# 备份单个数据库mysqldump -uroot -p密码 database_1 > database_1.sql# 备份全部数据库mysqldump -uroot -p密码 --all-databases# 或者mysqldump -uroot -p密码 --A更细致的操作：
参数 作用 --databases 或 -B 备份部分数据库 数据库名称后面跟表名 备份部分表 --where="id < 10" 备份表中的部分数据 --ignore-table=数据库.表名 不备份这个表 --no-create-info 不备份结构，仅备份数据 --no_data 不备份数据
二、逻辑恢复
在shell中恢复
mysql -uroot -p密码 数据库名 < .sql脚本在mysql中恢复
mysql> source 脚本路径/脚本名称.sql
一、物理备份
先给数据库上锁，防止备份过程中数据库被修改
mysql> flush tables with read lock;物理拷贝备份
cp -r database_1 /backup/database_1_bak数据库解锁（一定不能忘记解锁）
mysql> unlock tables;二、物理恢复
物理移动
cp 数据库 路径/名称重启mysql服务
systemctl restart mysql给mysql用户赋予权限
chown -R mysql.mysql /var/lib/mysql/数据库名
一、导出
1、通过INTO OUTFILE导出
先查看数据库是否可以导出
mysql> show variables like '%secure%';+--------------------------+------------------------------------------------+| Variable_name | Value |+--------------------------+------------------------------------------------+| require_secure_transport | OFF || secure_file_priv | D:\Program Files (x86)\MySQL\Data 8.0\Uploads\ |+--------------------------+------------------------------------------------+开始导出
mysql> select * from tb1 INTO OUTFILE "D:\Program Files (x86)\MySQL\Data 8.0\Uploads\tb1.txt";2、使用mysqldump导出
同时生成txt文件和sql脚本
mysqldump -uroot -p密码 -T "路径" 数据库名 表名;3、使用mysql命令导出
把查询内容导出内容到txt文件
mysql -uroot -p密码 --execute="select * from tb1;" 数据库名 > 名字.txt;二、导入
LOAD DATA INFILE 导入
mysql> LOAD DATA INFILE 'txt文本文件' INTO TABLE dbname.tbname;
- 取最近一次的全量备份。
- 用全量备份恢复出一个临时库。
- 取出全量备份之后的bin log日志。
- 剔除日志中的误操作SQL，把其他语句都应用到临时库。
- 回复完成之后，把临时库恢复到主库。
