centos升级mysql至5.7
1、备份原数据库
[root@www ~] #mysqldump -u root –p -E –all-database > /home/db-backup.sql
加-E是因为mysqldump默认并不处理mysql的事件，需要自己指明是否导出事件。
copy一份my.cnf，待安装mysql5.7的时候参照。
[root@www ~]# cp /etc/my.cnf /home/my.cnf
停止mysql服务
[root@www ~]# service mysqld stop
停止 mysqld： [确定]
卸载旧mysql版本。
[root@www ~]# yum remove mysql mysql-server
已加载插件：fastestmirror, priorities, refresh-packagekit
设置移除进程
Loading mirror speeds from cached hostfile
* base: mirrors.skyshe.cn
* epel: mirrors.hustunique.com
* extras: mirrors.163.com
* rpmforge: mirrors.neusoft.edu.cn
* updates: mirrors.163.com
132 packages excluded due to repository priority protections
解决依赖关系
--> 执行事务检查
---> Package mysql.i686 0:5.1.73-5.el6_6 will be 删除
--> 处理依赖关系 mysql = 5.1.73-5.el6_6，它被软件包 mysql-devel-5.1.73-5.el6_6.i 686 需要
---> Package mysql-server.i686 0:5.1.73-5.el6_6 will be 删除
--> 执行事务检查
---> Package mysql-devel.i686 0:5.1.73-5.el6_6 will be 删除
--> 处理依赖关系 mysql-devel = 5.1.73-5.el6_6，它被软件包 mysql-embedded-devel-5 .1.73-5.el6_6.i686 需要
--> 执行事务检查
---> Package mysql-embedded-devel.i686 0:5.1.73-5.el6_6 will be 删除
--> 完成依赖关系计算依赖关系解决
================================================================================
软件包 架构 版本 仓库 大小
================================================================================
正在删除:
mysql i686 5.1.73-5.el6_6 @updates 2.3 M
mysql-server i686 5.1.73-5.el6_6 @updates 24 M
为依赖而移除:
mysql-devel i686 5.1.73-5.el6_6 @updates 388 k
mysql-embedded-devel i686 5.1.73-5.el6_6 @updates 14 M事务概要
================================================================================
Remove 4 Package(s)Installed size: 41 M
确定吗？[y/N]：y
下载软件包：
运行 rpm_check_debug
执行事务测试
事务测试成功
执行事务
正在删除 : mysql-server-5.1.73-5.el6_6.i686 1/4
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
正在删除 : mysql-embedded-devel-5.1.73-5.el6_6.i686 2/4
正在删除 : mysql-devel-5.1.73-5.el6_6.i686 3/4
正在删除 : mysql-5.1.73-5.el6_6.i686 4/4
Verifying : mysql-5.1.73-5.el6_6.i686 1/4
Verifying : mysql-embedded-devel-5.1.73-5.el6_6.i686 2/4
Verifying : mysql-server-5.1.73-5.el6_6.i686 3/4
Verifying : mysql-devel-5.1.73-5.el6_6.i686 4/4删除:
mysql.i686 0:5.1.73-5.el6_6 mysql-server.i686 0:5.1.73-5.el6_6作为依赖被删除:
mysql-devel.i686 0:5.1.73-5.el6_6 mysql-embedded-devel.i686 0:5.1.73-5.el6_6完毕！
[root@www download]# yum remove mysql-embedded mysql-libs
… #删除中…
如果centos安装有mysql-client/mysql-devel，同样需要执行yum删除。
一个更好的办法是一次删除mysql开发的所有包
[root@www download]# yum remove mysql-*
不过这里可能会导致一个非常严重的问题，mysql-libs删除时，yum会将依赖它的crontab/postfix一起删除掉。
而新的mysql5.7安装后，如果再yum install crontab，则它又会将mysql5.1.*安装回系统，两个不同版本的mysql共存会共用/etc/my.cnf，yum将会警告冲突。
如果不幸发生这种情况。
# 一个技巧是通过yum dailyjobs安装crontabs yum install -y crontabs # 它只安装依赖cronie*及crontabs，而不安装libmysql # 问题在于安装postfix则必然会检查libmysqlclient，这又将导致安装了mysql5.1.* # 既然不能通过yum安装postfix，那么只能到官网下载编译了...
避免这种情况发生的一个方法是yum mysql-libs 时，通过rpm删除
# 声明不删除依赖mysql-libs的其它软件 rpm -e --nodeps mysql-libs-5.1.*
至于是否会导致其它情况，则有待观察。
后来观察到的一种情况是：
1)、下载mysql-libs兼容库（这东东只有mysql5.6以后才有)
wget -c https://dev.mysql.com/downloads/file/?id=463151
删除掉原来的mysql：
yum remove mysql-*
记住，不要加-y参数；yum分析依赖，记住这些依赖，比如：
cronie
cronie-anacron
crontabs
postfix
下载mysql install yum repo：
# wget -c http://dev.mysql.com/downloads/file/?id=462552
# yum -Uvh mysql57-community-release-e16-8.noarch.rpm
# yum repolist all | grep mysql
# vim /etc/yum.repos.d/mysql-community.repo
# yum repolist enabled | grep ^mysql
# yum install mysql-community-server
安装mysql-shared-compat：
rpm -Uvh MySQL-shared-compat-5.6.31-l.e16.x86_64.rpm
安装原来卸载的几个库：
yum install cronie* crontabs postfix
2、下载mysql5.7，需要先检查centos的系统位数
[root@www ~]#getconf LONG_BIT
[root@www ~]#getconf WORD_BIT
我这里两个显示都是32，因此应该下载32版本(对应i686)。
到http://dev.mysql.com/downloads/mysql/这个地址下载最新稳定版本，centos对应选择Red Hat…
[root@www download]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.10-1.el6.i686.rpm
[root@www download]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.10-1.el6.i686.rpm
[root@www download]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.10-1.el6.i686.rpm
[root@www download]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.10-1.el6.i686.rpm
安装依赖
[root@www download]# yum install -y libnuma*
分别安装
[root@www download]# rpm -ivh mysql-community-common-5.7.10-1.el6.i686.rpm
[root@www download]# rpm -ivh mysql-community-libs-5.7.10-1.el6.i686.rpm
[root@www download]# rpm -ivh mysql-community-client-5.7.10-1.el6.i686.rpm
[root@www download]# rpm -ivh mysql-community-server-5.7.10-1.el6.i686.rpm
查看安装信息
[root@www download]# yum list installed | grep ^mysql
mysql-community-client.i686 5.7.10-1.el6 installed
mysql-community-common.i686 5.7.10-1.el6 installed
mysql-community-libs.i686 5.7.10-1.el6 installed
mysql-community-server.i686 5.7.10-1.el6 installed
最后查看 mysql 版本
[root@www download]# mysql --version
mysql Ver 14.14 Distrib 5.7.10, for Linux (i686) using EditLine wrapper
看来真的已经正确安装了。
一个更便捷的办法就是通过yum仓库安装依赖，具体查看官网。
检查my.cnf
[root@www download]# vim /etc/my.cnf
1 # For advice on how to change settings please see
2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
3
4 [mysqld]
5 #
6 # Remove leading # and set to the amount of RAM for the most important data
7 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
8 # innodb_buffer_pool_size = 128M
9 #
10 # Remove leading # to turn on a very important data integrity option: loggin g
11 # changes to the binary log between backups.
12 # log_bin
13 #
14 # Remove leading # to set options mainly useful for reporting servers.
15 # The server defaults are faster for transactions and fast SELECTs.
16 # Adjust sizes as needed, experiment to find the optimal values.
17 # join_buffer_size = 128M
18 # sort_buffer_size = 2M
19 # read_rnd_buffer_size = 2M
20 datadir=/var/lib/mysql
21 socket=/var/lib/mysql/mysql.sock
22
datadir、socket位置查看
[root@www download]# stat /var/lib/mysql
File: "/var/lib/mysql"
Size: 4096 Blocks: 8 IO Block: 4096 目录
Device: fd00h/64768d Inode: 2621666 Links: 5
Access: (0751/drwxr-x--x) Uid: ( 27/ mysql) Gid: ( 27/ mysql)
Access: 2015-12-28 01:10:54.855025003 +0800
Modify: 2015-11-30 03:48:49.000000000 +0800
Change: 2015-12-28 00:54:11.506460247 +0800
看来数据还在。
这下可以启动mysql了吧。
[root@www ~]# /etc/init.d/mysqld start
MySQL Daemon failed to start.
正在启动 mysqld： [失败]
提示失败。先升级mysql配置
[root@www ~]# mysql_upgrade -u root –p
Enter password:
mysql_upgrade: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) while connecting to the MySQL server
Upgrade process encountered error and will not continue.
结果报错，应该是'/var/lib/mysql/mysql.sock' 不存在。先生成一个
[root@www ~]# touch /var/lib/mysql/mysql.sock
还是报错。
查看mysqld的启动日志发现从5.1升级到5.7变化很大，报了几个waring和Error
2015-12-28T13:41:08.287625Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
…
2015-12-28T13:41:09.316911Z 0 [Warning] System table 'plugin' is expected to be transactional.
2015-12-28T13:41:09.318169Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2015-12-28T13:41:09.318285Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key…
2015-12-28T13:41:09.385341Z 0 [Warning] Failed to open optimizer cost constant tables
2015-12-28T13:41:09.386799Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.
2015-12-28T13:41:09.386944Z 0 [ERROR] Aborting
5.7需要添加几个系统表。
先以安全模式启动mysqld守护进程。
[root@www ~]# mysqld_safe start
151228 22:10:42 mysqld_safe Logging to '/var/log/mysqld.log'.
151228 22:10:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
151228 22:10:46 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
最后mysqld进程好像没有启动成功…
查看3306端口
[root@www ~]# netstat -ano|grep 3306
[root@www ~]#
果然一无所获。安全进程并未启动，如错误日志所述，基本系统表缺失。初始化之
[root@www ~]# mysqld –initialize
2015-12-28T14:18:49.246041Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-28T14:18:49.250859Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2015-12-28T14:18:49.250941Z 0 [ERROR] Aborting
如果查看/etc/init.d/mysqld启动项应知，mysqld --initialize命令是service mysqld start的一部份，如果 start的时候没有发现data目录则安装之，发现错误则报错…。故，上面的尝试徒劳。
看来，它检查到了原5.1的系统表，发现冲突之处。因此，应该将my.cnf下datadir/socket配置指定的存放位置移位。
[mysqld]
datadir=/var/lib/mysql5.7
socket=/var/lib/mysql5.7/mysql.sock#上面socket目录放在[mysqld]模块内，那么[client]也需要指定socket位置，不然会报错
[client]
socket=/var/lib/mysql5.7/mysql.sock
移动到了mysql5.7目录。同时，将/etc/init.d/mysqld的47行配置进行修改
[root@www ~]# vim /etc/init.d/mysqld
…
get_mysql_option mysqld datadir "/var/lib/mysql5.7"
使它的datadir指向mysql5.7，根据上下文，mysql.sock的指向也变了，它也存放在mysql5.7目录下面。
再次执行initialize命令。
[root@www ~]# mysqld –initialize
2015-12-28T14:30:42.853099Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-28T14:30:46.950814Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-12-28T14:30:47.713023Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-12-28T14:30:47.950677Z 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: 965094c2-ad6f-11e5-8c8a-001641ad962e.
2015-12-28T14:30:47.999549Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2015-12-28T14:30:48.000932Z 1 [Note] A temporary password is generated for root@localhost: sfW2h2*Y55IS
查看工作目录是否建立
[root@www ~]# ls /var/lib/mysql5.7
auto.cnf ibdata1 ib_logfile1 performance_schema
ib_buffer_pool ib_logfile0 mysql sys
有关[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.Please use --explicit_defaults_for_timestamp server option (seedocumentation for more details).的警告查看http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp，这是个临时启动设置，将来会被废弃，现在提醒你转向新的mysql非标准sql行为。
再次启动mysqld安全模式
[root@www ~]# mysqld_safe
查看端口没有启动，提示mysql5.7所在目录权限
2015-12-28T15:32:06.227936Z 0 [ERROR] InnoDB: ./ib_logfile0 can't be opened in read-write mode.
安装了SELINUX策略后，无论APACHE或MYSQL都要更改目录的权限，如果是APACHE，方法为：
# chcon -R -h -t httpd_sys_content_t /www/web/
如果是mysql，方法为：
# chcon -R -t mysqld_db_t /mysql
改变 mysql5.7的所有者和权限为用户mysql
[root@www ~]# chown -R mysql: /var/lib/mysql5.7
[root@www ~]# chmod -R og+wr /var/lib/mysql5.7
[root@www ~]# chcon -R -t mysqld_db_t /var/lib/mysql5.7
[root@www ~]# ls -laZ /var/lib/mysql5.7drwxrwxrwx mysql mysql ? .
drwxr-xr-x root root ? ..
-rw-rw-rw- mysql mysql ? auto.cnf
-rw-rw-rw- mysql mysql ? ib_buffer_pool
-rw-rw-rw- mysql mysql ? ibdata1
-rw-rw-rw- mysql mysql ? ib_logfile0
-rw-rw-rw- mysql mysql ? ib_logfile1
drwxrwxrw- mysql mysql ? mysql
drwxrwxrw- mysql mysql ? performance_schema
drwxrwxrw- mysql mysql ? sys
再次启动mysqld_safe
[root@www ~]# mysqld_safe
151229 00:40:40 mysqld_safe Logging to '/var/log/mysqld.log'.
151229 00:40:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql5.7
151229 00:40:43 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
嗯，已经没有错误了！停止 mysqld_safe
[root@www ~]# mysqld_safe stop
执行启动 mysqld
[root@www ~]# /etc/init.d/mysqld start
正在启动 mysqld： [确定]
[root@www ~]# netstat -ano|grep 3306
tcp 0 0 :::3306 :::* LISTEN off (0.00/0/0)
正常启动。
登陆mysql客户端
[root@www ~]# mysql -uroot –p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这个在mysql5.7非常困扰，因为好像从mysql5.6开始，默认新安装mysql，初始密码不为空，mysql会随机生成一个。so，你需要自己重新设置一个。
与mysql5.6 不同， mysql.user下面，没有password字段：
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *D3BFB08382EB0AB95519518E0BFF147C0A4D03E6
password_expired: Y
password_last_changed: 2015-12-28 22:31:03
password_lifetime: NULL
account_locked: N
只有authentication_string，可以看到，初始阶段，它不为空。
因为没法从mysql客户端进入，因此，只能先关闭mysqld 进程，启动mysqld_safe，指定--skip-grant-tables。
[root@www ~]# /etc/init.d/mysqld stop
停止 mysqld： [确定]
[root@www ~]# mysqld_safe --skip-grant-tables151229 21:29:14 mysqld_safe Logging to '/var/log/mysqld.log'.
151229 21:29:14 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql5.7
mysqld守护进程打开成功。新打开一个terminal window，进入mysql client。
[root@www ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 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 its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456') WHERE User='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)mysql> exit
Bye
在新窗口中退出mysqld_safe进程，
[root@www ~]# ps -A|grep mysql
3758 pts/1 00:00:00 mysqld_safe
3939 pts/1 00:00:00 mysqld
[root@www ~]# kill -9 3758 3939
在mysqld_safe所在窗口(terminal)，可以看到mysqld_safe已杀死。
现在尝试正常开启mysqld，并进入mysql client。
[root@www ~]# /etc/init.d/mysqld start
正在启动 mysqld： [确定]
[root@www ~]# mysql -uroot –p
…
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
成功。
检查自启动项
[root@www download]# chkconfig –list
NetworkManager 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭
…
multipathd 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭 6:关闭
mysqld 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭 6:关闭
netconsole 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭 6:关闭
可见其没有开启，故开启之
[root@www ~]# chkconfig --add mysqld
现在再来看mysqld的启动项：
…
mysqld 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭
…
已经2 3 4 5级别level启动（依/ect/init.d/mysqld启动脚本设定）。
再有选择地导入之前保存的sql备份（略）。
这里需要提醒的是， mysql5.7增加了一个严格模式，它认为命令行的操作都是不安全的，因此需要指定客户端（命令行）连接的有效期。如果没有，它会查看session状态，一旦检查到其它操作，立即会提醒你更改密码。因此最好在退出客户端前通过下面语句更改密码。
mysql> ALTER USER USER() IDENTIFIED BY 'new_password';
需要注意的是，此mysql5.6起，mysql已经不允许密码设置得过于简单，内置加载一个叫validate-password.so的插件进行检查。密码强度，必须含有0-9，a-z,A-Z以及“-”或“_”，不然不给通过。
然后在/etc/my.cnf增加一个客户端（命令行）有效期，当然可以设置永不过期。
[mysqld]
default_password_lifetime=0
所有设置完毕，如果还不能启动，最大可能是my.cnf配置错误，参照这里。
至此结束。
