在《python 统计MySQL表信息》这篇博客中,链接如下:
https://www.cnblogs.com/xiao987334176/p/9901692.html
已经统计出来了一个MySQL服务器的所有表信息,但是还不够,还缺2个列。分别是备注和是否使用
库名 | 表名 | 表说明 | 建表语句 | 备注 | 是否使用 |
db1 | users | 用户表 | CREATE TABLE `users` (...) | 是 | |
log2 | CREATE TABLE `log2` (...) | 废弃 | 否 |
注意:前4列,我用python统计出来了,那么后2列,怎么办呢?
作为一名运维人员,你是不知道线上每个表的使用情况的,但是开发人员是知道的。所以最后2列,扔给开发去填写。
千万不要自作主张的去写,否则,你懂的...
OK,历经2天的时间,表终于整理完了。大概有1万条记录!
领导需要将不使用的表,全部删除掉。但是有一个前提:删除之前,一定要做备份。
怎么备份呢?有2个方案
1. 使用mysqldump 备份单个表,也就是要删除的表
2. 使用xtraBackup备份mysql
那么这2个方案,该如何选择呢?如果你不知道怎么选的时候,可以先用排除法!
mysqldump
先来看第一种方案,使用mysqldump 备份单表
mysqldump -h主机ip -u用户名 -p密码 数据库名 表名> 文件位置.sql
注意:对于千万级别以上的表,这个sql文件会非常的大。有办法缩小吗?有,使用gzip
gzip
mysqldump 备份并压缩sql文件
mysqldump -h主机ip -u用户名 -p密码(也可不输入) 数据库名 表名 | gzip > 压缩后文件位置.sql.gz
mysql直接用压缩文件恢复
gunzip < backupfile.sql.gz | mysql -u用户名 -p密码(也可不输入) 数据库名
一个16M的sql文件,使用gzip之后,可以压缩到2.2M。但是我们要知道,备份千万级别的表,非常耗时。
它需要一行行读取,并写入到备份文件中。这还只是单表的情况下,几千张表,就无法想象了!
所以不予采用
xtraBackup
具体使用方法,请参考以下链接:
http://blog.51cto.com/xiao987334176/1693176
它是基于文件式的备份,MySQL的数据库的信息,都写在文件中,那么我使用xtraBackup拷贝文件,比用mysqldump 一行行读取快多了!
所以,准予采用!
在贴完整代码之前,先来说几个小的知识点,有助于理解代码。
pymysql执行mysql命令
这是一个查看所有数据库的
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql
class MysqlHelp(object):
def __init__(self):
self.username = "root"
self.password = ""
self.host = "localhost"
self.port = 3306 # 注意,必须是数字
def connect(self): # 连接mysql
conn = pymysql.connect(
host=self.host, # mysql ip地址
user=self.username,
passwd=self.password,
port=self.port # mysql 端口号,注意:必须是int类型
)
return conn
def command(self,sql):
cur = self.connect().cursor() # 创建mysql游标
cur.execute(sql)
ret = cur.fetchall() # 执行结果
return ret
all_database = MysqlHelp().command('show databases')
print(all_database)
执行输出:
(('information_schema',), ('abc',), ('db1',), ('mysql',), ('performance_schema',), ('sys',))
logging 记录日志
使用logging模块,记录简单的日志。注意:屏幕和文件,会同时写入。屏幕写入,也就是打印在屏幕的意思
import logging
logger = logging.getLogger() # 实例化了一个logger对象
# 在国外叫handler,在中国翻译过来,叫句柄
# 设置文件名和编码
fh = logging.FileHandler('delete.log', encoding='utf-8') # 实例化了一个文件句柄 # 格式和文件句柄或者屏幕句柄关联
sh = logging.StreamHandler() # 用于输出到控制台
fmt = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') # 格式化
fh.setFormatter(fmt) # 格式和文件句柄或者屏幕句柄关联
sh.setFormatter(fmt)
# 吸星大法
logger.addHandler(fh) # 吸收写文件功能 和logger关联的只有句柄
logger.addHandler(sh) # 吸收输出屏幕功能
logger.setLevel(logging.DEBUG) # 设置警告级别为debug,此处DEBUG源码为DEBUG = 10
logger.debug('debug message')
logger.info('info message')
logger.warning('warning message')
logger.critical('critical message')
执行输出:
2018-11-10 17:46:28,280 - root - DEBUG - debug message2018-11-10 17:46:28,280 - root - INFO - info message2018-11-10 17:46:28,281 - root - WARNING - warning message2018-11-10 17:46:28,281 - root - CRITICAL - critical message
为什么要记录日志呢?因为我需要知道,哪些执行成功和失败了,便于后续的操作。
获取不使用的表
筛选问题
怎么筛选出没有使用的表呢?有2个方法:
1. 使用xlwr模块,读取出 是否使用 这一列为否的记录。提取出库名和表名!
2. 使用excel的筛选功能
先来说第一种,是可以做,但是太麻烦了。再来说,第二种,简单方便。那么我们始终坚持,简单方便的原则,使用第二种方式。
选中所有列,点击筛选,效果如下:
点击是否使用后面的按钮,勾选否的,点击确定,效果如下:
发行库名是空的,就无法知道它是哪个库的。所以,在筛选之前,要把所有的库名补齐,不能留空!
使用Pycharm创建delete.txt文件。注意:Pycharm创建的文件是utf-8编码。直接windows右键创建的txt文件,编码是gbk。
最后筛选出为否的记录,删除多余的列,只复制库名和表名到一个delete.txt文件中。
注意:删掉中文,效果如下:
读取内容函数
如果让一个python新手来读取一个文件,将文件的所有内容输出,并给其他变量调用。那么需要写一个函数,他会这么做
def read_file(): # 读取文件内容
with open('delete.txt', encoding='utf-8') as f: return f.read()
这样做完全是可以的,但是我要说的是,如果是一个非常大的文件,内存会直接溢出。这是我们不愿意接受的!
其实可以通过生成器来完成
生成器
def read_file(): # 读取文件内容
with open('delete.txt', encoding='utf-8') as f: for i in f: # 返回生成器,节省内存
yield i.split()
注意:生成器,保存的是某种算法,它并不存储真正的值。你调用它一次,它才会将值返回给你。所以非常节省内存!
那么将这个函数的调用复制给一个变量,对这个变量做for循环,就可以得到文件的所有内容。
获取CPU核心数
这里为什么要获取CPU核心数呢?先来回顾一个知识点,进程与线程的关系。
进程是资源分配的最小单位,线程是CPU调度的最小单位。每一个进程中至少有一个线程!
假设我的电脑是4核,那么上面的python代码执行之后,只会占用一个核。
对于这样的程序,它并没有充分的利用CPU。如果能开4个进程执行,那么就可以充分利用CPU了。
不同的电脑,配置是一样的,获取CPU核心数,使用以下代码
from multiprocessing import cpu_countprint(cpu_count())
OK,既然获得了CPU核心数,接下来,就是多线程的问题了
多线程
例子:
import time
from multiprocessing import cpu_count
from multiprocessing import Pool
def del_tad(num):
time.sleep(1)
standard_time = time.strftime('%Y-%m-%d %H:%M:%S')
print("{}, hi {} 执行了".format(standard_time,num))
return True
if __name__ == '__main__':
core_count = cpu_count() # 获取cpu核心数,这里是4个
p = Pool(core_count) # 创建进程池
for i in range(5):
# 异步执行del_tab方法,根据进程池中有的进程数,每次最多4个子进程在异步执行
res = p.apply_async(del_tad,args=(i,))
p.close() # 关闭进程池
# 异步apply_async用法:如果使用异步提交的任务,主进程需要使用join,等待进程池内任务都处理完
# 否则,主进程结束,进程池可能还没来得及执行,也就跟着一起结束了
p.join()
执行输出:
2018-11-10 18:26:26, hi 0 执行了2018-11-10 18:26:26, hi 1 执行了2018-11-10 18:26:26, hi 2 执行了2018-11-10 18:26:26, hi 3 执行了2018-11-10 18:26:27, hi 4 执行了
可以发现,同时执行的,只有4个。因为CPU核心数是4
如果担心CPU满负荷运行会死掉,可以取一半的数量
# CPU核心数取一半,有小数点时,向上取整。担心机器死掉!core_count = math.ceil(cpu_count() / 2)
花费时间
秒数
要删除接近1万张表,总得记录,花了多长时间吧!怎么计算呢?很简单
import time
startime = time.time() # 开始时间
# 执行主程序代码...
endtime = time.time() # 结束时间
take_time = endtime - startime
print("本次花费时间%s秒"%take_time)
执行输出:
本次花费时间253秒
那么问题来了,253秒,你能在1秒钟,算出,它是几分几秒吗? 数学功底比较好的人,可以心算出来。但是普通人,就不行了!
接下来,有一个方法,3行代码,就可以搞定了
时分秒
take_time = 253
if take_time < 1: # 判断不足1秒时
take_time = 1 # 设置为1秒
# 计算花费时间
m, s = divmod(take_time, 60)
h, m = divmod(m, 60)
print("本次花费时间 %02d:%02d:%02d" % (h, m, s))
执行输出:
本次花费时间 00:04:13
哈,怎么样,是不是看着很清爽!简单明了。
注意:take_time不能小于等于0,否则输出会有异样!所以做了一个判断。
完整代码
铺垫了这么多,就可以放出完整代码了!
#!/usr/bin/env python3
# coding: utf-8
import time
import math
import logging
import pymysql
from multiprocessing import Pool
from multiprocessing import cpu_count
class DeleteTable(object):
def __init__(self):
self.username = "root"
self.password = ""
self.host = "localhost"
self.port = 3306 # 注意:必须是数字
self.file_txt = "delete.txt" # 删除文件列表
self.logger = self.logger() # 日志对象
def connect(self): # 连接mysql
conn = pymysql.connect(
host=self.host, # mysql ip地址
user=self.username,
passwd=self.password,
port=self.port # mysql 端口号,注意:必须是int类型
)
return conn
def logger(self):
"""
写入日志
:return: logger对象
"""
logger = logging.getLogger() # 实例化了一个logger对象
# 在国外叫handler,在中国翻译过来,叫句柄
# 设置文件名和编码
fh = logging.FileHandler('delete.log', encoding='utf-8') # 实例化了一个文件句柄 # 格式和文件句柄或者屏幕句柄关联
sh = logging.StreamHandler() # 用于输出到控制台
fmt = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') # 格式化
fh.setFormatter(fmt) # 格式和文件句柄或者屏幕句柄关联
sh.setFormatter(fmt)
# 吸星大法
logger.addHandler(fh) # 吸收写文件功能 和logger关联的只有句柄
logger.addHandler(sh) # 吸收输出屏幕功能
logger.setLevel(logging.DEBUG) # 设置警告级别为debug,此处DEBUG源码为DEBUG = 10
# logger.debug('debug message')
# logger.info('info message')
# logger.warning('warning message')
return logger
def read_file(self): # 读取文件内容
with open(self.file_txt, encoding='utf-8') as f:
for i in f:
# 返回生成器,节省内存
yield i.split()
def del_tab(self,cur,dbname, table):
"""
删除表
:param cur: mysql游标
:param dbname: 数据库名
:param table: 表名
:return: bool
"""
try:
# 删除表drop
cur.execute('drop table {}.{}'.format(dbname, table))
# ret = cur.fetchall() # 执行结果
self.logger.debug('{}.{} 删除成功'.format(dbname,table)) # 写入日志
return True
except Exception as e:
print(e)
self.logger.critical('{}.{} 删除失败,失败原因:{}'.format(dbname, table,e))
return False
def main(self): # 获取内容
content = self.read_file() # 读取文件内容
# CPU核心数取一半,有小数点时,向上取整。担心机器死掉!
core_count = math.ceil(cpu_count() / 2)
cur = self.connect().cursor() # 创建mysql游标
p = Pool(core_count) # 创建进程池
for i in content: # 遍历文件
dbname, table = i # 数据库和表明
# 异步执行del_tab方法
p.apply_async(self.del_tab(cur,dbname, table))
p.close() # 关闭进程池
p.join() # 等待所有进程结束
return True
if __name__ == '__main__':
startime = time.time()
ret = DeleteTable().main() # 执行主程序
endtime = time.time()
take_time = endtime - startime
if take_time < 1: # 判断不足1秒时
take_time = 1 # 设置为1秒
# 计算花费时间
m, s = divmod(take_time, 60)
h, m = divmod(m, 60)
print("本次花费时间 %02d:%02d:%02d" % (h, m, s))
# print("本次花费时间%s"%take_time)
既然删除脚本,已经写好了。总得测试一下吧!那么多表呀?从哪里搞?
从备份文件,恢复一个到测试机上?卧槽,得花5个多小时呢!太漫长!
创建表脚本
诶,既然是要删除指定的表,那么我创建出那么表,不就完事了吗?
表结构不用完全和生产环境一样,弄成统一的模板即可。
日记也不需要记录,临时的而已!
完整代码如下:
#!/usr/bin/env python3
# coding: utf-8
# 创建要删除的表,测试删除脚本
import time
import math
import logging
import pymysql
from multiprocessing import Pool
from multiprocessing import cpu_count
class CreateTable(object):
def __init__(self):
self.username = "root"
self.password = ""
self.host = "localhost"
self.port = 3306
self.file_txt = "delete.txt" # 删除文件列表
def connect(self): # 连接mysql
conn = pymysql.connect(
host=self.host, # mysql ip地址
user=self.username,
passwd=self.password,
port=self.port # mysql 端口号,注意:必须是int类型
)
return conn
def read_file(self): # 读取文件内容
with open(self.file_txt, encoding='utf-8') as f:
for i in f:
# 返回生成器,节省内存
yield i.split()
def cre_tab(self,cur,dbname, table):
"""
创建表
:param cur: mysql游标
:param dbname: 数据库名
:param table: 表名
:return: bool
"""
try:
# 创建表,表结构图方便,统一了
sql = """CREATE TABLE %s (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8""" %'{}.{}'.format(dbname,table)
# print(sql)
cur.execute(sql)
print('创建表{}.{}成功'.format(dbname,table))
return True
except Exception as e:
print(e)
return False
def main(self): # 获取内容
content = self.read_file() # 读取文件内容
# CPU核心数取一半,有小数点时,向上取整
core_count = math.ceil(cpu_count() / 2)
cur = self.connect().cursor() # 创建mysql游标
# 创建数据库,由于要删除的只有3个库,这里手动创建一下,就可以了!
cur.execute('CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci')
cur.execute('CREATE DATABASE db2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci')
cur.execute('CREATE DATABASE db3 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci')
p = Pool(core_count) # 创建进程池
for i in content: # 遍历文件
dbname, table = i # 数据库和表名
# 异步执行del_tab方法
p.apply_async(self.cre_tab(cur,dbname, table))
p.close() # 关闭进程池
p.join() # 等待所有进程结束
return True
if __name__ == '__main__':
startime = time.time()
ret = CreateTable().main() # 执行主程序
endtime = time.time()
take_time = endtime - startime
if take_time < 1: # 判断不足1秒时
take_time = 1 # 设置为1秒
# 计算花费时间
m, s = divmod(take_time, 60)
h, m = divmod(m, 60)
print("本次花费时间 %02d:%02d:%02d" % (h, m, s))
安装MySQL
这里使用的是操作系统是 ubuntu-16.04.5-server-amd64,使用以下命令安装
apt-get install -y mysql-server
安装过程中,会提示输入mysql中root用户的密码
再次输入密码
安装完成之后,mysql会自动启动。使用以下命令进入mysql
mysql -u root -proot
查看所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
rows in set (0.00 sec)
允许root用户远程连接
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;
设置监听地址
默认的mysql配置文件是监听127.0.0.1的,如果要远程连接,必须要修改配置文件才行
vim /etc/mysql/mysql.conf.d/mysqld.cnf
找到
bind-address = 127.0.0.1
改成
bind-address = 0.0.0.0
重启mysql数据库
/etc/init.d/mysql restart
使用Navicat软件连接
能打开,说明连接成功了!
先执行创建表脚本
再执行删除表脚本
查看删除日志文件delete.log
2018-11-10 21:20:57,121 - root - DEBUG - db1.log2 删除成功
...
大功告成!
上面的脚本在测试环境,执行蛮顺利的。但是在预发布环境测试时,由于疏忽,忘了做备份了。导致测试环境,某些功能出现异常!
需要对相关表做恢复!怎么恢复呢?查看MySQL错误日志,出现了not found的表名,就手动恢复一下!
所以,为了线上执行时,避免出现类似问题。将删除操作改为重命名操作,一旦出现问题,可以快速恢复!
这里使用统一后缀名_rolls_royce,意思就是劳斯莱斯
#!/usr/bin/env python3
# coding: utf-8
import time
import math
import pymysql
from multiprocessing import Pool
from multiprocessing import cpu_count
class DeleteTable(object):
def __init__(self):
self.username = "root"
self.password = "root"
self.host = "192.168.91.128"
self.port = 3306
self.file_txt = "delete.txt" # 删除文件列表
self.prefix = '_rolls_royce' # 重命名的后缀
def connect(self): # 连接mysql
conn = pymysql.connect(
host=self.host, # mysql ip地址
user=self.username,
passwd=self.password,
port=self.port # mysql 端口号,注意:必须是int类型
)
return conn
def read_file(self): # 读取文件内容
with open(self.file_txt, encoding='utf-8') as f:
for i in f:
# 返回生成器,节省内存
yield i.split()
# def del_tab(self,cur,dbname, table):
# """
# 删除所有表
# :param cur: mysql游标
# :param dbname: 数据库名
# :param table: 表名
# :return: bool
# """
# try:
# # 删除表drop
# cur.execute('drop table {}.{}'.format(dbname, table))
# # ret = cur.fetchall() # 执行结果
# self.write_log('del_ok.log','{}.{} 删除成功'.format(dbname,table)) # 写入日志
# return True
# except Exception as e:
# print(e)
# self.write_log('del_error.log','{}.{} 删除失败,失败原因:{}'.format(dbname, table,e))
# return False
def write_log(self,path,content):
"""
写入日志文件
:param path:
:param content:
:return:
"""
with open(path,mode='a+',encoding='utf-8') as f:
content = time.strftime('%Y-%m-%d %H:%M:%S')+' '+content+"\n"
print(content)
f.write(content)
def rename_tab(self,cur,dbname, table):
"""
重命名所有表
:param cur: mysql游标
:param dbname: 数据库名
:param table: 表名
:return: bool
"""
try:
# 重命名表RENAME
# ALTER TABLE user10 RENAME TO user11;
cur.execute('ALTER TABLE {}.{} RENAME TO {}.{}{}'.format(dbname, table,dbname, table,self.prefix))
# ret = cur.fetchall() # 执行结果
self.write_log('rename_ok.log', '{}.{} 重命名表成功'.format(dbname, table)) # 写入日志
return True
except Exception as e:
print(e)
self.write_log('rename_error.log', '{}.{} 重命名表失败'.format(dbname, table))
return False
def recovery_tab(self,cur,dbname, table):
"""
恢复所有表名
:param cur: mysql游标
:param dbname: 数据库名
:param table: 表名
:return: bool
"""
try:
# 恢复表名
cur.execute('ALTER TABLE {}.{} RENAME TO {}.{}'.format(dbname, table+self.prefix,dbname, table))
self.write_log('recovery_ok.log', '{}.{} 恢复表名成功'.format(dbname, table))
return True
except Exception as e:
print(e)
self.write_log('recovery_error.log','{}.{} 恢复表名失败'.format(dbname, table))
return False
def main(self): # 获取内容
content = self.read_file() # 读取文件内容
# CPU核心数取一半,有小数点时,向上取整
core_count = math.ceil(cpu_count() / 2)
cur = self.connect().cursor() # 创建mysql游标
p = Pool(core_count) # 创建进程池
for i in content: # 遍历文件
dbname, table = i # 数据库和表明
# 异步执行方法
p.apply_async(self.rename_tab(cur,dbname, table))
p.close() # 关闭进程池
return True
if __name__ == '__main__':
startime = time.time()
ret = DeleteTable().main() # 执行主程序
endtime = time.time()
take_time = endtime - startime
if take_time < 1: # 判断不足1秒时
take_time = 1 # 设置为1秒
# 计算花费时间
m, s = divmod(take_time, 60)
h, m = divmod(m, 60)
print("本次花费时间 %02d:%02d:%02d" % (h, m, s))
# print("本次花费时间%s"%take_time)
执行效果同上!去掉了logger模块,测试发现,多线程执行会重复写入日志!
所以执行之后,重命名的表会一直存在,存放个半年左右。等到功能稳定之后,一并删除!