MySQL 5.1升级到Percona Server 5.6.17

时间:2014-12-26 23:24 来源:itpub.net 作者:张正

本文主要介绍使用mysql_upgrade
从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监控服务器)

旧版本数据库的参数文件：
[root@root lib]# cat /etc/my.cnf
[client]
socket=/var/lib/mysql/mysql.sock
port = 3306

[mysqld]
port = 3306
datadir=/var/lib/mysql
basedir=/usr
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/mysqld.pid
log-error=/var/lib/mysql/localhost.err
user=mysql
default-character-set=utf8
innodb_buffer_pool_size=1G

1.关闭旧版本数据库服务，备份数据文件：
service mysqld stop
cd /var/lib
tar zcvf mysql.tar.gz mysql

2.解压：
tar -zxf Percona-Server-5.6.17-rel65.0-587.Linux.x86_64.tar.gz
mv Percona-Server-5.6.17-rel65.0-587.Linux.x86_64 /home/mysql/mysql
/home/mysql/mysql作为新版本数据库的basedir

3.修改参数文件：
vi /etc/my.cnf
basedir=/home/mysql/mysql
character-set-server=utf8
skip-grant-tables
修改新版本数据库的basedir
default-character-set是老版本中的参数，新版本中使用character-set-server来代替。
skip-grant-tables在后面的mysql_upgrade用到。

检查其他不兼容或过时的参数并修改

4.修改mysqld文件：
移走老文件：
mv /etc/init.d/mysqld /etc/init.d/old_mysqld
cp /home/mysql/mysql/support-files/mysql.server /etc/init.d/mysqld

修改参数：
vi /etc/init.d/mysqld
basedir=/home/mysql/mysql
datadir=/var/lib/mysql

5.修改PATH
vi ~/.bash_profile
添加：
PATH=/home/mysql/mysql/bin:$PATH
export PATH

source ~/.bash_profile

6.启动数据库：
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 directory
140527 15:10:08 mysqld_safe mysqld from pid file /var/lib/mysql/root.pid ended

说没找到libssl.so.6
cd /usr/lib64
ls |grep libssl
发现确实没有该文件

cd /usr/lib
ls |grep libssl

libssl.so.0.9.8e
libssl.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: ELFCLASS32
140527 15:28:07 mysqld_safe mysqld from pid file /var/lib/mysql/root.pid ended

百度了一下，说是64位的系统使用了32位的包，查一下：
[root@root mysql]# rpm -qa|grep openssl
openssl-devel-1.0.0-20.el6.x86_64
openssl-1.0.0-20.el6.x86_64
openssl098e-0.9.8e-17.el6.i686

lrwxrwxrwx. 1 root root 16 May 27 15:23 libssl.so.6 -> libssl.so.0.9.8e
[root@root lib]# yum list|grep ssl
docbook-style-dsssl.noarch 1.79-10.el6 @anaconda-CentOS-201112091719.x86_64/6.2
nss_compat_ossl.x86_64 0.9.6-1.el6 @anaconda-CentOS-201112091719.x86_64/6.2
openssl.x86_64 1.0.0-20.el6 @anaconda-CentOS-201112091719.x86_64/6.2
openssl-devel.x86_64 1.0.0-20.el6 @anaconda-CentOS-201112091719.x86_64/6.2
openssl098e.i686 0.9.8e-17.el6 @name
qpid-cpp-client-ssl.x86_64 0.12-6.el6 @anaconda-CentOS-201112091719.x86_64/6.2
qpid-cpp-server-ssl.x86_64 0.12-6.el6 @anaconda-CentOS-201112091719.x86_64/6.2
krb5-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.i686
yum -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 structure
2014-05-27 15:38:12 26533 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
2014-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 structure
2014-05-27 15:38:12 26533 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure

7.进行升级：
因为上面参数文件中加了skip-grant-tables，不使用授权表，因此此时直接输入mysql即可进入数据库。

cd /home/mysql/mysql
./bin/mysql_upgrade

[root@root mysql]# ./bin/mysql_upgrade
Looking for 'mysql' as: ./bin/mysql
Looking for 'mysqlcheck' as: ./bin/mysqlcheck
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'
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.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info 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'...
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 connect
FATAL ERROR: Upgrade failed

说访问权限的问题，此时直接输入mysql，提示错误：
[root@root mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

进入数据库看一下授权标：
[root@root mysql]# mysql -uroot -pxxxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.17-65.0-rel65.0 Percona Server with XtraDB (GPL), Release rel65.0, Revision 587

Copyright (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 license

Type '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/mysql
Looking for 'mysqlcheck' as: ./bin/mysqlcheck
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'
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.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info 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'...
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 OK
test.test2 OK
test.test3 OK
zabbix.acknowledges OK
zabbix.actions OK
zabbix.alerts OK
zabbix.application_template OK
zabbix.applications OK
zabbix.auditlog OK
zabbix.auditlog_details OK
zabbix.autoreg_host OK
zabbix.conditions OK
zabbix.config OK
zabbix.dbversion OK
zabbix.dchecks OK
zabbix.dhosts OK
zabbix.drules OK
zabbix.dservices OK
zabbix.escalations OK
zabbix.events OK
zabbix.expressions OK
zabbix.functions OK
zabbix.globalmacro OK
zabbix.globalvars OK
zabbix.graph_discovery OK
zabbix.graph_theme OK
zabbix.graphs OK
zabbix.graphs_items OK
zabbix.group_discovery OK
zabbix.group_prototype OK
zabbix.groups OK
zabbix.history OK
zabbix.history_log OK
zabbix.history_str OK
zabbix.history_str_sync OK
zabbix.history_sync OK
zabbix.history_text OK
zabbix.history_uint OK
zabbix.history_uint_sync OK
zabbix.host_discovery OK
zabbix.host_inventory OK
zabbix.hostmacro OK
zabbix.hosts OK
zabbix.hosts_groups OK
zabbix.hosts_templates OK
zabbix.housekeeper OK
zabbix.httpstep OK
zabbix.httpstepitem OK
zabbix.httptest OK
zabbix.httptestitem OK
zabbix.icon_map OK
zabbix.icon_mapping OK
zabbix.ids OK
zabbix.images OK
zabbix.interface OK
zabbix.interface_discovery OK
zabbix.item_discovery OK
zabbix.items OK
zabbix.items_applications OK
zabbix.maintenances OK
zabbix.maintenances_groups OK
zabbix.maintenances_hosts OK
zabbix.maintenances_windows OK
zabbix.mappings OK
zabbix.media OK
zabbix.media_type OK
zabbix.node_cksum OK
zabbix.nodes OK
zabbix.opcommand OK
zabbix.opcommand_grp OK
zabbix.opcommand_hst OK
zabbix.opconditions OK
zabbix.operations OK
zabbix.opgroup OK
zabbix.opmessage OK
zabbix.opmessage_grp OK
zabbix.opmessage_usr OK
zabbix.optemplate OK
zabbix.profiles OK
zabbix.proxy_autoreg_host OK
zabbix.proxy_dhistory OK
zabbix.proxy_history OK
zabbix.regexps OK
zabbix.rights OK
zabbix.screens OK
zabbix.screens_items OK
zabbix.scripts OK
zabbix.service_alarms OK
zabbix.services OK
zabbix.services_links OK
zabbix.services_times OK
zabbix.sessions OK
zabbix.slides OK
zabbix.slideshows OK
zabbix.sysmap_element_url OK
zabbix.sysmap_url OK
zabbix.sysmaps OK
zabbix.sysmaps_elements OK
zabbix.sysmaps_link_triggers OK
zabbix.sysmaps_links OK
zabbix.timeperiods OK
zabbix.tmp3 OK
zabbix.trends OK
zabbix.trends_uint OK
zabbix.trigger_depends OK
zabbix.trigger_discovery OK
zabbix.triggers OK
zabbix.user_history OK
zabbix.users OK
zabbix.users_groups OK
zabbix.usrgrp OK
zabbix.valuemaps OK
OK

升级成功。

NOTES：

目前虽然暂未发现有什么bug，但是因为5.1版本与percona server 5.6版本的数据文件差异较大，因此在线上重要数据库升级场景下还是建议使用mysqldump来进行全部导出再导入。此次升级过程记录下来仅供参考。

(责任编辑：IT)