热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

关于mysql:Windows环境下MySQL80的binlog恢复

前言:数据库被误删过,想着学习mysql,将其整理出来,才有本次记录,本文为本人学习mysql记录,线上环境慎用,自己也不是业余的DBA。

前言: 数据库被误删过,想着学习 mysql ,将其整理出来,才有本次记录,本文为本人学习mysql记录,线上环境慎用,自己也不是业余的DBA。

一、MySQL日志

  1. mysql 有两个重要的日志模块 redo log (重做日志) 和 binlog (归档日志),
  • 当有一条数据须要更新时,InnoDB引擎会在零碎闲暇的时候,将记录更新到磁盘中。会先将记录寄存到 redo log 里,在更新内存,这个时候更新就实现了,InnoDB引擎会在零碎闲暇的时候,将记录更新到磁盘中,redo log是物理日志,记录在某个数据做了什么批改, redo log 是InnoDB引擎中特有的日志,换句话说 redo log 是InnoDB引擎为了进步零碎效率,用来做日志缓冲的。
  • binlog 是记录所有数据库表构造变更以及表数据批改的二进制日志(不会记录 SELECTSHOW 这类操作),binlog 是逻辑日志,记录语句的原始逻辑。
  • 数据恢复: 能够通过 mysqlbinlog工具进行数据恢复。

二、数据恢复

  1. 当初数据库执行上面命令,看MySQL日志是否开启

    mysql> show variables like 'log_bin%';
    +---------------------------------+---------------------------------------------------------+
    | Variable_name                   | Value                                                   |
    +---------------------------------+---------------------------------------------------------+
    | log_bin                         | ON                                                      |
    | log_bin_basename                | C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog       |
    | log_bin_index                   | C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.index |
    | log_bin_trust_function_creators | OFF                                                     |
    | log_bin_use_v1_row_events       | OFF                                                     |
    +---------------------------------+---------------------------------------------------------+
    

    这里显示的ON为开启,如果显示OFF为敞开,关上上面门路,编辑 my.ini

    C:\ProgramData\MySQL\MySQL Server 8.0

    增加上面属性,大略127行

    # 不记录每条sql语句的上下文信息,仅需记录哪条数据被批改了,批改成什么样了
    binlog_format = ROW
    # binlog过期清理工夫;
    expire_logs_days= 7 
    # binlog每个日志文件大小;
    max_binlog_size = 512m 
    # binlog缓存大小;
    binlog_cache_size = 128m 
    # 最大binlog缓存大小。
    max_binlog_cache_size = 512m 
  2. 筹备数据

    -- --------------------------------------------------------
    -- 主机:                           127.0.0.1
    -- 服务器版本:                        8.0.19 - MySQL Community Server - GPL
    -- 服务器操作系统:                      Win64
    -- HeidiSQL 版本:                  11.2.0.6213
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!50503 SET NAMES utf8mb4 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    
    
    -- 导出 mp_test 的数据库构造
    CREATE DATABASE IF NOT EXISTS `mp_test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
    USE `mp_test`;
    
    -- 导出  表 mp_test.user 构造
    CREATE TABLE IF NOT EXISTS `user` (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(255) DEFAULT NULL COMMENT '名字',
      `create_time` datetime DEFAULT NULL COMMENT '创立工夫',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    
    -- 正在导出表  mp_test.user 的数据:~0 rows (大概)
    DELETE FROM `user`;
    /*!40000 ALTER TABLE `user` DISABLE KEYS */;
    INSERT INTO `user` (`id`, `name`, `create_time`) VALUES
     (1, '王天风', '2021-09-17 16:15:49'),
     (2, '小南', '2021-09-14 19:16:04'),
     (3, '张强', '2021-09-16 16:16:17');
    /*!40000 ALTER TABLE `user` ENABLE KEYS */;
    
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
  3. 这个时候不小心运行了 drop TABLE user 数据和表都没了,别慌
  4. 找到以后 MySQL 记录的 binlog 文件,这里看到以后的日志是 binlog.000001 这个文件

    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000001 |     2246 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
  5. 在看一下所有的 binlog ,我这里清空过 binlog ,剩下一个

    mysql> show binary logs;
    +---------------+-----------+-----------+
    | Log_name      | File_size | Encrypted |
    +---------------+-----------+-----------+
    | binlog.000001 |      2246 | No        |
    +---------------+-----------+-----------+
  6. 查看 binlog,定位误操作的pos或者时间段,我这里是在 binlog.000001 阶段删除的,留神上面的
    Pos: 开始节点
    Event_type 类型
    End_log_pos :完结节点
    Info 形容

    mysql> show binlog events in 'binlog.000001';
    +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                             |
    +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | binlog.000001 |    4 | Format_desc    |         1 |         124 | Server ver: 8.0.19, Binlog ver: 4                                                                                                                                                                                                                |
    | binlog.000001 |  124 | Previous_gtids |         1 |         155 |                                                                                                                                                                                                                                                  |
    | binlog.000001 |  155 | Anonymous_Gtid |         1 |         232 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
    | binlog.000001 |  232 | Query          |         1 |         351 | CREATE DATABASE `mp_test` /* xid=288 */                                                                                                                                                                                                          |
    | binlog.000001 |  351 | Anonymous_Gtid |         1 |         430 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
    | binlog.000001 |  430 | Query          |         1 |         735 | use `mp_test`; CREATE TABLE `mp_test`.`user`  (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(255) NULL COMMENT '名字',
      `create_time` datetime NULL COMMENT '创立工夫',
      PRIMARY KEY (`id`)
    ) /* xid=320 */       |
    | binlog.000001 |  735 | Anonymous_Gtid |         1 |         814 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
    | binlog.000001 |  814 | Query          |         1 |         900 | BEGIN                                                                                                                                                                                                                                            |
    | binlog.000001 |  900 | Table_map      |         1 |         963 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
    | binlog.000001 |  963 | Write_rows     |         1 |        1023 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
    | binlog.000001 | 1023 | Xid            |         1 |        1054 | COMMIT /* xid=371 */                                                                                                                                                                                                                             |
    | binlog.000001 | 1054 | Anonymous_Gtid |         1 |        1133 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
    | binlog.000001 | 1133 | Query          |         1 |        1219 | BEGIN                                                                                                                                                                                                                                            |
    | binlog.000001 | 1219 | Table_map      |         1 |        1282 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
    | binlog.000001 | 1282 | Write_rows     |         1 |        1339 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
    | binlog.000001 | 1339 | Xid            |         1 |        1370 | COMMIT /* xid=374 */                                                                                                                                                                                                                             |
    | binlog.000001 | 1370 | Anonymous_Gtid |         1 |        1449 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
    | binlog.000001 | 1449 | Query          |         1 |        1535 | BEGIN                                                                                                                                                                                                                                            |
    | binlog.000001 | 1535 | Table_map      |         1 |        1598 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
    | binlog.000001 | 1598 | Write_rows     |         1 |        1655 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
    | binlog.000001 | 1655 | Xid            |         1 |        1686 | COMMIT /* xid=377 */                                                                                                                                                                                                                             |
    | binlog.000001 | 1686 | Anonymous_Gtid |         1 |        1765 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
    | binlog.000001 | 1765 | Query          |         1 |        1860 | BEGIN                                                                                                                                                                                                                                            |
    | binlog.000001 | 1860 | Table_map      |         1 |        1923 | table_id: 208 (mp_test.user)                                                                                                                                                                                                                     |
    | binlog.000001 | 1923 | Update_rows    |         1 |        2003 | table_id: 208 flags: STMT_END_F                                                                                                                                                                                                                  |
    | binlog.000001 | 2003 | Xid            |         1 |        2034 | COMMIT /* xid=380 */                                                                                                                                                                                                                             |
    | binlog.000001 | 2034 | Anonymous_Gtid |         1 |        2111 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                             |
    | binlog.000001 | 2111 | Query          |         1 |        2246 | use `mp_test`; DROP TABLE `user` /* generated by server */ /* xid=454 */                                                                                                                                                                         |
    +---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    通过 info 看到 我在 Pos 2034 End_los_pos 2246 删除了 user

  7. 先来到MySQL下的bin目录,这个目录有个 mysqlbinlog.exe,前面咱们能够通过它来进行复原,关上cmd进入上面目录

    C:\Program Files\MySQL\MySQL Server 8.0\bin
  8. cmd 运行上面命令,读取 binlog.000001从开始地位351,到完结地位2034,并输入到F:盘下 0917.sql 这个文件,binlogC:\ProgramData\MySQL\MySQL Server 8.0\Data\目录外面

    mysqlbinlog --start-position=351 --stop-position=2034 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\0917.sql
  9. 关上 MySQL 8.0 Command Line Client,就是 MySQL 自带的小黑窗口,运行上面命令

     source f:0917.sql
  10. 查看数据是否复原

三、其余参数

  1. cmd在 C:\ProgramData\MySQL\MySQL Server 8.0 门路下运行 mysqlbinlog --help

    mysqlbinlog  Ver 8.0.19 for Win64 on x86_64 (MySQL Community Server - GPL)
    Copyright (c) 2000, 2020, 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.
    
    Dumps a MySQL binary log in a format usable for viewing or for piping to
    the mysql command line client.
    
    Usage: mysqlbinlog [options] log-files
      -?, --help          Display this help and exit.
      --base64-output=name 
                       Determine when the output statements should be
                       base64-encoded BINLOG statements: 'never' disables it and
                       works only for binlogs without row-based events;
                       'decode-rows' decodes row events into commented
                       pseudo-SQL statements if the --verbose option is also
                       given; 'auto' prints base64 only when necessary (i.e.,
                       for row-based events and format description events).  If
                       no --base64-output[=name] option is given at all, the
                       default is 'auto'.
      --bind-address=name IP address to bind to.
      --character-sets-dir=name 
                       Directory for character set files.
      -d, --database=name List entries for just this database (local log only).
      --rewrite-db=name   Rewrite the row event to point so that it can be applied
                       to a new database
      -#, --debug[=#]     This is a non-debug version. Catch this and exit.
      --debug-check       This is a non-debug version. Catch this and exit.
      --debug-info        This is a non-debug version. Catch this and exit.
      --default-auth=name Default authentication client-side plugin to use.
      -D, --disable-log-bin 
                       Disable binary log. This is useful, if you enabled
                       --to-last-log and are sending the output to the same
                       MySQL server. This way you could avoid an endless loop.
                       You would also like to use it when restoring after a
                       crash to avoid duplication of the statements you already
                       have. NOTE: you will need a SUPER privilege to use this
                       option.
      -F, --force-if-open Force if binlog was not closed properly.
                       (Defaults to on; use --skip-force-if-open to disable.)
      -f, --force-read    Force reading unknown binlog events.
      -H, --hexdump       Augment output with hexadecimal and ASCII event dump.
      -h, --host=name     Get the binlog from server.
      -i, --idempotent    Notify the server to use idempotent mode before applying
                       Row Events
      -l, --local-load=name 
                       Prepare local temporary files for LOAD DATA INFILE in the
                       specified directory.
      -o, --offset=#      Skip the first N entries.
      -p, --password[=name] 
                       Password to connect to remote server.
      --plugin-dir=name   Directory for client-side plugins.
      -P, --port=#        Port number to use for connection or 0 for default to, in
                       order of preference, my.cnf, $MYSQL_TCP_PORT,
                       /etc/services, built-in default (3306).
      --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                       memory).
      -R, --read-from-remote-server 
                       Read binary logs from a MySQL server. This is an alias
                       for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
      --read-from-remote-master=name 
                       Read binary logs from a MySQL server through the
                       COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
                       setting the option to either BINLOG-DUMP-NON-GTIDS or
                       BINLOG-DUMP-GTIDS, respectively. If
                       --read-from-remote-master=BINLOG-DUMP-GTIDS is combined
                       with --exclude-gtids, transactions can be filtered out on
                       the master avoiding unnecessary network traffic.
      --raw               Requires -R. Output raw binlog data instead of SQL
                       statements, output is to log files.
      -r, --result-file=name 
                       Direct output to a given file. With --raw this is a
                       prefix for the file names.
      --server-id=#       Extract only binlog entries created by the server having
                       the given id.
      --server-id-bits=#  Set number of significant bits in server-id
      --set-charset=name  Add 'SET NAMES character_set' to the output.
      --shared-memory-base-name=name 
                       Base name of shared memory.
      -s, --short-form    Just show regular queries: no extra info and no row-based
                       events. This is for testing only, and should not be used
                       in production systems. If you want to suppress
                       base64-output, consider using --base64-output=never
                       instead.
      -S, --socket=name   The socket file to use for connection.
      --server-public-key-path=name 
                       File path to the server public RSA key in PEM format.
      --get-server-public-key 
                       Get server public key
      --ssl-mode=name     SSL connection mode.
      --ssl-ca=name       CA file in PEM format.
      --ssl-capath=name   CA directory.
      --ssl-cert=name     X509 cert in PEM format.
      --ssl-cipher=name   SSL cipher to use.
      --ssl-key=name      X509 key in PEM format.
      --ssl-crl=name      Certificate revocation list.
      --ssl-crlpath=name  Certificate revocation list path.
      --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1,
                       TLSv1.2, TLSv1.3
      --ssl-fips-mode=name 
                       SSL FIPS mode (applies only for OpenSSL); permitted
                       values are: OFF, ON, STRICT
      --tls-ciphersuites=name 
                       TLS v1.3 cipher to use.
      --start-datetime=name 
                       Start reading the binlog at first event having a datetime
                       equal or posterior to the argument; the argument must be
                       a date and time in the local time zone, in any format
                       accepted by the MySQL server for DATETIME and TIMESTAMP
                       types, for example: 2004-12-25 11:25:56 (you should
                       probably use quotes for your shell to set it properly).
      -j, --start-position=# 
                       Start reading the binlog at position N. Applies to the
                       first binlog passed on the command line.
      --stop-datetime=name 
                       Stop reading the binlog at first event having a datetime
                       equal or posterior to the argument; the argument must be
                       a date and time in the local time zone, in any format
                       accepted by the MySQL server for DATETIME and TIMESTAMP
                       types, for example: 2004-12-25 11:25:56 (you should
                       probably use quotes for your shell to set it properly).
      --stop-never        Wait for more data from the server instead of stopping at
                       the end of the last log. Implicitly sets --to-last-log
                       but instead of stopping at the end of the last log it
                       continues to wait till the server disconnects.
      --stop-never-slave-server-id=# 
                       The slave server_id used for --read-from-remote-server
                       --stop-never. This option cannot be used together with
                       connection-server-id.
      --connection-server-id=# 
                       The slave server_id used for --read-from-remote-server.
                       This option cannot be used together with
                       stop-never-slave-server-id.
      --stop-position=#   Stop reading the binlog at position N. Applies to the
                       last binlog passed on the command line.
      -t, --to-last-log   Requires -R. Will not stop at the end of the requested
                       binlog but rather continue printing until the end of the
                       last binlog of the MySQL server. If you send the output
                       to the same MySQL server, that may lead to an endless
                       loop.
      -u, --user=name     Connect to the remote server as username.
      -v, --verbose       Reconstruct pseudo-SQL statements out of row events. -v
                       -v adds comments on column data types.
      -V, --version       Print version and exit.
      --open-files-limit=# 
                       Used to reserve file descriptors for use by this program.
      -c, --verify-binlog-checksum 
                       Verify checksum binlog events.
      --binlog-row-event-max-size=# 
                       The maximum size of a row-based binary log event in
                       bytes. Rows will be grouped into events smaller than this
                       size if possible. This value must be a multiple of 256.
      --skip-gtids        Do not preserve Global Transaction Identifiers; instead
                       make the server execute the transactions as if they were
                       new.
      --include-gtids=name 
                       Print events whose Global Transaction Identifiers were
                       provided.
      --exclude-gtids=name 
                       Print all events but those whose Global Transaction
                       Identifiers were provided.
      --print-table-metadata 
                       Print metadata stored in Table_map_log_event
      -C, --compress      Use compression in server/client protocol.
      --compression-algorithms=name 
                       Use compression algorithm in server/client protocol.
                       Valid values are any combination of
                       'zstd','zlib','uncompressed'.
      --zstd-compression-level=# 
                       Use this compression level in the client/server protocol,
                       in case --compression-algorithms=zstd. Valid range is
                       between 1 and 22, inclusive. Default is 3.
      --require-row-format 
                       Fail when printing an event that was not logged using row
                       format or other forbidden events like Load instructions
                       or the creation/deletion of temporary tables.
    
    Default options are read from the following files in the given order:
    C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MySQL\MySQL Server 8.0\my.ini C:\Program Files\MySQL\MySQL Server 8.0\my.cnf 
    The following groups are read: mysqlbinlog client
    The following options may be given as the first argument:
    --print-defaults        Print the program argument list and exit.
    --no-defaults           Don't read default options from any option file,
                         except for login file.
    --defaults-file=#       Only read default options from the given file #.
    --defaults-extra-file=# Read this file after the global files are read.
    --defaults-group-suffix=#
                         Also read groups with concat(group, suffix)
    --login-path=#          Read this path from the login file.
    
    Variables (--variable-name=value)
    and boolean options {FALSE|TRUE}  Value (after reading options)
    --------------------------------- ----------------------------------------
    base64-output                     decode-rows
    bind-address                      (No default value)
    character-sets-dir                (No default value)
    database                          (No default value)
    rewrite-db                        (No default value)
    default-auth                      (No default value)
    disable-log-bin                   FALSE
    force-if-open                     TRUE
    force-read                        FALSE
    hexdump                           FALSE
    host                              (No default value)
    idempotent                        FALSE
    local-load                        (No default value)
    offset                            0
    plugin-dir                        (No default value)
    port                              0
    read-from-remote-server           FALSE
    read-from-remote-master           (No default value)
    raw                               FALSE
    result-file                       (No default value)
    server-id                         0
    server-id-bits                    32
    set-charset                       (No default value)
    shared-memory-base-name           (No default value)
    short-form                        FALSE
    socket                            (No default value)
    server-public-key-path            (No default value)
    get-server-public-key             FALSE
    ssl-ca                            (No default value)
    ssl-capath                        (No default value)
    ssl-cert                          (No default value)
    ssl-cipher                        (No default value)
    ssl-key                           (No default value)
    ssl-crl                           (No default value)
    ssl-crlpath                       (No default value)
    tls-version                       (No default value)
    tls-ciphersuites                  (No default value)
    start-datetime                    (No default value)
    start-position                    4
    stop-datetime                     (No default value)
    stop-never                        FALSE
    stop-never-slave-server-id        -1
    connection-server-id              -1
    stop-position                     18446744073709551615
    to-last-log                       FALSE
    user                              (No default value)
    open-files-limit                  18432
    verify-binlog-checksum            FALSE
    binlog-row-event-max-size         4294967040
    skip-gtids                        FALSE
    include-gtids                     (No default value)
    exclude-gtids                     (No default value)
    print-table-metadata              FALSE
    compress                          FALSE
    compression-algorithms            (No default value)
    zstd-compression-level            3
    require-row-format                FALSE
    

四、其余命令记录(慎用)

-- 是否开启binlog
show variables like 'log_bin%';

-- binlog过期工夫
show variables like '%expire_logs_days%';

-- 找到以后mysql记录的binlog文件
show master status;

-- 查看binlog日志
show binary logs;

-- binlog最大存储值
show variables like 'max_binlog_size';

-- 查看binlog,定位误操作的pos或者时间段
show binlog events in 'binlog.000001';

-- 清理3天前binlog日志(慎用)
PURGE MASTER LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

-- 删除指定日期以前的日志索引中binlog日志文件(慎用)
purge master logs before "2021-05-27 14:16:00";

-- 删除某个日志文件之前的所有日志文件(慎用)
purge binary logs to 'binlog.000003';

-- 重置所有的日志(慎用)
reset master;

-- 手动切换binlog日志
flush logs; 
-- 或者
flush binary logs;


-- 按开始和完结地位复原
mysqlbinlog --start-position=351 --stop-position=2034 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\0917.sql

mysqlbinlog --start-position=2882 --stop-position=3187 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\05271.sql

-- 按时间段复原
mysqlbinlog --start-datetime="2021-05-27 00:00:00" --stop-datetime="2021-05-27 10:01:59" "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\05276.sql

-- 按完结工夫复原
mysqlbinlog --stop-datetime="2021-05-27 10:08:59" "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000001"> f:\\05277.sql

-- 解析日志
mysqlbinlog --start-position=33839226 --stop-position=33839672 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000002"

-- mysqlbinlog 可视化查看sql语句
mysqlbinlog --base64-output=decode-rows -v "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000002" >f:\\0607.sql

-- 指定库名--database,-d
mysqlbinlog --start-position=155 --stop-position=1086 --database mp "C:\ProgramData\MySQL\MySQL Server 8.0\Data\binlog.000003">f:\\test5.sql

五、总结

数据的重要性,不必多说。在数据库中的一条数据,对应着事实中的业务,数据将虚拟世界和事实工夫相连接,对后续的业务产生重大影响。
如果是明天中午误删的表,拿到做昨天备份的数据库,在拿到明天的 binlog 就能够复原回来,缩小损失。由此可见 定期备份数据库的重要性,如果备份的周期短,复原的工夫也快,数据失落也能及时找回,不至于删库跑路。


推荐阅读
  • 基于PgpoolII的PostgreSQL集群安装与配置教程
    本文介绍了基于PgpoolII的PostgreSQL集群的安装与配置教程。Pgpool-II是一个位于PostgreSQL服务器和PostgreSQL数据库客户端之间的中间件,提供了连接池、复制、负载均衡、缓存、看门狗、限制链接等功能,可以用于搭建高可用的PostgreSQL集群。文章详细介绍了通过yum安装Pgpool-II的步骤,并提供了相关的官方参考地址。 ... [详细]
  • 本文详细介绍了SQL日志收缩的方法,包括截断日志和删除不需要的旧日志记录。通过备份日志和使用DBCC SHRINKFILE命令可以实现日志的收缩。同时,还介绍了截断日志的原理和注意事项,包括不能截断事务日志的活动部分和MinLSN的确定方法。通过本文的方法,可以有效减小逻辑日志的大小,提高数据库的性能。 ... [详细]
  • 计算机存储系统的层次结构及其优势
    本文介绍了计算机存储系统的层次结构,包括高速缓存、主存储器和辅助存储器三个层次。通过分层存储数据可以提高程序的执行效率。计算机存储系统的层次结构将各种不同存储容量、存取速度和价格的存储器有机组合成整体,形成可寻址存储空间比主存储器空间大得多的存储整体。由于辅助存储器容量大、价格低,使得整体存储系统的平均价格降低。同时,高速缓存的存取速度可以和CPU的工作速度相匹配,进一步提高程序执行效率。 ... [详细]
  • 高质量SQL书写的30条建议
    本文提供了30条关于优化SQL的建议,包括避免使用select *,使用具体字段,以及使用limit 1等。这些建议是基于实际开发经验总结出来的,旨在帮助读者优化SQL查询。 ... [详细]
  • 本文讨论了在数据库打开和关闭状态下,重新命名或移动数据文件和日志文件的情况。针对性能和维护原因,需要将数据库文件移动到不同的磁盘上或重新分配到新的磁盘上的情况,以及在操作系统级别移动或重命名数据文件但未在数据库层进行重命名导致报错的情况。通过三个方面进行讨论。 ... [详细]
  • 在Oracle11g以前版本中的的DataGuard物理备用数据库,可以以只读的方式打开数据库,但此时MediaRecovery利用日志进行数据同步的过 ... [详细]
  • 从Oracle安全移植到国产达梦数据库的DBA实践与攻略
    随着我国对信息安全和自主可控技术的重视,国产数据库在党政机关、军队和大型央企等行业中得到了快速应用。本文介绍了如何降低从Oracle到国产达梦数据库的技术门槛,保障用户现有业务系统投资。具体包括分析待移植系统、确定移植对象、数据迁移、PL/SQL移植、校验移植结果以及应用系统的测试和优化等步骤。同时提供了移植攻略,包括待移植系统分析和准备移植环境的方法。通过本文的实践与攻略,DBA可以更好地完成Oracle安全移植到国产达梦数据库的工作。 ... [详细]
  • 本文介绍了在开发Android新闻App时,搭建本地服务器的步骤。通过使用XAMPP软件,可以一键式搭建起开发环境,包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表,并设置相应的属性。最后,给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]
  • 本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目,以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数,以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]
  • 图解redis的持久化存储机制RDB和AOF的原理和优缺点
    本文通过图解的方式介绍了redis的持久化存储机制RDB和AOF的原理和优缺点。RDB是将redis内存中的数据保存为快照文件,恢复速度较快但不支持拉链式快照。AOF是将操作日志保存到磁盘,实时存储数据但恢复速度较慢。文章详细分析了两种机制的优缺点,帮助读者更好地理解redis的持久化存储策略。 ... [详细]
  • Java String与StringBuffer的区别及其应用场景
    本文主要介绍了Java中String和StringBuffer的区别,String是不可变的,而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象,内存使用上要优于String类。因此,在需要频繁对字符串进行修改的情况下,使用StringBuffer更加适合。同时,文章还介绍了String和StringBuffer的应用场景。 ... [详细]
  • 前景:当UI一个查询条件为多项选择,或录入多个条件的时候,比如查询所有名称里面包含以下动态条件,需要模糊查询里面每一项时比如是这样一个数组条件:newstring[]{兴业银行, ... [详细]
  • 本文详细介绍了如何使用MySQL来显示SQL语句的执行时间,并通过MySQL Query Profiler获取CPU和内存使用量以及系统锁和表锁的时间。同时介绍了效能分析的三种方法:瓶颈分析、工作负载分析和基于比率的分析。 ... [详细]
  • Oracle优化新常态的五大禁止及其性能隐患
    本文介绍了Oracle优化新常态中的五大禁止措施,包括禁止外键、禁止视图、禁止触发器、禁止存储过程和禁止JOB,并分析了这些禁止措施可能带来的性能隐患。文章还讨论了这些禁止措施在C/S架构和B/S架构中的不同应用情况,并提出了解决方案。 ... [详细]
  • 本文介绍了一种轻巧方便的工具——集算器,通过使用集算器可以将文本日志变成结构化数据,然后可以使用SQL式查询。集算器利用集算语言的优点,将日志内容结构化为数据表结构,SPL支持直接对结构化的文件进行SQL查询,不再需要安装配置第三方数据库软件。本文还详细介绍了具体的实施过程。 ... [详细]
author-avatar
书友74562696
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有