MySQL怎么批量修改存储引擎

本篇内容主要讲解“Mysql怎么批量修改存储引擎”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql怎么批量修改存储引擎”吧!

再看Mysql手册，看到有关修改存储引擎的部分，隧想到能否用shell脚本实现批量修改，于是便有了下面的脚本，以把MyISAM转换为InnoDB为例。

实验环境如下： OS: Centos 5.8 Final MySQL Version:5.5.19   脚本内容如下： 点击(此处)折叠或打开

1. #/bin/bash

2. #FileName:Convert_Storage_Engine.sh

3. #Desc:Conversion of a MySQL tables to other storage engines

4. #Create By:fedoracle

5. #Date:2012/06/27

6.  

7. DB=new

8. USER=test

9. PASSWD=test

10. HOST=192.168.25.121

11. MYSQL_BIN=/usr/local/mysql/bin

12. S_ENGINE=MyISAM

13. D_ENGINE=InnoDB

14.  

15. #echo "Enter MySQL bin path:"

16. #read MYSQL_BIN

17. #echo "Enter Host:"

18. #read HOST

19. #echo "Enter Uesr:"

20. #read USER

21. #echo "Enter PassWord:"

22. #read PASSWD

23. #echo "Enter DB name :"

24. #read DB

25. #echo "Enter the original engine:"

26. #read S_ENGINE

27. #echo "Enter the new engine:"

28. #read D_ENGINE

29.  

30. $MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "select TABLE_NAME from infORMation_schema.TABLES where TABLE_SCHEMA='"$DB"' and ENGINE='"$S_ENGINE"';" | grep -v "TABLE_NAME" >tables.txt

31. for t_name in `cat tables.txt`

32. do

33.     echo "Starting convert table $t_name......"

34.     sleep 1

35.     $MYSQL_BIN/mysql -h$HOST -u$USER -p$PASSWD $DB -e "alter table $t_name engine='"$D_ENGINE"'"

36.     if [ $? -eq 0 ]

37.     then

38.         echo "Convert table $t_name ended." >>con_table.log

39.         sleep 1

40.     else

41.         echo "Convert failed!" >> con_table.log

42.     fi

43. done

测试过程如下：

点击(此处)折叠或打开

1. [root@dbmaster ~]# mysql -h292.168.25.121 -utest -ptest

2. (test@192.168.25.121) [(none)] create database new;

3. Query OK, 1 row affected (0.01 sec)

4.  

5. (test@192.168.25.121) [(none)] show databases;

6. +--------------------+

7. | Database |

8. +--------------------+

9. | information_schema |

10. | 361 |

11. | mysql |

12. | new |

13. | performance_schema |

14. | test |

15. +--------------------+

16. 6 rows in set (0.00 sec)

17.  

18. [root@dbmaster ~]# mysql -h292.168.25.121 -utest -ptest new < 361.sql

19. (test@192.168.25.121) [(none)] use new;

20. Database changed

21. (test@192.168.25.121) [new] show tables;

22. +---------------------------+

23. | Tables_in_new |

24. +---------------------------+

25. | ad_magazine_content |

26. | ad_news_letter |

27. | conf_app |

28. | ip_province |

29. | ip_records |

30. | order_action |

31. | order_delivery |

32. | order_Goods |

33. ................................

34.  

35. (test@192.168.25.121) [information_schema] select TABLE_NAME,ENGINE from TABLES where TABLE_SCHEMA='new' and ENGINE='MyISAM';

36. +--------------------------+--------+

37. | TABLE_NAME | ENGINE |

38. +--------------------------+--------+

39. | ad_news_letter | MyISAM |

40. | conf_app | MyISAM |

41. | product_lib_attr_group | MyISAM |

42. | product_lib_brand | MyISAM |

43. | product_lib_ccard | MyISAM |

44. | product_lib_color | MyISAM |

45. | product_lib_fashion | MyISAM |

46. | product_lib_material | MyISAM |

47. | product_lib_season | MyISAM |

48. | product_lib_series | MyISAM |

49. | product_lib_size | MyISAM |

50. | product_lib_size_compare | MyISAM |

51. | product_lib_temperature | MyISAM |

52. | product_lib_type | MyISAM |

53. | product_lib_virtual_cat | MyISAM |

54. | req_conf_app | MyISAM |

55. | shop_keywords_details | MyISAM |

56. | system_api_user | MyISAM |

57. | system_payment | MyISAM |

58. | system_region | MyISAM |

59. | system_shop_dist | MyISAM |

60. | user_show_order | MyISAM |

61. +--------------------------+--------+

62. 22 rows in set (0.02 sec)

63.  

64. [root@dbmaster scripts]# bash ChangeStorageEngine.sh

65. Starting convert table ad_news_letter......

66. Starting convert table conf_app......

67. Starting convert table product_lib_attr_group......

68. Starting convert table product_lib_brand......

69. Starting convert table product_lib_ccard......

70. Starting convert table product_lib_color......

71. Starting convert table product_lib_fashion......

72. Starting convert table product_lib_material......

73. Starting convert table product_lib_season......

74. Starting convert table product_lib_series......

75. Starting convert table product_lib_size......

76. Starting convert table product_lib_size_compare......

77. Starting convert table product_lib_temperature......

78. Starting convert table product_lib_type......

79. ...............................

80.  

81. (test@192.168.25.121) [information_schema] select TABLE_NAME,ENGINE from TABLES where TABLE_SCHEMA='new' and ENGINE='MyISAM';

82. Empty set (0.01 sec)

83.  

84. [root@dbmaster scripts]# cat con_table.log 

85. Convert table ad_news_letter ended.

86. Convert table conf_app ended.

87. Convert table product_lib_attr_group ended.

88. Convert table product_lib_brand ended.

89. Convert table product_lib_ccard ended.

90. Convert table product_lib_color ended.

91. Convert table product_lib_fashion ended.

92. Convert table product_lib_material ended.

93. Convert table product_lib_season ended.

94. Convert table product_lib_series ended.

95. Convert table product_lib_size ended.

96. Convert table product_lib_size_compare ended.

97. Convert table product_lib_temperature ended.

98. Convert table product_lib_type ended.

99. Convert table product_lib_virtual_cat ended.

100. Convert table req_conf_app ended.

101. Convert table shop_keywords_details ended.

102. Convert table system_api_user ended.

103. Convert table system_payment ended.

104. Convert table system_region ended.

105. Convert table system_shop_dist ended.

106. Convert table user_show_order ended.

################################### 有些表在转换的时候由于字符集，字段长度，外键约束等原因会出现一些问题，如下 点击(此处)折叠或打开

1. ERROR 1217 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails

2. ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 1000 bytes

到此，相信大家对“MySQL怎么批量修改存储引擎”有了更深的了解，不妨来实际操作一番吧！这里是编程网网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！