MySQL数据库升级总结

备份和恢复

（1）为什么要备份

 灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据

（2）丢失场景

 备份注意要点
 能容忍最多丢失多少数据
 恢复数据需要在多长时间内完成
 需要恢复哪些数据

（3）还原要点

 做还原测试，用于测试备份的可用性
 还原演练

备份类型：

完全备份，部分备份

 完全备份：整个数据集
 部分备份：只备份数据子集，如部分库或表

完全备份、增量备份、差异备份

 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
 差异备份：仅备份最近一次完全备份以来变化的数据，
 备份较慢，还原简单

注意：二进制日志文件不应该与数据文件放在同一磁盘

冷、温、热备份

 冷备：读写操作均不可进行
 温备：读操作可执行；但写操作不可执行
 热备：读写操作均可执行

MyISAM：温备，不支持热备 InnoDB：都支持 物理和逻辑备份

 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
 逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

冷备份完全备份操作过程：特点，备份速度快，但是需要关闭服务器才能操作。

同版本 8.0安装

 [root@db01 opt]# rz mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 [root@db01 opt]# tar xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 [root@db01 opt]# du -sh *
 385M    mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
 630M    mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
 2.5G    mysql-8.0.20-linux-glibc2.12-x86_64
 469M    mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 [root@db01 opt]# ln -s /opt/mysql-8.0.20-linux-glibc2.12-x86_64 /usr/local/mysql
 [root@db01 opt]# ll /usr/local/mysql
 lrwxrwxrwx 1 root root 40 11月  2 11:21 /usr/local/mysql -> /opt/mysql-8.0.20-linux-glibc2.12-x86_64

基础环境准备
 # 移除冲突软件

 [root@db01 opt]# yum remove mariadb-libs -y

# 创建虚拟用户

 [root@db01 opt]# useradd -M -r mysql
 [root@db01 opt]# id mysql
 uid=998(mysql) gid=996(mysql) 组=996(mysql)

 # 创建目录并授权

 [root@db01 opt]# mkdir -p /data/3306/data
 [root@db01 opt]# chown -R mysql. /data/
 [root@db01 opt]# ls -ld /data/
 drwxr-xr-x 3 mysql mysql 18 11月  2 11:26 /data/

# 配置环境变量

 [root@db01 opt]# echo export PATH=\$PATH:/usr/local/mysql/bin/ >> /etc/profile && . /etc/profile

初始化数据（创建系统数据）

 [root@db01 opt]#yum install -y libaio-devel
 [root@db01 opt]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
 2020-11-02T03:32:56.683285Z 0 [System] [MY-013169] [Server] /opt/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.20)
 initializing of server in progress as process 2049
 2020-11-02T03:32:56.709263Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
 2020-11-02T03:32:58.296518Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
 2020-11-02T03:32:59.507707Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! 
Please consider switching off the --initialize-insecure option.
 参数说明:
 --initialize-insecure       # 初始化核心参数(不安全的)
 --user=mysql                # 指定初始化用户(默认用户root)
 --basedir=/usr/local/mysql  # 数据库软件位置
 --datadir=/data/3306/data   # 数据存储位置

配置文件

 cat > /etc/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql
 datadir=/data/3306/data
 port=3306
 socket=/tmp/mysql.sock
 [client]
 socket=/tmp/mysql.sock
 EOF
 加入systemctl服务管理，启动并开机自启
 [root@db01 ~]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
 [root@db01 ~]# systemctl enable mysqld
 mysqld.service is not a native service, redirecting to /sbin/chkconfig.
 Executing /sbin/chkconfig mysqld on
 [root@db01 ~]# systemctl start mysqld
 [root@db01 ~]# systemctl status mysqld.service

a. 多套目录

 mkdir -p /data/330{7..9}/data 
 chown -R mysql. /data

b. 配置文件

 cat >/data/3307/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql 
 datadir=/data/3307/data
 port=3307 
 socket=/tmp/mysql3307.sock 
 EOF
 cat >/data/3308/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql 
 datadir=/data/3308/data
 port=3308 
 socket=/tmp/mysql3308.sock 
 EOF
 cat >/data/3309/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql 
 datadir=/data/3309/data
 port=3309 
 socket=/tmp/mysql3309.sock 
 EOF

c. 初始化数据

 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data

d. 启动数据库

 mysqld_safe --defaults-file=/data/3307/my.cnf &
 mysqld_safe --defaults-file=/data/3308/my.cnf &
 mysqld_safe --defaults-file=/data/3309/my.cnf &

多实例

单版本单实例

vim /etc/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
 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
 LimitNOFILE = 5000
 cat >/etc/systemd/system/mysqld3307.service <<EOF
 [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
 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
 LimitNOFILE = 5000
 EOF
 cat >/etc/systemd/system/mysqld3308.service <<EOF
 [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
 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
 LimitNOFILE = 5000
 EOF
 cat >/etc/systemd/system/mysqld3309.service <<EOF
 [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
 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
 LimitNOFILE = 5000
 EOF
  systemctl start mysqld3307
  systemctl start mysqld3308
  systemctl start mysqld3309
  systemctl enable mysqld3309
   systemctl enable mysqld3308
  systemctl enable mysqld3307
  [root@db01 opt]# netstat -tnupl|grep 330
 tcp6       0      0 :::3306                 :::*                    LISTEN      7393/mysqld         
 tcp6       0      0 :::3307                 :::*                    LISTEN      8087/mysqld         
 tcp6       0      0 :::3308                 :::*                    LISTEN      8135/mysqld         
 tcp6       0      0 :::3309                 :::*                    LISTEN      8174/mysqld

多版本多实例

a. 5.6 和5.7解压和进行软连接

[root@db01 opt]# ls
 mysql-5.6.46-linux-glibc2.12-x86_64
 mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
 mysql-5.7.30-linux-glibc2.12-x86_64
 mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
 mysql-8.0.20-linux-glibc2.12-x86_64
 mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
 #软连接
 ln -s  /opt/mysql-5.6.46-linux-glibc2.12-x86_64  /usr/local/mysql56
 ln -s  /opt/mysql-5.7.30-linux-glibc2.12-x86_64  /usr/local/mysql57

b.创建目录并授权

 mkdir -p /data/331{6..7}/data 
 chown -R mysql. /data

c.配置文件

cat >/data/3316/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql56 
 datadir=/data/3316/data
 port=3316 
 socket=/tmp/mysql3316.sock 
 EOF
 ​
 cat >/data/3317/my.cnf <<EOF
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql57 
 datadir=/data/3317/data
 port=3317 
 socket=/tmp/mysql3317.sock 
 EOF

d.初始化数据

 /usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/3317/data
 /usr/local/mysql56/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56  --datadir=/data/3316/data

e.启动

 [root@db01 opt]# /usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &
 [root@db01 opt]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3317/my.cnf &

f.连接

 [root@db01 opt]# mysql -S /tmp/mysql3307.sock
 [root@db01 opt]# mysql -S /tmp/mysql3316.sock
 [root@db01 opt]# mysql -S /tmp/mysql3317.sock

升级

升级方式

a.inplace(就地升级)

适合于有主从环境。

b.merging（逻辑备份迁移升级）

升级注意事项（INPLACE)

网址：https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html

 来自于MySQL官网
 Upgrade is only supported between General Availability (GA) releases.
 Upgrade from MySQL 5.6 to 5.7 is supported. Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.6 release before upgrading to MySQL 5.7.
 Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.5 to 5.7 is not supported.
 Upgrade within a release series is supported. For example, upgrading from MySQL 5.7.x to 5.7.y is supported. Skipping a release is also supported. For example, upgrading from MySQL 5.7.x to 5.7.z is supported.
 翻译:
 a. 支持GA版本之间升级
 b. 5.6--> 5.7  ,先将5.6升级至最新版，再升级到5.7
 c. 5.5 ---> 5.7 ,先将5.5 升级至最新，再5.5---> 5.6最新，再5.6--->5.7 最新
 d. 回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
 e. 降低停机时间（停业务的时间)

INPLACE升级过程原理

 0. 备份原数据库数据
 a. 安装新版本软件
 b. 关闭原数据库（挂维护页）
 c. 使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,--skip-networking)
 d. 升级 ： 只是升级系统表。升级时间和数据量无关的。
 e. 正常重启数据库。
 f. 验证各项功能是否正常。
 g. 业务恢复。

5.6.48 ----> 5.7.30 Inplace 升级演练

a. 安装 新版本软件 5.7.30 ok。

b.停源库,做冷备

# 1. 快速关库功能关闭(优雅关闭) 连接到数据库中（5.6.48），执行以下语句。

mysql -S /tmp/mysql3316.sock -e "set global innodb_fast_shutdown=0 ;"

查询：

 [root@db01 ~]# mysql -S /tmp/mysql3316.sock -e "select @@innodb_fast_shutdown ;"
 +------------------------+
 | @@innodb_fast_shutdown |
 +------------------------+
 |                      0 |
 +------------------------+
 [root@db01 data]# /usr/local/mysql56/bin/mysqladmin -S /tmp/mysql3316.sock shutdown
 201103 15:11:10 mysqld_safe mysqld from pid file /data/3316/data/db01.pid ended
 [1]-  Done                    /usr/local/mysql56/bin/mysqld_safe --defaults-file=/data/3316/my.cnf  (wd: /opt)
 (wd now: /data/3316/data)
冷备:
 [root@db01 ~]# cp -r /data/3316/data/ /tmp/bak
c.使用高版本软件（5.7.30）挂低版本（5.6.48）数据启动
 [root@db01 data]# cat > /data/3316/my.cnf <<EOF 
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql57
 datadir=/data/3316/data
 socket=/tmp/mysql3316.sock
 port=3316
 innodb_fast_shutdown=0
 EOF
 [root@db01 data]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3316/my.cnf --skip-grant-tables --skip-networking &
d. 升级 （升级到8.0可以省略）
 [root@db01 data]# /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql3316.sock --force
e. 重启数据库到正常状态
 [root@db01 data]# /usr/local/mysql57/bin/mysqladmin -S /tmp/mysql3316.sock shutdown
 [root@db01 data]# /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &
f.连接查看
 [root@db01 data]# mysql -S /tmp/mysql3316.sock
Mysql5.7.30Inplace升级到MySQL8.0.20
图形网址:https://www.processon.com/view/link/5fa0c8ffe401fd4885495a93
a. 安装mysqlsh
 [root@db01 opt]# tar xf mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz 
 [root@db01 opt]# ln -s /opt/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit /usr/local/mysqlsh
 [root@db01 opt]# cd /usr/local/mysqlsh/
 [root@db01 bin]# vim /etc/profile
 export PATH=/usr/local/mysqlsh/bin:$PATH
 [root@db01 bin]# source /etc/profile
 [root@db01 bin]# mysqlsh --version
 mysqlsh  Ver 8.0.20 for Linux on x86_64 - for MySQL 8.0.20 (MySQL Community Server (GPL))
b. 在5730数据库中创建链接用户
 [root@db01 bin]# mysql -S /tmp/mysql3317.sock
 mysql> create user root@'10.0.0.%' identified with mysql_native_password by '123';
 mysql> grant all on *.* to root@'10.0.0.%';
c. 升级前检测
 [root@db01 ~]# mysqlsh root:123@10.0.0.51:3317 -e "util.checkForServerUpgrade()" >>/tmp/up.log
7.5.2 正式升级 a. 安装 新版本软件 8.0.20 ok。
b. 停源库
# 1. 快速关库功能关闭(优雅关闭)
连接到数据库中（5.7.30），执行以下语句。
 mysql> set global innodb_fast_shutdown=0 ;
 mysql> select @@innodb_fast_shutdown;
 mysql> shutdown ;
c. 使用高版本软件（8.0.20）挂低版本（5.7.30）数据启动
 [root@db01 data]# cat > /data/3317/my.cnf <<EOF 
 [mysqld]
 user=mysql
 basedir=/usr/local/mysql
 datadir=/data/3317/data
 socket=/tmp/mysql3317.sock
 port=3317
 EOF
 [root@db01 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf --skip-grant-tables --skip-networking &
d. 重启数据库到正常状态
 [root@db01 data]# mysqladmin -S /tmp/mysql3317.sock shutdown
 [root@db01 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf &

f.连接查看
 [root@db01 data]# mysql -S /tmp/mysql3317.sock