In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >
Share
Shulou(Shulou.com)06/03 Report--
This article mainly explains "how to collect and aggregate MySQL information through Python". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to collect and aggregate MySQL table information through Python.
Catalogue
one。 Demand
two。 Description of the common foundation document
1. Configuration file
two。 Define a declared db connection
3. Define the operation that declares access to the db
three。 Main code
3.1 create a script to save data
3.2 collected feature scripts
one。 Demand
Statistics collect the information of table on each instance, mainly the number and size of records in the table.
The scope of the collection is all database instances in cmdb.
two。 Description of public basic documents 1. Configuration file
The configuration document is db_servers_conf.ini, assuming that the DBServer of cmdb is 119.119.119.119, and the DBserver of separate storage of collected monitoring data is 110.110.110.110. The access usernames of these two DB instances are the same, which are defined in the [uid_mysql] section. For each DB instance that needs to be collected, the account password is another, which is defined in the [collector_mysql] section.
[uid_mysql] dbuid = user * name dbuid_p_w_d = corresponding * secret * code [cmdb_server] db_host = 119.119.119.119db_port = 3306 [dbmonitor_server] db_host = 110.110.110.110db_port = 3306 [collector_mysql] collector = DB* * example * user * name collector_p_w_d = DB* real * example * secret * code 2. Define a declared db connection
The file is get_mysql_db_connect.py
#-*-coding: utf-8-*-import sysimport osimport configparserimport pymysql# get connection string information def mysql_get_db_connect (db_host, db_port): db_host = db_host db_port = db_port db_ps_file = os.path.join (sys.path [0], "db_servers_conf.ini") config = configparser.ConfigParser () config.read (db_ps_file) Encoding= "utf-8") db_user = config.get ('uid_mysql',' dbuid') db_pwd = config.get ('uid_mysql',' dbuid_p_w_d') conn = pymysql.connect (host=db_host, port=db_port, user=db_user, password=db_pwd, connect_timeout=5, read_timeout=5, write_timeout=5) return conn# get connection string information def mysql_get_collectdb_connect (db_host) Db_port): db_host = db_host db_port = db_port db_ps_file = os.path.join (sys.path [0], "db_servers_conf.ini") config = configparser.ConfigParser () config.read (db_ps_file, encoding= "utf-8") db_user = config.get ('collector_mysql',' collector') db_pwd = config.get ('collector_mysql' 'collector_p_w_d') conn = pymysql.connect (host=db_host, port=db_port, user=db_user, password=db_pwd, connect_timeout=5, read_timeout=5, write_timeout=5) return conn3. Define the operation that declares access to the db
The file is mysql_exec_sql.py. Note that you need to import the above model.
#-*-coding: utf-8-*-import get_mysql_db_connectdef mysql_exec_dml_sql (db_host, db_port, exec_sql): conn = mysql_get_db_connect.mysql_get_db_connect (db_host Db_port) with conn.cursor () as cursor_db: cursor_db.execute (exec_sql) conn.commit () # # need to explicitly close cursor_db.close () conn.close () def mysql_exec_select_sql (db_host, db_port, exec_sql): conn = mysql_get_db_connect.mysql_get_db_connect (db_host Db_port) with conn.cursor () as cursor_db: cursor_db.execute (exec_sql) sql_rst = cursor_db.fetchall () # # explicitly close conn cursor_db.close () conn.close () return sql_rstdef mysql_exec_select_sql_include_colnames (db_host, db_port, exec_sql): conn = mysql_get_db_connect.mysql_get_db_connect (db_host Db_port) with conn.cursor () as cursor_db: cursor_db.execute (exec_sql) sql_rst = cursor_db.fetchall () col_names = cursor_db.description return sql_rst, col_names III. Main Code 3.1 create a script to save the data
The table used to hold the collected table information: table_info
Create table `table_ info` (`id`int (11) NOT NULL AUTO_INCREMENT, `port`ip`varchar (50) NOT NULL DEFAULT '0entries, `port`varchar (10) NOT NULL DEFAULT' 3306database, `db_ name`varchar 'NOT NULL DEFAULT' 'COMMENT' database name', `table_ name`varchar 'NOT NULL DEFAULT' 'COMMENT' table name', `table_ rows`bigint NOT NULL DEFAULT 0 COMMENT 'table rows', `table_data_ rowth` bigint, `table_index_ roomth`bigint, `table_data_ free` bigint `table_auto_ increment`bigint, `creator`varchar (50) NOT NULL DEFAULT'', `create_ time`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `operator`varchar (50) NOT NULL DEFAULT'', `operate_ time`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
Collection process, if you access an instance exception, save the failed information to the table gather_error_info for tracking and analysis.
Create table `gather_error_ info` (`id`int (11) NOT NULL AUTO_INCREMENT, `app_ name` NOT NULL DEFAULT 'error reporting program', `host_ ip`varchar (50) NOT NULL DEFAULT '0programs, `port`varchar (10) NOT NULL DEFAULT' 3306databases, `db_ name`varchar (60) NOT NULL DEFAULT'0' COMMENT 'database name', `error_ msg` varchar (500) NOT NULL DEFAULT 'error reporting program', `status` int (11) NOT NULL DEFAULT'2' `creator` varchar (50) NOT NULL DEFAULT'', `creator` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `operator` varchar (50) NOT NULL DEFAULT'', `creator` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 3.2 collected feature scripts
Define the script collect_tables_info.py that collects DB_info
#-*-coding: utf-8-*-import sysimport osimport datetimeimport configparserimport pymysqlimport mysql_get_db_connectimport mysql_exec_sqlimport mysql_collect_exec_sqlimport pandas as pddef collect_tables_info (): db_ps_file = os.path.join (sys.path [0], "db_servers_conf.ini") config = configparser.ConfigParser () config.read (db_ps_file, encoding= "utf-8") cmdb_host = config.get ('cmdb_server' 'db_host') cmdb_port = config.getint (' cmdb_server', 'db_port') monitor_db_host = config.get (' dbmonitor_server', 'db_host') monitor_db_port = config.getint (' dbmonitor_server', 'db_port') # get the DB list to be traversed exec_sql_1 = "" select vm_ip_address,port,b.vm_host_name,remarkFROM cmdbdb.mysqldb_instance "" exec_sql_tablesizeinfo = "" select TABLE_SCHEMA,table_name,table_rows,data_length, index_length,data_free,auto_incrementfrom information_schema.tableswhere TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys') and TABLE_TYPE =' BASE TABLE' "exec_sql_insert_tablesize =" insert into monitordb.table_info (host_ip,port,db_name,table_name,table_rows,table_data_length,table_index_length,table_data_free,table_auto_increment)\ VALUES ('% ScriptMagazine% SJM% SJM% s,% s% s,% s,% s) "exec_sql_error =" insert into monitordb.gather_db_error (app_name,host_ip,port,error_msg)\ VALUES ('% sling,% swatch,%% s','% s') "sql_rst_1 = mysql_exec_sql.mysql_exec_select_sql (cmdb_host, cmdb_port) Exec_sql_1) if len (sql_rst_1): for i in range (len (sql_rst_1)): rw_host = list (sql_rst_1 [I]) db_host_ip = rw_host [0] db_port_s = rw_host [1] # # print (type (rw_host)) # ValueError: port should be of type int db_port = int (db_port_s) try: sql_rst_tablesize = mysql_collect_exec_sql.mysql_exec_select_sql (db_host_ip Db_port Exec_sql_tablesizeinfo) # # print (sql_rst_tablesize) if len (sql_rst_tablesize): for i in range (len (sql_rst_tablesize)): rw_tableinfo = list (sql_rst_ tablesize [I]) rw_db_name = rw_tableinfo [0] Rw_table_name = rw_tableinfo [1] rw_table_rows = rw_tableinfo [2] rw_data_length = rw_tableinfo [3] rw_index_length = rw_tableinfo [4] rw_data_free = rw_tableinfo [5] rw _ auto_increment = rw_tableinfo [6] # # print (rw_auto_increment) # # the judgment of whether the variable is None in Python if rw_auto_increment is None: rw_auto_increment = 0 # there must be an exec_sql_insert_table_com If exec_sql_insert_tablesize = exec_sql_insert_tablesize% (db_host_ip. # the error message is TypeError: not all arguments converted during string formatting exec_sql_insert_table_com = exec_sql_insert_tablesize% (db_host_ip, db_port_s, rw_db_name, rw_table_name, rw_table_rows, rw_data_length, rw_index_length, rw_data_free) Rw_auto_increment) print (exec_sql_insert_table_com) sql_insert_rst_1 = mysql_exec_sql.mysql_exec_dml_sql (monitor_db_host, monitor_db_port) Exec_sql_insert_table_com) # print (sql_insert_rst_1) except: # print (the error message of 'TypeError is as follows:' + str (TypeError)) print (db_host_ip +'+ str (db_port) + 'login exception cannot get table information, please check the instance and access the account!') Exec_sql_error_sql = exec_sql_error% ('collect_tables_info',db_host_ip, str (db_port),' login exception Failed to obtain table information. Check the instance and the account you visited!!') sql_insert_err_rst_1 = mysql_exec_sql.mysql_exec_dml_sql (monitor_db_host, monitor_db_port, exec_sql_error_sql) # # print (sql_rst_1) else: print ('query no result set') collect_tables_info () here I believe you have a deeper understanding of "how to collect MySQL information through Python". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.