你的位置:首页 > 数据库

[数据库]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删除。

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

看来真的已经正确安装了。

检查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 start

查看端口没有启动,提示mysql5.7所在目录权限

2015-12-28T15:32:06.227936Z 0 [ERROR] InnoDB: ./ib_logfile0 can't be opened in read-write mode.

改变 mysql5.7的所有者和权限为用户mysql

[root@www ~]# chown -R mysql: /var/lib/mysql5.7

[root@www ~]# chmod -R og+wr /var/lib/mysql5.7
[root@www ~]# ls -laZ /var/lib/mysql5.7

drwxrwxrwx 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 start

[root@www ~]# mysqld_safe start

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-tables

151229 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 -A

Database 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: 1

mysql> 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.10

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.

成功。

检查自启动项

[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备份(略)。

至此结束。