首页 注册 登录
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 · 4602 次点击
    这是一个创建于 2283 天前的主题，其中的信息可能已经有所发展或是发生改变。

    问题一：

     /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才行

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

  • set
  • Server
  • crc32
  • echo
    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   ·   实用小工具   ·   2757 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 08:16 · PVG 16:16 · LAX 00:16 · JFK 03:16
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.