Centos下升级Mysql(5.1 -> 5.7)

安装软件需要版本更高的Mysql,就做了个升级。

  1. 如果担心数据丢失,可以先备份数据库。

    1
    mysqldump -u xxx -h xxx -P 3306 -p --all-databases > databases.sql
  2. 停止Mysql服务。

    1
    service mysqld stop
  3. 卸载旧版Mysql。

    1
    yum remove mysql mysql-*

    移除命令执行后,可再看看是否有残余的mysql

    1
    yum list installed | grep mysql

    如果有,删除:

    1
    yum remove mysql-libs
  4. 下载5.7版本的rpm文件。

    1
    rpm -Uvh http://repo.mysql.com/mysql57-community-release-el6-7.noarch.rpm
  5. 安装Mysql。

    1
    yum install mysql-community-server
  6. 验证。

    1
    2
    mysql -V
    mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper

问题1:
Mysql服务启动失败,需要做初始化

1
mysqld --initialize

遇到如下错误,

1
2
3
2018-06-5T05:36:21.07069Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-06-5T05:36:21.08821Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2018-06-5T05:36:21.08870Z 0 [ERROR] Aborting

做如下操作:

1
2
rm -rf /var/lib/mysql
service mysqld start

问题2:
无法登录,

1
2
3
[root@Automation mysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

新版Mysql会设置默认密码,通过下面获得,

1
2
grep 'temporary password' /var/log/mysqld.log
2018-06-05T05:38:02.215252Z 1 [Note] A temporary password is generated for root@localhost: adwyZ;K=7Ub<

修改密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
[root@Automation mysql]# service mysqld stop
停止 mysqld: [确定]
[root@Automation mysql]# ps -ef | grep mysql
root 18570 11168 0 15:32 pts/3 00:00:00 grep mysql
[root@Automation mysql]# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
[1] 18571
2018-06-05T07:32:24.885303Z mysqld_safe Logging to '/var/log/mysqld.log'.
2018-06-05T07:32:24.928701Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^C
[root@Automation mysql]# mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> update mysql.user set authentication_string=password('admin') where User='root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> update mysql.user set Host='%' where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

登录之后,还是不行,做如下操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[root@Automation mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23

Copyright (c) 2000, 2018, 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> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'admin';
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+

| Database |
+--------------------+

| information_schema |
| mysql |
| performance_schema |

| sys |
+--------------------+

4 rows in set (0.00 sec)

到此,Mysql升级完成。

唐胡璐 wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
分享创造价值,您的支持将鼓励我继续前行!