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

推薦閱讀：

InnoDB儲存引擎的啟動、關閉與恢複

MySQL InnoDB獨立資料表空間的配置

MySQL Server 層和 InnoDB 引擎層 體繫結構圖

InnoDB 死結案例解析

MySQL Innodb獨立資料表空間的配置