MySQL管理工具MySQL Utilities — mysqldbcopy改变存储引擎(12)

虽说强烈建议使用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/