1.批量更改表存储引擎
读取information_schema库中对应的库中所有ENGINE = InnoDB的表名,并与‘ALTER TABLE‘,‘ENGINE=MyISAM;’GROUP_CONCAT方法将所有分组拼接到一起;
SELECT GROUP_CONCAT(CONCAT('ALTER TABLE ',table_name,' ENGINE=MyISAM;') SEPARATOR '') FROM information_schema.tables WHERE table_schema="davis_base" AND ENGINE="InnoDB";
ALTER TABLE aff_software_code ENGINE=myisam;ALTER TABLE site_config ENGINE=myisam;ALTER TABLE term_affiliate_relationship ENGINE=myisam;ALTER TABLE term_http_code ENGINE=myisam;ALTER TABLE term_media_link ENGINE=myisam;ALTER TABLE term_severe_content ENGINE=myisam;
再运行这些语句即可;
2.Mysql备份压缩
1) 备份单个库
mysqldump -uroot-pPASSWORD basename | gzip > /tmp/basename.sql.gz
2) 备份所有库
mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz