工作需要，对公司的mysql数据库进行备份，赶上刚刚开始学python，看了一套简单的python教学视频，简单的写了个备份脚本，个人表示 对python 的class 、function、build-in function 、私有变量、全局变量 等等，该怎么用，啥时候用等 毫无概念 ，仅此记录一下吧，也欢迎路过的pythoner赐教。
个人已知的一些问题：
1、该脚本必须要求 mysql配置文件内的所有行为 key=value的格式，并且不能存在多余的注释，否则configparser模块解析配置文件时会出错，由于没研究过configparser是不是有容错的方法可以调用，也没时间写容错处理，而是通过整理my.ini 配置文件使其符合configparser的要求解决的。后面会附上我用的mysql配置文件。
2、大量使用类私有成员变量，因为完全不知道python 变量、类方法、等等啥时候该私有化，以及有啥区别，只知道类私有成员变量在别的脚本中import 或者继承时，是不可见的。
3、比较多的进行文件操作，以及传值操作，目前只保证按正确格式传值没问题，没有做多余的容错处理。 4、大量的在进行字符串拼接，第一次写运维相关脚本，由于要调用系统命令，和传递很多参数，也不会subprocess模块，不知道别人写运维脚本都具体咋做，就直接拼接了。
5、其他未知的bug、未发现的逻辑错误等等。
环境：
– server : dell poweredge t110
– os: centos 6.3_x86_64
– pythonversion: 2.7.3
– mysqlversion: 5.5.28 linux x86_64
mysqlbackupscript.py
#!/usr/bin/env python
# coding: utf8
# script mysqlbackupscript
# by becareful
# version v1.0
“””
this scripts provides auto backup mysql(version == 5.5.x) database .
“””
import os
import sys
import datetime #用于生成备份文件的日期
import linecache #用于读取文件的指定行
import configparser #解析mysql配置文件
class databaseargs(object):
“””
“””
__mysql_base_dir = r’/usr/local/mysql’ #mysql安装目录
__mysql_bin_dir = __mysql_base_dir + ‘/bin’ #mysql二进制目录
__mysql_config_file = r’/usr/local/mysql/my.cnf’ #mysql配置文件
__oneday = datetime.timedelta(days=1) #一天的时长，用于计算下面的前一天和后一天日期
__today = datetime.date.today() #当天日期格式为 yyyy-mm-dd
__yesterday = __today – __oneday #计算昨天日期
__tomorrow = __today + __oneday #计算明天日期
__weekday = __today.strftime(‘%w’) #计算当天是一星期的星期几
__mysql_dump_args = { #用一个字典存储mysqldump 命令备份数据库的参数
‘myisam’: ‘ -v -e -e -r –triggers -f -n –opt –master-data=2 –hex-blob -b ‘,
‘innodb’: ‘ -v -e -e -r –triggers -f –single-transaction -n –opt –master-data=2 –hex-blob -b ‘
}
__dump_command = __mysql_bin_dir + ‘/mysqldump’ #mysqldump 命令的 路径 用于dump mysql数据
__flush_log_command = __mysql_bin_dir + ‘/mysqladmin’ #mysqladmin 命令的路径 ，用于执行 flush-logs 生成每天增量binlog
__backup_dir = r’/backup/’ # 指定备份文件存放的目录
__projectname = ‘example’ # 指定需要备份的数据库对应的项目名，将来会生成 projectname-yyyy-mm-dd.sql 等文件
__database_list = [] # 指定需要备份的数据库名，可以是多个，使用列表
__host = ‘localhost’
__port = 3306
__username = ‘root’
__password = ”
__loginargs = ” # 如果在localhost登陆，需要密码，可以设定登陆的参数，具体在下面有说明
__logfile = __backup_dir + ‘/backup.logs’
def __init__(self, basedir=__mysql_base_dir, backdir=__backup_dir, engine=’myisam’, projectname=__projectname,
dblist=__database_list, host=__host, port=__port, user=__username, passwd=__password):
“””
实例化对象时传入的参数，如不传入默认使用类的私有成员变量作为默认值
:param basedir:
:param backdir:
:param engine:
:param projectname:
:param dblist:
:param host:
:param port:
:param user:
:param passwd:
“””
self.__mysql_base_dir = basedir
self.__backup_dir = backdir
self.__projectname = projectname
self.__database_list = dblist
self.__host = host
self.__port = port
self.__username = user
self.__password = passwd
self.__engine = self.__mysql_dump_args[engine]
#下面定义了如需登陆时，参数 其实就是生成 这样的格式 “-hlocalhost -uroot –password=‘xxxx’”
self.__loginargs = ” -h” + self.__host + ” -p” + str(
self.__port) + ” -u” + self.__username + ” –password='” + self.__password + “‘”
self.checkdatabaseargs() #调用检查函数
def __getconfig(self, cnf=__mysql_config_file, item=none): # 解析mysql配置文件的小函数，简单封装了下，传入一个值作为my.cnf的key去查找对应的value
__mycnf = configparser.configparser()
__mycnf.read(cnf)
try:
return __mycnf.get(“mysqld”, item)
except baseexception, e:
sys.stderr.write(str(e))
sys.exit(1)
def __getbinlogpath(self): # 取每天需要增量备份的binlog日志的绝对路径，从mysql的binlog.index文件取倒数第二行
__binlog_index = self.__getconfig(item=’log-bin’) + ‘.index’
if not os.path.isfile(__binlog_index):
sys.stderr.write(‘binlog index file: [‘ + __binlog_index + ‘ ] not found! \n’)
sys.exit(1)
else:
try:
__binlog_path = linecache.getline(__binlog_index, len(open(__binlog_index,’r’).readlines()) – 1)
linecache.clearcache()
except baseexception, e:
sys.stderr.write(str(e))
sys.exit(1)
return __binlog_path.strip()
def flushdatabasebinlog(self): # 调用此函数，将会执行 mysqladmin flush-logs ,刷新binlog日志
return os.popen(self.__flush_log_command + self.__loginargs + ‘ flush-logs’)
def dumpdatabasesql(self): #|通过mysqladmin 对指定数据库进行全备
if not os.path.isfile(self.__backup_dir + ‘/’ + self.__projectname + ‘/’ + str(self.__yesterday) + ‘-‘ + self.__projectname + ‘.sql’):
return os.popen(self.__dump_command + self.__loginargs + self.__engine + ‘ ‘.join(
self.__database_list) + ‘ >> ‘ + self.__backup_dir + ‘/’ + self.__projectname + ‘/’ +str(
self.__yesterday) + ‘-‘ + self.__projectname + ‘.sql’)
else:
sys.stderr.write(‘backup file [‘ + str(self.__yesterday) + ‘-‘ + self.__projectname +’.sql] already exists.\n’)
def dumpdatabasebinlog(self):#通过copy2() 将需要备份的binlog日志复制到指定备份目录
if not os.path.isfile(self.__backup_dir + ‘/’ + self.__projectname + ‘/’ +str(self.__yesterday) + ‘-‘ + os.path.split(self.__getbinlogpath())[1]):
from shutil import copy2
try:
copy2(self.__getbinlogpath(), self.__backup_dir + ‘/’ + self.__projectname + ‘/’ +str(self.__yesterday) + ‘-‘ + os.path.split(self.__getbinlogpath())[1])
except baseexception, e:
sys.stderr.write(str(e))
else:
sys.stderr.write(‘binlog file [‘ + str(self.__yesterday) + ‘-‘ +os.path.split(self.__getbinlogpath())[1] + ‘] already exists\n’ )
def checkdatabaseargs(self): #对一些必要条件进行检查
__rv = 0
if not os.path.isdir(self.__mysql_base_dir): #检查指定的mysql安装目录是否存在
sys.stderr.write(‘mysql base dir: [ ‘ + self.__mysql_base_dir + ‘ ] not found\n’)
__rv += 1
if not os.path.isdir(self.__backup_dir): #检查指定的备份目录是否存在，如不存在自动创建
sys.stderr.write(‘backup dir: [ ‘ + self.__backup_dir + ‘/’ + self.__projectname + ‘ ] not found ,auto created\n’)
os.makedirs(self.__backup_dir + ‘/’ + self.__projectname)
if not os.path.isfile(self.__mysql_config_file): #检查mysql配置文件是否存在
sys.stderr.write(‘mysql config file: [‘ + self.__mysql_config_file + ‘ ] not found\n’)
__rv += 1
if not os.path.isfile(self.__dump_command): #检查备份数据库时使用的mysqldump命令是否存在
sys.stderr.write(‘mysql dump command: [‘ + self.__dump_command + ‘ ] not found\n’)
__rv += 1
if not os.path.isfile(self.__flush_log_command): #检查刷新mysql binlog日志使用的mysqladmin命令是否存在
sys.stderr.write(‘mysql flush log command: [‘ + self.__dump_command + ‘ ] not found\n’)
__rv += 1
if not self.__database_list: #检查需要备份的数据库列表是否存在
sys.stderr.write(‘database list is none \n’)
__rv += 1
if __rv: # 判断返回值，由于上述任何一步检查失败，都会导致 __rv 值 +1 ，只要最后__rv ！= 0就直接退出了。
sys.exit(1)
def crontab(): # 使用字典，来进行相关参数传递，并实例化对象，调用相关方法进行操作
zabbix = {
‘basedir’: ‘/usr/local/mysql/’,
‘backdir’: ‘/backup/’,
‘projectname’: ‘monitor’,
‘dblist’: [‘zabbix’],
‘host’: ‘localhost’,
‘port’: 3306,
‘user’: ‘root’,
‘passwd’: ‘xxxxxxx’
}
monitor = databaseargs(**zabbix)
monitor.dumpdatabasesql()
monitor.dumpdatabasebinlog()
monitor.flushdatabasebinlog()
if __name__ == ‘__main__’:
crontab()
my.cnf
[client]
port = 3306
socket = /mysql/var/db.socket
[mysqld]
socket = /mysql/var/db.socket
datadir = /mysql/db/
skip-external-locking = 1
skip-innodb = 0
key_buffer_size = 256m
max_allowed_packet = 10m
table_open_cache = 2048
sort_buffer_size = 4m
read_buffer_size = 4m
read_rnd_buffer_size = 8m
myisam_sort_buffer_size = 64m
myisam_max_sort_file_size = 1g
myisam_repair_threads = 1
myisam_recover = default
thread_cache_size = 32
query_cache_size = 32m
query_cache_min_res_unit = 2k
bulk_insert_buffer_size = 64m
tmp_table_size = 128m
thread_stack = 192k
skip-name-resolve = 1
max_connections = 65500
default-storage-engine = myisam
federated = 0
server-id = 1
slave-skip-errors = all
#log = /var/log/sql_query.log
slow-query-log = 1
slow-query-log-file = /mysql/log/sql_query_slow.log
long-query-time = 5
log-queries-not-using-indexes = 1
log-slow-admin-statements = 1
log-bin = /mysql/var/log/binlog/bin-log
log-error = /mysql/var/log/mysql.err
master-info-file = /mysql/var/log/master.info
relay-log = /mysql/var/log/relay-bin/relay-bin
relay-log-index = /mysql/var/log/relay-bin/relay-bin.index
relay-log-info-file = /mysql/var/log/relay-bin/relay-bin.info
binlog_cache_size = 8m
binlog_format = mixed
max_binlog_cache_size = 20m
max_binlog_size = 1g
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = information_schema
innodb_data_home_dir = /mysql/ibdata/
innodb_data_file_path = ibdata:156m:autoextend
innodb_log_group_home_dir = /mysql/ibdata/
log-slave-updates = 0
back_log = 512
transaction_isolation = read-committed
max_heap_table_size = 246m
interactive_timeout = 120
wait_timeout = 120
innodb_additional_mem_pool_size = 16m
innodb_buffer_pool_size = 512m
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16m
innodb_log_file_size = 128m
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
innodb_open_file = 327500
open_files_limit = 327500
[mysqldump]
quick = 1
max_allowed_packet = 50m
[mysql]
auto-rehash = 1
socket = /mysql/var/db.socket
safe-updates = 0
[myisamchk]
key_buffer_size = 256m
sort_buffer_size = 256m
read_buffer = 2m
write_buffer = 2m
[mysqlhotcopy]
interactive-timeout = 100
最终生成的备份目录结构是这样的
[root@zabbix backup]# find ./
./
./monitor
./monitor/2013-03-16-bin-log.000008
./monitor/2013-03-14-bin-log.000006
./monitor/2013-03-16-monitor.sql
./monitor/2013-03-15-monitor.sql
./monitor/2013-03-15-bin-log.000007
./monitor/2013-03-14-monitor.sql
~end~