虽说强烈建议使用InnoDB存储引擎,但是有些时候希望复制的新库或新的服务器改变存储引擎,在这种情况下,使用mysqldbcopy工具非常便捷,谁用谁知道。
关于mysqldbcopy介绍参见《MySQL管理工具MySQL Utilities — mysqldbcopy(6)》。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
将新拷贝的数据库在目标服务器上使用MyISAM引擎,只需使用 --new-storage-engine 即可。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
instance_3306文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where TABLE_SCHEMA='ttlsa_com'; +--------------+-------------------+--------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | +--------------+-------------------+--------+ | ttlsa_com | bbs_categories | InnoDB | | ttlsa_com | bbs_comments | InnoDB | | ttlsa_com | bbs_favorites | InnoDB | | ttlsa_com | bbs_forums | InnoDB | | ttlsa_com | bbs_links | InnoDB | | ttlsa_com | bbs_notifications | InnoDB | | ttlsa_com | bbs_page | InnoDB | | ttlsa_com | bbs_settings | InnoDB | | ttlsa_com | bbs_tags | InnoDB | | ttlsa_com | bbs_tags_relation | InnoDB | | ttlsa_com | bbs_user_follow | InnoDB | | ttlsa_com | bbs_user_groups | InnoDB | | ttlsa_com | bbs_users | InnoDB | | ttlsa_com | data | InnoDB | | ttlsa_com | t_data | InnoDB | +--------------+-------------------+--------+ 15 rows in set (0.00 sec)
转换成MyISAM文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
# mysqldbcopy --source=instance_3306 --destination=instance_3308 --new-storage-engine=myisam ttlsa_com:ttlsa_com_copy # Source on localhost: ... connected. # Destination on localhost: ... connected. # Copying database ttlsa_com renamed as ttlsa_com_copy # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_categories`. # Copying TABLE ttlsa_com.bbs_categories # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_comments`. # Copying TABLE ttlsa_com.bbs_comments # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_favorites`. # Copying TABLE ttlsa_com.bbs_favorites # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_forums`. # Copying TABLE ttlsa_com.bbs_forums # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_links`. # Copying TABLE ttlsa_com.bbs_links # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_notifications`. # Copying TABLE ttlsa_com.bbs_notifications # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_page`. # Copying TABLE ttlsa_com.bbs_page # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_settings`. # Copying TABLE ttlsa_com.bbs_settings # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_tags`. # Copying TABLE ttlsa_com.bbs_tags # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_tags_relation`. # Copying TABLE ttlsa_com.bbs_tags_relation # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_users`. # Copying TABLE ttlsa_com.bbs_users # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_user_follow`. # Copying TABLE ttlsa_com.bbs_user_follow # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`bbs_user_groups`. # Copying TABLE ttlsa_com.bbs_user_groups # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`data`. # Copying TABLE ttlsa_com.data # Replacing ENGINE=InnoDB with ENGINE=myisam for table `ttlsa_com_copy`.`t_data`. # Copying TABLE ttlsa_com.t_data # Copying data for TABLE ttlsa_com.bbs_categories # Copying data for TABLE ttlsa_com.bbs_comments # Copying data for TABLE ttlsa_com.bbs_favorites # Copying data for TABLE ttlsa_com.bbs_forums # Copying data for TABLE ttlsa_com.bbs_links # Copying data for TABLE ttlsa_com.bbs_notifications # Copying data for TABLE ttlsa_com.bbs_page # Copying data for TABLE ttlsa_com.bbs_settings # Copying data for TABLE ttlsa_com.bbs_tags # Copying data for TABLE ttlsa_com.bbs_tags_relation # Copying data for TABLE ttlsa_com.bbs_users # Copying data for TABLE ttlsa_com.bbs_user_follow # Copying data for TABLE ttlsa_com.bbs_user_groups # Copying data for TABLE ttlsa_com.data # Copying data for TABLE ttlsa_com.t_data #...done.
instance_3308文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where TABLE_SCHEMA='ttlsa_com_copy'; +----------------+-------------------+--------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | +----------------+-------------------+--------+ | ttlsa_com_copy | bbs_categories | MyISAM | | ttlsa_com_copy | bbs_comments | MyISAM | | ttlsa_com_copy | bbs_favorites | MyISAM | | ttlsa_com_copy | bbs_forums | MyISAM | | ttlsa_com_copy | bbs_links | MyISAM | | ttlsa_com_copy | bbs_notifications | MyISAM | | ttlsa_com_copy | bbs_page | MyISAM | | ttlsa_com_copy | bbs_settings | MyISAM | | ttlsa_com_copy | bbs_tags | MyISAM | | ttlsa_com_copy | bbs_tags_relation | MyISAM | | ttlsa_com_copy | bbs_user_follow | MyISAM | | ttlsa_com_copy | bbs_user_groups | MyISAM | | ttlsa_com_copy | bbs_users | MyISAM | | ttlsa_com_copy | data | MyISAM | | ttlsa_com_copy | t_data | MyISAM | +----------------+-------------------+--------+ 15 rows in set (0.01 sec)
所需权限
对源数据库要有SELECT 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
对目标数据库要有CREATE, INSERT, UPDATE 权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
是不是很简单?哪里不会就看此教程。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-change-database-storage-engine/
评论