mysqlbing 回滚数据问题

2018-08-25 00:50:35 +08:00
 fanne

问题一：

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

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

4602 次点击
所在节点    MySQL
2 条回复
likuku
2018-08-25 01:05:11 +08:00
是因为没有可用的有效备份嘛？才搞这么麻烦？
fanne
2018-08-25 01:10:46 +08:00
@likuku #1 不是，只是用此列个自己疑惑的案例，希望 v2 这里能解惑一下。

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/483038

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX