2019-02-28
mysql具有日志机制,以保证数据库的数据安全。常见的主要有两种,redo log和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)
至此数据库就恢复完成了。但是只能恢复到误删数据的时刻,误删之后丢失的数据是无法恢复,这就需要在相应的软件中有相应的机制,以应对数据库误删情况的数据的持久化问题。