Convert InnoDB ke MyiSAM MySQL Server

Konvert InnoDB ke MyiSAM
=============================

1. mysqldump -uroot -p --routines --no-data mbs-online > mbs_innodb.dump      # dump structur only
2. mysqldump -uroot -p --no-create-info --skip-triggers --no-create-db mbs-online > mbs_data_only.dump # dump data only
3. sed -e 's/ENGINE=InnoDB/ENGINE=MyISAM/g' mbs_innodb.dump > mbs_myisam.dump # convert structur innodb to myisam
4. mysql -uroot -p mbsbackup < mbs_myisam.dump    # restore structur only
5. mysql -uroot -p mbsbackup < mbs_data_only.dump # restore to mbsbackup

=========================================================================

1. membuat Direktori

mkdir /var/backups/mbs
mkdir /var/backups/mbs/data
mkdir /var/backups/mbs/innodb
mkdir /var/backups/mbs/myisam
mkdir /var/backups/mbs/log

2. membuat File pada direktori /sbin/

pico /sbin/innodb_to_myisam

# masukan script seperti berikut :

MY_USER='autobackup'
MY_PASSWORD='ot0b4ckup!'

MY_LOG=`date --date="yesterday" +'%Y%m%d'`
MY_DATE=`date --date="yesterday" +'%Y%m%d'`.dump

mysqldump -u$MY_USER -p$MY_PASSWORD --routines --no-data mbs-online > /var/backups/mbs/innodb/blora-$MY_DATE
mysqldump -u$MY_USER -p$MY_PASSWORD --no-create-info --skip-triggers --no-create-db mbs-online > /var/backups/mbs/data/blora-$MY_DATE
sed -e 's/ENGINE=InnoDB/ENGINE=MyISAM/g' /var/backups/mbs/innodb/blora-$MY_DATE > /var/backups/mbs/myisam/blora-$MY_DATE

mysql -u $MY_USER -p$MY_PASSWORD --execute='DROP DATABASE IF EXISTS `mbsbackup`'
mysql -u $MY_USER -p$MY_PASSWORD --execute='CREATE DATABASE `mbsbackup`'
mysql -u $MY_USER -p$MY_PASSWORD mbsbackup < /var/backups/mbs/myisam/blora-$MY_DATE
mysql -u $MY_USER -p$MY_PASSWORD mbsbackup < /var/backups/mbs/data/blora-$MY_DATE

gzip /var/backups/mbs/innodb/blora-$MY_DATE
gzip /var/backups/mbs/myisam/blora-$MY_DATE
gzip /var/backups/mbs/data/blora-$MY_DATE

echo "Proses Autobackup telah selesai pada hari :" `date` > /var/backups/mbs/log/$MY_LOG

3. berikan permisien chmod 777 /sbin/innodb_to_myisam
4. jalankan pada " crontab "