本文共 11065 字，大约阅读时间需要 36 分钟。
从Cent OS 6.2中yum安装的mysql 5.1 升级到 percona server 5.6.17 的过程。 [root@root mysql]# uname -a Linux root 2.6.32-220.el6.x86_64 #1 SMP Tue Dec 6 19:48:22 GMT 2011 x86_64 x86_64 x86_64 GNU/Linux (本机器为Zabbix监控服务器)
blog：http://space.itpub.net/26355921 旧版本数据库的参数文件：[root@root lib]# cat /etc/my.cnf[client]socket=/var/lib/mysql/mysql.sockport = 3306[mysqld]port = 3306datadir=/var/lib/mysqlbasedir=/usrsocket=/var/lib/mysql/mysql.sockpid-file=/var/lib/mysql/mysqld.pidlog-error=/var/lib/mysql/localhost.erruser=mysqldefault-character-set=utf8innodb_buffer_pool_size=1G1.关闭旧版本数据库服务，备份数据文件：service mysqld stopcd /var/libtar zcvf mysql.tar.gz mysql2.解压：tar -zxf Percona-Server-5.6.17-rel65.0-587.Linux.x86_64.tar.gzmv Percona-Server-5.6.17-rel65.0-587.Linux.x86_64 /home/mysql/mysql/home/mysql/mysql作为新版本数据库的basedir3.修改参数文件：vi /etc/my.cnfbasedir=/home/mysql/mysqlcharacter-set-server=utf8skip-grant-tables修改新版本数据库的basedirdefault-character-set是老版本中的参数，新版本中使用character-set-server来代替。skip-grant-tables在后面的mysql_upgrade用到。检查其他不兼容或过时的参数并修改4.修改mysqld文件：移走老文件：mv /etc/init.d/mysqld /etc/init.d/old_mysqldcp /home/mysql/mysql/support-files/mysql.server /etc/init.d/mysqld修改参数： vi /etc/init.d/mysqldbasedir=/home/mysql/mysqldatadir=/var/lib/mysql5.修改PATHvi ~/.bash_profile添加：PATH=/home/mysql/mysql/bin:$PATHexport PATHsource ~/.bash_profile6.启动数据库：service mysqld start遇到报错：140527 15:10:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/home/mysql/mysql/bin/mysqld: error while loading shared libraries: libssl.so.6: cannot open shared object file: No such file or directory140527 15:10:08 mysqld_safe mysqld from pid file /var/lib/mysql/root.pid ended说没找到libssl.so.6cd /usr/lib64ls |grep libssl发现确实没有该文件cd /usr/libls |grep libssllibssl.so.0.9.8elibssl.so.6发现有这个文件，建立软链接：ln -s /usr/lib/libssl.so.6 /usr/lib64/libssl.so.6再次启动：service mysqld start再次报错：140527 15:28:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/home/mysql/mysql/bin/mysqld: error while loading shared libraries: libssl.so.6: wrong ELF class: ELFCLASS32140527 15:28:07 mysqld_safe mysqld from pid file /var/lib/mysql/root.pid ended百度了一下，说是64位的系统使用了32位的包，查一下：[root@root mysql]# rpm -qa|grep opensslopenssl-devel-1.0.0-20.el6.x86_64openssl-1.0.0-20.el6.x86_64openssl098e-0.9.8e-17.el6.i686lrwxrwxrwx. 1 root root 16 May 27 15:23 libssl.so.6 -> libssl.so.0.9.8e[root@root lib]# yum list|grep ssldocbook-style-dsssl.noarch 1.79-10.el6 @anaconda-CentOS-201112091719.x86_64/6.2nss_compat_ossl.x86_64 0.9.6-1.el6 @anaconda-CentOS-201112091719.x86_64/6.2openssl.x86_64 1.0.0-20.el6 @anaconda-CentOS-201112091719.x86_64/6.2openssl-devel.x86_64 1.0.0-20.el6 @anaconda-CentOS-201112091719.x86_64/6.2openssl098e.i686 0.9.8e-17.el6 @nameqpid-cpp-client-ssl.x86_64 0.12-6.el6 @anaconda-CentOS-201112091719.x86_64/6.2qpid-cpp-server-ssl.x86_64 0.12-6.el6 @anaconda-CentOS-201112091719.x86_64/6.2krb5-pkinit-openssl.x86_64 1.9-22.el6 name mod_ssl.x86_64 1:2.2.15-15.el6 name openssl.i686 1.0.0-20.el6 name openssl-devel.i686 1.0.0-20.el6 name openssl098e.x86_64 0.9.8e-17.el6 name qca-ossl.i686 2.0.0-0.8.beta3.1.el6 name qca-ossl.x86_64 2.0.0-0.8.beta3.1.el6 name qpid-cpp-client-ssl.i686 0.12-6.el6 name发现确实是装的openssl098e-0.9.8e-17.el6.i686，是32位的包。rpm -e --nodeps openssl098e-0.9.8e-17.el6.i686yum -y install openssl098e.x86_64再一次启动：service mysqld start数据库正常启动，发现localhost.err中有大量[ERROR]：2014-05-27 15:38:12 26533 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure2014-05-27 15:38:12 26533 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure2014-05-27 15:38:12 26533 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure.....................................................................................................2014-05-27 15:38:12 26533 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure2014-05-27 15:38:12 26533 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure7.进行升级：因为上面参数文件中加了skip-grant-tables，不使用授权表，因此此时直接输入mysql即可进入数据库。cd /home/mysql/mysql./bin/mysql_upgrade[root@root mysql]# ./bin/mysql_upgrade Looking for 'mysql' as: ./bin/mysqlLooking for 'mysqlcheck' as: ./bin/mysqlcheckRunning 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' '--port=3306' Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' '--port=3306' mysql.columns_priv OKmysql.db OKmysql.event OKmysql.func OKmysql.general_log OKmysql.help_category OKmysql.help_keyword OKmysql.help_relation OKmysql.help_topic OKmysql.host OKmysql.innodb_index_stats OKmysql.innodb_table_stats OKmysql.ndb_binlog_index OKmysql.plugin OKmysql.proc OKmysql.procs_priv OKmysql.proxies_priv OKmysql.servers OKmysql.slave_master_info OKmysql.slave_relay_log_info OKmysql.slave_worker_info OKmysql.slow_log OKmysql.tables_priv OKmysql.time_zone OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKRunning 'mysql_fix_privilege_tables'...Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' '--port=3306' ./bin/mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connectFATAL ERROR: Upgrade failed说访问权限的问题，此时直接输入mysql，提示错误：[root@root mysql]# mysqlERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)进入数据库看一下授权标：[root@root mysql]# mysql -uroot -pxxxxxxWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 21Server version: 5.6.17-65.0-rel65.0 Percona Server with XtraDB (GPL), Release rel65.0, Revision 587Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.This software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL v2 licenseType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select user,host,password from mysql.user;+--------+-----------+-------------------------------------------+| user | host | password |+--------+-----------+-------------------------------------------+| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || root | root | || root | 127.0.0.1 | || | localhost | || | root | || zabbix | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || bak | % | *2DB8F616A9DE18495770B5EA50BD86E3533E0F97 || bak | localhost | *2DB8F616A9DE18495770B5EA50BD86E3533E0F97 |+--------+-----------+-------------------------------------------+8 rows in set (0.00 sec)mysql> set password for 'root'@'localhost'=password('');Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> exit发现root@localhost 访问必须要输入密码，但是上面参数文件中加了skip-grant-tables，理论上来讲，不用输入密码就能登录数据库。但是此处反复试了多次，重启mysqld后，可以不用密码登录，执行mysql_upgrade后会failed，然后就会出现无法访问，之后就必须要密码才能登录。此时只能进入数据库，手动干预了：set password for root@localhost=password('');flush privileges;重启mysqld服务，再执行mysql_upgrade：[root@root mysql]# ./bin/mysql_upgrade Looking for 'mysql' as: ./bin/mysqlLooking for 'mysqlcheck' as: ./bin/mysqlcheckRunning 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' '--port=3306' Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' '--port=3306' mysql.columns_priv OKmysql.db OKmysql.event OKmysql.func OKmysql.general_log OKmysql.help_category OKmysql.help_keyword OKmysql.help_relation OKmysql.help_topic OKmysql.host OKmysql.innodb_index_stats OKmysql.innodb_table_stats OKmysql.ndb_binlog_index OKmysql.plugin OKmysql.proc OKmysql.procs_priv OKmysql.proxies_priv OKmysql.servers OKmysql.slave_master_info OKmysql.slave_relay_log_info OKmysql.slave_worker_info OKmysql.slow_log OKmysql.tables_priv OKmysql.time_zone OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKRunning 'mysql_fix_privilege_tables'...Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' '--port=3306' Running 'mysqlcheck' with connection arguments: '--socket=/var/lib/mysql/mysql.sock' '--port=3306' test.test OKtest.test2 OKtest.test3 OKzabbix.acknowledges OKzabbix.actions OKzabbix.alerts OKzabbix.application_template OKzabbix.applications OKzabbix.auditlog OKzabbix.auditlog_details OKzabbix.autoreg_host OKzabbix.conditions OKzabbix.config OKzabbix.dbversion OKzabbix.dchecks OKzabbix.dhosts OKzabbix.drules OKzabbix.dservices OKzabbix.escalations OKzabbix.events OKzabbix.expressions OKzabbix.functions OKzabbix.globalmacro OKzabbix.globalvars OKzabbix.graph_discovery OKzabbix.graph_theme OKzabbix.graphs OKzabbix.graphs_items OKzabbix.group_discovery OKzabbix.group_prototype OKzabbix.groups OKzabbix.history OKzabbix.history_log OKzabbix.history_str OKzabbix.history_str_sync OKzabbix.history_sync OKzabbix.history_text OKzabbix.history_uint OKzabbix.history_uint_sync OKzabbix.host_discovery OKzabbix.host_inventory OKzabbix.hostmacro OKzabbix.hosts OKzabbix.hosts_groups OKzabbix.hosts_templates OKzabbix.housekeeper OKzabbix.httpstep OKzabbix.httpstepitem OKzabbix.httptest OKzabbix.httptestitem OKzabbix.icon_map OKzabbix.icon_mapping OKzabbix.ids OKzabbix.images OKzabbix.interface OKzabbix.interface_discovery OKzabbix.item_discovery OKzabbix.items OKzabbix.items_applications OKzabbix.maintenances OKzabbix.maintenances_groups OKzabbix.maintenances_hosts OKzabbix.maintenances_windows OKzabbix.mappings OKzabbix.media OKzabbix.media_type OKzabbix.node_cksum OKzabbix.nodes OKzabbix.opcommand OKzabbix.opcommand_grp OKzabbix.opcommand_hst OKzabbix.opconditions OKzabbix.operations OKzabbix.opgroup OKzabbix.opmessage OKzabbix.opmessage_grp OKzabbix.opmessage_usr OKzabbix.optemplate OKzabbix.profiles OKzabbix.proxy_autoreg_host OKzabbix.proxy_dhistory OKzabbix.proxy_history OKzabbix.regexps OKzabbix.rights OKzabbix.screens OKzabbix.screens_items OKzabbix.scripts OKzabbix.service_alarms OKzabbix.services OKzabbix.services_links OKzabbix.services_times OKzabbix.sessions OKzabbix.slides OKzabbix.slideshows OKzabbix.sysmap_element_url OKzabbix.sysmap_url OKzabbix.sysmaps OKzabbix.sysmaps_elements OKzabbix.sysmaps_link_triggers OKzabbix.sysmaps_links OKzabbix.timeperiods OKzabbix.tmp3 OKzabbix.trends OKzabbix.trends_uint OKzabbix.trigger_depends OKzabbix.trigger_discovery OKzabbix.triggers OKzabbix.user_history OKzabbix.users OKzabbix.users_groups OKzabbix.usrgrp OKzabbix.valuemaps OKOK升级成功 。NOTES：目前虽然暂未发现有什么bug，但是因为5.1版本与percona server 5.6版本的数据文件差异较大，因此在线上重要数据库升级场景下还是建议使用mysqldump来进行全部导出再导入。此次升级过程记录下来仅供参考。 本文转自ITPUB博客84223932的博客，原文链接：，如需转载请自行联系原博主。