Linux MySQL8.0.24 二进制安装 单机-多实例-多实例升级 [测试环境]

Linux MySQL8.0.24 二进制安装 单机-多实例-多实例升级 测试环境

1.安装MySQL 单机

软件获取地址：https://downloads.mysql.com/archives/community/

tar xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz 
ln -s /opt/mysql-8.0.24-linux-glibc2.12-x86_64  /usr/local/mysql 

groupadd mysql
useradd -r -g mysql -s /bin/false mysql
id mysql

yum remove mariadb-libs -y 

mkdir -p /data/3306/data
chown -R mysql. /data
ls -ld /data

# vim /etc/profile 

export PATH=/usr/local/mysql/bin:$PATH
source /etc/profile

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

mysqld --initialize-insecure

cd /usr/local/mysql/support-files/
cp mysql.server /etc/init.d/mysqld
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
mysql -e "show databases;"

2.安装MySQL 同版本多实例

2.1 多套目录

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

2.2 配置文件

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

2.3 初始化数据

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

2.4 启动数据库

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


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

3.安装MySQL 多版本多实例

3.1 MySQL5.6和5.7 解压和软连接

[root@db01 opt]# ln -s /opt/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql57
[root@db01 opt]# ln -s /opt/mysql-5.6.46-linux-glibc2.12-x86_64  /usr/local/mysql56

3.2 目录及授权

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

3.3 配置文件

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

3.4 初始化数据

/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

3.5 启动

[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 &

3.6 连接

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

4.MySQL升级

说明：以下为多实例环境升级，单机升级需要主从同步升级。

4.1 升级方式

a. inplace (就地升级) 适合于有主从环境.
b. merging (逻辑备份迁移升级)

4.2 升级注意事项（INPLACE）

来自于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. 降低停机时间（停业务的时间）

4.3 INPLACE 升级过程原理

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

4.4 MySQL升级案例- 5.6.48 --> 5.7.30 Inplace 升级演练

4.1 安装 新版本软件 5.7.30 此处略

4.2 停原库 ,做冷备

a. 快速关库功能关闭(优雅关闭) 连接到数据库中（5.6.48），执行以下语句:
[root@db01 ~]# mysql -S /tmp/mysql3316.sock -e "set global innodb_fast_shutdown=0 ;"
[root@db01 ~]# mysql -S /tmp/mysql3316.sock -e "select @@innodb_fast_shutdown ;"
[root@db01 ~]# /usr/local/mysql56/bin/mysqladmin -S /tmp/mysql3316.sock  shutdown 

b. 冷备:
[root@db01 ~]# cp -r /data/3316/data/ /tmp/bak

c. 使用高版本软件（5.7.30）挂低版本（5.6.48）数据启动
[root@db01 data]# vim /data/3316/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/data/3316/data
socket=/tmp/mysql3316.sock
port=3316
innodb_fast_shutdown=0

[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 &

5 MySQL升级案例- 5.7.30 --> 8.0.20 Inplace 升级演练

5.1 升级之前的预检查
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

5.2 正式升级
a. 安装 新版本软件 8.0.20

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]# vim /data/3317/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3317/data
socket=/tmp/mysql3317.sock
port=3317

[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 &