Automated Mysql database management in Python

MySQL database is a very good open source database, which is widely used. I am no exception, database instance creation, database status detection, database backup, and so on. I am doing repetitive and boring work every day. To reduce the workload, I used Python to write a tool to automatically manage Mysql databases.

1. Specific functions:

Main functions of the Mysql management tool:

> Create a database instance

> Database Backup

> Database Configuration Detection

> Master-slave Database Synchronization

> Reload the configuration file

2. Tool code structure:

Library: encapsulate shared functions into a single library.

Mysqlmanager: myman. py script for basic Mysql management.

3. Tool code Display

Library/mysql. py:

#! /Usr/local/bin/python2.7 #-*-coding: UTF-8-*-encoding (cur): '''query the data Warehouse configuration information ''cur.exe cute ('showglobalvariables; ') data = cur. fetchall () returndict (data) classMySQLDConfig (ConfigParser): ''' encapsulate all common functions into a class ''' def _ init _ (self, config, ** kw): ''' the Python version must be later than 2.7. version 2.6 does not have the allow_no_value attribute ''' ConfigParser. _ init _ (self, allow_no_value = True) self. config = configself. mysqld_vars ={} ifos. path. exists (self. config): self. read (self. config) self. get_mysqld_vars () else: self. set_mysqld_defaults_var () self. set_mysqld_vars (kw) defset_mysqld_vars (self, kw): ''' get the configuration file information, overwrite the default configuration ''' fork, vinkw. items (): self. mysqld_vars [k] = vdefget_mysqld_vars (self): ''' get existing configuration file information ''' options = self. options ('mysqld') rst ={} foroinoptions: rst [o] = self. get ('mysqld ', o) self. set_mysqld_vars (rst) defset_mysqld_defaults_var (self): ''' if the configuration file does not exist, set the default configuration ''' ults = {"user": "mysql", "pid-file ": "/var/run/mysqld. pid "," socket ":"/var/lib/mysql. sock "," port ":" 3306 "," basedir ":"/usr "," datadir ":"/tmp/mysql "," tmpdir ":"/tmp ", "skip-external-locking": None, "bind-address": "127.0.0.1", "key_buffer": "16 M", "max_allowed_packet": "16 M ", "thread_stack": "192 K", "thread_cache_size": "8", "myisam-recover": "BACKUP", "query_cache_limit": "1 M", "query_cache_size ": "16 M", "log_error": "/var/log/mysqld. log "," expire_logs_days ":" 10 "," max_binlog_size ":" 100 M "} self. set_mysqld_vars (defaults) defsave (self): ''' Save the configuration information to the configuration file ''' ifnotself. has_section ('mysqld '): self. add_section ('mysqld') fork, vinself. mysqld_vars.items (): self. set ('mysqld ', k, v) withopen (self. config, 'w') asfd: self. write (fd) if _ name __= = "_ main _": mc = MySQLDConfig ('/root/david/mysqlmanager/cnfs/my. cnf ', max_connection = 200, user = 'mysql') mc. set_var ('skip-slave-start', None) mc. save ()

Library/utils. py

#! /Usr/local/bin/python2.7 #-*-coding: UTF-8-*-''' format time conversion, Database Configuration File unit (*. cnf) is different from the unit configured in the database global (mysql> showglobalvariables;). You need to convert '''unit = {'T': 2 ** 40, 'G ': 2 ** 30, 'M': 2 ** 20, 'K': 2 ** 10, 'B': 1} defconvertUnit (s): s = s. lower () lastchar = s [-1] num = int (s [:-1]) iflastcharinunit: returnnum * unit [lastchar] else: returnint (s) defscaleUnit (d ): fork, vinunit. items (): num = d/vif (0 <num <2 ** 10): returnnum, k

Mysqlmanager/myman. py:

5 #! /Usr/local/bin/python2.7 #-*-coding: UTF-8-*-fromosimportpathfromoptparseimportOptionParserfromsubprocessimportPIPE, paths = path. dirname (_ file _) OPSTOOLS_DIR = path. abspath (path. join (DIRNAME ,'.. ') sys. path. append (OPSTOOLS_DIR) fromlibrary. mysqlimportMySQLDConfig, getMyVariablesREPLICATION_USER = 'repl' REPLICATION _ PASS = '123qwe 'MYS QL_DATA_DIR = '/home/david/data' MYSQL _ CONF_DIR ='/home/david/cnfs 'mysql _ BACK_DIR = '/home/david/backup' defopts (): parser = OptionParser (usage = "usage: % prog [options] arg1arg2") parser. add_option ("-c", "-- cmd", dest = "cmd", action = "store", default = "check", help = "Checktheconfigurationfileanddatabaseconfigurationparametersaredifferent. [% options] ") parser. add_option ("-n", "-- name", dest = "name", action = "store", default = "mysq Linstance ", help =" CreateExamples. ") parser. add_option ("-p", "-- port", dest = "port", action = "store", default = "3306", help = "Examplesofport. ") returnparser. parse_args () defcheckPort (d, p): ''' instance port detection ''' formind: ifp = m. mysqld_vars ['Port']: returnTruereturnFalsedefsetReplMaster (cur): ''' sets the slave database to synchronize user authorization ''' SQL = "GRANTREPLICATIONSLAVEON *. * TO % s @ 'localhost' IDENTIFIEDBY '% S' "Optional (replication_user,replication_pass=cur.exe cute (s Ql) defconnMySQLd (mc): ''' connect to the database ''' host = '2017. 0.0.1 'user = 'root' port = int (mc. mysqld_vars ['Port']) conn = MySQLdb. connect (host, port = port, user = user) cur = conn. cursor () returncurdefrun_mysql (cnf): ''' run database ''' cmd = "mysqld_safe -- defaults-file = % s &" % cnfp = Popen (cmd, stdout = PIPE, shell = True) time. sleep (5) returnp. returncodedefsetOwner (p, user): ''' sets the directory permission ''' OS. system ("chown-R % s: % s" % (user, user, p) defmysql_install_db (cnf ): ''' Data library initialization ''' p = Popen ("mysql_install_db -- defaults-file = % s" % cnf, stdout = PIPE, shell = True) # p = Popen ("mysql_install_db -- user = mysql -- datadir = % s" % MYSQL_DATA_DIR, stdout = PIPE, shell = True) stdout, stderr = p. communicate () returnp. returncodedef_genDict (name, port): ''' sets the file storage directory and listening port ''' return {'pid-file': path. join (MYSQL_DATA_DIR, name, "% s. pid "% name), 'socket ':'/tmp/% s. sock '% name, 'Port': port, 'datadir': path. join (MYSQL_DATA_DIR, na Me) + '/', 'Log _ error': path. join (MYSQL_DATA_DIR, name)} defreadConfs (): ''' read the configuration file. If the configuration file does not exist, use the default configuration to generate the configuration file ''' confs = glob. glob (path. join (MYSQL_CONF_DIR ,'*. cnf ') return [MySQLDConfig (c) forcinconfs] defgetCNF (name): ''' obtain the complete configuration file path ''' returnpath. join (MYSQL_CONF_DIR, "% s. cnf "% name) defrunMySQLdump (cmd): ''' start Mysql Command ''' p = Popen (cmd, stdout = PIPE, shell = True) stdout, stderr = p. communicate () returnp. returncodedefgetBinlogPOS (f ):' ''Get binlog''' withopen (f) asfd: f, p = findLogPos (l) iffandp: returnf, pdeffindLogPos (s): rlog = re. compile (r "MASTER_LOG_FILE = '(\ S +)',", re. IGNORECASE) rpos = re. compile (r "MASTER_LOG_POS = (\ d + ),? ", Re. IGNORECASE) log = rlog. search (s) pos = rpos. search (s) iflogandpos: returnlog. group (1), int (pos. group (1) else: return (None, None) defchangeMaster (cur, host, port, user, mpass, mf, p): reset cute (SQL) defcreateInstance (name, port, dbtype = "master", ** kw ):''' Create a database instance ''' cnf = path. join (MYSQL_CONF_DIR, "% s. cnf "% name) datadir = path. join (MYSQL_DATA_DIR, name) exists_cnfs = readConfs () ifcheckPort (exists_cnfs, port): print> sys. stderr, "portexist. "sys. exit (-1) ifnotpath. exists (cnf): c = _ genDict (name, port) c. update (kw) mc = MySQLDConfig (cnf, ** c) mc. save () else: mc = MySQLDConfig (cnf, ** kw) ifnotpath. exists (datadir): mysql_install_db (cnf) setOwner (datadir, mc. mysqld_vars ['user']) run_mysql (Cnf) time. sleep (3) cur = connMySQLd (mc) setReplMaster (cur) defdiffVariables (instance_name): ''' query differences between database configuration files and Database Configuration ''' cnf = getCNF (instance_name) ifpath. exists (cnf): mc = MySQLDConfig (cnf) printmccur = connMySQLd (mc) vars = getMyVariables (cur) fork, vinmc. mysqld_vars.items (): k = k. replace ('-', '_') ifkinvarsandvars [k]! = V: printk, v, vars [k] defsetVariable (instance_name, variable, value): ''' reload configuration ''' cnf = getCNF (instance_name) ifpath. exists (cnf): mcw.mysqldconfig(cnfw.currentconnmysqld(mcw.cur.exe cute ('setglobal % s = % s' % (variable, value) mc. set_var (variable, value) mc. save () defbackupMySQL (instance_name): ''' backup database ''' cnf = getCNF (instance_name) ifpath. exists (cnf): mc = MySQLDConfig (cnf) now = datetime. datetime. now () timestamp = now. strftime ('% Y-% m-% d-% H % M % s') backup_file = path. join (MYSQL_BACK_DIR, instance_name, timestamp + '. SQL ') _ dir = path. dirname (backup_file) ifnotpath. exists (_ dir): OS. makedirs (_ dir) cmd = 'mysqldump-A-x-F -- master-data = 1 -- host = 127.0.0.1 -- user = root -- port = % s> % s' % (mc. mysqld_vars ['Port'], backup_file) runMySQLdump (cmd) defrestoreMySQL (instance_name, instance_port, sqlfile, ** kw): createInstance (instance_name, instance_port, ** kw) cnf = getCNF (instance_name) ifpath. exists (cnf): mc = MySQLDConfig (cnf) cur = connMySQLd (mc) cmd = "mysql-h127.0.0.1-P % s-uroot <% s" % (mc. mysqld_vars ['Port'], sqlfile) f, p = getBinlogPOS (sqlfile) runMySQLdump (cmd) changeMaster (cur, host = kw ['master-host'], port = kw ['master-port'], user = REPLICATION_USER, mpass = REPLICATION_PASS, mf = f, p = p) def_init (): ''' query whether several mysql directories exist. If not, ''' ifnotpath is automatically created. exists (MYSQL_DATA_DIR): OS. makedirs (MYSQL_DATA_DIR) ifnotpath. exists (MYSQL_CONF_DIR): OS. makedirs (MYSQL_CONF_DIR) ifnotpath. exists (MYSQL_BACK_DIR): OS. makedirs (MYSQL_BACK_DIR) defmain (): opt, args = opts () instance_name = opt. nameinstance_port = opt. portcommand = opt. cmdifcommand = "create": ifnotargs: createInstance (instance_name, instance_port) else: dbtype = args [0] serverid = args [1] mysqld_options = {'server-id ': serverid} ifdbtype = 'master': mysqld_options ['Log-bin'] = 'mysql-bin' elifdbtype = 'slave ': master_host = args [2] master_port = args [3] mysqld_options ['master-host'] = master_hostmysqld_options ['master-port'] = master_portmysqld_options ['master-user'] = bytes ['master-password'] = REPLICATION_PASSmysqld_options ['skip-slave-start'] = Nonemysqld_options ['replicate-ignore-db'] = 'mysqld _ options ['read- only '] = NonecreateInstance (instance_name, instance_port, dbtype = dbtype, ** mysqld_options) elifcommand = 'check': diffVariables (instance_name) elifcommand = 'adjust ': variable = args [0] value = args [1] setVariable (instance_name, variable, value) elifcommand = 'backup ': backupMySQL (instance_name) elifcommand = 'restore ': serverid = args [0] mhost = args [1] mport = args [2] sqlfile = args [3] mysqld_options = {"master-host": mhost, "master-port": mport, "server-id": serverid, "skip-slave-start": None,} restoreMySQL (instance_name, instance_port, sqlfile, ** mysqld_options) if _ name __= = "_ main _": printmain ()

4. Test

Help information:

Create a master instance:

Create an slave instance:

Check Configuration File and database load configuration differences:

The Unit format is different. You can use library/utils. py to convert the unit.

Database Backup:

Note:

1. python versions must be 2.7 and later.

2. The MYSQL_DATA_DIR directory cannot be placed in the/root directory. If it is placed in the root directory, an error will be reported during database initialization (permission issues ). I made a mistake here.