简单的mysql的bin-log实现数据恢复

于 2019-07-12 17:44:23 发布
阅读量240 收藏
点赞数
分类专栏： Linux 文章标签： bin-log数据恢复
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/LDR1109/article/details/95639777
版权 
==----------------------------------------------------------------------------------------------------------------------
-----==Linux  简单的mysql的bin-log来实现数据恢复

前提：必须开启bin-log日志，至于如何开启，网上有帖子可自行查看！

1、授权从机
    grant all on *.* to slave@39.100.51.121 identified by "slave121"

2、查询授权
    select Host, User, Password from mysql.user
    mysql> select Host, User, Password from mysql.user
        -> ;
    +-------------------------+-------+-------------------------------------------+
    | Host                    | User  | Password                                  |
    +-------------------------+-------+-------------------------------------------+
    | localhost               | root  |                                           |
    | izm5eizpokikoertia0x31z | root  |                                           |
    | 127.0.0.1               | root  |                                           |
    | ::1                     | root  |                                           |
    | localhost               |       |                                           |
    | izm5eizpokikoertia0x31z |       |                                           |
    | localhost               | LDR   | *0F20228F92B4C2607708E1083957B11DC1E3AF0A |
    | %                       | root  | *C0C54EB873E6B870D86CC3C56EDA4F59D3737954 |
    | 39.100.51.121           | slave | *E34DCEEB1AE97C93158E6CFBB4C680000479E6D7 |
    +-------------------------+-------+-------------------------------------------+
    9 rows in set (0.00 sec)

3、刷新bin-log日志 （新增新的，与旧的分离）
    -- 刷新前
    -rw-rw---- 1 mysql mysql 269766957 Jul  4 19:04 mysql-bin.000028
    -rw-rw---- 1 mysql mysql       107 Jul  9 11:25 mysql-bin.000029
    -rw-rw---- 1 mysql mysql       107 Jul  9 11:36 mysql-bin.000030
    -rw-rw---- 1 mysql mysql       107 Jul  9 12:18 mysql-bin.000031
    -rw-rw---- 1 mysql mysql       107 Jul  9 12:37 mysql-bin.000032
    -rw-rw---- 1 mysql mysql       107 Jul  9 14:04 mysql-bin.000033
    -rw-rw---- 1 mysql mysql       126 Jul 12 11:59 mysql-bin.000034
    -rw-rw---- 1 mysql mysql       126 Jul 12 13:21 mysql-bin.000035
    -rw-rw---- 1 mysql mysql       126 Jul 12 13:36 mysql-bin.000036
    -rw-rw---- 1 mysql mysql      3729 Jul 12 15:53 mysql-bin.000037
    -rw-rw---- 1 mysql mysql       107 Jul 12 15:53 mysql-bin.000038
    -rw-rw---- 1 mysql mysql       722 Jul 12 15:53 mysql-bin.index
    drwx------ 2 mysql mysql      4096 Jul 12 15:38 newdata

    -- 刷新
    flush logs;

    -- 刷新后
    -rw-rw---- 1 mysql mysql 269766957 Jul  4 19:04 mysql-bin.000028
    -rw-rw---- 1 mysql mysql       107 Jul  9 11:25 mysql-bin.000029
    -rw-rw---- 1 mysql mysql       107 Jul  9 11:36 mysql-bin.000030
    -rw-rw---- 1 mysql mysql       107 Jul  9 12:18 mysql-bin.000031
    -rw-rw---- 1 mysql mysql       107 Jul  9 12:37 mysql-bin.000032
    -rw-rw---- 1 mysql mysql       107 Jul  9 14:04 mysql-bin.000033
    -rw-rw---- 1 mysql mysql       126 Jul 12 11:59 mysql-bin.000034
    -rw-rw---- 1 mysql mysql       126 Jul 12 13:21 mysql-bin.000035
    -rw-rw---- 1 mysql mysql       126 Jul 12 13:36 mysql-bin.000036
    -rw-rw---- 1 mysql mysql      3729 Jul 12 15:53 mysql-bin.000037
    -rw-rw---- 1 mysql mysql       150 Jul 12 15:54 mysql-bin.000038
    -rw-rw---- 1 mysql mysql       107 Jul 12 15:54 mysql-bin.000039 【新增】
    -rw-rw---- 1 mysql mysql       741 Jul 12 15:54 mysql-bin.index
    drwx------ 2 mysql mysql      4096 Jul 12 15:38 newdata

4、文件太多，清理一下

    -- 清理：reset master;

    root@iZm5eizpokikoertia0x31Z data]# ll
    total 413772
    -rw-rw---- 1 mysql mysql 413138944 Jul 12 15:53 ibdata1
    -rw-rw---- 1 mysql mysql   5242880 Jul 12 15:53 ib_logfile0
    -rw-rw---- 1 mysql mysql   5242880 Jul 12 15:53 ib_logfile1
    -rw-r----- 1 mysql root      40056 Jun 19 18:53 iZm5eizpokikoertia0x31Z.err
    drwx------ 4 mysql root       4096 Jul 12 13:36 mysql
    -rw-rw---- 1 mysql mysql       107 Jul 12 15:54 mysql-bin.000001 【仅剩】
    -rw-rw---- 1 mysql mysql        19 Jul 12 15:54 mysql-bin.index
    drwx------ 2 mysql mysql      4096 Jul 12 15:38 newdata
    drwx------ 2 mysql mysql      4096 Jun 21 15:39 performance_schema
    drwx------ 2 mysql mysql      4096 Jun 17 17:05 sdkdata
    drwxr-xr-x 2 mysql mysql      4096 Jun 17 16:18 test
    [root@iZm5eizpokikoertia0x31Z data]#

5、创建测试库、表

    mysql> use newdata;
    Database changed
    mysql> set names utf8mb4;
    Query OK, 0 rows affected (0.00 sec)

    mysql> CREATE TABLE `specialty` (
        ->   `specialtyid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
        ->   `specialty_name` varchar(50) NOT NULL COMMENT '特长名称',
        ->   `type` tinyint(1) unsigned NOT NULL DEFAULT '2' COMMENT '1 配置 2 自定义',
        ->   `user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
        ->   `coach_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '教练id',
        ->   `createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
        ->   PRIMARY KEY (`specialtyid`)
        -> ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COMMENT='特长表';
    Query OK, 0 rows affected (0.01 sec)

    mysql> show tables;
    +-------------------+
    | Tables_in_newdata |
    +-------------------+
    | specialty         |
    +-------------------+
    1 row in set (0.00 sec)

    mysql> insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('跑步1','1','23','1000',now());
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('读书1','1','24','1000',now());
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('写字1','1','25','1000',now());
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('画画1','1','26','1000',now());
    Query OK, 1 row affected (0.02 sec)

    mysql> insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('跳舞1','1','27','1000',now());
    Query OK, 1 row affected (0.01 sec)

    mysql> select * from specialty;
    +-------------+----------------+------+---------+----------+---------------------+
    | specialtyid | specialty_name | type | user_id | coach_id | createtime          |
    +-------------+----------------+------+---------+----------+---------------------+
    |           1 | 跑步1          |    1 |      23 |     1000 | 2019-07-12 17:15:42 |
    |           2 | 读书1          |    1 |      24 |     1000 | 2019-07-12 17:15:42 |
    |           3 | 写字1          |    1 |      25 |     1000 | 2019-07-12 17:15:42 |
    |           4 | 画画1          |    1 |      26 |     1000 | 2019-07-12 17:15:42 |
    |           5 | 跳舞1          |    1 |      27 |     1000 | 2019-07-12 17:15:47 |
    +-------------+----------------+------+---------+----------+---------------------+
    5 rows in set (0.00 sec)


6、查看bin-log日志的记录位置

    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 |     1717 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

    mysql> update specialty set coach_id = 10001 where specialtyid = 1;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> update specialty set coach_id = 10002 where specialtyid = 2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> update specialty set coach_id = 10003 where specialtyid = 3;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> update specialty set coach_id = 10004 where specialtyid = 4;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> update specialty set coach_id = 10005 where specialtyid = 5;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 |     2832 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)


7、我们查看一下bin-log日志

    /mine/serve/mysql/bin/mysqlbinlog mysql-bin.000002

    [root@iZm5eizpokikoertia0x31Z data]# /mine/serve/mysql/bin/mysqlbinlog mysql-bin.000002
     /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
     /*!40019 SET @@session.max_insert_delayed_threads=0*/;
     /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
     DELIMITER /*!*/;
     # at 4
     #190712 17:14:55 server id 1  end_log_pos 107     Start: binlog v 4, server v 5.5.49-log created 190712 17:14:55
     # Warning: this binlog is either in use or was not closed properly.
     BINLOG '
     j08oXQ8BAAAAZwAAAGsAAAABAAQANS41LjQ5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
     AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
     '/*!*/;
     # at 107
     #190712 17:15:42 server id 1  end_log_pos 186     Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     SET @@session.pseudo_thread_id=2/*!*/;
     SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
     SET @@session.sql_mode=0/*!*/;
     SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
     /*!\C utf8mb4 *//*!*/;
     SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
     SET @@session.time_zone='SYSTEM'/*!*/;
     SET @@session.lc_time_names=0/*!*/;
     SET @@session.collation_database=DEFAULT/*!*/;
     BEGIN
     /*!*/;
     # at 186
     #190712 17:15:42 server id 1  end_log_pos 214     Intvar
     SET INSERT_ID=1/*!*/;
     # at 214
     #190712 17:15:42 server id 1  end_log_pos 402     Query  thread_id=2    exec_time=0    error_code=0
     use `newdata`/*!*/;
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('跑步1','1','23','1000',now())
     /*!*/;
     # at 402
     #190712 17:15:42 server id 1  end_log_pos 429     Xid = 2864
     COMMIT/*!*/;
     # at 429
     #190712 17:15:42 server id 1  end_log_pos 508     Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     BEGIN
     /*!*/;
     # at 508
     #190712 17:15:42 server id 1  end_log_pos 536     Intvar
     SET INSERT_ID=2/*!*/;
     # at 536
     #190712 17:15:42 server id 1  end_log_pos 724     Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('读书1','1','24','1000',now())
     /*!*/;
     # at 724
     #190712 17:15:42 server id 1  end_log_pos 751     Xid = 2865
     COMMIT/*!*/;
     # at 751
     #190712 17:15:42 server id 1  end_log_pos 830     Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     BEGIN
     /*!*/;
     # at 830
     #190712 17:15:42 server id 1  end_log_pos 858     Intvar
     SET INSERT_ID=3/*!*/;
     # at 858
     #190712 17:15:42 server id 1  end_log_pos 1046    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('写字1','1','25','1000',now())
     /*!*/;
     # at 1046
     #190712 17:15:42 server id 1  end_log_pos 1073    Xid = 2866
     COMMIT/*!*/;
     # at 1073
     #190712 17:15:42 server id 1  end_log_pos 1152    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     BEGIN
     /*!*/;
     # at 1152
     #190712 17:15:42 server id 1  end_log_pos 1180    Intvar
     SET INSERT_ID=4/*!*/;
     # at 1180
     #190712 17:15:42 server id 1  end_log_pos 1368    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('画画1','1','26','1000',now())
     /*!*/;
     # at 1368
     #190712 17:15:42 server id 1  end_log_pos 1395    Xid = 2867
     COMMIT/*!*/;
     # at 1395
     #190712 17:15:47 server id 1  end_log_pos 1474    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922947/*!*/;
     BEGIN
     /*!*/;
     # at 1474
     #190712 17:15:47 server id 1  end_log_pos 1502    Intvar
     SET INSERT_ID=5/*!*/;
     # at 1502
     #190712 17:15:47 server id 1  end_log_pos 1690    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562922947/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('跳舞1','1','27','1000',now())
     /*!*/;
     # at 1690
     #190712 17:15:47 server id 1  end_log_pos 1717    Xid = 2868
     COMMIT/*!*/;
     # at 1717
     #190712 17:17:49 server id 1  end_log_pos 1788    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923069/*!*/;
     BEGIN
     /*!*/;
     # at 1788
     #190712 17:17:49 server id 1  end_log_pos 1913    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923069/*!*/;
     update specialty set coach_id = 10001 where specialtyid = 1
     /*!*/;
     # at 1913
     #190712 17:17:49 server id 1  end_log_pos 1940    Xid = 2871
     COMMIT/*!*/;
     # at 1940
     #190712 17:18:01 server id 1  end_log_pos 2011    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923081/*!*/;
     BEGIN
     /*!*/;
     # at 2011
     #190712 17:18:01 server id 1  end_log_pos 2136    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923081/*!*/;
     update specialty set coach_id = 10002 where specialtyid = 2
     /*!*/;
     # at 2136
     #190712 17:18:01 server id 1  end_log_pos 2163    Xid = 2872
     COMMIT/*!*/;
     # at 2163
     #190712 17:18:10 server id 1  end_log_pos 2234    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923090/*!*/;
     BEGIN
     /*!*/;
     # at 2234
     #190712 17:18:10 server id 1  end_log_pos 2359    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923090/*!*/;
     update specialty set coach_id = 10003 where specialtyid = 3
     /*!*/;
     # at 2359
     #190712 17:18:10 server id 1  end_log_pos 2386    Xid = 2873
     COMMIT/*!*/;
     # at 2386
     #190712 17:18:18 server id 1  end_log_pos 2457    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923098/*!*/;
     BEGIN
     /*!*/;
     # at 2457
     #190712 17:18:18 server id 1  end_log_pos 2582    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923098/*!*/;
     update specialty set coach_id = 10004 where specialtyid = 4
     /*!*/;
     # at 2582
     #190712 17:18:18 server id 1  end_log_pos 2609    Xid = 2874
     COMMIT/*!*/;
     # at 2609
     #190712 17:18:26 server id 1  end_log_pos 2680    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923106/*!*/;
     BEGIN
     /*!*/;
     # at 2680
     #190712 17:18:26 server id 1  end_log_pos 2805    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923106/*!*/;
     update specialty set coach_id = 10005 where specialtyid = 5
     /*!*/;
     # at 2805
     #190712 17:18:26 server id 1  end_log_pos 2832    Xid = 2875
     COMMIT/*!*/;
     # at 2832
     #190712 17:20:42 server id 1  end_log_pos 2916    Query  thread_id=2    exec_time=0    error_code=0
     SET TIMESTAMP=1562923242/*!*/;
     truncate specialty
     /*!*/;
     # at 2916
     #190712 17:15:42 server id 1  end_log_pos 2987    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     BEGIN
     /*!*/;
     # at 2987
     #190712 17:15:42 server id 1  end_log_pos 3015    Intvar
     SET INSERT_ID=1/*!*/;
     # at 3015
     #190712 17:15:42 server id 1  end_log_pos 3203    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('跑步1','1','23','1000',now())
     /*!*/;
     # at 3203
     #190712 17:15:42 server id 1  end_log_pos 3230    Xid = 2900
     COMMIT/*!*/;
     # at 3230
     #190712 17:15:42 server id 1  end_log_pos 3301    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     BEGIN
     /*!*/;
     # at 3301
     #190712 17:15:42 server id 1  end_log_pos 3329    Intvar
     SET INSERT_ID=2/*!*/;
     # at 3329
     #190712 17:15:42 server id 1  end_log_pos 3517    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('读书1','1','24','1000',now())
     /*!*/;
     # at 3517
     #190712 17:15:42 server id 1  end_log_pos 3544    Xid = 2906
     COMMIT/*!*/;
     # at 3544
     #190712 17:15:42 server id 1  end_log_pos 3615    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     BEGIN
     /*!*/;
     # at 3615
     #190712 17:15:42 server id 1  end_log_pos 3643    Intvar
     SET INSERT_ID=3/*!*/;
     # at 3643
     #190712 17:15:42 server id 1  end_log_pos 3831    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('写字1','1','25','1000',now())
     /*!*/;
     # at 3831
     #190712 17:15:42 server id 1  end_log_pos 3858    Xid = 2912
     COMMIT/*!*/;
     # at 3858
     #190712 17:15:42 server id 1  end_log_pos 3929    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     BEGIN
     /*!*/;
     # at 3929
     #190712 17:15:42 server id 1  end_log_pos 3957    Intvar
     SET INSERT_ID=4/*!*/;
     # at 3957
     #190712 17:15:42 server id 1  end_log_pos 4145    Query  thread_id=2    exec_time=351  error_code=0
     SET TIMESTAMP=1562922942/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('画画1','1','26','1000',now())
     /*!*/;
     # at 4145
     #190712 17:15:42 server id 1  end_log_pos 4172    Xid = 2918
     COMMIT/*!*/;
     # at 4172
     #190712 17:15:47 server id 1  end_log_pos 4243    Query  thread_id=2    exec_time=346  error_code=0
     SET TIMESTAMP=1562922947/*!*/;
     BEGIN
     /*!*/;
     # at 4243
     #190712 17:15:47 server id 1  end_log_pos 4271    Intvar
     SET INSERT_ID=5/*!*/;
     # at 4271
     #190712 17:15:47 server id 1  end_log_pos 4459    Query  thread_id=2    exec_time=346  error_code=0
     SET TIMESTAMP=1562922947/*!*/;
     insert into specialty(specialty_name, type, user_id, coach_id, createtime) values('跳舞1','1','27','1000',now())
     /*!*/;
     # at 4459
     #190712 17:15:47 server id 1  end_log_pos 4486    Xid = 2924
     COMMIT/*!*/;
     # at 4486
     #190712 17:17:49 server id 1  end_log_pos 4557    Query  thread_id=2    exec_time=224  error_code=0
     SET TIMESTAMP=1562923069/*!*/;
     BEGIN
     /*!*/;
     # at 4557
     #190712 17:17:49 server id 1  end_log_pos 4682    Query  thread_id=2    exec_time=224  error_code=0
     SET TIMESTAMP=1562923069/*!*/;
     update specialty set coach_id = 10001 where specialtyid = 1
     /*!*/;
     # at 4682
     #190712 17:17:49 server id 1  end_log_pos 4709    Xid = 2929
     COMMIT/*!*/;
     # at 4709
     #190712 17:18:01 server id 1  end_log_pos 4780    Query  thread_id=2    exec_time=212  error_code=0
     SET TIMESTAMP=1562923081/*!*/;
     BEGIN
     /*!*/;
     # at 4780
     #190712 17:18:01 server id 1  end_log_pos 4905    Query  thread_id=2    exec_time=212  error_code=0
     SET TIMESTAMP=1562923081/*!*/;
     update specialty set coach_id = 10002 where specialtyid = 2
     /*!*/;
     # at 4905
     #190712 17:18:01 server id 1  end_log_pos 4932    Xid = 2934
     COMMIT/*!*/;
     # at 4932
     #190712 17:18:10 server id 1  end_log_pos 5003    Query  thread_id=2    exec_time=203  error_code=0
     SET TIMESTAMP=1562923090/*!*/;
     BEGIN
     /*!*/;
     # at 5003
     #190712 17:18:10 server id 1  end_log_pos 5128    Query  thread_id=2    exec_time=203  error_code=0
     SET TIMESTAMP=1562923090/*!*/;
     update specialty set coach_id = 10003 where specialtyid = 3
     /*!*/;
     # at 5128
     #190712 17:18:10 server id 1  end_log_pos 5155    Xid = 2939
     COMMIT/*!*/;
     # at 5155
     #190712 17:18:18 server id 1  end_log_pos 5226    Query  thread_id=2    exec_time=195  error_code=0
     SET TIMESTAMP=1562923098/*!*/;
     BEGIN
     /*!*/;
     # at 5226
     #190712 17:18:18 server id 1  end_log_pos 5351    Query  thread_id=2    exec_time=195  error_code=0
     SET TIMESTAMP=1562923098/*!*/;
     update specialty set coach_id = 10004 where specialtyid = 4
     /*!*/;
     # at 5351
     #190712 17:18:18 server id 1  end_log_pos 5378    Xid = 2944
     COMMIT/*!*/;
     # at 5378
     #190712 17:18:26 server id 1  end_log_pos 5449    Query  thread_id=2    exec_time=187  error_code=0
     SET TIMESTAMP=1562923106/*!*/;
     BEGIN
     /*!*/;
     # at 5449
     #190712 17:18:26 server id 1  end_log_pos 5574    Query  thread_id=2    exec_time=187  error_code=0
     SET TIMESTAMP=1562923106/*!*/;
     update specialty set coach_id = 10005 where specialtyid = 5
     /*!*/;
     # at 5574
     #190712 17:18:26 server id 1  end_log_pos 5601    Xid = 2949
     COMMIT/*!*/;
     # at 5601
     #190712 17:20:42 server id 1  end_log_pos 5685    Query  thread_id=2    exec_time=51   error_code=0
     SET TIMESTAMP=1562923242/*!*/;
     truncate specialty
     /*!*/;
     DELIMITER ;
     # End of log file
     ROLLBACK /* added by mysqlbinlog */;
     /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
     /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    最后一次执行删除，也是在5685这个位置；

    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 |     5685 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)



    查看字符集
    mysql> \s
    --------------
    /mine/serve/mysql/bin/mysql  Ver 14.14 Distrib 5.5.49, for Linux (x86_64) using readline 5.1

    Connection id:    2
    Current database:  newdata
    Current user:     root@localhost
    SSL:         Not in use
    Current pager:    stdout
    Using outfile:    ''
    Using delimiter:   ;
    Server version:       5.5.49-log Source distribution
    Protocol version:  10
    Connection:       Localhost via UNIX socket
    Server characterset:   utf8                   【服务器字符集】
    Db     characterset:   utf8                   【数据库字符】
    Client characterset:   utf8mb4                【客户端字符集】
    Conn.  characterset:   utf8mb4                【连接字符集】
    UNIX socket:      /tmp/mysql.sock
    Uptime:          2 hours 50 min 4 sec

    Threads: 1  Questions: 2549  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.249
    --------------

8、删除数据表数据
    mysql> truncate specialty;
    Query OK, 0 rows affected (0.01 sec)

    mysql> select * from specialty;
    Empty set (0.00 sec)

9、从库进行bin-log数据恢复

    -- 从库连接主库
    /fitness/serve/mysql/bin/mysql -h 47.104.176.145 -u slave -p slave121

    服务器数据库连接2003了，处理一下：/bin/systemctl stop firewalld.service [后续再处理]

    [root@iZ8vb2uz59jd5pisp87ljjZ ~]# /fitness/serve/mysql/bin/mysql -h 47.104.176.145 -u slave -p newdata
    Enter password:
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 5.5.49-log Source distribution

    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql>

    mysql> show tables;
    +-------------------+
    | Tables_in_newdata |
    +-------------------+
    | specialty         |
    +-------------------+
    1 row in set (0.01 sec)

    mysql> select * from specialty;
    Empty set (0.02 sec)

    -- 主库通过bin-log日志恢复数据

    [root@iZm5eizpokikoertia0x31Z bin]# pwd
    /mine/serve/mysql/bin

    [root@iZm5eizpokikoertia0x31Z bin]# ./mysqlbinlog /mine/serve/mysql/data/mysql-bin.000002 | ./mysql -u root -p
    Enter password:

    mysql> select * from specialty;
    Empty set (0.00 sec)

    数据为什么为空了呢？没有恢复吗？还是命令写差了？

    仔细想了想，刚刚执行的是整个bin-log日志，最后一次执行删除，也是在5685这个位置；

    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000002 |     5685 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

    整个执行bin-log日志，相当于插入了，又删除了，所以要避开5685这个位置。

    ./mysqlbinlog /mine/serve/mysql/data/mysql-bin.000002 --start-position=107 --stop-position=5601 | ./mysql -u root -p

    [root@iZm5eizpokikoertia0x31Z bin]# ./mysqlbinlog /mine/serve/mysql/data/mysql-bin.000002 --start-position=107 --stop-position=5601 | ./mysql -u root -p
    Enter password:
    [root@iZm5eizpokikoertia0x31Z bin]#

    查看结果：
    mysql> select * from specialty;
    +-------------+----------------+------+---------+----------+---------------------+
    | specialtyid | specialty_name | type | user_id | coach_id | createtime          |
    +-------------+----------------+------+---------+----------+---------------------+
    |           1 | 跑步1          |    1 |      23 |    10001 | 2019-07-12 17:17:49 |
    |           2 | 读书1          |    1 |      24 |    10002 | 2019-07-12 17:18:01 |
    |           3 | 写字1          |    1 |      25 |    10003 | 2019-07-12 17:18:10 |
    |           4 | 画画1          |    1 |      26 |    10004 | 2019-07-12 17:18:18 |
    |           5 | 跳舞1          |    1 |      27 |    10005 | 2019-07-12 17:18:26 |
    +-------------+----------------+------+---------+----------+---------------------+
    5 rows in set (0.01 sec)


----------------------------------------------------------------------------------------------------------------------==
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值