简述：由于数据更新比较频繁且数据量增长太快，导致机器磁盘跟不上节奏。已经采用数据库的分库和迁移历史库了。现在对数据的优化操作，减少数据占用磁盘空间，达到节省磁盘空间。
1.利用optimize 来挪动数据 避免空隙。节省空间。
使用方法：0 3 * * 3 /usr/local/bin/optimize_lixian_dbp_innodb.sh hj db /tmp/mysql.sock &> /tmp/optimize_innodb.hj.data.log
[root@hj data]# cat /usr/local/bin/optimize_lixian_dbp_innodb.sh
#!/bin/bash
if [ "$1" == "" ] || [ "$2" == "" ] || [ "$3" == "" ];then
echo " Usage: $0 {tj|bj} <db_name> <mysql sock file's path>"
exit 1
fi
### 定义变量
db_type="$1"
db_name="$2"
sock_file="$3"
MYSQL="/usr/local/mysql/bin/mysql -u root -phj -S ${sock_file}"
tb_list="/tmp/optimize_lixian_dbp_innodb.${db_type}_${db_name#*_}.list"
pid_file="/tmp/optimize_lixian_dbp_innodb.${db_type}_${db_name#*_}.pid"
### 判断是否有同样的脚本在运行，有则退出，无则生成该脚本PID文件
if [ -f ${pid_file} ];then
old_pid=`cat ${pid_file}`
ps ax -o pid,cmd|grep -v grep|grep -q "${old_pid}" && echo `date +"%F %T"` - this sh is already running ... && exit 1
fi
echo $$ > ${pid_file}
### 导出所有innodb表的表名到文件
> ${tb_list}
echo show table status|${MYSQL} -N ${db_name}|awk '/InnoDB/{print $1}' > ${tb_list}
### 开始优化
num=`cat ${tb_list}|wc -l`
count=1
for tb in `cat ${tb_list}`
do
echo `date +"%F %T"` - ${count}/${num} - $tb
echo "optimize local table ${tb} ;"|${MYSQL} -N ${db_name}
sleep 10
count=$((count+1))
done
### 删除该脚本的PID文件
rm -f ${pid_file}2.对修改比较少的数据库，修改数据的存储方式
[root@hj ~]# cat /usr/local/bin/alter_lixian_dbp_innodb_to_myisam.sh
#!/bin/bash
#
# 2014.01.23 hj
#
if [ "$1" == "" ] || [ "$2" == "" ] || [ "$3" == "" ];then
echo " Usage: $0 {tj|bj} <db_name> <mysql sock file's path>"
exit 1
fi
### 定义变量
db_type="$1"
db_name="$2"
sock_file="$3"
MYSQL="/usr/local/mysql/bin/mysql -u root -phj -S ${sock_file}"
tb_list="/tmp/alter_lixian_dbp_innodb_to_myisam.${db_type}_${db_name#*_}.list"
pid_file="/tmp/alter_lixian_dbp_innodb_to_myisam.${db_type}_${db_name#*_}.pid"
### 判断是否有同样的脚本在运行，有则退出，无则生成该脚本PID文件
if [ -f ${pid_file} ];then
old_pid=`cat ${pid_file}`
ps ax -o pid,cmd|grep -v grep|grep -q "${old_pid}" && echo `date +"%F %T"` - this sh is already running ... && exit 1
fi
echo $$ > ${pid_file}
### 导出所有innodb表的表名到文件
> ${tb_list}
echo show table status|${MYSQL} -N ${db_name}|awk '/InnoDB/{print $1}' > ${tb_list}
### 开始优化
num=`cat ${tb_list}|wc -l`
count=1
for tb in `cat ${tb_list}`
do
echo `date +"%F %T"` - ${count}/${num} - $tb
echo "alter table ${tb} engine=myisam;"|${MYSQL} -N ${db_name}
sleep 10
count=$((count+1))
done
### 删除该脚本的PID文件
rm -f ${pid_file}增加一个python写的。
cat /usr/local/bin/optimize_tab.py
import MySQLdb
import sys
USER='optimize_user'
PASSWD='123456'
PORT=3306
HOST="10.1.1.1"
if sys.argv[1] == "data_hj":
PORT=3307
param="gdrive_hj" + '%'
elif sys.argv[1] == "data_hj1":
PORT=3308
param="gdrive_hj1" + '%'
elif sys.argv[1] == "data_hj2":
PORT=3309
param="gdrive_hj2" + '%'
else:
print "No MySQL instance '%s'" % sys.argv[1]
sys.exit()
def get_dblist(param):
conn=MySQLdb.connect(user=USER,host=HOST,passwd=PASSWD,port=PORT)
cur=conn.cursor()
sql="show databases like '%s'" % param
cur.execute(sql)
dblist=cur.fetchall()
cur.close()
conn.close()
DB_LIST=[]
for db in dblist:
DB_LIST.append(db[0])
return DB_LIST
def optimize_tab(DB):
conn=MySQLdb.connect(host=HOST,user=USER,passwd=PASSWD,port=PORT,db=DB)
cur=conn.cursor()
sql="show tables from %s;" % DB
cur.execute(sql)
for TAB_LIST in cur.fetchall():
for TAB in TAB_LIST:
cur.execute("set sql_log_bin=0;")
sql="optimize table %s;" % TAB
cur.execute(sql)
conn.commit()
cur.close()
conn.close()
if __name__=='__main__':
DB_LIST=get_dblist(param)
for DB in DB_LIST:
optimize_tab(DB)crontab：
### optimize tables
0 3 * * 1 python /usr/local/bin/optimize_tab.py data_hj
0 3 * * 2 python /usr/local/bin/optimize_tab.py data_hj1
0 3 * * 3 python /usr/local/bin/optimize_tab.py data_hj2
