今天做了MySQL升级，从 5.1.26-rc 升级 到 5.5.15，都是从官网下载的源码包进行编译的，其中MySQL 5.5.15是由我的同事提供的
由于现有数据库采用innodb的引擎，故采用mysqldump导出备份，然后再导入备份的方式进行升级
升级大致步骤准备：
1、对现有数据库做备份
2、对现有的MySQL程序重命名(防止升级失败还能恢复)
3、对现有权限库做单独备份
4、替换现有MySQL程序
5、启动MySQL 5.5.15，导入备份
注：特别注意mysql日志提示
在升级后启动MySQL 5.5.15的过程中遇到很多提示错误，根据错误提示加以修改，很快解决问题。
a、我所安装升级的MySQL 5.5.15，编译目录选择的是/usr/local/mysql,但是我们线上的数据库程序所在目录为/usr/local/mysql5，我按照线上的进行升级，也将程序按照线上服务部署，故在启动的过程中提示如下错误：
120717 11:19:41 mysqld_safe The file /usr/local/mysql/bin/mysqld
does not exist or is not executable. Please cd to the mysql installation
directory and restart this script from there as follows:
./bin/mysqld_safe&
See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information
解决方法：将程序目录更改为/usr/local/mysql
b、配置文件中有[mysqld]选项段有如下选项--log-slow-queries，这个选项属于老版本的选项，根据mysql日志文件GY_ZGC_97.err错误提示加以修改:
120717 11:21:32 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data
120717 11:21:32 [Warning] The syntax &＃39;--log-slow-queries&＃39; is deprecated and will be removed in a future release. Please use &＃39;--slow-query-log&＃39;/&＃39;--slow-query-log-file&＃39; instead.
120717 11:21:32 InnoDB: The InnoDB memory heap is disabled
120717 11:21:32 InnoDB: Mutexes and rw_locks use InnoDB&＃39;s own implementation
120717 11:21:32 InnoDB: Compressed tables use zlib 1.2.3
120717 11:21:33 InnoDB: Initializing buffer pool, size = 1000.0M
120717 11:21:33 InnoDB: Completed initialization of buffer pool
解决方法：将配置文件中的log-slow-queries替换为slow-query-log
c、mysql启动有个选项--datadir，该选项指定的目录没有权限问题：
120717 11:21:33 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: &＃39;create&＃39;.
InnoDB: Cannot continue operation.
120717 11:21:33 mysqld_safe mysqld from pid file /home/mysql/data/GY_ZGC_97.pid ended
解决方法：将mysql数据目录属主更改为mysql用户：chown -R mysql.mysql data
d、mysql配置文件有如下选项：default-character-set=utf8
120717 11:22:46 InnoDB: 1.1.8 started; log sequence number 0
120717 11:22:46 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable &＃39;default-character-set=utf8&＃39;
120717 11:22:46 [ERROR] Aborting
120717 11:22:46 InnoDB: Starting shutdown...
120717 11:22:47 InnoDB: Shutdown completed; log sequence number 1595675
120717 11:22:47 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
120717 11:22:47 mysqld_safe mysqld from pid file /home/mysql/data/GY_ZGC_97.pid ended
解决方法：注释掉该选项
e、mysql权限库直接用的原来的权限库，有如下错误提示：
120717 11:25:00 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;events_waits_current&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;events_waits_history&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;events_waits_history_long&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;setup_consumers&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;setup_instruments&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;setup_timers&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;performance_timers&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;threads&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;events_waits_summary_by_thread_by_event_name&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;events_waits_summary_by_instance&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;events_waits_summary_global_by_event_name&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;file_summary_by_event_name&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;file_summary_by_instance&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;mutex_instances&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;rwlock_instances&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;cond_instances&＃39; has the wrong structure
120717 11:25:00 [ERROR] Native table &＃39;performance_schema&＃39;.&＃39;file_instances&＃39; has the wrong structure
解决方法：根据提示只需要运行bin/mysql_upgrade，类似如下命令(特别注意：本地在启动多个数据库的情况下，需要通过--sock制定要访问的数据库)
[root@245 mysql]# bin/mysql_upgrade --sock=/tmp/mysql_log.sock -p
Enter password:
Looking for &＃39;mysql&＃39; as: ./mysql
Looking for &＃39;mysqlcheck&＃39; as: ./mysqlcheck
Running &＃39;mysqlcheck&＃39; with connection arguments: &＃39;--socket=/tmp/mysql_log.sock&＃39;
Running &＃39;mysqlcheck&＃39; with connection arguments: &＃39;--socket=/tmp/mysql_log.sock&＃39;
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.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 &＃39;mysql_fix_privilege_tables&＃39;...
OK
对于MySQL升级有很多种方法，我个人选择了导入导出的方法，如果有读者对其他的方式感兴趣，可以做相关尝试。
以上是升级过程中的相关问题，做了简单总结，适合初学者。
京公网安备 11010802041100号