文章目录
- 一 、binlog简介
- 二、binlog的格式
- 1)Statement
- 2)Row
- 3)Mixed
- 三、配置和查看binlog格式
- 1)修改binlog格式并配置MySQL日志文件
- 2)查看binlog的格式
- 四、创建数据库并备份数据
- 1)创建数据库
- 2) 创建完毕,刷新binlog文件
- 五、使用备份恢复数据库并使用binlog来完成回滚操作
- 1)进入数据库并插入数据库
- 2)查看数据
- 3)update修改数据库,模拟失败操作
- 4)查看一下mysql的binlog文件
- 5)删除表格,利用备份恢复数据库
一 、binlog简介
binlog:binlog是一个二进制格式的文件,它记录了数据库上的所有改变,并以二进制的形式保存在磁盘中;它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制(主从数据库的复制)。
二、binlog的格式
1)Statement
每一条会修改数据的sql都会记录在binlog中。
优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。
缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题。
总结:
Statement level优点:
1、解决了row level的缺点,不需要记录每一行的变化。
2、日志量少,节约IO,从库应用日志块。
Statement level缺点:一些新功能同步可能会有障碍,比如函数、触发器等。
2)Row
5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息,仅保存哪条记录被修改。
优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题.
缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容。
总结:
row level的优点:
1、记录详细
2、解决statement level模式无法解决的复制问题。
row level的缺点:日志量大,因为是按行来拆分。
3)Mixed
从5.1.8版本开始,MySQL提供了Mixed格式,实际上就是Statement与Row的结合。
在Mixed模式下,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。
三、配置和查看binlog格式
1)修改binlog格式并配置MySQL日志文件
# 使用命令
[root@ chenc01 ~]# vim /etc/my.cnf
# 打开配置文件,并在[mysqld]下面增加以下内容
log-bin = mysql-bin
binlog_format="ROW"
重启数据库,会新生成一个文件/var/lib/mysql/mysql-bin.000001,如果以前把mysql-bin.000001删除了,则会在当前的index基础上增加文件。
# 使用命令重启数据库
[root@ chenc01 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
# 使用命令 ls /var/lib/mysql 查看binlog文件
[root@ chenc01 ~]# ls /var/lib/mysql
ibdata1 ib_logfile1 mysql-bin.000001 mysql.sock
ib_logfile0 mysql mysql-bin.index test
2)查看binlog的格式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
四、创建数据库并备份数据
1)创建数据库
mysql> create database demo;
Query OK, 1 row affected (0.11 sec)
mysql> use demo;
Database changed
mysql> create table user (
-> id int(10) not null auto_increment,
-> name varchar(32) not null,
-> type int(10) not null,
-> primary key(id)
-> ) ENGINE=innoDB;
Query OK, 0 rows affected (0.05 sec)
mysql>
2) 创建完毕,刷新binlog文件
mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)
mysql>
3)备份数据库
使用命令 mysqldump -u root -p demo user > db_demo_bak.sql
或者 mysqldump -u root -p --databases demo > db_demo_bak.sql
[root@ chenc01 ~]# mysqldump -u root -p demo user > db_demo_bak.sql
Enter password:
五、使用备份恢复数据库并使用binlog来完成回滚操作
1)进入数据库并插入数据库
mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into user(id,name,type) value (10001,"A",1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user(id,name,type) value (10002,"B",1);
Query OK, 1 row affected (0.00 sec)
mysql>
2)查看数据
mysql> select * from user;
+-------+------+------+
| id | name | type |
+-------+------+------+
| 10001 | A | 1 |
| 10002 | B | 1 |
+-------+------+------+
2 rows in set (0.00 sec)
mysql>
3)update修改数据库,模拟失败操作
mysql> update user set name="C";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from user;
+-------+------+------+
| id | name | type |
+-------+------+------+
| 10001 | C | 1 |
| 10002 | C | 1 |
+-------+------+------+
2 rows in set (0.00 sec)
mysql>
# user表的name字段被误操作修改,抓紧刷新掉binlog文件
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)
mysql>
4)查看一下mysql的binlog文件
[root@ chenc01 ~]# ls /var/lib/mysql
demo ib_logfile1 mysql-bin.000002 mysql.sock
ibdata1 mysql mysql-bin.000003 test
ib_logfile0 mysql-bin.000001 mysql-bin.index
5)删除表格,利用备份恢复数据库
mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table user;
Query OK, 0 rows affected (0.01 sec)
mysql> source /root/db_demo_bak.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
....................................
mysql> select * from user;
Empty set (0.00 sec)
mysql>
# 此时数据库表就备份好了,但是没有数据。
mysql> show binlog events in"mysql-bin.000002";
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| mysql-bin.000002 | 106 | Query | 1 | 174 | BEGIN |
| mysql-bin.000002 | 174 | Table_map | 1 | 221 | table_id: 15 (demo.user) |
[root@ chenc01 ~]# mysqlbinlog --start-position=4 --stop-position=1037 --database=demo /var/lib/mysql/mysql-bin.000004 | /var/lib/mysql -u root -p TestBicon@123 -v demo
# 然后我们来检查一下user表
mysql> select * from user;
+-------+------+------+
| id | name | type |
+-------+------+------+
| 10001 | A | 1 |
| 10002 | B | 1 |
+-------+------+------+
2 rows in set (0.00 sec)