MySQL 5.7 升级到 8.0

1.升级前检查

要注意操作系统 glibc 的版本, 下载可支持的版本的 mysql 相关的程序包

?

1 2	# 查看操作系统支持的 glibc 版本 strings /lib/x86_64-linux-gnu/libc.so.6 | grep GLIBC_

?

1 2 3 4

wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.36-linux-glibc2.12-x86-64bit.tar.gz tar xf mysql-shell-8.0.36-linux-glibc2.12-x86-64bit.tar.gz -C /tmp/ cd /tmp/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit/bin ./mysqlsh -uroot -p -S /tmp/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log

2.下载安装包

?

1 2 3

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz tar xf mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz -C /usr/local/ cd /usr/local && mv mysql-8.0.36-linux-glibc2.12-x86_64 mysql-8.0.36

3.逻辑备份

?

1 2	mysqldump -uroot -p --routines --set-gtid-purged=OFF --single-transaction --databases XXX1  XXX2 > /data/all-databases-`date +%F`.sql # --routines 备份存储过程和函数

4.关闭数据库

?

1 2 3 4 5 6

mysql -h127.0.0.1 -uroot -p'xxx' select version(); show variables like 'innodb_fast_shutdown'; set global innodb_fast_shutdown=0; shutdown; exit;

5.备份数据目录和配置文件

?

1 2	cp -r /data/local/mysql/data /data/local/mysql/data_`date +%F` cp /etc/my.cnf /etc/my.cnf_`date +%F`

6.修改配置文件

?

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

# /etc/my.cnf   [mysqld] user=mysql basedir=/usr/local/mysql-8.0.36 datadir=/data/local/mysql/data server_id=3 port=3306 socket=/tmp/mysql.sock log_bin=/data/local/mysql/binlog/bin-log binlog_format = ROW max_binlog_size = 100M log_slave_updates expire_logs_days=7   long_query_time=5 slow_query_log=ON slow_query_log_file=/data/local/mysql/slow_query.log   log-error=/data/local/mysql/mysql-error.log log_timestamps=SYSTEM   max_connections=1000 max_allowed_packet=500M skip-name-resolve=1   character_set_server=utf8 collation-server=utf8_general_ci   # For 8.0 sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION default_authentication_plugin=mysql_native_password default-storage-engine=INNODB   [mysql] socket=/tmp/mysql.sock default-character-set=utf8

7.执行升级

?

1 2 3 4 5 6 7 8

/usr/local/mysql-8.0.36/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE   # 会一直卡住 # error 日志看到以下输出, 说明升级完成 # Server upgrade from '80036' to '80036' completed.   # 新开窗口, 连上 mysql 执行命令查看 select version();

8.修改环境变量及启动脚本

?

1 2 3 4

export PATH=$PATH:/usr/local/mysql-8.0.36/bin   # /etc/init.d/mysqld basedir=/usr/local/mysql-8.0.36

9.重启 mysql

?

1 2	systemctl daemon-reload /etc/init.d/mysqld restart