由于我们知道上述错误主要是因为mysql.proc表的comment字段在5.1中是char(64)类型,在5.5中是text类型引起的,因此考虑修改dump文件中该字段的类型。
如下:
[root@testdb src]# sed -i '1580s/char(64)/text/' 3311.dump
[root@testdb src]# sed -n '1580p' 3311.dump
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
在全新环境中,导入到5.5,未报任何错误:
[root@testdb src]# /usr/local/mysql55302/bin/mysql -uroot --socket=/tmp/mysql55302.sock 《 /usr/local/src/3311.dump
运行mysql_upgrade,可以看到test的表导入了:
[root@testdb src]# /usr/local/mysql55302/bin/mysql_upgrade -uroot --socket=/tmp/mysql55302.sock
Looking for 'mysql' as: /usr/local/mysql55302/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql55302/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3312' '--socket=/tmp/mysql55302.sock' '--socket=/tmp/mysql55302.sock'
Running 'mysqlcheck' with connection arguments: '--port=3312' '--socket=/tmp/mysql55302.sock' '--socket=/tmp/mysql55302.sock'
act.act_account_conf OK
act.act_address OK
act.act_attend_user OK
act.act_browse_log OK
act.act_cate_relation OK
act.act_category OK
act.act_comment OK
act.act_element OK
act.act_element_conf OK
act.act_extend_info OK
act.act_filter_badword OK
act.act_filter_badword_cate OK
act.act_index OK
act.act_info OK
act.act_model OK
act.act_model_style OK
act.act_nav OK
act.act_news OK
act.act_object_type OK
act.act_picture OK
act.act_price_conf OK
act.act_price_dic OK
act.act_price_result OK
act.act_prod_category OK
act.act_prod_group OK
act.act_production OK
act.act_range_conf OK
act.act_reg_info OK
act.act_video OK
act.act_vote OK
act.area OK
act.province OK
act.school_level OK
act.sequence OK
act.sub_area OK
act.sys_change_log OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
pic.pic_randcode_data OK
pic.pic_randcode_data_his20100805 OK
pic.pic_upload_log OK
pic.pic_upload_log_his OK
pic.sequence OK
test.act_production OK
test.act_production1205 OK
Running 'mysql_fix_privilege_tables'...
OK
2、查看mysql的最新版本
Generally Available (GA) Releases x
5.1.69 (我们目前生产环境使用的是5.1.57版本)
5.5.31
5.6.11 (5.6版本的第二个稳定版本,之前版本为5.6.10)
如下:
[root@testdb src]# sed -i '1580s/char(64)/text/' 3311.dump
[root@testdb src]# sed -n '1580p' 3311.dump
`comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
在全新环境中,导入到5.5,未报任何错误:
[root@testdb src]# /usr/local/mysql55302/bin/mysql -uroot --socket=/tmp/mysql55302.sock 《 /usr/local/src/3311.dump
运行mysql_upgrade,可以看到test的表导入了:
[root@testdb src]# /usr/local/mysql55302/bin/mysql_upgrade -uroot --socket=/tmp/mysql55302.sock
Looking for 'mysql' as: /usr/local/mysql55302/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql55302/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3312' '--socket=/tmp/mysql55302.sock' '--socket=/tmp/mysql55302.sock'
Running 'mysqlcheck' with connection arguments: '--port=3312' '--socket=/tmp/mysql55302.sock' '--socket=/tmp/mysql55302.sock'
act.act_account_conf OK
act.act_address OK
act.act_attend_user OK
act.act_browse_log OK
act.act_cate_relation OK
act.act_category OK
act.act_comment OK
act.act_element OK
act.act_element_conf OK
act.act_extend_info OK
act.act_filter_badword OK
act.act_filter_badword_cate OK
act.act_index OK
act.act_info OK
act.act_model OK
act.act_model_style OK
act.act_nav OK
act.act_news OK
act.act_object_type OK
act.act_picture OK
act.act_price_conf OK
act.act_price_dic OK
act.act_price_result OK
act.act_prod_category OK
act.act_prod_group OK
act.act_production OK
act.act_range_conf OK
act.act_reg_info OK
act.act_video OK
act.act_vote OK
act.area OK
act.province OK
act.school_level OK
act.sequence OK
act.sub_area OK
act.sys_change_log OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
pic.pic_randcode_data OK
pic.pic_randcode_data_his20100805 OK
pic.pic_upload_log OK
pic.pic_upload_log_his OK
pic.sequence OK
test.act_production OK
test.act_production1205 OK
Running 'mysql_fix_privilege_tables'...
OK
2、查看mysql的最新版本
Generally Available (GA) Releases x
5.1.69 (我们目前生产环境使用的是5.1.57版本)
5.5.31
5.6.11 (5.6版本的第二个稳定版本,之前版本为5.6.10)