mysql多实例部署&Xtrabackup备份与恢复
一、mysql多实例部署
软件下载
在网页 https://downloads.mysql.com/archives/community/ 下载mysql软件包
//下载二进制格式的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 for more details).
2021-08-28T09:12:22.858670Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:12:22.885687Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:12:22.941696Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0dee0fa8-07e0-11ec-900d-000c294867eb.
2021-08-28T09:12:22.942353Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:12:23.686412Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:12:23.998834Z 1 [Note] A temporary password is generated for root@localhost: Jf:*4eh;*jq5
[root@localhost ~]# echo 'Jf:*4eh;*jq5' > 6pass
//初始化3307实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2021-08-28T09:12:57.571122Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:12:57.728518Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:12:57.755585Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:12:57.813257Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 22b7094a-07e0-11ec-8ad7-000c294867eb.
2021-08-28T09:12:57.813834Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:12:58.552405Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:12:58.696956Z 1 [Note] A temporary password is generated for root@localhost: soh48HPZmC&f
[root@localhost ~]# echo 'soh48HPZmC&f' > 7pass
//初始化3308实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2021-08-28T09:13:16.718677Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T09:13:16.867023Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T09:13:16.891560Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T09:13:16.944966Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2e1e4e83-07e0-11ec-ac1a-000c294867eb.
2021-08-28T09:13:16.945450Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T09:13:17.582553Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T09:13:17.710582Z 1 [Note] A temporary password is generated for root@localhost: w)Lo>+qki3OV
[root@localhost ~]# echo 'w)Lo>+qki3OV' > 8pass
[root@localhost ~]# ls
6pass 公共 图片 音乐 initial-setup-ks.cfg
7pass 模板 文档 桌面
8pass 视频 下载 anaconda-ks.cfg
安装perl
[root@localhost ~]# yum -y install perl
安装mysql命令
[root@localhost ~]# ldd /usr/local/mysql/bin/mysql
linux-vdso.so.1 (0x00007ffccebe3000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fea0bb0a000)
librt.so.1 => /lib64/librt.so.1 (0x00007fea0b901000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fea0b6fd000)
libncurses.so.5 => not found
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fea0b368000)
libm.so.6 => /lib64/libm.so.6 (0x00007fea0afe6000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fea0adce000)
libc.so.6 => /lib64/libc.so.6 (0x00007fea0aa0c000)
/lib64/ld-linux-x86-64.so.2 (0x00007fea0bd2a000)
libtinfo.so.5 => not found
[root@localhost ~]# yum whatprovides libncurses.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:05:13 前，执行于 2021年08月28日 星期六 16时41分38秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility
: libraries
仓库 ：BaseOS
匹配来源：
提供 : libncurses.so.5
[root@localhost ~]# yum whatprovides libtinfo.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查：0:05:30 前，执行于 2021年08月28日 星期六 16时41分38秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility
: libraries
仓库 ：BaseOS
匹配来源：
提供 : libtinfo.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs
配置配置文件/etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error = /var/log/3306.log
[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log
[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log
启动各实例
[root@localhost ~]# mysqld_multi start
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
//mysqld_multi命令只能用来开启，要想停止就需要kill命令去杀进程号
[root@localhost ~]# ps -ef|grep mysqld
root 83701 1 0 17:24 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3306 --port=3306 --socket=/tmp/3306.sock --pid-file=/opt/data/3306/mysql.pid --log-error=/var/log/mysql_3306.log
root 83704 1 0 17:24 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3307 --port=3307 --socket=/tmp/3307.sock --pid-file=/opt/data/3307/mysql.pid --log-error=/var/log/mysql_3307.log
root 83722 1 0 17:24 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data/3308 --port=3308 --socket=/tmp/3308.sock --pid-file=/opt/data/3308/mysql.pid --log-error=/var/log/mysql_3308.log
mysql 84118 83701 0 17:24 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data/3306 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql_3306.log --pid-file=/opt/data/3306/mysql.pid --socket=/tmp/3306.sock --port=3306
mysql 84122 83704 0 17:24 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data/3307 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql_3307.log --pid-file=/opt/data/3307/mysql.pid --socket=/tmp/3307.sock --port=3307
mysql 84124 83722 0 17:24 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data/3308 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql_3308.log --pid-file=/opt/data/3308/mysql.pid --socket=/tmp/3308.sock --port=3308
root 95420 10667 0 17:30 pts/1 00:00:00 grep --color=auto mysqld
[root@localhost ~]# kill -9 83701 83704 83722 84118 84122 84124
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
初始化密码
[root@localhost ~]# ls
6pass 公共 图片 音乐 initial-setup-ks.cfg
7pass 模板 文档 桌面
8pass 视频 下载 anaconda-ks.cfg
[root@localhost ~]# cat 6pass
Jf:*4eh;*jq5
[root@localhost ~]# mysql -uroot -p'Jf:*4eh;*jq5' -h127.0.0.1 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('wjj');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 7pass
soh48HPZmC&f
[root@localhost ~]# mysql -uroot -p'soh48HPZmC&f' -h127.0.0.1 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('wjj');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# cat 8pass
w)Lo>+qki3OV
[root@localhost ~]# mysql -uroot -p'w)Lo>+qki3OV' -h127.0.0.1 -P3308
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password = password('wjj');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
多实例设置开机自启
1、把/usr/local/mysql/bin/support_files/mysqld_multi.service文件复制到/etc/init.d/mysqld_multi
2、在/etc/init.d/mysqld_multi文件中增加环境变量设置exoprt PATH=/usr/local/mysql/bin:$PATH
3、chkconfig --add mysqld_multi
二、Xtrabackup备份与恢复
1、Xtrabackup介绍
MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。
Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。
2、Xtrabackup优点
(1)备份速度快，物理备份可靠
(2)备份过程不会打断正在执行的事务(无需锁表)
(3)能够基于压缩等功能节约磁盘空间和流量
(4)自动备份校验
(5)还原速度快
(6)可以流传将备份传输到另外一台机器上
(7)在不增加服务器负载的情况备份数据
3、Xtrabackup备份原理
（1）innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；
（2）xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）
（3）xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）
（4）innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。
（5）当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；
（6）xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；
（7）innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；
（8）最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出
4、Xtrabackup的安装部署以及备份恢复
4.1 Xtrabackup的安装
下载地址： https://centos.pkgs.org/8/percona-x86_64/
可以选择rpm包方式安装，也可以下载源码包编译安装，这里直接采用rpm包的方式进行安装
[root@localhost ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
--2021-08-28 18:16:35-- https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
正在解析主机 repo.percona.com (repo.percona.com)... 167.71.118.3, 157.245.119.64, 167.99.233.229
正在连接 repo.percona.com (repo.percona.com)|167.71.118.3|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：8060704 (7.7M) [application/x-redhat-package-manager]
正在保存至: “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm”
percona-xtraback 100%[=========>] 7.69M 3.83MB/s 用时 2.0s
2021-08-28 18:16:39 (3.83 MB/s) - 已保存 “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm” [8060704/8060704])
[root@localhost ~]# ls
公共 文档 anaconda-ks.cfg
模板 下载 initial-setup-ks.cfg
视频 音乐 percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
图片 桌面
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
Xtrabackup中主要包含两个工具:
xtrabackup:是用于热备innodb, xtradb表中数据的工具， 不能备份其他类型的表，也不能备份数据表结构;
innobackupex:是将xtrabackup进行封装的per 1脚本，提供了备份myi sam表的能力。
常用选项:
–host 指定主机
–user 指定用户名
– password 指定密码
–port 指定端口
–databases 指定数据库
– incremental 创建增量备份
– incremental -basedir 指定包含完全备份的目录
– incremental-dir 指定包含增量备份的目录
–app1y-1og 对备份进行预处理操作
-般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。
– redo-only 不回滚未提交事务
–copy-back 恢复备份目录
4.2 Xtrabackup全量备份与恢复
备份：
[root@localhost ~]# mkdir /backup
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=wjj --host=127.0.0.1 -P3306 /backup/
恢复
[root@localhost ~]# ls /backup/
2021-08-28_18-50-04
[root@localhost ~]# innobackupex --apply-log /backup/2021-08-28_18-50-04
[root@localhost ~]# cd /opt/data/3306
[root@localhost 3306]# ls
auto.cnf ibdata1 performance_schema
ca-key.pem ib_logfile0 private_key.pem
ca.pem ib_logfile1 public_key.pem
client-cert.pem ibtmp1 server-cert.pem
client-key.pem mysql server-key.pem
ib_buffer_pool mysql.pid sys
[root@localhost 3306]# rm -rf *
[root@localhost 3306]# ls
[root@localhost 3306]# innobackupex --defaults-file=/etc/my.cnf --copy-back -uroot -pwjj --host=127.0.0.1 /backup/2021-08-28_18-50-04/
166
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
