InnoDB数据库优化

由于数据更新比较频繁且数据量增长太快，导致机器磁盘跟不上节奏。已经采用数据库的分库和迁移历史库了。现在对数据的优化操作，

简述：由于数据更新比较频繁且数据量增长太快，导致机器磁盘跟不上节奏。已经采用数据库的分库和迁移历史库了。现在对数据的优化操作，减少数据占用磁盘空间，达到节省磁盘空间。

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} "
    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} "
    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}

推荐阅读：

InnoDB存储引擎的启动、关闭与恢复

MySQL InnoDB独立表空间的配置

MySQL Server 层和 InnoDB 引擎层 体系结构图

InnoDB 死锁案例解析

MySQL Innodb独立表空间的配置