centos 6.6升级自带mysql版本文档

centos 6.6升级自带mysql版本文档

2015/9/9 11:12:45

实验环境:

主机名 ip地址 操作系统 应用角色 操作 Linux 192.168.1.107 Centos 6.6 zabbix-server & zabbix-agent 升级mysql版本

软件版本:

软件名 升级前软件版本 升级后版本 Mysql 5.1.73 5.6.26

安装配置过程

1.备份并停止MySQL服务

[root@Linux mysql]# pwd/var/lib/mysql[root@Linux mysql]# tar zcf ../mysql.tar.gz mysql[root@Linux]# cp /etc/my.cnf /etc/my.cnf.bak[root@Linux]# service mysqld stop

2.下载并安装mysql repo源

[root@Linux]# wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm[root@Linux ~]# yum localinstall mysql-community-release-el6-5.noarch.rpm#可以通过下面的命令来确认这个仓库被成功添加：[root@Linux yum.repos.d]# yum repolist enabled | grep "mysql.*-community.*"mysql-connectors-community MySQL Connectors Community                         14mysql-tools-community      MySQL Tools Community                              27mysql56-community          MySQL 5.6 Community Server                        180

3.查看/etc/yum.repos.d/mysql-community.repo配置文件

查看/etc/yum.repos.d/mysql-community.repo配置文件，里面列出了 mysql-5.5 mysql-5.7，但是默认打开了 mysql-5.6，其余两个关闭，重点看enabled=xxx这一项，=0为关闭，=1为打开,如下图注意：一次只能打开一个版本，否则yum不会选择这个文件的，又自动升级 5.1版本去了。

4.重建yum repo元数据

[root@Linux yum.repos.d]# yum makecache

5.安装zabbix相关包

#查看mysql repo相关软件包[root@Linux]# yum list | grep mysql-communitymysql-community-release.noarch              el6-5                        @/mysql-community-release-el6-5.noarchmysql-community-bench.x86_64                5.6.26-2.el6                 mysql56-communitymysql-community-client.i686                 5.6.26-2.el6                 mysql56-communitymysql-community-client.x86_64               5.6.26-2.el6                 mysql56-communitymysql-community-common.i686                 5.6.26-2.el6                 mysql56-communitymysql-community-common.x86_64               5.6.26-2.el6                 mysql56-communitymysql-community-devel.i686                  5.6.26-2.el6                 mysql56-communitymysql-community-devel.x86_64                5.6.26-2.el6                 mysql56-communitymysql-community-embedded.i686               5.6.26-2.el6                 mysql56-communitymysql-community-embedded.x86_64             5.6.26-2.el6                 mysql56-communitymysql-community-embedded-devel.i686         5.6.26-2.el6                 mysql56-communitymysql-community-embedded-devel.x86_64       5.6.26-2.el6                 mysql56-communitymysql-community-libs.i686                   5.6.26-2.el6                 mysql56-communitymysql-community-libs.x86_64                 5.6.26-2.el6                 mysql56-communitymysql-community-libs-compat.i686            5.6.26-2.el6                 mysql56-communitymysql-community-libs-compat.x86_64          5.6.26-2.el6                 mysql56-communitymysql-community-server.x86_64               5.6.26-2.el6                 mysql56-communitymysql-community-test.x86_64                 5.6.26-2.el6                 mysql56-community#安装高版本mysql[root@Linux yum.repos.d]# yum install mysql-community-server.x86_64 -y

6.查看mysql版本。

[root@Linux ~]# mysql --versionmysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1[root@Linux ~]# mysql --versionmysql  Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using  EditLine wrapper

7.测试mysql是否能正常使用。

[root@Linux yum.repos.d]# mysql --versionmysql  Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using  EditLine wrapper[root@Linux yum.repos.d]# service mysqld restart停止 mysqld：                                              [确定]正在启动 mysqld：                                          [确定][root@Linux yum.repos.d]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.6.26 MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

端口监听情况

[root@Linux yum.repos.d]# lsof -i:3306COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAMEmysqld  3327 mysql   10u  IPv6  30158      0t0  TCP *:mysql (LISTEN)

查看机器上所跑的zabbix是否恢复正常。

8.问题记录

#zabbix原先有监控mysql数据模板，并测试可以正常使用，可以去参考我直接发表的zabbix 2.4监控mysql所示，但是升级完后一直监控不到，查看mysql日志，发现如下：

#上述所说的是缺表现象，解决办法：#由于是将低版本的MySQL datadir目录直接拷贝到5.6.21-enterprise-commercial-advanced-log的，低版本的mysql这个schema中并没有innodb_table_stats，innodb_index_stats这些表。所以登录到mysql这个schema中手工创建这些表后，重启mysql即可。[root@Linux log]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 43Server version: 5.6.26 MySQL Community Server (GPL)Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               || zabbix             |+--------------------+4 rows in set (0.00 sec)mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> CREATE TABLE `innodb_table_stats` (    ->   `database_name` varchar(64) COLLATE utf8_bin NOT NULL,    ->   `table_name` varchar(64) COLLATE utf8_bin NOT NULL,    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    ->   `n_rows` bigint(20) unsigned NOT NULL,    ->   `clustered_index_size` bigint(20) unsigned NOT NULL,    ->   `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,    ->   PRIMARY KEY (`database_name`,`table_name`)    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;Query OK, 0 rows affected (0.17 sec)mysql> CREATE TABLE `innodb_index_stats` (    ->   `database_name` varchar(64) COLLATE utf8_bin NOT NULL,    ->   `table_name` varchar(64) COLLATE utf8_bin NOT NULL,    ->   `index_name` varchar(64) COLLATE utf8_bin NOT NULL,    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    ->   `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,    ->   `stat_value` bigint(20) unsigned NOT NULL,    ->   `sample_size` bigint(20) unsigned DEFAULT NULL,    ->   `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,    ->   PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;Query OK, 0 rows affected (0.02 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)#再查看zabbix监控mysql图像，看是否有数据

已经可以从数据库读取数据，mysql升级成功。

(●’◡’●)

参考文献：

http://blog.chinaunix.net/uid-28216282-id-4848404.htmlhttp://www.linuxidc.com/Linux/2014-11/109144.htmhttp://blog.csdn.net/lwei_998/article/details/41278457