mysql bin-log 日志

mysql> show variables like "%bin%";
+-----------------------------------------+------------+
| Variable_name                           | Value      |
+-----------------------------------------+------------+
| binlog_cache_size                       | 32768      |
| binlog_direct_non_transactional_updates | OFF        |
| binlog_format                           | STATEMENT  |
| innodb_locks_unsafe_for_binlog          | OFF        |
| log_bin                                 | ON         |
| log_bin_trust_function_creators         | OFF        |
| log_bin_trust_routine_creators          | OFF        |
| max_binlog_cache_size                   | 4294963200 |
| max_binlog_size                         | 1073741824 |
| sql_log_bin                             | ON         |
| sync_binlog                             | 0          |
+-----------------------------------------+------------+

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)  #此时会多一个最新的bin-log 日志

mysql> show master status; #查看最后一个bin-log日志
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec

mysql> reset master;
Query OK, 0 rows affected (0.03 sec) #清空所有bin-log日志

[root@os mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 #查看bin-log日志内容

mysql> trancate t1; #删除t1表
[root@os mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 | /usr/bin/mysql -uroot -p123456 test #把删除的数据导入到test数据库里面

[root@os mnt]# mysqldump -uroot -p123456 -l -F test > '/mnt/test.sql'#备份数据库
[root@os mysql]# mysql -uroot -p123456 test < /mnt/test.sql   #恢复备份的数据库

[root@os mysql]# /usr/bin/mysqlbinlog --start-position="1515" --stop-position="1744" mysql-bin.000006 |more  #后边加more查看要恢复的哪些内容

[root@os mysql]# /usr/bin/mysqlbinlog --start-position="1515" --stop-position="1744" mysql-bin.000006 |/usr/bin/mysql -uroot -p123456 test #恢复pos位置从1515到1744之间的数据