mysql5.7 in-place升级到8

1. 源码安装mysql5.7.29

清理系统自带maria包

rpm -qa|grep maria|awk '{print "rpm -e ",$1,"--nodeps"}'|sh

创建mysql用户

groupadd -g 512 mysql

useradd -u 512 -g 512 -d /mysql mysql

将源码包sftp到/soft下，unzip到/soft目录生成mysql-advanced-5.7.29-el7-x86_64.tar.gz，tar解压到/usr/local下

cd /usr/local

tar -xvf /soft/mysql-advanced-5.7.29-el7-x86_64.tar.gz

mv mysql-advanced-5.7.29-el7-x86_64 mysql5.7

chown -R mysql.mysql mysql5.7

cd /mysql

mkdir bin_log

mkdir log

mkdir relay_log

mkdir data

mkdir tmp

mkdir undo_tbls

 

cat /mysql/my.cnf

[client]

socket=/var/lib/mysql/mysql.sock

[mysqld]

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"

server_id=1

gtid_mode=ON

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

log_slave_updates=ON

log_bin=binlog

binlog_format=ROW

auto_increment_increment=3

auto_increment_offset=1

port=3306

datadir=/mysql/data

socket=/var/lib/mysql/mysql.sock

log-error=/mysql/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

log-bin=/mysql/bin_log/mysql-binlog

slow_query_log_file=/mysql/log/slow.log

general_log_file=/mysql/log/general.log

log_timestamps=SYSTEM

relay_log=/mysql/relay_log/mysql-relaylog

transaction-isolation = READ-COMMITTED

character-set-server=utf8mb4

collation-server=utf8mb4_bin

lower_case_table_names

expire_logs_days=7

 

chown -R mysql.mysql /mysql

cd /var/lib

mkdir mysql

chown -R mysql.mysql mysql

cd /var/run

mkdir mysqld

chown -R mysql.mysql mysqld

 

编辑/usr/lib/systemd/system/mysqld.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

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=forking

PIDFile=/var/run/mysqld/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.

TimeoutSec=0

# Execute pre and post scripts as root

PermissionsStartOnly=true

# Start main service

ExecStart=/usr/local/mysql5.7/bin/mysqld --defaults-file=/mysql/my.cnf --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

# Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit

LimitNOFILE = 15000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

 

修改/etc/profile，添加

export PATH=/usr/local/mysql5.7/bin:$PATH

 

source /etc/profile

 

mysqld --defaults-file=/mysql/my.cnf --initialize --user=mysql

systemctl start mysqld

alter user root@'localhost' identified by '123';

然后造些数据

2. 源码安装mysql8.0.19

将源码包sftp到/soft下，unzip到/soft目录生成mysql-advanced-5.7.29-el7-x86_64.tar.gz，tar解压到/usr/local下

cd /usr/local

tar -xvf /soft/mysql-commercial-8.0.19-el7-x86_64.tar.gz

mv mysql-commercial-8.0.19-el7-x86_64 mysql8

chown -R mysql.mysql mysql8

 

cat /usr/lib/systemd/system/mysqld.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

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=forking

PIDFile=/var/run/mysqld/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.

TimeoutSec=0

# Execute pre and post scripts as root

PermissionsStartOnly=true

# Start main service

ExecStart=/usr/local/mysql8/bin/mysqld --defaults-file=/mysql/my.cnf --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS

# Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit

LimitNOFILE = 15000

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

 

systemctl daemon-reload

 

修改/etc/profile，将5.7相关注释，添加

export PATH=/usr/local/mysql8/bin:$PATH

 

source /etc/profile

mysqld --version确认版本

3. 升级

service mysqld start

可以看到/mysql/data/mysql下的表结构在变化，由myisam变成innodb；/mysql/log/mysqld.log显示如下：

mysql8.0.16版本及以上都不用mysql_upgrade了，在上一步就已经把MySQL 5.7和MySQL 8.0的架构更改完成了

 

可以重启下mysqld，service mysqld restart

 

当然我们也可以进行逻辑mysqldump备份再导入8进行升级，但是逻辑备份恢复比较慢，而物理备份mysqlbackup跨大版本恢复行不通

 

参考文档：

https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html#upgrade-procedure-inplace