mysql多实例部署&Xtrabackup备份与恢复

一、mysql多实例部署

在https://downloads.mysql.com/archives/community/下载软件包

//下载二进制格式的mysql软件包,从官网下载mysql压缩包，再把下载好的压缩包拉进/usr/src下面
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug  kernels  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

配置用户和组并解压二进制程序至/usr/local下

// 解压软件至/usr/local/
[root@localhost src]# ls
debug  kernels  mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost local]# ls
bin    include  libexec                              share
etc    lib      mysql-5.7.34-linux-glibc2.12-x86_64  src
games  lib64    sbin

//做一个软连接
[root@localhost local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root   6 8月  12 2018 bin
drwxr-xr-x. 2 root root   6 8月  12 2018 etc
drwxr-xr-x. 2 root root   6 8月  12 2018 games
drwxr-xr-x. 2 root root   6 8月  12 2018 include
drwxr-xr-x. 2 root root   6 8月  12 2018 lib
drwxr-xr-x. 2 root root   6 8月  12 2018 lib64
drwxr-xr-x. 2 root root   6 8月  12 2018 libexec
lrwxrwxrwx. 1 root root  47 8月  28 16:25 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月  28 16:22 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 8月  12 2018 sbin
drwxr-xr-x. 5 root root  49 3月   1 22:59 share
drwxr-xr-x. 2 root root   6 8月  12 2018 src

// 创建用户和组
[root@localhost src]# useradd -r -M -s /sbin/nologin mysql
[root@localhost src]# grep mysql /etc/group
mysql:x:973:

//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root  root    6 8月  12 2018 bin
drwxr-xr-x. 2 root  root    6 8月  12 2018 etc
drwxr-xr-x. 2 root  root    6 8月  12 2018 games
drwxr-xr-x. 2 root  root    6 8月  12 2018 include
drwxr-xr-x. 2 root  root    6 8月  12 2018 lib
drwxr-xr-x. 2 root  root    6 8月  12 2018 lib64
drwxr-xr-x. 2 root  root    6 8月  12 2018 libexec
lrwxrwxrwx. 1 mysql mysql  47 8月  28 16:25 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 8月  28 16:22 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 8月  12 2018 sbin
drwxr-xr-x. 5 root  root   49 3月   1 22:59 share
drwxr-xr-x. 2 root  root    6 8月  12 2018 src

//添加环境变量
[root@localhost local]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost local]# source /etc/profile.d/mysql.sh 
[root@localhost local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

创建各实例数据存放的目录

[root@localhost ]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost ]# tree /opt/
/opt/
└── data
    ├── 3306
    ├── 3307
    └── 3308

4 directories, 0 files
[root@localhost ]# chown -R mysql.mysql /opt/data/
[root@localhost ]# ll /opt/
总用量 0
drwxr-xr-x. 5 mysql mysql 42 8月  28 16:33 data
[root@localhost local]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月  28 16:33 3306
drwxr-xr-x. 2 mysql mysql 6 8月  28 16:33 3307
drwxr-xr-x. 2 mysql mysql 6 8月  28 16:33 3308

初始化各实例

//初始化3306实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3306
2021-08-28T09:12:22.702410Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation