1. A reproduction of the scene
Bless The Child At work in the afternoon
Operation and maintenance ： are you there ？ Just now the tools came out and there was a batch of Mysql The table does not conform to the new specification , You look at the ！
I ： well , let me have a look .
inner OS： What the hell? ？ I can't do a good job with the code ？
After a while of investigation , Basically, it's all parts Mysql Table has no primary key , It's very simple , Add a data id Just a matter of ！
What the hell? , How does this watch twenty million Data. , I don't think there should be so much data in my mind , Code review , Sure enough, it's a pot of ancestral heritage , This deletion logic doesn't work at all .
The problem is coming. , 20 million Mysql surface , With the current performance of online machines , I'm worried that I'll hang up as soon as I operate .
Next is the world of search engines , Searching ...
Why , This MysqlTools What is it ？ It's an operation and maintenance tool , But it seems that batch deletion of historical data is also very useful .
2. Tool is introduced
MysqlTools-Python It's a monitoring acquisition 、 Backup 、 On-Site Inspection 、 Log analysis 、 Automated troubleshooting Kit .
|Tool name||Functional specifications|
|mtls-monitor||Monitor item collection|
|mtls-backup||Automated database backup|
|mtls-delete-rows||partial ( moderate ) Delete rows from large tables|
|mtls-file-truncate||partial ( moderate ) Truncated physical file|
|mtls-big-files||Find out the large file name in the given directory|
|mtls-http||tcp(http) Port connectivity test|
|mtls-log||Slow query log slice|
|mtls-perf-bench||Database running tool ( In development )|
|mtls-kill-all-connections||Kill all client connections|
|mtls-sql-distribution||Statistics slow query file in SQL Type and hotspot table|
|mtls-file-stat||Statistics of the latest update time of the table|
|mtls-expired-tables||Find a table that hasn't been used for a long time|
3.Linux Environmental installation steps
3.1 Environment depends on
MySqlTools-Python Tool support Python-3.x All versions under , Directly through pip install .
3.2 Python install
stay Linux In the system , System native Python The environment is basically 2.x edition , Need to be reinstalled Python 3 Environmental Science .
Be careful ： Do not delete the system's own without special reasons Python 2 Environmental Science , Avoid relying on Python 2 There's something wrong with our tools .
Python Download address of each version ：https://www.python.org/ftp/py...
Download installation package
Unzip the installation package to the current directory
tar -xvf Python-3.9.1.tgz
Depending on the number of packages installed directly affects Python Functions supported by the interpreter , The following dependency packages can basically cover the function support .
yum -y install gcc gcc-c++ libffi libyaml-devel libffi-devel zlib zlib-devel openssl shadow-utils net-tools openssl-devel libyaml sqlite-devel libxml2 libxslt-devel libxml2-devel wget vim mysql-devel
Be careful ： If it's due to environmental problems mysql-devel Installation error , No installation .
newly build Python 3 Catalog
Enter the unzip installation package directory
Appoint Python 3 The installation directory
Compile Android Python 3
make && make install
add to Python 3 The chain of soft
ln -s /data/python3.9/bin/python3.9 /usr/bin/python3
add to pip3 The chain of soft
ln -s /data/python3.9/bin/pip3 /usr/bin/pip3
3.3 MysqlTools-Python install
Installation command ：
pip3 install mysqltools-python
verification （mtlsmonitor see MySQL Execute after startup Select Statement quantity ）：
/data/python3.9/bin/mtls-monitor --host=127.0.0.1 --port=3306 --user=monitor --password=yiyufxst ComSelect
Normal return ：
Possible misstatements ：
Traceback (most recent call last): File "/data/python3.9/bin/mtls-monitor", line 4, in <module> from mtls import base,statu,variable,mgr,replication,binlog,innodb_statu File "/data/python3.9/lib/python3.9/site-packages/mtls/base.py", line 11, in <module> import mysql.connector ModuleNotFoundError: No module named 'mysql'
mysql-connector that will do
pip3 install mysql-connector
4. Tool use
Delete big table data
Large amount of data , hypothesis test For large tables that need to be deleted , Delete data as
id < 19685538The data of , The amount of data is 221 All around .
mysql> select count(*) from test where id < 19685538; +----------+ | count(*) | +----------+ | 2209566 | +----------+ 1 row in set (1.33 sec)
Delete statement executed
cat /tmp/delete-test.sql delete from tempdb.test where id < 19685538;
adopt mlts-delete-rows Complete batch operations
mtlsdeleterows --host=127.0.0.1 --port=3306 --user=root --password=yiyufxst --rows=1000 --sql-file=/tmp/dlt.sql exec 2021-01-21 20:43:35,413 INFO 1000 row(s) affected by delete from tempdb.test where id < 19685538 limit 1000; 2021-01-21 20:43:36,422 INFO 1000 row(s) affected by delete from tempdb.test where id < 19685538 limit 1000; 2021-01-21 20:43:37,430 INFO 1000 row(s) affected by delete from tempdb.test where id < 19685538 limit 1000; ... 2021-01-21 20:51:53,561 INFO 1000 row(s) affected by delete from tempdb.test where id < 19685538 limit 1000; 2021-01-21 20:51:54,569 INFO 1000 row(s) affected by delete from tempdb.test where id < 19685538 limit 1000; 2021-01-21 20:51:55,576 INFO 566 row(s) affected by delete from tempdb.test where id < 19685538 limit 1000; 2021-01-21 20:51:56,578 INFO compelete
Special thanks MysqlTools-Python The author of Jiang lexing bosses , Because the test environment is often filled with miscellaneous things , There are many problems in the actual installation process , Search for me with no solution , With the big guy's wechat , Ask a lot Python The basic problem of installation , Thank you for your explanation , At last it worked .
Python 3 install ：https://www.sqlpy.com/blogs/c...