之前公司大部分项目都上了5.5版本，手上刚好有个项目(自由交易平台)数据量不是很大，单台服务器在线上跑得比较稳定，很少去搭理，为了统一版本于是打算升级到mys
#注释掉default_table_type = myisam 在5.5里已经不识别了
#注释掉myisam_max_extra_sort_file_size 在5.5里已经不识别了
#innodb_file_io_threads已经不识别了，改成
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# 提高刷新脏页数量和合并插入数量，改善磁盘io处理能力，根据自己的磁盘iops能力调整
innodb_io_capacity = 2000
# 把以前的built-in文件格式antelope改为barracuda
innodb_file_format = barracuda
# 脏页的调整，从以前的25改为90，这个值可在70--90之间选取
innodb_max_dirty_pages_pct = 90
# 事务日志redo log的调整，从以前的256m改为1024m
innodb_log_file_size = 1024
# 采用独立表空间
innodb_file_per_table = 1
重启mysql服务生效：
[root@db_fb05 mysql-5.1.62]# service mysqld restart
shutting down mysql. success!
starting mysql.. success!
[root@db_fb05 mysql-5.1.62]#
二、升级具体步骤
升级前有一个重要参数需要注意：innodb_fast_shutdown
升级前要关闭这个功能(为什么要关请参考手册)：
(root:opdba.com:sat dec 1 18:14:48 2012)[(none)]> show variables like '%fast%';
+----------------------+-------+
| variable_name | value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
(root:opdba.com:sat dec 1 18:35:20 2012)[(none)]> set global innodb_fast_shutdown=0;
query ok, 0 rows affected (0.00 sec)
(root:opdba.com:sat dec 1 18:35:43 2012)[(none)]> show variables like '%fast%';
+----------------------+-------+
| variable_name | value |
+----------------------+-------+
| innodb_fast_shutdown | 0 |
+----------------------+-------+
1 row in set (0.00 sec)
1.停止当前mysql服务
service mysqld stop
2.链接到新mysql目录
cd /opt/webserver/
unlink mysql
ln -sv mysql-5.5.34-linux2.6-x86_64 mysql
3.启动服务
service mysqld start
4.执行更新程序升级
/opt/webserver/mysql/bin/mysql_upgrade
looking for 'mysql' as: /opt/webserver/mysql/bin/mysql
looking for 'mysqlcheck' as: /opt/webserver/mysql/bin/mysqlcheck
running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/data/mysql/mysql.sock'
/opt/webserver/mysql/bin/mysqlcheck: got error: 2002: can't connect to local mysql server through socket '/data/mysql/mysql.sock' (111) when trying to connect
fatal error: upgrade failed
出现上面错误是由于当前mysql服务器的root用户设置了密码，mysql_upgrade 加上-p参数批量root用户密码即可：
/opt/webserver/mysql/bin/mysql_upgrade -padmin
looking for 'mysql' as: /opt/webserver/mysql/bin/mysql
looking for 'mysqlcheck' as: /opt/webserver/mysql/bin/mysqlcheck
running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/data/mysql/mysql.sock'
running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/data/mysql/mysql.sock'
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
running 'mysql_fix_privilege_tables'...
ok
出现上面信息就说明升级ok！
查看下当前的版本：
[root@db_fb010 ~]# /opt/webserver/mysql/bin/mysql -v
logging to file '/data/mysql/logs/query.log'
/opt/webserver/mysql/bin/mysql ver 14.14 distrib 5.5.34, for linux2.6 (x86_64) using readline 5.1
mysql_upgrade命令实际执行了以下操作：
mysqlcheck --all-databases --check-upgrade --auto-repair
mysql
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names
5.存储过程问题修复
从5.1升级至5.5后，发现存储过程不能用的，创建存储过程和查看存储过程状态，会报如下错：
(root:opdba.com:sat dec 1 18:14:48 2012)[(none)]> show procedure status;
error 1548 (hy000): cannot load from mysql.proc. the table is probably corrupted
通过上述报错，，立马查看两边表 proc 的状态发现：show create table proc\g------>
在5.1中mysql.proc表的comment字段是char(64)：