首页 > 数据库 > 其他综合 >

MySQL之数据库工具

2019-02-27

MySQL之数据库工具。[root@wallet01 ~] git clone https: github com danfengcao binlog2sql git Initialized empty Git repository in root binlog2sql git 。

[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&#39;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


相关文章
最新文章
热点推荐