mysql 数据恢复实例

最新推荐文章于 2024-09-05 09:22:58 发布

weixin_30300225

最新推荐文章于 2024-09-05 09:22:58 发布

阅读量176

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/jiangyatao/p/11087085.html

博客介绍了基于二进制日志进行数据恢复，包含MySQL数据恢复演练实例，转载自相关博客。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

基于二进制日志数据恢复

mysql数据恢复演练实例

    如何按需截取日志
    基于position好的截取
        --start-position=
        --stop-position=
       mysqlbinlog --start-position=219 --stop-position=1272 /data/binlog/mysql-bin.000002 >/tmp/back.sql
    恢复
        删除库 mysql> drop database oldboy1;
               mysql> show databases;
        恢复时不产生二进制日志
            mysql> set sql_log_bin=0;    //临时关闭二进制日志
            mysql> source /tmp/bin.sql   //恢复
基于时间点的截取
    --start-datetime
    --stop-datetime
    for example: 2004-12-25 11:25:56
案例: 使用binlog日志进行数据恢复
    模拟:
    1. 
    [(none)]>create database binlog charset utf8mb4;
    2. 
    [(none)]>use binlog;
    [binlog]>create table t1(id int);
    3. 
    [binlog]>insert into t1 values(1);
    [binlog]>commit;
    [binlog]>insert into t1 values(2);
    [binlog]>commit;
    [binlog]>insert into t1 values(3);
    [binlog]>commit;
    4. 
    [binlog]>drop database binlog;
    恢复
        1、找到起点和终点
        mysql> show master status;
        +------------------+----------+--------------+------------------+-----------------
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set
        +------------------+----------+--------------+------------------+-----------------
        | mysql-bin.000002 |     1435 |              |                  |                  
        +------------------+----------+--------------+------------------+-----------------
        mysql> show binlog events in 'mysql-bin.000002';
        命令行：mysqlbinlog --start-position=219 --stop-position=1272 /data/binlog/mysql-bin.000002 >/tmp/back.sql
        恢复
              mysql> set sql_log_bin=0;    //临时关闭二进制日志
              mysql> source /tmp/bin.sql   //恢复
              # 5. 验证数据
                    mysql> use binlog
                    Database changed
                    mysql> select * from t1;
                    +------+
                    | id   |
                    +------+
                    |    1 |
                    |    2 |
                    |    3 |
                    +------+

转载于:https://www.cnblogs.com/jiangyatao/p/11087085.html