1.mysql bin-log日志基本操作与数据库备份还原

mysql bin-log日志基本操作与数据库备份还原

bin-log是指mysql以二进制形式记录了mysql的增删改操作

基本操作

/*
1.开启bin-log
2.刷新bin-log日志
3.查看最新的bin-log日志
4.清空所有的bin-log日志
5.查看某个bin-log日志内容
6.删除bin-log日志，保留最新的
*/

---

数据库备份与还原

1.mysqldump 备份数据
2.mysql 命令行导入sql文件 恢复数据

1.1 mysql 开启bin-log日志

# 开启log-bin日志 编辑mysql配置文件 添加以下内容 重启msyql
1.vim /usr/local/mysql/my.cnf

# log-bin日志 
log_bin=mysql-bin

# 查看log-bin日志 
#1.通过mysql变量查看 是否开启（需登录mysql）
#2.通过查看mysql data目录下的
mysql>show variables like '%binlog%';

+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | IGNORE_ERROR         |
| binlog_format                           | STATEMENT            |
| binlog_gtid_simple_recovery             | OFF                  |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| binlogging_impossible_mode              | IGNORE_ERROR         |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| simplified_binlog_gtid_recovery         | OFF                  |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
19 rows in set (0.00 sec)

1.2 mysql刷新日志

#刷新bin-log日志 会创建一个新的bin-log日志
mysql>flush logs

1.3 mysql查看bin-log日志

# 查看最新的日志节点
mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      954 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 查看log-bin日志细节
#命令（利用mysql bin目录里的工具）
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position=494 --stop-position=495
#再后面可以追加参数 查看指定位置点或者时间段的数据
#--start-position=494 --stop-position=495
#--start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' 

1.4 利用bin-log日志恢复数据

其原理是将bin-log里面记录的操作提取出来再执行一次

1.整个binlog日志恢复
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004 | mysql -uroot -pdh977094 master
#此时是将mysql-bin.000004里面记录的对数据库的操作再取出来在数据库中执行一次

2.恢复指定的binlog日志中的操作
#1).按指定位置恢复，2).按指定时间恢复
#在mysqlbinlog命令后加参数
#--start-position="100" --stop-position="100"
#--start-date="2016-01-04 21:15:50" --start-date="2016-02-04 21:15:50"
#命令如下
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004 --start-position="100" --stop-position="100" | mysql -uroot -pdh977094 master

1.5 bin-log日志恢复测试与练习

# 情景：今天早上九点半对数据进行了一个完整的备份（通过mysqldump）
#       在早上十点的时候数据库突然奔溃，数据丢失，现在恢复所有mysql数据？
#       通过mysqldump恢复九点半以前的数据，九点半以后的数据利用bin-log日志恢复
# 解决方案
#1.通过mysqldump恢复数据到九点半时的数据
#2.然后导入在备份后新建的bin-log日志到数据库中恢复数据
# 命令如下
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 |/usr/local/mysql/bin/mysql -uroot -pdh977094 master