mysql5.7升级到mysql8.0.28(服务器自动完成)
环境:
OS:Centos 7
旧版本:5.7.29
新版本:8.0.28
新旧版本都在同一台服务器上面
1.参考如下方法先安装好mysql8.0并正常启动
https://www.cnblogs.com/hxlasky/p/15960873.html
注意这个时候旧的数据是正常运行的,新版本的可以使用新的端口启动
2.停掉新旧的数据库
旧数据库:
/opt/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -p -S /opt/mysql57/mysql.sock shutdown
新数据库:
/opt/mysql8/bin/mysqladmin -h localhost -uroot -P23306 -p -S /opt/mysql8/mysql.sock shutdown
3.备份新版本的data目录
[root@localhost mysql8]# cd /opt/mysql8
[root@localhost mysql8]# mv data bakdata
[root@localhost mysql8]# mkdir data
4.将旧版本目录下的data目录拷贝到新版本(新版本已经停掉)的相应目录下
[root@localhost mysql5640]# cd /opt/mysql57 ##进入到旧数据库的安装目录
[root@localhost mysql5640]# cp -r data /opt/mysql8/ ##将data文件夹拷贝到新的安装目录
5.修改目录目录权限
[root@localhost binlog]# cd /opt
[root@localhost opt]# chown -R mysql:mysql ./mysql8
6.启动新版本数据库
[root@localhost opt]# /opt/mysql8/bin/mysqld_safe --defaults-file=/opt/mysql8/conf/my.cnf --user=mysql &
查看启动日志
[root@localhost opt]# more /opt/mysql8/mysqllog/logfile/mysql-err.log
2023-05-10T03:05:52.458729Z 11 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (V
ersion: 8.0.28).
2023-05-10T03:05:54.471537Z 0 [Warning] [MY-010909] [Server] /opt/mysql8/bin/mysqld: Forcing close of thread 10 user:
'root'.
2023-05-10T03:05:55.733405Z 0 [System] [MY-010910] [Server] /opt/mysql8/bin/mysqld: Shutdown complete (mysqld 8.0.28)
MySQL Community Server - GPL.
2023-05-10T03:14:11.457411Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location i
s accessible to all OS users. Consider choosing a different directory.
2023-05-10T03:14:11.457486Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be
removed in a future release. Please use authentication_policy instead.
2023-05-10T03:14:11.457574Z 0 [System] [MY-010116] [Server] /opt/mysql8/bin/mysqld (mysqld 8.0.28) starting as process
32397
2023-05-10T03:14:11.476936Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-05-10T03:14:11.476966Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-05-10T03:14:16.996656Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-05-10T03:14:18.296071Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2023-05-10T03:14:18.916914Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80028' started.
2023-05-10T03:14:23.363222Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80028' completed.
2023-05-10T03:14:23.626635Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-05-10T03:14:23.626674Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted co
nnections are now supported for this channel.
2023-05-10T03:14:23.642441Z 0 [System] [MY-010931] [Server] /opt/mysql8/bin/mysqld: ready for connections. Version: '8
.0.28' socket: '/opt/mysql8/mysql.sock' port: 23306 MySQL Community Server - GPL.
2023-05-10T03:14:23.642557Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 3
3060, socket: /tmp/mysqlx.sock
数据库已经正常启动
7.登陆查看当前的版本:
/opt/mysql8/bin/mysql -h localhost -uroot -pmysql -P23306 -S /opt/mysql8/mysql.sock
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)
8.升级处理
[root@localhost opt]# /opt/mysql8/bin/mysql_upgrade -s -pmysql -P23306
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.
MySQL 8.0.16开始 mysql_upgrade 升级程序已经废弃,数据库启动的时候服务器自动完成升级操作.
在 mysqld 额外添加了一个新的选项 --upgrade。可选值为 NONE，AUTO，MINIMAL，FORCE。
姿势是这样的:
/opt/mysql8/bin/mysqld --upgrade=AUTO
各选项都代表什么
NONE：不尝试进行升级
AUTO：默认选项，MySQL 进行数据字典升级和服务升级
MINIMAL：仅升级数据字典
FORCE：强制升级，类似旧的 mysql_upgrade –force
若安装的是8.0.16之前的版本需要执行如下命令进行升级
若安装的是8.0.16之前的版本需要执行如下命令进行升级
[root@localhost opt]# /opt/mysql8/bin/mysql_upgrade -s -pmysql -P23306 -S /opt/mysql8/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Found outdated sys schema version 1.5.2.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
说明:
升级完成后建议重启下数据库.
