mysqlbing 回滚数据问题

问题一：

 /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-datetme=="$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才行

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