mysql 碎片整理存储过程与shell脚本

一、存储过程
1、代码

DELIMITER $$
    DROP PROCEDURE IF EXISTS `mysql`.`sp_optimize_tables`$$
    CREATE PROCEDURE `mysql`.`sp_optimize_tables`(
     IN db_name varchar(255))
    BEGIN
      -- Created by david yeung 20080128.
      -- To optimize all the tables in exact database.
      declare cnt int default 0;
      declare i int default 0;
      select count(*) as total from information_schema.tables where table_schema = db_name into cnt;
      while i < cnt
      do
        -- Get the table's exact name.
        set @stmt = concat('select table_name from information_schema.tables where table_schema = ''',db_name,''' order by table_name asc limit ',i,',1 into @tb_name');
        prepare s1 from @stmt;
        execute s1;
        drop prepare s1;
        set @stmt = '';
        set @stmt = concat('optimize table ',db_name,'.',@tb_name);
        prepare s1 from @stmt;
        execute s1;
        drop prepare s1;
        set @stmt = '';
        set i = i + 1;
      end while;
      -- Refresh tables.
      flush tables;
    END$$
    DELIMITER ;

2、调用示例：

mysql> use mysql
Database changed
mysql> call sp_optimize_tables('david_test');
+------------------------------+----------+----------+----------+
| Table                        | Op       | Msg_type | Msg_text |
+------------------------------+----------+----------+----------+
| david_test.test1 | optimize | status   | OK       |
+------------------------------+----------+----------+----------+
1 row in set (0.26 sec)

3、在SHELL中调用

#!/bin/sh
#
# Created by david yeung.
# Optimize all the table one by one.
#
cd /usr/local/mysql/bin
./mysql -uoptimize_user -poptimize -e "use \"$1\";call mysql.sp_optimize_tables(\"$1\")"
不过要注意给optimize_user的select,insert权限。

二、碎片整理的shell脚本

#!/bin/sh
    mysql_user=root
    mysql_pass=123123
    time_log=/opt/time
    databases=/opt/databases
    /usr/bin/mysql -u$mysql_user -p$mysql_pass -e "show databases" | grep -v "Database" > /opt/databases
    sed -i "s/information_schema//" $databases
    sed -i "s/mysql//" $databases
    sed -i "s/test//" $databases
    databases1=$(cat /opt/databases)
    for i in $databases1
    do
    echo "database $i starting"
    tables=$(/usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "show tables" | grep -v "Tables" > /opt/$i)
    tablelist=$(cat /opt/$i)
    echo "optimize database $i starting" >> $time_log
    echo "$i start at $(date  +[%Y/%m/%d/%H:%M:%S])" >> $time_log
    for list in $tablelist
    do
    echo $list
    /usr/bin/mysql $i -u$mysql_user -p$mysql_pass -e "optimize table $list"
    done
    echo "$i end   at $(date  +[%Y/%m/%d/%H:%M:%S])" >> $time_log
    echo >> $time_log
    done