Mysql5.7升级Mysql8.0

CentOS 7 yum无法使用解决方法Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=_loading mirror speeds from cached hostfile could n-CSDN博客

环境说明

Linux系统以及centos7.9镜像

本文讲解视频

Mysql5.7升级到Mysql8.0_哔哩哔哩_bilibili

 Mysql5.7安装

MySQL :: Download MySQL Community Server (Archived Versions)

rpm -qa | grep mariabd
yum remove mariabd-libs* -y

tar xf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz -C /opt

cd /opt
[root@mysql57 opt]# ln -s /opt/mysql* /usr/local/mysql

[root@mysql57 local]# cd
[root@mysql57 ~]# vi .bashrc
放末尾
export PATH=$PATH:/usr/local/mysql/bin
source .bashrc

mysqld服务器进程

socket启动mysqld进程

用户是mysql

vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




adduser -r mysql不用mysql登陆
which mysqld


mysqld --verbose --help

mysqld --initialize mysql初始化 有密码root@localhost那里


mkdir /var/run/mysqld
chown mysql:mysql /var/run/mysqld
启动mysql数据库
mysqld_safe & 启动mysqld_safe服务



[root@mysql57 ~]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

4qTLSd#F)OM4

ps -ef | grep mysql

ERROR 2002 (HY000)

这个是mysql服务器没有启动所导致原因

[root@mysql57 local]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
 

这个是二进制文件里默认的/tmp/mysql.sock

启动的对应的是这个/var/lib/mysql/mysql.sock 利用ps -ef | grep mysql 查看--socket=

所以会报上面的错配一下这个就行

vi /etc/my.cnf
这个东西放最上面
[client]
socket=/var/lib/mysql/mysql.sock
...

-h不写默认是localhost

mysql> select user();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> alter user user() identified by 'password';


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

alter user root@localhost identified by 'password';和上面命令相同

启动关闭数据库

关闭操作一
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
[root@mysql57 ~]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
启动服务
mysqld_safe &

关闭操作二
[root@mysql57 ~]# mysqladmin -uroot -ppassword shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.


[root@mysql57 ~]# ps -ef | grep mysql
root      10180   2137  0 04:56 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysql     10315  10180  6 04:56 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      10344  10147  0 04:56 pts/1    00:00:00 grep --color=auto mysql

kill -9 10180
kill -9 10315

systemctl 配置

mysqladmin -uroot -ppassword shutdown
cd /usr/lib/systemd/system
vi mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(7)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=simple
PIDFile=/var/run/mysqld/mysqld.pid
TimeoutSec=0
PermisstionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false


systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld.service

Mysql5.7升级到Mysql8.0

MySQL :: MySQL Documentation

 MySQL :: MySQL 5.7 Reference Manual :: 2.10 Upgrading MySQL

 Mysql-shell 下载地址：https://dev.mysql.com/downloads/shell/

注意跟着我步骤走不然会出问题 

tar -xf mysql-shell-8.0.20-linux-glibc2.12-x86-64bit

cd /root/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit/bin

./mysqlsh --help | less

./mysqlsh root@localhost -- util checkForServerUpgrade 执行这个命令会报socket文件找不到我直接改的/etc/my.cnf把socket目录改成/tmp/mysql.sock

cd /opt
因为数据和程序是分离的
程序在opt下
数据在/var/lib下


[root@mysql57 opt]# cd
[root@mysql57 ~]# ll
total 1139896
-rw-------. 1 root root       1287 Aug  9 00:44 anaconda-ks.cfg
-rw-r--r--. 1 root root  641798603 Aug  9 00:48 mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
-rw-r--r--. 1 root root  490922012 Aug  9 05:52 mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
drwxr-xr-x. 5 7161 31415        41 Mar 11  2020 mysql-shell-8.0.20-linux-glibc2.12-x86-64bit
-rw-r--r--. 1 root root   34520328 Aug  9 06:19 mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz
[root@mysql57 ~]# tar -xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@mysql57 ~]# mv mysql-8.0.20-linux-glibc2.12-x86_64 /opt
[root@mysql57 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.21 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> select version();
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> exit
Bye

[root@mysql57 opt]# cd /usr/local/
unlink mysql
ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql

一定要注意启动服务要检查一下服务是否关闭！！！！我出问题在这里
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

mysql -uroot -p

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

[root@mysql57 ~]# mysql_upgrade -uroot -ppassword
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.
mysql8.0.16以后启动mysql以后就已经更新了！

mysqladmin -uroot -ppassword shutdown
cd /usr/lib/systemd/system
vi mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(7)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=simple
PIDFile=/var/run/mysqld/mysqld.pid
TimeoutSec=0
PermisstionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false


systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld.service

 到此mysql5.7升级到mysql8.0就结束了！

参考文献：

04.MySQL5.7升级到MySQL8.0_哔哩哔哩_bilibili

MySQL配置通过systemctl管理 - PiscesCanon - 博客园 (cnblogs.com)

从MySQL5.7平滑升级到MySQL8.0的最佳实践分享_mysql8平滑升级-CSDN博客

MySQL5.7升级到8.0过程详解 - MySQL技术 - 博客园 (cnblogs.com)

总结：

本人基本上是个人理解加参考其他大佬的肯定有很多问题欢迎指正，我会及时修改。