mysql的日志机制
mysql具有日志机制,以保证数据库的数据安全。常见的主要有两种,redo log和binlog。binlog是mysql本身具备的备份机制,为逻辑日志,会记录每一次操作的数据行前后变化,或者是每一次操作的sql。而redo log为物理日志,会记录每一次操作的具体描述;
redo log是InnoDB引擎的插件,并且日志是循环记录的,如果采用其他的引擎,就不会有这个日志。redo log日志能够保证事务的完成。也就是当mysql当掉的时候,保证提交事务在下一次重启之后依然会正确完成;
binlog是归档机制,会依次记录数据库的所有操作,因此当误删数据的时候,能够依靠binlog实现数据的恢复。备份频率高的话,消耗资源大,但是当出现数据丢失的时候,恢复速度快。
开启mysql的binlog机制
binlog比较占用磁盘空间,所有binlog不一定默认打开了,我使用的MySQL容器里面,binlog是打开的,可能是制作容器的前辈已经配置好了。在我mac上的MySQL默认是没有打开的。在打开MySQL之后,在命令行可以查看binlog是否开启。
显示log_bin_ 开头的变量值。
mysql> show variables like ‘log_bin%’;
+———————————+————————————–+
| Variable_name | Value |
+———————————+————————————–+
| log_bin | ON |
| log_bin_basename | /usr/local/var/mysql/mysql-bin |
| log_bin_index | /usr/local/var/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+———————————+————————————–+
5 rows in set (0.01 sec)
在这里是ON表示binlog已经开启了,如果是OFF的话,需要修改配置文件,并重启mysql服务。
显示MySQL配置文件的位置,
➜ ~ mysql –help –verbose | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnfsudo /usr/local/MySQL/support-files/mysql.server restart
/etc/my.cnf默认可能没有,可以自己创建一下。
#vim /etc/my.cnf
[mysqld]
log-bin = mysql-bin #开启binlog
binlog-format = ROW #选择row模式
server_id = 1 #配置mysql replication需要定义,不能喝canal的slaveId重复
重启MySQL
sudo /usr/local/MySQL/support-files/mysql.server restart
进入MySQL后,查看binlog信息:
mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000001 | 1600 |
| mysql-bin.000002 | 1411 |
| mysql-bin.000003 | 6827 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 601 |
| mysql-bin.000006 | 1548 |
| mysql-bin.000007 | 201 |
| mysql-bin.000008 | 4045 |
| mysql-bin.000009 | 2186 |
+——————+———–+
9 rows in set (0.00 sec)
查找mysql-bin. 000001的存放位置
➜ ~ sudo find / -name mysql-bin.000001
/usr/local/var/mysql/mysql-bin.000001
/usr/local/mysql-5.7.18-macos10.12-x86_64/data/mysql-bin.000001
我的存放地址其实就是' /usr/local/var/mysql/mysql-bin.000001 第二个地址暂时不太清楚,之后在查一查。两个目录详细信息如下:
➜ ~ sudo ls -lsh /usr/local/mysql-5.7.18-macos10.12-x86_64/data/
total 221248
16 -rw-r—– 1 _mysql _mysql 6.9K May 18 2017 MacdeMacBook-Pro.local.err
8 -rw-r—– 1 _mysql _mysql 56B Apr 26 2017 auto.cnf
16 -rw-r—– 1 _mysql _mysql 6.9K Feb 28 16:13 bogon.err
8 -rw-r—– 1 _mysql _mysql 300B Feb 28 16:13 ib_buffer_pool
98304 -rw-r—– 1 _mysql _mysql 48M Feb 28 16:13 ib_logfile0
98304 -rw-r—– 1 _mysql _mysql 48M Apr 26 2017 ib_logfile1
24576 -rw-r—– 1 _mysql _mysql 12M Feb 28 16:13 ibdata1
0 drwxr-x— 77 _mysql _mysql 2.4K Apr 26 2017 mysql
8 -rw-r—– 1 _mysql _mysql 177B Feb 28 16:13 mysql-bin.000001
8 -rw-r—– 1 _mysql _mysql 19B Feb 28 16:13 mysql-bin.index
0 drwxr-x— 90 _mysql _mysql 2.8K Apr 26 2017 performance_schema
0 drwxr-x— 108 _mysql _mysql 3.4K Apr 26 2017 sys
➜ ~ sudo ls -lh /usr/local/var/mysql
total 385352
-rw-r—– 1 liu admin 13K Oct 31 15:39 JevadedeMBP.lan.err
-rw-r—– 1 liu admin 4B Oct 29 19:39 JevadedeMBP.lan.pid
-rw-r—– 1 liu admin 358K Feb 25 14:48 JevadedeMacBook-Pro.local.err
-rw-r—– 1 liu admin 1.0M Dec 21 2017 MacdeMacBook-Pro-2.local.err
-rw-r—– 1 liu admin 4B Dec 16 2017 MacdeMacBook-Pro-2.local.pid
-rw-r—– 1 liu admin 728K Jul 18 2017 MacdeMacBook-Pro.local.err’’ mysqldump -uroot -p123456 -lF –log-error=/Users/liu/myDump.err -B T > ~/BAK.T.sql
。。。。。。。
数据库恢复
这里简单操作一下,看看怎么样进行数据的恢复操作。备份数据库
可以设置数据库的自动备份,并且每次备份之后重新在一个新的binlog文件中写日志,此处手动备份一下。
mysqldump -uroot -p123456 -lF –log-error=/Users/liu/myDump.err -B T > ~/BAK.T.sql
进入mysql中,进行数据库的相关操作,增删改,然后模拟误删数据库。
mysql> select * from T;
+—-+——+
| ID | c |
+—-+——+
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 4 | 1 |
| 6 | 3 |
| 7 | 1 |
| 8 | 202 |
| 9 | 203 |
| 10 | 3 |
| 11 | 82 |
| 12 | 1 |
+—-+——+
11 rows in set (0.00 sec)
mysql> insert into T values(3,1),(5,2),(13,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看当前数据库状态:
mysql> select * from T.T;
+—-+——+
| ID | c |
+—-+——+
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 3 |
| 7 | 1 |
| 8 | 202 |
| 9 | 203 |
| 10 | 3 |
| 11 | 82 |
| 12 | 1 |
| 13 | 3 |
+—-+——+
14 rows in set (0.00 sec)
mysql> drop database T;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| awesome |
| comacserver |
| db_apiserver |
| django |
| goadmin |
| mxonline |
| mysql |
| performance_schema |
| sys |
| vps_information |
| xixi |
+——————–+
12 rows in set (0.01 sec)
在命令行备份binlog文件:
mkdir ~/temp
cp /usr/local/var/mysql/mysql-bin.00000* ~/temp/
恢复数据库到备份时刻的状态
mysql -uroot -p123456 -v < BAK.T.sql;
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| T |
| awesome |
| comacserver |
| db_apiserver |
| django |
| goadmin |
| mxonline |
| mysql |
| performance_schema |
| sys |
| vps_information |
| xixi |
+——————–+
13 rows in set (0.00 sec)
mysql> select * from T;
+—-+——+
| ID | c |
+—-+——+
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 4 | 1 |
| 6 | 3 |
| 7 | 1 |
| 8 | 202 |
| 9 | 203 |
| 10 | 3 |
| 11 | 82 |
| 12 | 1 |
+—-+——+
11 rows in set (0.00 sec)将binlog文件转化成sql
mysqlbinlog –no-defaults ~/temp/mysql-bin.000009 > . ~/temp/09.sql
打开sql文件删除drop database T 行以及之后所有行的内容。
vim .~/temp/09.sql
恢复增量数据
mysql -u root -p < ~/temp 09.sql
mysql> select * from T.T;
+—-+——+
| ID | c |
+—-+——+
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 3 |
| 7 | 1 |
| 8 | 202 |
| 9 | 203 |
| 10 | 3 |
| 11 | 82 |
| 12 | 1 |
| 13 | 3 |
+—-+——+
14 rows in set (0.00 sec)
至此数据库就恢复完成了。但是只能恢复到误删数据的时刻,误删之后丢失的数据是无法恢复,这就需要在相应的软件中有相应的机制,以应对数据库误删情况的数据的持久化问题。