Write by lyc at 2018-07-25
Modify by lyc at 2018-11-15

一、myisam引擎改innodb 项目背景

1.故障背景

某项目的mysql云实例为高可用版，历史遗留原因表引擎都是myisam的。

今天把mysql云实例调整配置，引起高可用版备机无法正常同步。

原因是myisam引擎表过多造成同步异常。

2.解决措施

由云技术支持提供解决方案:

1
2
3
4
5
6
7
8
您好 我这边来同步一下上面的问题排查进展
首先 咱们这边刚才操作的内存降级是失败了  我们的降级流程是这样的  备库降级-备库重启-切换主备-新备库降级-新备库重启
现在问题是出现在备库重启后跟咱们主库同步出现了问题   无法进行主备切换   导致升级操作终止了
控制台状态显示关闭也跟这个是有关的
备库和主库同步异常的原因是咱们这边有比较多的myisam表。
这个过程不影响咱们云实例正常使用，不过现在咱们高可用云实例的备库和主库的同步现在是异常的，后端同事帮咱们修复需要咱们这边选择一下两个方案
1.现在帮咱们修复，因为有myisam表，修复阶段会有锁库，会影响到咱们主库的业务写入，预计锁库影响几分钟
2.咱们这边先手动扫一遍咱们的数据库，把myisam表修改为innodb  然后后端同事这边再操作修复不会有锁库影响 不影响咱们主库使用

二、myisam引innodb 实操

1.检查哪些表是非InnoDB引擎

只能一张张表改引擎，每一次改表都会引起锁表，若表中数据量较大，会影响锁表时间（锁表和解锁表的过程）

建议在业务低峰时期执行上述表引擎修改语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT table_schema database_name, table_name, engine
FROM information_schema.tables 
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys') 
AND engine <> 'InnoDB';

# 若返回结果为空，则表明业务所用表均为InnoDB。若返回结果如下例，表明以下表为非InnoDB引擎：
+---------------+------------+--------+
| database_name | table_name | engine |
+---------------+------------+--------+
| db1           | user       | MyISAM |
| db1           | order      | MyISAM |
| db6           | log        | MyISAM |
| db2           | table1     | MEMORY |
+---------------+------------+--------+

2.修改存储引擎（sql拼接）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') sql_text
FROM information_schema.tables 
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys') 
AND engine <> 'InnoDB';

# 如返回结果如下例，可复制下述SQL并执行：
+---------------------------------------+
| sql_text                              |
+---------------------------------------+
| ALTER TABLE db1.user ENGINE=InnoDB;   |
| ALTER TABLE db1.order ENGINE=InnoDB;  | 
| ALTER TABLE db6.log ENGINE=InnoDB;    |
| ALTER TABLE db2.table1 ENGINE=InnoDB; |
+---------------------------------------+
4 rows in set (0.04 sec)

3.批量修改表引擎脚本

表不宜过大，否则要改好久。 我改了一个11G的表，耗时45分钟。5G的表耗时10分钟。

批量修改存储引擎（MySIAM改InnoDB）脚本参考

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
$ cat update_engine.sh
#!/bin/sh
#Write by lyc at 2018-08-05
MYUSER="root"
MYPWD=""
PORT=""
IP=""
MYCMD="/usr/bin/mysql -u$MYUSER -p$MYPWD -h$IP -P$PORT --default-character-set=utf8"
LIST_FILE="/root/update_engine/list_myisam.txt"
RUN_LOG="/root/update_engine/update_engine.log"
CMD1="SELECT table_schema database_name, table_name, engine FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys') AND engine <> 'InnoDB';"
CMD2="SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' ENGINE=InnoDB;') sql_text FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys') AND engine <> 'InnoDB';"

function echoColor(){
    case "$1" in
        [rR][eE][dD])
            echo -e "\033[35m  --- `date +%Y-%m-%d[%T]` \033[0m" | tee -a ${RUN_LOG}
            echo -e "\033[31m Error: $2 \033[0m" | tee -a ${RUN_LOG}
        ;;  
        [gG][rR][eE][eE][nN])
            echo -e "\033[35m  --- `date +%Y-%m-%d[%T]` \033[0m" | tee -a ${RUN_LOG}
            echo -e "\033[32m OK: $2 \033[0m" | tee -a ${RUN_LOG}
        ;;  
        [yY][eE][lL][lL][oO][wW])
            echo -e "\033[35m  --- `date +%Y-%m-%d[%T]` \033[0m" | tee -a ${RUN_LOG}
            echo -e "\033[33m Warn: $2 \033[0m" | tee -a ${RUN_LOG}
        ;;  
        [bB][lL][uU][eE])
            echo -e "\033[35m  --- `date +%Y-%m-%d[%T]` \033[0m" | tee -a ${RUN_LOG}
            echo -e "\033[34m $2 \033[0m" | tee -a ${RUN_LOG}
        ;;  
    esac
}

function main(){
    flag=`$MYCMD -e "$CMD1" | wc -l`
    echo "myISAM is $flag"
    if [ $flag -ne 0 ];then
        $MYCMD -e "$CMD2" | sed  '1d' > $LIST_FILE
        cat $LIST_FILE|while read line
        do
            $MYCMD -e "$line"
            if [ $? -eq 0 ];then
                echoColor green "update $line is ok"
            else
                echoColor red "update $line is false"
            fi
        done
    else
        echoColor green "all table is innodb"
    fi
}

main