MYSQL 升级

mysql作为一款开源数据库(尤其在被oracel收购后，innodb引擎功能上越来越像oracle高级功能进步)，功能再不断强化，各迭代版本性能方面也是有飞跃式提升，如5.6比5.5多了很多特性，5.7相比5.6又有很大提升，因此，当前版本升级稳定而又功能强大的新版本还是有必要的。
mysql 升级官方地址说明http://dev.mysql.com/doc/refman/5.7/en/upgrading.html（5.7版本）

以mysql 5.6.27 升级到5.7.13为例：

测试采用单机环境：

分别采用rpm与二进制安装包实现升级

二进制版升级：
1、安装新版本【mysql5.7.13】
2、生成新版本配置文件【并将其配置改为5.5.27相关配置。如改为5.6.27对应目录和端口】
3、重启新库
一、对于5.7.13二进制版安装
1、下载解压mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
wget http://101.96.10.72/dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
tar zxvf mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
2、移动到 /usr/local/mysql
mv mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz /usr/local/mysql
3、初始化库
./bin/mysqld –initialize –datadir=/usr/local/mysql/data

【初始化时默认会按照/etc/my.cnf加载初始化信息
如：原mysql-server-5.6.27配置文件有参数innodb_additional_mem_pool_size=128M
执行时出现
[root@localhost mysql]# ./bin/mysqld –initialize –datadir=/usr/local/mysql/data
2016-10-03T08:44:14.145104Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2016-10-03T08:44:15.684047Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-10-03T08:44:15.762482Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-10-03T08:44:15.822484Z 0 [ERROR] unknown variable ‘innodb_additional_mem_pool_size=10M’
2016-10-03T08:44:15.822509Z 0 [ERROR] Aborting
注释该参数项后初始化成功】

[root@localhost mysql]# ./bin/mysqld –initialize –datadir=/usr/local/mysql/data
2016-10-03T08:45:00.174485Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2016-10-03T08:45:01.256143Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-10-03T08:45:01.342699Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-10-03T08:45:01.438160Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ac17ffc2-8945-11e6-961c-000c29691dd6.
2016-10-03T08:45:01.439628Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2016-10-03T08:45:01.441239Z 1 [Note] A temporary password is generated for root@localhost: pl&lLOC/y7l/###
2016-10-03T08:45:02.916751Z 1 [Warning] ‘user’ entry ‘root@localhost’ ignored in –skip-name-resolve mode.
2016-10-03T08:45:02.916781Z 1 [Warning] ‘user’ entry ‘mysql.sys@localhost’ ignored in –skip-name-resolve mode.
2016-10-03T08:45:02.916794Z 1 [Warning] ‘db’ entry ‘sys mysql.sys@localhost’ ignored in –skip-name-resolve mode.
2016-10-03T08:45:02.916803Z 1 [Warning] ‘proxies_priv’ entry ‘@ root@localhost’ ignored in –skip-name-resolve mode.
2016-10-03T08:45:02.916829Z 1 [Warning] ‘tables_priv’ entry ‘sys_config mysql.sys@localhost’ ignored in –skip-name-resolve mode.
注：初始化密码 pl&lLOC/y7l/

修改目录权限
chown -R mysql.mysql /usr/local/mysql/data/
初始化my.cnf文件
cp /usr/local/mysql/support-files/my-default.cnf /usr/local/mysql/my.cnf

my.cnf放到安装目录下，否则使用/etc/my.cnf下的配置文件，因该配置文件为5.6.27配置文件，故要做区分

如 vi /usr/local/mysql/my.cnf
datadir = /usr/local/mysql/data
port = 3307
server_id = 2
socket = /usr/local/mysql/mysql57.sock【要与5.6.27配置区分】
pid-file=/usr/local/mysql/mysql.pid【要与5.6.27配置区分】
[mysqld_safe]
log-error=/usr/local/mysql/mysqld.log 【要与5.6.27配置区分】
4、启动
./support-files/mysql.server start 【将加载 /usr/local/mysql/my.cnf 或者使用mysqld_safe –defaults-file= /usr/local/mysql/my.cnf 】

启动信息查看[root@localhost mysql]# ps -ef|grep mysql
root 13903 1 0 17:10 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –datadir=/usr/local/mysql/data –pid-file=/usr/local/mysql/mysql.pid
mysql 14611 13903 2 17:10 pts/1 00:00:00 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –log-error=/usr/local/mysql/mysqld.log –open-files-limit=65535 –pid-file=/usr/local/mysql/mysql.pid –socket=/usr/local/mysql/mysql57.sock –port=3307
【两个版本同时可同时运行】
root 14655 1 1 17:10 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe –datadir=/data/mysql_data/mysql –pid-file=/data/mysql_data/mysql/mysql.pid
mysql 15282 14655 25 17:10 pts/0 00:00:00 /usr/sbin/mysqld –basedir=/usr –datadir=/data/mysql_data/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=/data/mysql_data/log/mysqld.log –open-files-limit=65535 –pid-file=/data/mysql_data/mysql/mysql.pid –socket=/var/lib/mysql/mysql.sock –port=3306
root 15302 5547 0 17:10 pts/1 00:00:00 grep –color=auto mysql
[root@localhost mysql]#

5、连接
[root@localhost mysql]# mysql -uroot -p -P3307 -S /usr/local/mysql/mysql57.sock
进入后需修改密码
mysql>set password=password(‘123456’);

（-S /usr/local/mysql/mysql57.sock 不添加这项 连接老去通过/var/lib/mysql/mysql.sock连接 导致连接异常）
解决方式参考
http://www.aiezu.com/db/mysql_cant_connect_through_socket.html
添加以下
[mysqld]

socket=/storage/db/mysql/mysql.sock

[mysql]
socket=/storage/db/mysql/mysql.sock

[mysqldump]
socket=/storage/db/mysql/mysql.sock

[mysqladmin]
socket=/storage/db/mysql/mysql.sock

6、修改配置文件，将5.7中的配置文件datadir目录改为5.6.27对应数据目录

7、升级【主要升级授权信息】
../bin/mysql_upgrade -uroot -p -S /usr/local/mysql/mysql57.sock

至此 5.6.27升级为5.7.13
注：对于分区表需要 ALTER TABLE master.user_r_redbag_bak UPGRADE PARTITIONING
PS：也可将5.6.27数据使用mysqldump 导出后导入5.7.13数据库，并停止5.6.27连接5.7.13即可

8、文件替换：将5.6.27时的mysql* 相关文件（如mysql启动文件、mysqldump等命令文件）用5.7文件替换。
如：/usr/local/mysql/bin 目录下 /etc/init.d/mysql 用./support-files/mysql.server替换

升级方式二：
rpm安装相对简单，安装新版本yum源，执行升级操作
首先安装mysql yum库
mysql57-community-release-el6-8.noarch.rpm
yum update mysql*