My Certification

Oracle Database 10g OCP
Oracle Database 11g OCP
Oracle Database 12c OCP
Oracle GoldenGate 10 OCS
Oracle 11gR2 RAC OCE
MySQL 5.6 Database OCP
Novell SUSE Linux 10 CLA
IBM Power System AIX 7.1



Displays MySQL size the database/table/index

select table_schema,
       sum(data_length + index_length) / 1024 / 1024 as total_mb,
       sum(data_length) / 1024 / 1024 as data_mb,
       sum(index_length) / 1024 / 1024 as index_mb,
       count(*) as tables,
       curdate() as today
  from information_schema.tables
 group by table_schema
 order by 2 desc;  


select table_schema,
       sum(data_length) / 1024 / 1024 as data_length,
       sum(index_length) / 1024 / 1024 as index_length,
       sum(data_length + index_length) / 1024 / 1024 as sum
  from information_schema.tables;


select table_schema,
       sum(data_length + index_length) / 1024 / 1024 as total_mb,
       sum(data_length) / 1024 / 1024 as data_mb,
       sum(index_length) / 1024 / 1024 as index_mb,
       count(*) as tables,
       curdate() as today
  from information_schema.tables
 group by table_schema
 order by 2 desc;


select concat(truncate(sum(data_length) / 1024 / 1024, 2), 'mb') as data_size,
       concat(truncate(sum(max_data_length) / 1024 / 1024, 2), 'mb') as max_data_size,
       concat(truncate(sum(data_free) / 1024 / 1024, 2), 'mb') as data_free,
       concat(truncate(sum(index_length) / 1024 / 1024, 2), 'mb') as index_size
  from information_schema.tables
 where table_schema = 'mysql';  
 
 
select table_name,
       (data_length / 1024 / 1024) as data_mb,
       (index_length / 1024 / 1024) as index_mb,
       ((data_length + index_length) / 1024 / 1024) as all_mb,
       table_rows
  from tables
 where information_schema.table_schema = 'mysql';

How to Investigate InnoDB Lock Issues

The two commands that can be used in all versions of MySQL to investigate locks preventing InnoDB queries to proceed are SHOW [FULL] PROCESSLIST and SHOW ENGINE INNODB STATUS. Additionally in MySQL 5.1 using InnoDB Plugin and in MySQL 5.5 and later, it is possible to use the InnoDB tables in the Information Schema to get details about InnoDB locks.

 

In MySQL 5.6 and later you can use the innodb_lock_waits view in the Sys Schema to find the transactions waiting for locks and the transactions holding the blocking locks.


mysql> SET @sys.statement_truncate_len = 16;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2015-11-24 12:16:18
                    wait_age: 00:00:05
               wait_age_secs: 5
                locked_table: `locktest`.`t1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 5400
         waiting_trx_started: 2015-11-24 12:16:18
             waiting_trx_age: 00:00:05
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 3
               waiting_query: UPDATE … id = 3
             waiting_lock_id: 5400:45:3:6
           waiting_lock_mode: X
             blocking_trx_id: 5397
                blocking_pid: 5
              blocking_query: NULL
            blocking_lock_id: 5397:45:3:6
          blocking_lock_mode: X
        blocking_trx_started: 2015-11-24 11:28:49
            blocking_trx_age: 00:47:34
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 5
sql_kill_blocking_connection: KILL 5
1 row in set (0.00 sec)

 

An advantage of the Information Schema tables over the process list and the InnoDB status output is that it is possible to write a query that directly gives information about the transactions and locks involved in a lock wait scenario. An example of such a query is:


SELECT r.trx_wait_started AS wait_started,
       TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age,
       TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_secs,
       rl.lock_table AS locked_table,
       rl.lock_index AS locked_index,
       rl.lock_type AS locked_type,
       r.trx_id AS waiting_trx_id,
       r.trx_started as waiting_trx_started,
       TIMEDIFF(NOW(), r.trx_started) AS waiting_trx_age,
       r.trx_rows_locked AS waiting_trx_rows_locked,
       r.trx_rows_modified AS waiting_trx_rows_modified,
       r.trx_mysql_thread_id AS waiting_pid,
       r.trx_query AS waiting_query,
       rl.lock_id AS waiting_lock_id,
       rl.lock_mode AS waiting_lock_mode,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_pid,
       b.trx_query AS blocking_query,
       bl.lock_id AS blocking_lock_id,
       bl.lock_mode AS blocking_lock_mode,
       b.trx_started AS blocking_trx_started,
       TIMEDIFF(NOW(), b.trx_started) AS blocking_trx_age,
       b.trx_rows_locked AS blocking_trx_rows_locked,
       b.trx_rows_modified AS blocking_trx_rows_modified,
       CONCAT('KILL QUERY ', b.trx_mysql_thread_id) AS sql_kill_blocking_query,
       CONCAT('KILL ', b.trx_mysql_thread_id) AS sql_kill_blocking_connection
  FROM information_schema.innodb_lock_waits w
       INNER JOIN information_schema.innodb_trx b    ON b.trx_id = w.blocking_trx_id
       INNER JOIN information_schema.innodb_trx r    ON r.trx_id = w.requesting_trx_id
       INNER JOIN information_schema.innodb_locks bl ON bl.lock_id = w.blocking_lock_id
       INNER JOIN information_schema.innodb_locks rl ON rl.lock_id = w.requested_lock_id
 ORDER BY r.trx_wait_started\G

MySQL Sys Schema Formatting Functions

formatting functions如下
format_bytes()
format_path()
format_statement()
format_time()

 
 
format_bytes()
以字节为单位进行参数转换,转换后增强了可读性,支持的最大单位是PiB(1024*1024*1024*1024*1024字节)。

mysql> SELECT sys.format_bytes(244905105);
+—————————–+
| sys.format_bytes(244905105) |
+—————————–+
| 233.56 MiB                  |
+—————————–+
1 row in set (0.01 sec)

mysql> 
mysql> SELECT FILE_NAME,
    ->               sys.format_bytes(SUM_NUMBER_OF_BYTES_READ) AS TotalRead,
    ->               sys.format_bytes(SUM_NUMBER_OF_BYTES_WRITE) AS TotalWrite
    ->          FROM performance_schema.file_summary_by_instance
    ->         ORDER BY (SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE) DESC
    ->         LIMIT 5;
+———————————————————————–+————+————+
| FILE_NAME                                                             | TotalRead  | TotalWrite |
+———————————————————————–+————+————+
| /data/mysql/mysql57/data/trans_master/t_oracle_status_history.ibd     | 27.27 MiB  | 0 bytes    |
| /data/mysql/mysql57/data/trans_master/t_oracle_tablespace_history.ibd | 19.64 MiB  | 0 bytes    |
| /data/mysql/mysql57/data/ibtmp1                                       | 0 bytes    | 13.61 MiB  |
| /data/mysql/mysql57/data/ibdata1                                      | 7.53 MiB   | 64.00 KiB  |
| /data/mysql/mysql57/data/mysql/proc.MYD                               | 885.72 KiB | 0 bytes    |
+———————————————————————–+————+————+
5 rows in set (0.00 sec)

 

format_path()
可以使用相应的配置选项来替换路径的部分,例如datadir下面的文件将使用@@datadir代替datadir路径。

mysql> SELECT sys.format_path(FILE_NAME),
    ->               (SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE) AS TotalIO
    ->          FROM performance_schema.file_summary_by_instance
    ->         ORDER BY (SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE) DESC
    ->         LIMIT 5;
+——————————————————–+———-+
| sys.format_path(FILE_NAME)                             | TotalIO  |
+——————————————————–+———-+
| @@datadir/trans_master/t_oracle_status_history.ibd     | 28590080 |
| @@datadir/trans_master/t_oracle_tablespace_history.ibd | 20594688 |
| @@datadir/ibtmp1                                       | 14270464 |
| @@datadir/ibdata1                                      |  7962624 |
| @@datadir/mysql/proc.MYD                               |   913601 |
+——————————————————–+———-+
5 rows in set (0.01 sec)

 

format_statement()
接受一个字符串,字符总长度取决于@sys.statement_truncate_len的值。

mysql> SELECT * FROM sys.sys_config WHERE variable = 'statement_truncate_len';
+————————+——-+———————+——–+
| variable               | value | set_time            | set_by |
+————————+——-+———————+——–+
| statement_truncate_len | 64    | 2017-03-14 10:44:47 | NULL   |
+————————+——-+———————+——–+

mysql> SELECT sys.format_statement(SQL_TEXT) AS Query,
              TIMER_WAIT FROM performance_schema.events_statements_history
        ORDER BY EVENT_ID DESC
        LIMIT 3;
+——————————————————————-+————-+
| Query                                                             | TIMER_WAIT  |
+——————————————————————-+————-+
| UPDATE world.Country SET Popul … n + 1250000 WHERE Code = 'AUS' | 10716179000 |
| UPDATE world.City SET Population = 4500000 WHERE ID = 130         |   521569000 |
| SELECT * FROM world.City WHERE CountryCode = 'AUS'                |   440458000 |
+——————————————————————-+————-+
3 rows in set (0.00 sec)


format_time()
将延时语句转换为可读的格式。

mysql> SELECT SQL_TEXT AS Query,
              sys.format_time(TIMER_WAIT) AS Latency
         FROM performance_schema.events_statements_history
        ORDER BY EVENT_ID DESC
        LIMIT 3;
+——————————————————————————-+———–+
| Query                                                                         | Latency   |
+——————————————————————————-+———–+
| UPDATE world.Country SET Population = Population + 1250000 WHERE Code = 'AUS' | 10.72 ms  |
| UPDATE world.City SET Population = 4500000 WHERE ID = 130                     | 521.57 us |
| SELECT * FROM world.City WHERE CountryCode = 'AUS'                            | 440.46 us |
+——————————————————————————-+———–+
3 rows in set (0.00 sec)

使用MySQL Enterprise Backup进行Partial Backups

MySQL version 5.7.16

MySQL Enterprise Backup version 4.1.0

 

Tables i1, i2, i3 in all databases use InnoDB storage engine and tables m1, m2, m3 use MyISAM storage engine.
mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| demo               |
| dtmonitor          |
| monitor            |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test2              |
| test_exclude       |
| test_include       |
| trans_master       |
| zwc                |
+——————–+

mysql> show tables from test_exclude;
+————————+
| Tables_in_test_exclude |
+————————+
| i1                     |
| i2                     |
| i3                     |
| m1                     |
| m2                     |
| m3                     |
+————————+
6 rows in set (0.00 sec)

mysql> show tables from test_include;
+————————+
| Tables_in_test_include |
+————————+
| i1                     |
| i2                     |
| i3                     |
| m1                     |
| m2                     |
| m3                     |
+————————+
6 rows in set (0.00 sec)

Continue reading 使用MySQL Enterprise Backup进行Partial Backups

Kubernetes的应用部署,scale Up Down和滚动升级

当前环境是一套3 master nodes和11 worker nodes的k8s 1.7 HA Cluster
[root@k8s-master1 /root]#kubectl get nodes -o wide
NAME          STATUS                     AGE       VERSION   EXTERNAL-IP   OS-IMAGE                KERNEL-VERSION
k8s-master1   Ready,SchedulingDisabled   194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-master2   Ready,SchedulingDisabled   194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-master3   Ready,SchedulingDisabled   194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node1     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node10    Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node11    Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node2     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node3     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node4     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node5     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node6     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node7     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node8     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64
k8s-node9     Ready                      194d      v1.7.0    <none>        CentOS Linux 7 (Core)   3.10.0-514.el7.x86_64


部署应用程序
Continue reading Kubernetes的应用部署,scale Up Down和滚动升级

Installation GreenPlum Performance Monitor on CentOS 7.3

使用gpadmin用户创建gpperfmon的数据库, 数据库默认使用gpmon用户
[gpadmin@mdw /home/gpadmin]$ gpperfmon_install –enable –password gpmon –port 5432
20180129:16:55:45:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-createdb gpperfmon >& /dev/null
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-PGPORT=5432 psql -f /usr/local/greenplum-db/./lib/gpperfmon/gpperfmon.sql gpperfmon >& /dev/null
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-PGPORT=5432 psql template1 -c "DROP ROLE IF EXISTS gpmon"  >& /dev/null
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-PGPORT=5432 psql template1 -c "CREATE ROLE gpmon WITH SUPERUSER CREATEDB LOGIN ENCRYPTED PASSWORD 'gpmon'"  >& /dev/null
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-echo "local    gpperfmon         gpmon         md5" >> /data/gpmaster/gpseg-1/pg_hba.conf
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-echo "host     all         gpmon         127.0.0.1/28    md5" >> /data/gpmaster/gpseg-1/pg_hba.conf
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-echo "host     all         gpmon         ::1/128    md5" >> /data/gpmaster/gpseg-1/pg_hba.conf
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-touch /home/gpadmin/.pgpass >& /dev/null
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-mv -f /home/gpadmin/.pgpass /home/gpadmin/.pgpass.1517216145 >& /dev/null
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-echo "*:5432:gpperfmon:gpmon:gpmon" >> /home/gpadmin/.pgpass
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-cat /home/gpadmin/.pgpass.1517216145 >> /home/gpadmin/.pgpass
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-chmod 0600 /home/gpadmin/.pgpass >& /dev/null
20180129:16:55:47:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-PGPORT=5432 gpconfig -c gp_enable_gpperfmon -v on >& /dev/null
20180129:16:55:48:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-PGPORT=5432 gpconfig -c gpperfmon_port -v 8888 >& /dev/null
20180129:16:55:49:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-PGPORT=5432 gpconfig -c gp_external_enable_exec -v on –masteronly >& /dev/null
20180129:16:55:49:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-PGPORT=5432 gpconfig -c gpperfmon_log_alert_level -v warning >& /dev/null
20180129:16:55:49:081504 gpperfmon_install:mdw:gpadmin-[INFO]:-gpperfmon will be enabled after a full restart of GPDB


重启gpdb查看gpmmon进程
[gpadmin@mdw /usr/local/greenplum-db/lib/gpperfmon]$ gpstop -afr
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -afr
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating the environment…
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Obtaining Segment details from master…
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.4.0 build commit:1971b301f52979ac74fb3d0a141bbaae06b70857'
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-There are 0 connections to the database
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast'
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Master host=mdw
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Detected 0 connections to database
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Using standard WAIT mode of 120 seconds
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Commencing Master instance shutdown with mode=fast
20180129:17:04:59:082798 gpstop:mdw:gpadmin-[INFO]:-Master segment instance directory=/data/gpmaster/gpseg-1
20180129:17:05:00:082798 gpstop:mdw:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process
20180129:17:05:00:082798 gpstop:mdw:gpadmin-[INFO]:-Terminating processes for segment /data/gpmaster/gpseg-1
20180129:17:05:00:082798 gpstop:mdw:gpadmin-[INFO]:-Stopping master standby host smdw mode=fast
20180129:17:05:02:082798 gpstop:mdw:gpadmin-[INFO]:-Successfully shutdown standby process on smdw
20180129:17:05:02:082798 gpstop:mdw:gpadmin-[INFO]:-Targeting dbid [2, 8, 3, 9, 4, 10, 5, 11, 6, 12, 7, 13] for shutdown
20180129:17:05:02:082798 gpstop:mdw:gpadmin-[INFO]:-Commencing parallel primary segment instance shutdown, please wait…
20180129:17:05:02:082798 gpstop:mdw:gpadmin-[INFO]:-0.00% of jobs completed
20180129:17:05:03:082798 gpstop:mdw:gpadmin-[INFO]:-100.00% of jobs completed
20180129:17:05:03:082798 gpstop:mdw:gpadmin-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait…
20180129:17:05:03:082798 gpstop:mdw:gpadmin-[INFO]:-0.00% of jobs completed
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-100.00% of jobs completed
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:—————————————————–
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-   Segments stopped successfully      = 12
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-   Segments with errors during stop   = 0
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:—————————————————–
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-Successfully shutdown 12 of 12 segment instances 
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-Database successfully shutdown with no errors reported
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-Cleaning up leftover gpmmon process
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-No leftover gpmmon process found
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts
20180129:17:05:04:082798 gpstop:mdw:gpadmin-[INFO]:-Cleaning up leftover shared memory
20180129:17:05:05:082798 gpstop:mdw:gpadmin-[INFO]:-Restarting System…
[gpadmin@mdw /usr/local/greenplum-db/lib/gpperfmon]$ ps -ef|grep gpmmon|grep -v grep
gpadmin   83061  83052  0 17:05 ?        00:00:00 /usr/local/greenplum-db-5.4.0/bin/gpmmon -D /data/gpmaster/gpseg-1/gpperfmon/conf/gpperfmon.conf -p 5432

Continue reading Installation GreenPlum Performance Monitor on CentOS 7.3

Installation GreenPlum 5.4.0 on CentOS 7.3

第一部分OS安装配置
安装环境
172.172.20.72 mdw
172.172.20.73 smdw
172.172.20.74 sdw1
172.172.20.75 sdw2
172.172.20.76 sdw3


操作系统
CentOS Linux release 7.3.1611 (Core)


关闭selinux
vi /etc/selinux/config
SELINUX=disabled


关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service


修改内核参数
可参考Setting the Greenplum Recommended OS Parameters
vi /etc/sysctl.conf
kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 250 512000 100 2048
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152
vm.overcommit_memory = 2

Continue reading Installation GreenPlum 5.4.0 on CentOS 7.3

MySQL flashback 工具测试

使用限制
1.binlog_format = ROW ; binlog_row_image = FULL
2.支持版本MySQL 5.6/5.7
3.只支持DML操作


使用方式
[root@sata-test ~]# flashback -h
Usage:
  flashback [OPTION...]

Help Options:
  -h, –help                  Show help options

Application Options:
  –databaseNames             databaseName to apply. if multiple, seperate by comma(,)
  –tableNames                tableName to apply. if multiple, seperate by comma(,)
  –start-position            start position
  –stop-position             stop position
  –start-datetime            start time (format %Y-%m-%d %H:%M:%S)
  –stop-datetime             stop time (format %Y-%m-%d %H:%M:%S)
  –sqlTypes                  sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
  –maxSplitSize              max file size after split, the uint is M
  –binlogFileNames           binlog files to process. if multiple, seperate by comma(,)  
  –outBinlogFileNameBase     output binlog file name base
  –logLevel                  log level, available option is debug,warning,error
  –include-gtids             gtids to process
  –exclude-gtids             gtids to skip


测试表结构
CREATE TABLE `testFlashback2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nameShort` varchar(20) DEFAULT NULL,
  `nameLong` varchar(260) DEFAULT NULL,
  `amount` decimal(19,9) DEFAULT NULL,
  `amountFloat` float DEFAULT NULL,
  `amountDouble` double DEFAULT NULL,
  `createDatetime6` datetime(6) DEFAULT NULL,
  `createDatetime` datetime DEFAULT NULL,
  `createTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `nameText` text,
  `nameBlob` blob,
  `nameMedium` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB


insert flashback
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> select count(*) from testFlashback2;
+———-+
| count(*) |
+———-+
|        2 |
+———-+
1 row in set (0.00 sec)

[root@sata-test log]# flashback –binlogFileNames=5557-bin.000017 –outBinlogFileNameBase=insert_flashback
[root@sata-test log]# /service/mysql56/bin/mysqlbinlog –skip-gtids insert_flashback.flashback | mysql56 demo 

mysql> select count(*) from testFlashback2;
+———-+
| count(*) |
+———-+
|        0 |
+———-+
1 row in set (0.01 sec)


delete flashback
mysql> insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> select count(*) from testFlashback2;
+———-+
| count(*) |
+———-+
|        2 |
+———-+
1 row in set (0.00 sec)

mysql> delete from testFlashback2;
Query OK, 2 rows affected (0.01 sec)

mysql> select count(*) from testFlashback2;
+———-+
| count(*) |
+———-+
|        0 |
+———-+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

[root@sata-test log]# flashback –binlogFileNames=5557-bin.000019 –outBinlogFileNameBase=delete_flashback
[root@sata-test log]# /service/mysql56/bin/mysqlbinlog –skip-gtids delete_flashback.flashback | mysql56 demo 

mysql> select count(*) from testFlashback2;
+———-+
| count(*) |
+———-+
|        2 |
+———-+
1 row in set (0.00 sec)


update flashback
mysql> insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.111,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> checksum table testFlashback2;
+———————+———–+
| Table               | Checksum  |
+———————+———–+
| demo.testflashback2 | 428336336 |
+———————+———–+
1 row in set (0.00 sec)

mysql> update testFlashback2 set amount=10.222;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> checksum table testFlashback2;
+———————+————+
| Table               | Checksum   |
+———————+————+
| demo.testflashback2 | 3103556756 |
+———————+————+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

[root@sata-test log]# flashback –binlogFileNames=5557-bin.000021 –outBinlogFileNameBase=update_flashback
[root@sata-test log]# /service/mysql56/bin/mysqlbinlog –skip-gtids update_flashback.flashback | mysql56 demo 

mysql> checksum table testFlashback2;
+———————+———–+
| Table               | Checksum  |
+———————+———–+
| demo.testflashback2 | 428336336 |
+———————+———–+
1 row in set (0.00 sec)