mysql数据恢复

2.基本操作(1)查看所有日志文件:mysql> show binary logs; 或show master logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 120 |+------------------+-----------+(2)查看正在写入的日志文件：mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+(3)查看当前binlog文件内容：mysql> show binlog events; #可以格式化输出 show binlog events\G; 或指定日志文件查看show binlog events in 'mysql-bin.000001';+------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+---------------------------------------+| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-log, Binlog ver: 4 |+------------------+-----+-------------+-----------+-------------+-------------------- 注：Log_name:此条log存在哪个文件中 Pos:log在bin-log中的开始位置 Event_type:log的类型信息 Server_id:可以查看配置中的server_id,表示log是哪个服务器产生 End_log_pos：log在bin-log中的结束位置 Info:log的一些备注信息，可以直观的看出进行了什么操作 (4)手动启用新的日志文件,一般备份完数据库后执行 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> flush logs; #结束正在写入日志文件Query OK, 0 rows affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+(5)删除所有二进制日志，并从新开始记录mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000002 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> reset master; #重新开始Query OK, 0 rows affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+ 还有：mysql> purge master logs to 'mysql-bin.000002'; #是将'mysql-bin.000002'编号之前的所有日志进行删除mysql> purge master logs before 'yyyy-mm-dd hh:mm:ss' #是将在'yyyy-mm-dd hh:mm:ss'时间之前