V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
fanne
V2EX  ›  MySQL

mysqlbing 回滚数据问题

  •  
  •   fanne · 2018-08-25 00:50:35 +08:00 · 4453 次点击
    这是一个创建于 2042 天前的主题,其中的信息可能已经有所发展或是发生改变。

    问题一:

     /usr/local/bin/mysqlbinlog  --start-datetime="2018-08-24 16:33:57" --stop-datetime="2018-08-24 16:34:36" mysql-bin.000037 > abc.sql 
    
    mysql-bin.000037文件不带时间直接使用mysqlbinlog导出,数据为
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #180824 16:24:46 server id 1  end_log_pos 123 CRC32 0x9d42afe1 	Start: binlog v 4, server v 5.7.23-log created 180824 16:24:46
    BINLOG '
    zsB/Ww8BAAAAdwAAAHsAAAAAAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
    AeGvQp0=
    '/*!*/;
    # at 123
    #180824 16:24:46 server id 1  end_log_pos 154 CRC32 0xc6c7a9ef 	Previous-GTIDs
    # [empty]
    # at 154
    #180824 16:33:57 server id 1  end_log_pos 219 CRC32 0x4237dfaf 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 219
    #180824 16:33:57 server id 1  end_log_pos 410 CRC32 0xce570534 	Query	thread_id=67	exec_time=0	error_code=0
    use `mytest`/*!*/;
    SET TIMESTAMP=1535099637/*!*/;
    SET @@session.pseudo_thread_id=67/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1436549152/*!*/;
    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.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    CREATE TABLE `mytest`.`addrinfo`  (
      `addr` char(40) NULL,
      `phone` int(11) NOT NULL,
      PRIMARY KEY (`phone`)
    )
    /*!*/;
    # at 410
    #180824 16:34:36 server id 1  end_log_pos 475 CRC32 0x7c6e12af 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 475
    #180824 16:34:36 server id 1  end_log_pos 558 CRC32 0x2a7049d9 	Query	thread_id=67	exec_time=0	error_code=0
    SET TIMESTAMP=1535099676/*!*/;
    BEGIN
    /*!*/;
    # at 558
    #180824 16:34:36 server id 1  end_log_pos 723 CRC32 0x5a243519 	Query	thread_id=67	exec_time=0	error_code=0
    SET TIMESTAMP=1535099676/*!*/;
    INSERT INTO `mytest`.`addrinfo`(`addr`, `phone`) VALUES ('类似树挪死', 1238374628)
    /*!*/;
    # at 723
    #180824 16:34:36 server id 1  end_log_pos 754 CRC32 0xcdb7d6c9 	Xid = 7802
    COMMIT/*!*/;
    # at 754
    #180824 16:36:22 server id 1  end_log_pos 801 CRC32 0x7f91c86c 	Rotate to mysql-bin.000038  pos: 4
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    
    导出abc.sql文件的内容为
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #180824 16:24:46 server id 1  end_log_pos 123 CRC32 0x9d42afe1 	Start: binlog v 4, server v 5.7.23-log created 180824 16:24:46
    BINLOG '
    zsB/Ww8BAAAAdwAAAHsAAAAAAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
    AeGvQp0=
    '/*!*/;
    # at 154
    #180824 16:33:57 server id 1  end_log_pos 219 CRC32 0x4237dfaf 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 219
    #180824 16:33:57 server id 1  end_log_pos 410 CRC32 0xce570534 	Query	thread_id=67	exec_time=0	error_code=0
    use `mytest`/*!*/;
    SET TIMESTAMP=1535099637/*!*/;
    SET @@session.pseudo_thread_id=67/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1436549152/*!*/;
    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.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    CREATE TABLE `mytest`.`addrinfo`  (
      `addr` char(40) NULL,
      `phone` int(11) NOT NULL,
      PRIMARY KEY (`phone`)
    )
    /*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    
    那么问题来了

    开始时间为2018-08-24 16:33:57,那abc.sql文件中为啥180824 16:24:46这个时间点的

    结束时间为2018-08-24 16:34:36,那abc.sql文件中为啥没有180824 16:34:36这个时间点的

    问题二

    mac os下关于date时间函数问题 我写了一个脚本,用作,输入时间点进行binlog回滚点,时间点使用参数形式传进去后,是一个字符串的形式,我想把这个字符串转为时间类型,不知要如何转,试了几种方式,不得其法。

    我的大概脚本内容

    incre_recov(){
        echo '增量回滚数据,需要三个参数,binlog 文件,start-datetime,stop-datetime'
        echo 'start-datetime 格式为:"2018-08-23 14:01:00",此处为双引号'
        echo $#
        if [ $# != 6 ];then
            echo "参数数量不对"
        else
            echo '$1' + 'is' + $1
            echo '$2' + 'is' + "$2"
            echo '$3' + 'is' + "$3"
            echo '$4' + 'is' + "$4"
            echo '$5' + 'is' + "$5"
            echo '$6' + 'is' + "$6"
          
            start_time=`date -j -f "%Y-%m-%d %H:%M:%S" "$3 $4" "+%s"`
            end_time=`date -j -f "%Y-%m-%d %H:%M:%S" "$5 $6" "+%s"`
            ${MysqlPath}/mysqlbinlog  --start-datetime="$start_time" --stop-datetime=="$end_time"  $2 > abc.sql
            echo "${MysqlPath}/mysqlbinlog  --start-datetime="$start_time" --stop-datetime="$end_time"  $2 > abc.sql"
           
        fi
        
    case "$1" in
        incre_recov)
        incre_recov $*
        ;;
        *)
    	echo "$0 [incre_recov]"
    
    
    	exit 0
    esac
    }
    

    脚本运行结果

    sos:work_shell apple$ sh MysqlFullBack.sh incre_recov /Users/apple/work_shell/incre_backup/20180824/mysql-bin.000037 "2018-08-24 16:33:57" "2018-08-24 16:34:36"
    增量回滚数据,需要三个参数,binlog 文件全路径,start-datetime,stop-datetime
    start-datetime 格式为:"2018-08-23 14:01:00",此处为双引号
    6
    $1 + is + incre_recov
    $2 + is + /Users/apple/work_shell/incre_backup/20180824/mysql-bin.000037
    $3 + is + 2018-08-24
    $4 + is + 16:33:57
    $5 + is + 2018-08-24
    $6 + is + 16:34:36
    ERROR: Incorrect date and time argument: 1535099637
    /usr/local/bin/mysqlbinlog  --start-datetime=1535099637 --stop-datetime=1535099676  /Users/apple/work_shell/incre_backup/20180824/mysql-bin.000037 > abc.sql
    

    按照mysqlbin --help这里--start-datetime--stop-datetime=,格式要为DATETIME and TIMESTAMP才行

    那么我这个传进去的起止时间要怎么处理?

    2 条回复    2018-08-25 01:10:46 +08:00
    likuku
        1
    likuku  
       2018-08-25 01:05:11 +08:00
    是因为没有可用的有效备份嘛?才搞这么麻烦?
    fanne
        2
    fanne  
    OP
       2018-08-25 01:10:46 +08:00
    @likuku #1 不是,只是用此列个自己疑惑的案例,希望 v2 这里能解惑一下。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5442 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 08:39 · PVG 16:39 · LAX 01:39 · JFK 04:39
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.