mysql多实例部署与Xtrabackup备份
mysql多实例部署
软件下载
[root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
--2021-08-28 21:35:23-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
正在解析主机 downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
正在连接 downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz [跟随至新的 URL]
--2021-08-28 21:35:28-- https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 223.119.242.56
正在连接 cdn.mysql.com (cdn.mysql.com)|223.119.242.56|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：665389778 (635M) [application/x-tar-gz]
正在保存至: “mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz”
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 100%[=====================================================================================================================>] 634.56M 5.38MB/s 用时 2m 48s
2021-08-28 21:38:16 (3.78 MB/s) - 已保存 “mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz” [665389778/665389778])
配置用户和组并解压二进制程序至/usr/local下
[root@localhost src]# groupadd -r mysql #创建组
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql #创建用户并不允许登录
##解压并创建软连接
[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 src]# cd /usr/local/
[root@localhost local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share
[root@localhost local]# ln -sv mysql-5.7.34-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.34-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 5月 18 2020 bin
drwxr-xr-x. 2 root root 6 5月 18 2020 etc
drwxr-xr-x. 2 root root 6 5月 18 2020 games
drwxr-xr-x. 2 root root 6 5月 18 2020 include
drwxr-xr-x. 2 root root 6 5月 18 2020 lib
drwxr-xr-x. 3 root root 17 7月 16 07:33 lib64
drwxr-xr-x. 2 root root 6 5月 18 2020 libexec
lrwxrwxrwx. 1 root root 36 8月 28 21:44 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64/
##修改目录的所属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll -d /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 8月 28 21:44 /usr/local/mysql -> mysql-5.7.34-linux-glibc2.12-x86_64/
##编辑环境变量
[root@localhost profile.d]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost profile.d]# . /etc/profile.d/mysql.sh
[root@localhost profile.d]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
创建数据存放目录
[root@localhost opt]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost opt]# chown -R mysql.mysql /opt/data
[root@localhost opt]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 8月 28 22:05 3306
drwxr-xr-x. 2 mysql mysql 6 8月 28 22:05 3307
drwxr-xr-x. 2 mysql mysql 6 8月 28 22:05 3308
初始化实列
#3306
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2021-08-29T02:07:55.590188Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T02:07:55.735327Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T02:07:55.765978Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T02:07:55.771323Z 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: ecba00e7-086d-11ec-a65b-000c29114d9c.
2021-08-29T02:07:55.772702Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T02:07:56.314763Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T02:07:56.450617Z 1 [Note] A temporary password is generated for root@localhost: G62,uB3+td/V
#3307
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2021-08-29T02:08:41.479023Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T02:08:41.622614Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T02:08:41.647048Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T02:08:41.712157Z 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: 081c0368-086e-11ec-aa0d-000c29114d9c.
2021-08-29T02:08:41.712871Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T02:08:42.036226Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T02:08:42.196217Z 1 [Note] A temporary password is generated for root@localhost: O,4>doY2gR.1
#3308
[root@localhost ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2021-08-29T02:13:40.041901Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T02:13:40.179267Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T02:13:40.204397Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T02:13:40.260113Z 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: ba0ed253-086e-11ec-a111-000c29114d9c.
2021-08-29T02:13:40.261117Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T02:13:40.718606Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T02:13:40.795257Z 1 [Note] A temporary password is generated for root@localhost: 9*7eoP+kqQs7
安装perl
[root@localhost ~]# yum -y install perl
配置文件/etc/my.con
[root@localhost ~]# vim /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 3306
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# mysqld_multi start 3307
Wide character in print at /usr/local/mysql/bin/mysqld_multi line 678.
[root@localhost ~]# mysqld_multi start 3308
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 Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
初始化密码
[root@localhost ~]# mysql -uroot -p'G62,uB3+td/V' -S /tmp/mysql3306.sock
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('lq123456!');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
#3307
[root@localhost ~]# mysql -uroot -p'O,4>doY2gR.1' -S /tmp/mysql3307.sock
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 3
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('lq123456!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
#3308
[root@localhost ~]# mysql -uroot -p'9*7eoP+kqQs7' -S /tmp/mysql3308.sock
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('lq123456!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
多实例设置开机自启
[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld.server
[root@localhost ~]# ls /etc/init.d/
functions mysqld.server README
[root@localhost ~]# vim /etc/init.d/mysqld.server
[root@localhost ~]# vim /etc/init.d/mysqld.server
[root@localhost ~]# cat /etc/init.d/mysqld.server
#!/bin/sh
#
# A simple startup script for mysqld_multi by Tim Smith and Jani Tolonen.
# This script assumes that my.cnf file exists either in /etc/my.cnf or
# /root/.my.cnf and has groups [mysqld_multi] and [mysqldN]. See the
# mysqld_multi documentation for detailed instructions.
#
# This script can be used as /etc/init.d/mysql.server
#
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
#
# Version 1.0
#
export PATH=/usr/local/mysql/bin/:$PATH #添加该内容
basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
······
[root@localhost ~]# chkconfig --add mysqld.server
[root@localhost ~]# chkconfig --list
注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。
要列出 systemd 服务，请执行 'systemctl list-unit-files'。
查看在具体 target 启用的服务请执行
'systemctl list-dependencies [target]'。
mysqld.server 0:关 1:关 2:开 3:开 4:开 5:开 6:关
Xtrabackup介绍
MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。
Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。
Xtrabackup优点
（1）备份速度快，物理备份可靠
（2）备份过程不会打断正在执行的事务（无需锁表）
（3）能够基于压缩等功能节约磁盘空间和流量
（4）自动备份校验
（5）还原速度快
（6）可以流传将备份传输到另外一台机器上
（7）在不增加服务器负载的情况备份数据
Xtrabackup备份原理
Xtrabackup备份流程图：
（1）innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；
（2）xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）
（4）xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）
（5）innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。
（6）当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；
（7）xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；
（8）innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；
（9）最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。
Xtrabackup工具
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
--host 指定主机
--user 指定用户名
--password 指定密码
--port 指定端口
--databases 指定数据库
--incremental 创建增量备份
--incremental-basedir 指定包含完全备份的目录
--incremental-dir 指定包含增量备份的目录
--apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--redo-only 不回滚未提交事务
--copy-back 恢复备份目录
xtrabackup安装
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
--2021-08-28 23:05:10-- https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
正在解析主机 downloads.percona.com (downloads.percona.com)... 74.121.199.231, 162.220.4.222, 162.220.4.221
正在连接 downloads.percona.com (downloads.percona.com)|74.121.199.231|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：8060704 (7.7M) [application/octet-stream]
正在保存至: “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm”
percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm 100%[=====================================================================================================================>] 7.69M 328KB/s 用时 29s
2021-08-28 23:05:41 (273 KB/s) - 已保存 “percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm” [8060704/8060704])
[root@localhost ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
上次元数据过期检查：0:18:32 前，执行于 2021年08月28日 星期六 22时47分30秒。
依赖关系解决。
===================================================================================================================================================================================================================
软件包 架构 版本 仓库 大小
===================================================================================================================================================================================================================
安装:
percona-xtrabackup-24 x86_64 2.4.23-1.el8 @commandline 7.7 M
安装依赖关系:
libev x86_64 4.24-6.el8 appstream 52 k
······
验证 : percona-xtrabackup-24-2.4.23-1.el8.x86_64 7/7
Installed products updated.
已安装:
libev-4.24-6.el8.x86_64 mariadb-connector-c-3.1.11-2.el8_3.x86_64 mariadb-connector-c-config-3.1.11-2.el8_3.noarch percona-xtrabackup-24-2.4.23-1.el8.x86_64
perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64 perl-DBI-1.641-3.module_el8.3.0+413+9be2aeb5.x86_64 rsync-3.1.3-12.el8.x86_64
完毕！
xtrabackup备份
备份时需启动MySQL,恢复时需关闭MySQL,清空mysql数据目录且不能重新初始化,恢复数据后应该立即进行一次完全备份
mysql> create database liuqiang; #创建一个库验证后面是否备份成功
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| liuqiang |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
##开始备份
[root@localhost ~]# innobackupex --defaults-files=/etc/my.cnf --password=lq123456! --user=root -P3306 --host=127.0.0.1 /opt/backup
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
210828 23:07:03 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
210828 23:07:03 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3306' as 'root' (using password: YES).
210828 23:07:03 version_check Connected to MySQL server
210828 23:07:03 version_check Executing a version check against the server...
# A software update is available:
210828 23:07:06 version_check Done.
210828 23:07:06 Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: 3306, socket: not set
Using server version 5.7.34
······
210828 23:07:08 [00] Writing /opt/backup/2021-08-28_23-07-03/xtrabackup_info
210828 23:07:08 [00] ...done
xtrabackup: Transaction log of lsn (2747353) to (2747362) was copied.
210828 23:07:08 completed OK!
##删除库和/opt/date的数据
mysql> drop database liuqiang;
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# rm -rf /opt/data/*
[root@localhost ~]#
##停止mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
[root@localhost ~]# pkill mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
##恢复
[root@localhost ~]# innobackupex --copy-back /opt/backup/2021-08-28_23-07-03/
xtrabackup: recognized server arguments: --datadir=/opt/data/3308
xtrabackup: recognized client arguments:
210828 23:29:02 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
·····
innobackupex version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)
210828 23:29:02 [01] Copying ib_logfile0 to /opt/data/3308/ib_logfile0
210828 23:29:02 [01] ...done
210828 23:29:02 [01] Copying ib_logfile1 to /opt/data/3308/ib_logfile1
210828 23:29:02 [01] ...done
210828 23:29:02 [01] Copying ibdata1 to /opt/data/3308/ibdata1
210828 23:29:02 [01] ...done
210828 23:29:02 [01] Copying ./mysql/plugin.ibd to /opt/data/3308/mysql/plugin.ibd
210828 23:29:02 [01] ...done
210828 23:29:02 [01] Copying ./ibtmp1 to /opt/data/3308/ibtmp1
210828 23:29:02 [01] ...done
210828 23:29:02 completed OK!
[root@localhost ~]# ls /opt/data/3308/ #查看已经恢复
ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema sys xtrabackup_info xtrabackup_master_key_id
110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
