1 多实例部署

1.1 同版本多实例部署

  1. 将安装包解压至安装目录，并创建软链接

cd /app/mysql/
tar -zxvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
ln -s /app/mysql/mysql-8.0.28-linux-glibc2.12-x86_64 /usr/local/mysql
echo “export PATH=$PATH:/usr/local/mysql/bin” >> /etc/profile
source /etc/profile

  1. 创建实例数据目录

mkdir -pv /data/mysql-330{6,7,8,9}/{data,etc,log,binlog,tmp}

  1. 创建配置文件 ```shell cat > /data/mysql-3306/etc/my.cnf <<EOF [mysqld] user = mysql port = 3306 server_id = 6 basedir = /usr/local/mysql datadir = /data/mysql-3306/data socket = /data/mysql-3306/tmp/mysql.sock pid_file = /data/mysql-3306/data/mysql.pid

    LOGGING

    错误日

    log_error_verbosity = 2 log_error = /data/mysql-3306/log/mysql.err

    普通日志

    general_log = 0 general_log_file = /data/mysql-3306/log/mysql.log

    二进制日志

    log_bin=/data/mysql-3306/binlog/mydb-binlog

    慢日志

    slow_query_log = 1 slow_query_log_file = /data/mysql-3306/log/mydb-slow.log long_query_time=0.5 log_queries_not_using_indexes=1 log_throttle_queries_not_using_indexes=1000

[mysql] socket = /data/mysql-3306/tmp/mysql.sock EOF

  1. ```shell
  2. cat > /data/mysql-3307/etc/my.cnf <<EOF
  3. [mysqld]
  4. user                             = mysql
  5. port                             = 3307
  6. server_id = 7
  7. basedir                          = /usr/local/mysql
  8. datadir                          = /data/mysql-3307/data
  9. socket                           = /data/mysql-3307/tmp/mysql.sock
  10. pid_file                         = /data/mysql-3307/data/mysql.pid
  11. ### LOGGING ###
  12. #错误日
  13. log_error_verbosity            = 2
  14. log_error                      = /data/mysql-3307/log/mysql.err
  15. #普通日志
  16. general_log                    = 0
  17. general_log_file               = /data/mysql-3307/log/mysql.log
  18. #二进制日志
  19. log_bin=/data/mysql-3307/binlog/mydb-binlog
  20. #慢日志
  21. slow_query_log = 1
  22. slow_query_log_file = /data/mysql-3307/log/mydb-slow.log
  23. long_query_time=0.5
  24. log_queries_not_using_indexes=1
  25. log_throttle_queries_not_using_indexes=1000
  27. [mysql]
  28. socket                         = /data/mysql-3307/tmp/mysql.sock
  29. EOF
cat > /data/mysql-3307/etc/my.cnf <<EOF
[mysqld]
user                             = mysql
port                             = 3308
server_id = 8
basedir                          = /usr/local/mysql
datadir                          = /data/mysql-3308/data
socket                           = /data/mysql-3308/tmp/mysql.sock
pid_file                         = /data/mysql-3308/data/mysql.pid
### LOGGING ###
#错误日
log_error_verbosity            = 2
log_error                      = /data/mysql-3308/log/mysql.err
#普通日志
general_log                    = 0
general_log_file               = /data/mysql-3308/log/mysql.log
#二进制日志
log_bin=/data/mysql-3308/binlog/mydb-binlog
#慢日志
slow_query_log = 1
slow_query_log_file = /data/mysql-3308/log/mydb-slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000

[mysql]
socket                         = /data/mysql-3308/tmp/mysql.sock
EOF

cat > /data/mysql-3309/etc/my.cnf <<EOF
[mysqld]
user                             = mysql
port                             = 3309
server_id = 9
basedir                          = /usr/local/mysql
datadir                          = /data/mysql-3309/data
socket                           = /data/mysql-3309/tmp/mysql.sock
pid_file                         = /data/mysql-3309/data/mysql.pid
### LOGGING ###
#错误日
log_error_verbosity            = 2
log_error                      = /data/mysql-3309/log/mysql.err
#普通日志
general_log                    = 0
general_log_file               = /data/mysql-3309/log/mysql.log
#二进制日志
log_bin=/data/mysql-3309/binlog/mydb-binlog
#慢日志
slow_query_log = 1
slow_query_log_file = /data/mysql-3309/log/mydb-slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000

[mysql]
socket                         = /data/mysql-3309/tmp/mysql.sock
EOF
  1. 创建用户，修改文件宿主宿组

useradd mysql -s /sbin/nologin
chown -R mysql.mysql /data/*

  1. 初始化数据库

    mysqld --defaults-file=/data/mysql-3306/etc/my.cnf --initialize-insecure
mysqld --defaults-file=/data/mysql-3307/etc/my.cnf --initialize-insecure
mysqld --defaults-file=/data/mysql-3308/etc/my.cnf --initialize-insecure
mysqld --defaults-file=/data/mysql-3309/etc/my.cnf --initialize-insecure

  2. 创建systemctl文件 ```shell cat > /usr/lib/systemd/system/mysql-3306.service << EOF [Unit] Description=MySQL Server After=network.target

[Install] WantedBy=multi-user.target [Service] Type=forking TimeoutSec=0 PermissionsStartOnly=true ExecStart=/usr/local/mysql/bin/mysqld —defaults-file=/data/mysql-3306/etc/my.cnf —daemonize LimitNOFILE = 65535 Restart=on-failure RestartSec=10 RestartPreventExitStatus=1 PrivateTmp=false EOF

```shell
cat > /usr/lib/systemd/system/mysql-3307.service << EOF 
[Unit]
Description=MySQL Server
After=network.target

[Install]
WantedBy=multi-user.target
[Service]
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql-3307/etc/my.cnf --daemonize
LimitNOFILE = 65535
Restart=on-failure
RestartSec=10
RestartPreventExitStatus=1
PrivateTmp=false
EOF

cat > /usr/lib/systemd/system/mysql-3308.service << EOF 
[Unit]
Description=MySQL Server
After=network.target

[Install]
WantedBy=multi-user.target
[Service]
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql-3308/etc/my.cnf --daemonize
LimitNOFILE = 65535
Restart=on-failure
RestartSec=10
RestartPreventExitStatus=1
PrivateTmp=false
EOF

cat > /usr/lib/systemd/system/mysql-3309.service << EOF 
[Unit]
Description=MySQL Server
After=network.target

[Install]
WantedBy=multi-user.target
[Service]
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql-3309/etc/my.cnf --daemonize
LimitNOFILE = 65535
Restart=on-failure
RestartSec=10
RestartPreventExitStatus=1
PrivateTmp=false
EOF

  1. 启动数据库实例

    1.2 多版本多实例部署

  2. 将5.6.50和5.7.37的安装包上传至数据包目录、解压、创建软链接

cd /app/mysql/
tar -zxvf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
ln -s /app/mysql/mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql-56
ln -s /app/mysql/mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql-57

  1. 创建数据目录并修改宿主宿组

mkdir /data/mysql-335{6,7}/{data,log,tmp,etc,binlog}

  1. 实例配置文件 ```shell cat > /data/mysql-3356/etc/my.cnf <<EOF [mysqld] user = mysql port = 3356 server_id = 56 basedir = /usr/local/mysql-56 datadir = /data/mysql-3356/data socket = /data/mysql-3356/tmp/mysql.sock

LOGGING

错误日志

log_error = /data/mysql-3356/log/mysql.err

普通日志

general_log = 0 general_log_file = /data/mysql-3356/log/mysql.log

二进制日志

log_bin=/data/mysql-3356/binlog/mydb-binlog

慢日志

slow_query_log = 1 slow_query_log_file = /data/mysql-3356/log/mydb-slow.log long_query_time=0.5 log_queries_not_using_indexes=1 log_throttle_queries_not_using_indexes=1000

[mysql] socket = /data/mysql-3356/tmp/mysql.sock EOF

```shell
cat > /data/mysql-3357/etc/my.cnf <<EOF
[mysqld]
user = mysql
port = 3357
server_id = 57
basedir = /usr/local/mysql-57
datadir = /data/mysql-3357/data
socket  = /data/mysql-3357/tmp/mysql.sock

### LOGGING ###
#错误日志
log_error                      = /data/mysql-3357/log/mysql.err
#普通日志
general_log                    = 0
general_log_file               = /data/mysql-3357/log/mysql.log
#二进制日志
log_bin=/data/mysql-3357/binlog/mydb-binlog
#慢日志
slow_query_log = 1
slow_query_log_file = /data/mysql-3357/log/mydb-slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000

[mysql]
socket  = /data/mysql-3357/tmp/mysql.sock
EOF
  1. 修改文件宿主宿组

chown -R mysql.mysql /data/*

  1. 初始化实例数据

    #5.6.50初始化
/usr/local/mysql-56/scripts/mysql_install_db --user=mysql --datadir=/data/mysql-3356/data --basedir=/usr/local/mysql-56
#5.7.37初始化
/usr/local/mysql-57/bin/mysqld --user=mysql --datadir=/data/mysql-3357/data --basedir=/usr/local/mysql-57

  2. 启动数据库

    /usr/local/mysql-56/mysqld_safe --defaults-file=/data/mysql-3356/etc/my.cnf &
/usr/local/mysql-57/mysqld_safe --defaults-file=/data/mysql-3357/etc/my.cnf &

    2 版本升级

  • 注意事项
  1. 支持GA版本之间的升级
  2. 5.6—>5.7， 先将5.6升级至最新版，再升级到5.7
  3. 5.5—>5.7，先将5.5升级至最新版本，再将5.5升级至5.6最新，再将5.6升级到5.7最新版本
  4. 回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本的升级)
  5. 降低停机时间，在业务不繁忙期间升级，做好足够的审计演练
  • 升级思路
  1. 安装新版本软件
  2. 关闭原数据库业务（挂维护页）设置innodb_fast_shutdown0，备份原数据库数据（冷备）
  3. 使用新版本软件”挂“旧版本数据启动（—skip-grant-tables, —skip-networking）
  4. 升级：只是升级系统表，升级时间和数据量无关的
  5. 正常重启数据库
  6. 验证各项功能是否正常
  7. 业务恢复

建议：inplace升级最好是主从环境，先从库升级再主库升级

2.1 5.6升级至5.7

  1. 关闭数据库（优雅关闭，干净的关闭）

mysql -S /data/mysql-3356/tmp/mysql.sock -e “set global innodb_fast_shutdown=0;”
/usr/local/mysql-56/bin/mysqladmin -S /data/mysql-3356/tmp/mysql.sock

  1. 数据库备份
  2. 修改配置文件，将basedir由原来的5.6修改为5.7版本
  3. 使用高版本软件挂版本数据启动

/usr/local/mysql-57/bin/mysqld_safe —defaults-file=/data/mysql-3356/etc/my.cnf —skip-grant-tables —skip-networking

  1. 升级系统表

/usr/local/mysql-57/bin/mysql_upgrade -S /data/mysql-3356/tmp/mysql.sock —force

  1. 重启数据库，升级完成

    2.2 5.7升级至8.0

    注意：在MySQL 8.0之后，MySQL提供了mysql-shell工具，可以调用这个命令，可以在升级之前进行预检查

  2. 下载mysql-shell，版本同8.0数据库版本一致即可，并安装，可直接从官网下载

  3. 创建连接用户

/usr/local/mysql-57/bin/mysql -S /data/mysql-3357/tmp/mysql.sock
grant all on . to root@’’10.0.0.%’ indentified by ‘abc123..’;

  1. 预检查

mysqlsh root:abc123..@10.0.0.80:3357 -e “util.checkForServerUpgrade()” > /tmp/up.log

  1. 停源库

/usr/local/mysql-57/bin/mysql -S /data/mysql-3357/tmp/mysql.sock -e “set global innodb_fast_shutdown=0;’
/usr/local/mysql-57/bin/mysqladmin -S /data/mysql-3357/tmp/mysql.sock shutdown

  1. 使用高版本软件挂低版本数据启动

/usr/local/mysql/bin/mysqld_safe —defaults-file=/data/mysql-3357/etc/my.cnf —skip-grant-tables —skip-networking &

  1. 重启数据库即可

/usr/local/mysql/bin/mysqld_safe —defaults-file=/data/mysql-3357/etc/my.cnf &