[root@wallet01 ~]# tar zxvf Python-3.6.1.tgz [root@wallet01 ~]# cd Python-3.6.1 [root@wallet01 Python-3.6.1]# ./configure --prefix=/usr/local/python3 [root@wallet01 Python-3.6.1]# make && make install [root@wallet01 ~]# rm -rf /usr/bin/python [root@wallet01 ~]# rm -rf /usr/bin/pip [root@wallet01 ~]# ln -s /usr/local/python3/bin/python3 /usr/bin/python [root@wallet01 ~]# ln -s /usr/local/python3/bin/pip3 /usr/bin/pip [root@wallet01 ~]# vi /etc/profile PATH=$PATH:$HOME/bin:/usr/local/python3/bin [root@wallet01 ~]# source /etc/profile [root@wallet01 ~]# python -V Python 3.6.1 [root@wallet01 ~]# pip -V pip 9.0.1 from /usr/local/python3/lib/python3.6/site-packages (python 3.6) [root@wallet01 ~]# pip install --upgrade pip [root@wallet01 ~]# pip -V pip 18.0 from /usr/local/python3/lib/python3.6/site-packages/pip (python 3.6) [root@wallet01 ~]# git clone https://github.com/danfengcao/binlog2sql.git Initialized empty Git repository in /root/binlog2sql/.git/ remote: Enumerating objects: 4, done. remote: Counting objects: 100% (4/4), done. remote: Compressing objects: 100% (4/4), done. Receiving objects: 100% (307/307), 147.79 KiB | 68 KiB/s, done. remote: Total 307 (delta 0), reused 1 (delta 0), pack-reused 303 Resolving deltas: 100% (158/158), done. [root@wallet01 ~]# cd binlog2sql [root@wallet01 binlog2sql]# pip install -r requirements.txt [root@wallet01 binlog2sql]# cd binlog2sql/ [root@wallet01 binlog2sql]# python binlog2sql.py --help usage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]] [-P PORT] [--start-file START_FILE] [--start-position START_POS] [--stop-file END_FILE] [--stop-position END_POS] [--start-datetime START_TIME] [--stop-datetime STOP_TIME] [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]] [-t [TABLES [TABLES ...]]] [--only-dml] [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B] [--back-interval BACK_INTERVAL] --start-file --起始解析文件 --stop-file --终止解析文件 --start-position --起始解析位置 --stop-position --终止解析位置 --start-datetime --起始解析时间,格式'%Y-%m-%d %H:%M:%S'。 --stop-datetime --终止解析时间,格式'%Y-%m-%d %H:%M:%S'。 -d --仅解析目标db的sql -t --仅解析目标table的sql --only-dml --仅解析dml,忽略ddl。 --sql-type --仅解析指定类型,支持insert,update,delete。 -B --生成回滚SQL [root@wallet01 ~]# mysql -uroot -pabcd.1234 mysql> grant select,replication client,replication slave on *.* to 'flashback'@'%' identified by 'flashback'; mysql> flush privileges; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) [root@wallet01 mysql]# mysql -utpcc -ptpcc mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-01-04 17:01:07 | +---------------------+ 1 row in set (0.00 sec) mysql> use tpcc100 mysql> select count(*) from warehouse; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> delete from warehouse where w_id = 1; Query OK, 1 row affected (0.07 sec) mysql> select count(*) from warehouse; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-01-04 17:14:09 | +---------------------+ 1 row in set (0.00 sec) [root@wallet01 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uflashback -p'flashback' -dtpcc100 --start-file='mysql-bin.000008' \ > --start-datetime='2019-01-04 17:01:07' \ > --stop-datetime='2019-01-04 17:14:09' DELETE FROM `tpcc100`.`warehouse` WHERE `w_id`=1 AND `w_name`='ltjfn6aUnj' AND `w_street_1`='eWRUrBRPS9nukJ' AND `w_street_2`='GXyRahKIL6Mva9cl' AND `w_city`='9CGyLhLlp9fXQhmxdD' AND `w_state`='JV' AND `w_zip`='815964094' AND `w_tax`=0.15 AND `w_ytd`=3680226.00 LIMIT 1; #start 430 end 712 time 2019-01-04 17:04:25 [root@wallet01 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uflashback -p'flashback' -dtpcc100 --start-file='mysql-bin.000008' \ > --start-position=430 --stop-position=712 -B > rollback.sql [root@wallet01 binlog2sql]# cat rollback.sql INSERT INTO `tpcc100`.`warehouse`(`w_id`, `w_name`, `w_street_1`, `w_street_2`, `w_city`, `w_state`, `w_zip`, `w_tax`, `w_ytd`) VALUES (1, 'ltjfn6aUnj', 'eWRUrBRPS9nukJ', 'GXyRahKIL6Mva9cl', '9CGyLhLlp9fXQhmxdD', 'JV', '815964094', 0.15, 3680226.00); #start 430 end 712 time 2019-01-04 17:04:25 [root@wallet01 binlog2sql]# mysql -utpcc -ptpcc < rollback.sql [root@wallet01 ~]# mysql -utpcc -ptpcc mysql> use tpcc100 Database changed mysql> select count(*) from warehouse; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)
[root@wallet04 ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-TermReadKey [root@wallet04 ~]# git clone https://github.com/innotop/innotop.git Initialized empty Git repository in /root/innotop/.git/ remote: Enumerating objects: 621, done. remote: Total 621 (delta 0), reused 0 (delta 0), pack-reused 621 Receiving objects: 100% (621/621), 1.17 MiB | 230 KiB/s, done. Resolving deltas: 100% (382/382), done. [root@wallet04 ~]# cd innotop [root@wallet04 innotop]# perl Makefile.PL [root@wallet04 innotop]#make [root@wallet04 innotop]#make install [root@wallet04 ~]# innotop --version innotop Ver 1.11.4 [root@wallet04 ~]# innotop --help Usage: innotop--askpass Prompt for a password when connecting to MySQL --[no]color -C Use terminal coloring (default) --config -c Config file to read --count Number of updates before exiting --delay -d Delay between updates in seconds --help Show this help message --host -h Connect to host --[no]inc -i Measure incremental differences --mode -m Operating mode to start in --nonint -n Non-interactive, output tab-separated fields --password -p Password to use for connection --port -P Port number to use for connection --skipcentral -s Skip reading the central configuration file --socket -S MySQL socket to use for connection --spark Length of status sparkline (default 10) --timestamp -t Print timestamp in -n mode (1: per iter; 2: per line) --user -u User for login if not current user --version Output version information and exit --write -w Write running configuration into home directory if no config files were loaded [root@wallet04 ~]# innotop --user root --askpass --host 192.168.1.201 Enter password : Switch to a different mode: A Dashboard I InnoDB I/O Info Q Query List B InnoDB Buffers K InnoDB Lock Waits R InnoDB Row Ops C Command Summary L Locks S Variables & Status D InnoDB Deadlocks M Replication Status T InnoDB Txns F InnoDB FK Err O Open Tables U User Statistics Actions: d Change refresh interval p Pause innotop i Toggle incremental status display q Quit innotop n Switch to the next connection Other: TAB Switch to the next server group / Quickly filter what you see ! Show license and warranty = Toggle aggregation # Select/create server groups @ Select/create server connections $ Edit configuration settings \ Clear quick-filters Press any key to continue Q Query List When Load Cxns QPS Slow Se/In/Up/De% QCacheHit KCacheHit BpsIn BpsOut Now 0.02 29 365.11 0 97/ 0/ 0/ 0 0.00% 100.00% 297.38k 1.59M Total 0.00 100 13.98 28 69/ 7/ 6/ 0 0.00% 99.99% 4.06k 23.66k Cmd ID State User Host DB Time Query B InnoDB Buffers _____________________________ Buffer Pool _____________________________ Size Free Bufs Pages Dirty Pages Hit Rate Memory Add'l Pool 256.00k 179732 78992 0 100 / 100 4.09G 0 ____________________ Page Statistics _____________________ Reads Writes Created Reads/Sec Writes/Sec Creates/Sec 73859 922096 6753 0.00 0.00 0.00 ______________________ Insert Buffers ______________________ Inserts Merged Recs Merges Size Free List Len Seg. Size 0 0 1 1811 1813 __________________ Adaptive Hash Index __________________ Size Cells Used Node Heap Bufs Hash/Sec Non-Hash/Sec 8.44M 3418 0.50 0.00 I InnoDB I/O Info _________________________ I/O Threads __________________________ Thread Purpose Thread Status 0 insert buffer thread waiting for completed aio requests 1 log thread waiting for completed aio requests 2 read thread waiting for completed aio requests 3 read thread waiting for completed aio requests 4 read thread waiting for completed aio requests 5 read thread waiting for completed aio requests 6 write thread waiting for completed aio requests 7 write thread waiting for completed aio requests 8 write thread waiting for completed aio requests 9 write thread waiting for completed aio requests ____________________________ Pending I/O _____________________________ Async Rds Async Wrt IBuf Async Rds Sync I/Os Log Flushes Log I/Os 0 0 0 0 ________________________ File I/O Misc _________________________ OS Reads OS Writes OS fsyncs Reads/Sec Writes/Sec Bytes/Sec 74180 2867756 2094872 0.00 0.00 0 _____________________ Log Statistics ______________________ Sequence No. Flushed To Last Checkpoint IO Done IO/Sec 75091882485 75091882485 75091882485 1829529 0.00