- 1. 多实例部署MySQL
- 2. xtrabackup备份与恢复
- 2.1 xtrabackup的简介
- 2.1.1 xtrabackup的优点
- 2.1.2 xtrabackup备份的原理
- 3 xtrabackup的安装部署以及备份与恢复
- 3.1 增量备份
首先下载MySQL软件包
[root@node2 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
创建MySQL用户
[root@node2 src]# useradd -M -s /sbin/nologin mysql
创建软连接
[root@node2 src]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql [root@node2 src]# ll 总用量 0 drwxr-xr-x. 2 root root 6 8月 12 2018 debug drwxr-xr-x. 2 root root 6 8月 12 2018 kernels lrwxrwxrwx 1 root root 36 8月 27 22:03 mysql -> mysql-5.7.32-linux-glibc2.12-x86_64/ drwxr-xr-x 9 root root 129 8月 27 19:27 mysql-5.7.32-linux-glibc2.12-x86_64
修改目录/usr/src/mysql的属主属组
[root@node2 src]# ll /usr/src/mysql lrwxrwxrwx 1 mysql mysql 36 8月 27 22:03 /usr/src/mysql -> mysql-5.7.32-linux-glibc2.12-x86_64/
配置环境变量
[root@node2 src]# echo 'export PATH=/usr/src/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@node2 src]# . /etc/profile.d/mysql.sh [root@node2 src]# echo $PATH /usr/src/mysql/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
创建各实例数据库存放达到目录
[root@node2 data]# mkdir -p /opt/data/{3306,3307,3308}
修改/opt/data目录的属主和属主
[root@node2 data]# chown -R mysql.mysql /opt/data/
初始化各个实例
3306实例初始化
[root@node2 data]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql 2021-08-27T14:58:10.553518Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-08-27T14:58:11.174129Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-08-27T14:58:11.315106Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-08-27T14:58:11.334046Z 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: 3285bb8e-0747-11ec-8d24-000c29b869b7. 2021-08-27T14:58:11.335272Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-08-27T14:58:11.938447Z 0 [Warning] CA certificate ca.pem is self signed. 2021-08-27T14:58:12.292856Z 1 [Note] A temporary password is generated for root@localhost: sf1P%DNitRwl [root@node2 data]# echo 'sf1P%DNitRwl' > 3306_password
3307实例初始化
[root@node2 data]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql 2021-08-27T15:00:06.328966Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-08-27T15:00:06.815655Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-08-27T15:00:06.889250Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-08-27T15:00:06.900944Z 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: 7767d7bf-0747-11ec-b755-000c29b869b7. 2021-08-27T15:00:06.901943Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-08-27T15:00:08.200956Z 0 [Warning] CA certificate ca.pem is self signed. 2021-08-27T15:00:08.298757Z 1 [Note] A temporary password is generated for root@localhost: s?aIZf-sV2l> [root@node2 data]# echo 's?aIZf-sV2l>' > 3307_password
3308实例初始化
[root@node2 data]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql 2021-08-27T15:01:26.715767Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-08-27T15:01:27.193179Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-08-27T15:01:27.275084Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-08-27T15:01:27.290861Z 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: a7525ee4-0747-11ec-ab93-000c29b869b7. 2021-08-27T15:01:27.291757Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-08-27T15:01:28.159963Z 0 [Warning] CA certificate ca.pem is self signed. 2021-08-27T15:01:28.256708Z 1 [Note] A temporary password is generated for root@localhost: (J?siJDh(9hu [root@node2 data]# echo '(J?siJDh(9hu' > 3308_password
安装Perl
[root@node2 data]# yum -y install perl 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:39:09 前，执行于 2021年08月27日 星期五 22时24分33秒。 软件包 perl-4:5.26.3-416.el8.x86_64 已安装。 依赖关系解决。 无需任何处理。 完毕！
// 配置mysql的配置文件
[mysqld_multi] mysqld = /usr/src/mysql/bin/mysqld_safe mysqladmin = /usr/src/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/mysql3306.sock pid-file = /opt/data/3308/mysql_3308.pid log_error = /var/log/3308.log
// 启动各个实例
[root@node2 ~]# mysqld_multi start 3306 Wide character in print at /usr/src/mysql/bin/mysqld_multi line 678. [root@node2 ~]# mysqld_multi start 3307 Wide character in print at /usr/src/mysql/bin/mysqld_multi line 678. [root@node2 ~]# mysqld_multi start 3308 Wide character in print at /usr/src/mysql/bin/mysqld_multi line 678. [root@node2 ~]# ss -anlt State Recv-Q Send-Q Local Address:Port Peer Address:Port 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 *:*
初始化各个数据库的密码
3307和3308数据库同下
若进入数据库失败请安装libncurses*
yum install libncurses*
[root@node2 ~]# mysql -uroot -p'sf1P%DNitRwl' -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.32
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
mysql> set password = password('1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
// 设置开启自启
[root@node2 init.d]# cp /usr/lib/systemd/system/sshd.service ./mysqld.service [root@node2 init.d]# vim mysqld.service [root@node2 init.d]# mv ./mysqld.service /usr/lib/systemd/system/ [root@node2 init.d]# vim /usr/lib/systemd/system/mysqld.service [Unit] Description=Mysqld server daemon After=network.target [Service] Type=notify ExecStart=/usr/src/mysql/bin/mysqld_multi start Execstop=/usr/bin/kill -KILL $MAINPID KillMode=process [Install] WantedBy=multi-user.target [root@node2 support-files]# pwd /usr/src/mysql/support-files [root@node2 support-files]# cd /usr/src/mysql/support-files/ [root@node2 support-files]# cp -a mysqld_multi.server /etc/init.d/mysqld_multi [root@node2 support-files]# vim /etc/init.d/mysqld_multi export PATH=/usr/src/mysql/bin:$PATH basedir=/usr/local/mysql bindir=/usr/local/mysql/bin [root@node2 support-files]# service mysqld_multi start [root@node2 support-files]# ss -anlt State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 80 *:3307 *:* LISTEN 0 80 *:3308 *:* LISTEN 0 128 [::]:22 [::]:* 最后添加mysqld_multi服务就可以实现开机自启 [root@node2 init.d]# chkconfig --add mysqld_multi 若想关闭MySQL服务需要使用pkill mysqld命令进行关闭 手动开启的命令是： [root@node2 init.d]# service mysqld_multi start2. xtrabackup备份与恢复 2.1 xtrabackup的简介
- xtrabackup可以实现增量备份这是MySQL、mysqldump都无法实现的，这在实际生产环境中是非常实用的。
- xtrabackup还有2个工具分别是：xtrabackup、innobackup.
- xtrabackup的缺陷是只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MylSAM数据表
- 备份速度快，物理备份可靠
- 备份过程不会打断正在执行的事务
- 能够基于压缩等功能节约磁盘空间和流量
- 自动备份校验
- 还原速度快
- 可以流传将备份传输到另一台设备上
- 可以在不增加服务器负载的情况下备份数据。
备份流程图：
- innobackupex启动后，会去fork一个进程，也就是会启动进程，然后innobackupex就处于等待的状态。
- xtrabackup有两种线程：在xtrabackup启动后会先启动redo拷贝线程，并对redo.log日志进行拷贝；再去启动ibd线程，对ibd数据文件进行拷贝，当ibd数据拷贝完成之后，线程就退出。
- 当xtrabackup拷贝完成ibd数据文件后，就会告诉innobackupe，xibd数据拷贝完成。
- innobackupex收到xtrabackup的通知之后，就会去执行FTWRL，取得一致性位点，然后再去备份非InnoDB文件，在拷贝过程中数据库处于全局只读状态。
- 当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态。
- xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；
- innobackupex收到redo.log备份完成后，就进行解锁操作，执行UNLOCK TABLES；
- 最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息，而innobackupex需要等xtrabackup子进程结束后退出。
下载rpm包，
[root@node2 ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm [root@node2 ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm
在备份之前恢复单实例
[root@node2 support-files]# vim /etc/my.cnf 删除或注释掉所有行只留3306的配置文件 [root@node2 support-files]# pwd /usr/src/mysql/support-files [root@node2 support-files]# vim mysql.server basedir= /usr/src/mysql datadir= /opt/data/3306 找到basedir在后面添加两行内容 [root@node2 support-files]# ./mysql.server start 启动3306服务
进入数据库,创建数据库和表
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
mysql> create table form (id int not null auto_increment primary key,name varchar(100)not null,age tinyint);
Query OK, 0 rows affected (0.06 sec)
mysql> insert form (name,age) values ('tom',20),('admin',15),('kill',18);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
进行全量备份
[root@node2 ~]# innobackupex --default-file=/etc/my.cnf --user=root --password=1 --host=127.0.0.1 -P3306 /root/all [root@node2 all]# pwd /root/all [root@node2 all]# ls 2021-08-28_16-15-27
删除数据库，并进行恢复
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> drop database test; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) 在恢复之前先检查事务日志 [root@node2 all]# innobackupex --apply-log /root/all/2021-08-28_16-15-27/
在执行恢复命令之前要先将/opt/data/3306/目录下的文件和目录清空
[root@node2 all]# cd /opt/data/3306/ [root@node2 3306]# rm -rf *
再执行恢复命令
[root@node2 all]# innobackupex --default-file=/etc/my.cnf --copy-back -uroot -p -P3306 --host=127.0.0.1 /root/all/2021-08-28_16-15-27/
发现3306目录下的文件和目录都恢复了，再到数据库去查看
[root@node2 all]# cd /opt/data/3306/ [root@node2 3306]# ls ib_buffer_pool ib_logfile1 performance_schema xtrabackup_info ibdata1 ibtmp1 sys xtrabackup_master_key_id ib_logfile0 mysql test mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)3.1 增量备份
先全备 [root@node2 3306]# innobackupex --user=root --password=1 --host=127.0.0.1 /root/all/ 使用innobackup进行增量备份 [root@node2 3306]# innobackupex --user=root --password=1 --host=127.0.0.1 --incremental /root/all/ --incremental-basedir=/root/all/2021-08-28_17-00-50/ [root@node2 2021-08-28_17-00-50]# cat xtrabackup_checkpoints backup_type = full-backuped #备份类型为全量备份 from_lsn = 0 #lsn从0开始 to_lsn = 2754600 #lsn到2754600结束 last_lsn = 2754600 compact = 0 recover_binlog_info = 0 flushed_lsn = 2767030 增量备份的xtrabackup_checkpoints [root@node2 2021-08-28_17-21-18]# cat xtrabackup_checkpoints backup_type = incremental #备份类型为增量备份 from_lsn = 2754600 #lsn从2754600开始 to_lsn = 2754600 lsn到2754600结束 last_lsn = 2754600 compact = 0 recover_binlog_info = 0 flushed_lsn = 2767192
模拟数据库故障，关闭数据库
[root@node2 3306]# pkill mysqld 删除数据目录中的所有数据 [root@node2 3306]# pwd /opt/data/3306 [root@node2 3306]# rm -rf *
合并全备数据目录，确保数据的一致性
[root@node2 3306]# innobackupex --apply-log --redo-only /root/all/2021-08-28_17-00-50/
将增量备份的数据合并到全备数据目录当中
[root@node2 3306]# innobackupex --apply-log --redo-only /root/all/2021-08-28_17-00-50/ --incremental-dir=/root/all/2021-08-28_17-21-18/ [root@node2 all]# cat 2021-08-28_17-00-50/xtrabackup_checkpoints backup_type = log-applied #查看到数据备份类型是增加 from_lsn = 0 #lsn从0开始 to_lsn = 2754600 #lsn结束号为最新的lsn last_lsn = 2754600 compact = 0 recover_binlog_info = 0 flushed_lsn = 2767192
恢复数据
[root@node2 3306]# innobackupex --copy-back /root/all/2021-08-28_17-00-50/ 更改目录属主和属组 [root@node2 3306]# chown -R mysql.mysql /opt/data/3306 启动MySQL [root@node2 support-files]# ./mysql.server start [root@node2 support-files]# pwd /usr/src/mysql/support-files 查看数据的恢复 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | xx | +----------------+ 1 row in set (0.00 sec)
