[MySQL] table engine 변경

[MySQL] table engine 변경

mysql table 엔진 변경

ALTER TABLE `테이블 명` ENGINE=INNODB

특정 DB의 모든 table 엔진 수정

• mysql_to_innodb.sh
  • ENGINE_FROM 으로 찾은 table을 ENGINE_TO로 변경

    #!/bin/bash
    
    DBNAME='test'
    DBID='id'
    DBPWD='pwd'
    ENGINE_FROM='MYISAM'
    ENGINE_TO='INNODB'
    
    select_qry="""SELECT table_name
    FROM INFORMATION_SCHEMA.TABLES
    WHERE engine='${ENGINE_FROM}' and table_schema='${DBNAME}'
    """
    ret="$(mysql -u${DBID} -p${DBPWD} -D ${DBNAME} -N -e "${select_qry}")"
    for table_name in ${ret} ; do
        /usr/local/mysql/bin/mysql -u${DBID} -p${DBPWD} -D ${DBNAME} -e "ALTER TABLE ${table_name} ENGINE=${ENGINE_TO}"
    done