|
本文主要介绍使用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) |