MySQL 多实例部署开机自启 xtrabackup备份与恢复

最新推荐文章于 2024-05-06 10:39:48 发布

linlusama

最新推荐文章于 2024-05-06 10:39:48 发布

阅读量235

点赞数

分类专栏： Mysql 文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/weixin_46634416/article/details/119985293

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL 多实例部署开机自启 xtrabackup备份与恢复

1. MySQL 多实例部署

// 拉去安装包
[root@localhost ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

//解压到/usr/local/
[root@localhost ~]# tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin      lib                                  sbin
etc      lib64                                share
games    libexec                              src
include  mysql-5.7.35-linux-glibc2.12-x86_64

//做软连接
[root@localhost local]# ln -sv mysql-5.7.35-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.35-linux-glibc2.12-x86_64/'
[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  36 8月  29 05:50 mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 8月  29 05:48 mysql-5.7.35-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 8月  12 2018 sbin
drwxr-xr-x. 5 root root  49 7月  17 03:33 share
drwxr-xr-x. 2 root root   6 8月  12 2018 src

//修改属组 属主
[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# ll -d mysql
lrwxrwxrwx. 1 mysql mysql 36 8月  29 05:50 mysql -> mysql-5.7.35-linux-glibc2.12-x86_64/

//做环境变量
[root@localhost local]# vim /etc/profile.d/mysql.sh
[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]# which mysql
/usr/local/mysql/bin/mysql

//创建各实例数据存放的目录
[root@localhost local]# mkdir -p /opt/data/{3306,3307,3308}
[root@localhost local]# cd /opt/data/
[root@localhost data]# ls
3306  3307  3308

//初始化 3306
[root@localhost profile.d]#  mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2021-08-29T10:22:56.524466Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T10:22:56.692402Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T10:22:56.721919Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T10:22:56.777888Z 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: 13e7f1c7-08b3-11ec-aed0-000c2988823d.
2021-08-29T10:22:56.778394Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T10:22:57.784376Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T10:22:57.784497Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T10:22:57.784999Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T10:22:57.842963Z 1 [Note] A temporary password is generated for root@localhost: ;ahco8p#)#L,

[root@localhost data]# echo ';ahco8p#)#L,' > 06pass
[root@localhost data]# ls
06pass  3306  3307  3308

//初始化 3307
[root@localhost data]#  mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2021-08-29T10:24:16.378846Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T10:24:16.582488Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T10:24:16.613573Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T10:24:16.668606Z 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: 43864d29-08b3-11ec-96b3-000c2988823d.
2021-08-29T10:24:16.669168Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T10:24:17.812484Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T10:24:17.812505Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T10:24:17.813031Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T10:24:18.034818Z 1 [Note] A temporary password is generated for root@localhost: tkQ5EaoOlp=c

[root@localhost data]# echo 'tkQ5EaoOlp=c' > 07pass

//初始化 3308
[root@localhost data]#  mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2021-08-29T10:26:15.428001Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-29T10:26:15.661359Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-29T10:26:15.692969Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-29T10:26:15.747904Z 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: 8a805c3a-08b3-11ec-b41c-000c2988823d.
2021-08-29T10:26:15.748473Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-29T10:26:16.636444Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T10:26:16.636501Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-08-29T10:26:16.637108Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-29T10:26:16.887528Z 1 [Note] A temporary password is generated for root@localhost: e;ewfIsXp0Mj

[root@localhost data]# echo 'e;ewfIsXp0Mj' > 08pass

//安装perl 
yum -y install  perl

//查看依赖包
[root@localhost data]# ldd /usr/local/mysql/bin/mysql
        linux-vdso.so.1 (0x00007ffc1e333000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fc1f9911000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fc1f9708000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fc1f9504000)
        libncurses.so.5 => not found
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fc1f916f000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fc1f8ded000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fc1f8bd5000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fc1f8813000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fc1f9b31000)
        libtinfo.so.5 => not found

[root@localhost data]# yum -y install ncurses-compat-libs
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.

//编辑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/3306.sock
pid-file = /opt/date/3306/mysql.pid
log-error = /var/log/mysql_3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/3307.sock
pid-file = /opt/date/3307/mysql.pid
log-error = /var/log/mysql_3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/3308.sock
pid-file = /opt/date/3308/mysql.pid
log-error = /var/log/mysql_3308.log

//登入该密码
[root@localhost ~]# mysql -uroot -p !1Zy!fLeJ;W1 -h127.0.0.1 -h3306

Welcome to the MySQLmonitor.  Commands end with ; or \g.

Your MySQL connection idis 1

Server version: 5.5.22-logSource distribution

 

Copyright (c) 2000, 2011,Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

 

Type 'help;' or '\h' forhelp. Type '\c' to clear the current input statement.

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.01 sec)

mysql> set password = password('xym');
Query OK, 0 rows affected, 1 warning (0.01 sec)

//验证
[root@localhost ~]# mysql -uroot -pxym -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 15
Server version: 5.7.35 MySQL Community Server (GPL)

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.

2. 开机自启

//将服务文件拷贝到init.d下，并重命名为mysql
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

//
[root@localhost ~]#echo export PATH=/usr/local/mysql/bin:$PATH >> /etc/init.d/mysqld

//赋予可执行权限
[root@localhost ~]# chmod +x /etc/init.d/mysqld

//添加服务
[root@localhost ~]# chkconfig --add mysqld

//启动
[root@localhost ~]# ss -antl 
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      128              [::]:22             [::]:*

3.xtrabackup

3.1.什么是xtrabackup

Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。开源、免费的MySQL热备份软件，它可以为InnoDB和XtraDB数据库执行非阻塞备份（对于MyISAM的备份同样需要加表锁）。
在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。
Percona XtraBackup是一个开源的热备份工具，适用于基于MySQL的服务器，它不会在备份期间锁定你的数据库。它的设计目的是使备份成为一个无缝的过程，而不会影响生产环境中服务器的性能。
重要提示:版本8.0.6中添加了对MyRocks存储引擎的支持。
Percona XtraBackup 8.0不支持TokuDB存储引擎。由于MySQL 8.0引入的数据字典、redo日志和undo日志与以前的版本不兼容，Percona XtraBackup 8.0目前也不支持8.0之前的版本。
特点：

创建热InnoDB备份而不暂停数据库
对MySQL进行增量备份
流压缩MySQL备份到另一个服务器
在线在MySQL服务器之间移动表
轻松创建新的MySQL复制从服务器
备份MySQL而不增加服务器的负载

3.2.xtrabackup实现机制

Percona XtraBackup基于InnoDB的崩溃恢复功能。它复制你的InnoDB数据文件，导致在内部数据不一致随后它会对文件执行崩溃恢复，以使它们具有一致性和可用性。这是因为InnoDB维护了一个重做日志，也称为事务日志。其中包含了每次对InnoDB更改的记录。当InnoDB启动时，它检查数据文件和事务日志，并执行两个步骤。它将提交的事务日志项应用到数据文件中，并对撤销任何未提交但是已经修改数据的事务。
Percona XtraBackup通过在启动时记住日志序列号(LSN)，然后复制来工作
数据文件。这需要一些时间，所以如果文件正在更改，那么它们将反映数据库的不同状态的时间点。同时，Percona XtraBackup运行一个监视事务的后台进程记录文件，并从中复制更改。Percona XtraBackup需要不断地这样做，因为事务日志以循环方式编写，一段时间后可以重用。Percona XtraBackup需要事务日志记录自开始执行以来对数据文件的每次更改。以循环方式编写，一段时间后可以重用。Percona XtraBackup需要事务日志记录自开始执行以来对数据文件的每次更改。
Percona XtraBackup使用备份锁作为刷新表的轻量级替代
只有在Percona XtraBackup完成对所有InnoDB/XtraDB数据和日志的备份后，才会对MyISAM和其他非InnoDB表进行锁定。Percona XtraBackup使用这个自动复制非innodb数据以避免阻塞
DML查询修改InnoDB表。

3.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子进程结束后退出。

3.4 Xtrabackup中主要包含的两个工具：

xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

选项	注释
–host	指定主机
–user	指定用户名
–password	指定密码
–port	指定端口
–databases	指定数据库
–incremental	创建增量备份
–incremental-basedir	指定包含完全备份的目录
–incremental-dir	指定包含增量备份的目录
–apply-log	对备份进行预处理操作

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

选项	注释
–redo-only	不回滚未提交事务
–copy-back	恢复备份目录

3.5 部署与测试

[root@localhost ~]# yum -y install percona-xtrabackup-2.3.10-1.el7.x86_64.rpm
[root@localhost ~]# ls
anaconda-ks.cfg
percona-x
[root@localhost ~]# yum -y install percona-xtrabackup-2.3.10-1.el7.x86_64.rpm

//进行备份与恢复
[root@localhost ~]# innobackupex --user=root--password=xym /opt/mysqlbackup/full/

MySQL binlog position:filename 'mysql-bin.000004', position '107'

210827 21:03:23 [00]Writing backup-my.cnf

210827  21:03:23 [00]        ...done

210827 21:03:23 [00]Writing xtrabackup_info

210827  21:03:23 [00]        ...done

xtrabackup: Transactionlog of lsn (1595675) to (1595675) was copied.

210827  21:03:24 completedOK!

//查看全量备份的文件
[root@localhost ~]# ls /opt/mysqlbackup/full  

2021-08-29_20-01-06

[root@localhost ~]# ls /opt/mysqlbackup/full/2021-08-29_20-01-06/

backup-my.cnf  mysql               xtrabackup_binlog_info  xtrabackup_info

ibdata1        performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

//备份数据库的用户需要具有相应权限.
[root@localhost ~]mysql -uroot -pxym
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35

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> create user 'bkpuser'@'localhost'identified by '123456';

Query OK, 0 rows affected(0.06 sec)

 

mysql> revoke all privileges,grantoption from 'bkpuser'@'localhost';

Query OK, 0 rows affected(0.00 sec)

 

mysql>  grant reload,lock tables,replication client,process on *.* to 'bkpuser'@'localhost';

Query OK, 0 rows affected(0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected(0.05 sec)

//创建数据库 表 插入数据
mysql> create database xym;

Query OK, 1 row affected(0.00 sec)

 

mysql> use xym;

Database changed

mysql> create table 1 (id int,name char(16) not null);

Query OK, 0 rows affected(0.05 sec)

 

mysql> insert into 1 values(1,'caiaochen');

Query OK, 1 row affected(0.02 sec)

 

mysql> insert into 1 values(2,'xk');

Query OK, 1 row affected(0.02 sec)

 

mysql> select * from tb1;

+------+----------+

| id   | name    |

+------+----------+

|    1 | caiaochen |

|    2 | xk        |

+------+----------+

2 rows in set (0.00 sec)

mysql> quit

Bye

//增量备份二进制文件
[root@localhost ~]# mysqlbinlog --start-position=107 /usr/local/mysql/data/mysql-bin.000004> /opt/mysqlbackup/inc/`date +%F`.sql

//执行误操作删库
[root@localhost ~]# rm -rf /usr/local/mysql/data/* 

//准备全备的redo日志
[root@localhost ~]# innobackupex --apply-log/opt/mysqlbackup/full/2021-08-29_20-01-06/

.........

InnoDB: File './ibtmp1'size is now 12 MB.

InnoDB: 96 redo rollbacksegment(s) found. 1 redo rollback segment(s) are active.

InnoDB: 32 non-redorollback segment(s) are active.

InnoDB: 5.7.13 started;log sequence number 1595925

xtrabackup: startingshutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimizethread exiting.

InnoDB: Startingshutdown...

InnoDB: Shutdowncompleted; log sequence number 1595944

210827 21:33:32 completedOK!

//使用innobakupex命令的 --copy-back 进行拷贝
[root@localhost ~]# innobackupex --copy-back/opt/mysqlbackup/full/2021-08-29_20-01-06/

//修改属主属组
[root@localhost ~]# chown -R mysql:mysql/usr/local/mysql/data/

//关闭二进制文件
mysql> set sql_log_bin=0;

Query OK, 0 rows affected(0.00 sec)

//读取增量备份文件
mysql> source/opt/mysqlbackup/inc/2021-08-29.sql

Query OK, 0 rows affected(0.00 sec)

 

Query OK, 0 rows affected(0.00 sec)

 

Query OK, 0 rows affected(0.00 sec)

 

Query OK, 0 rows affected (0.00sec)

mysql> set sql_log_bin=1;

Query OK, 0 rows affected(0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| xym                |

| mysql              |

| performance_schema |

+--------------------+

4 rows in set (0.00 sec)

 

mysql> use xym;

Database changed

mysql> select * from 1;

+------+----------+

| id   | name     |

+------+----------+

|    1 | caiaochen|

|    2 | xk       |

+------+----------+

2 rows in set (0.01 sec)

linlusama

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 多实例部署开机自启 xtrabackup备份与恢复

MySQL 多实例部署开机自启 xtrabackup备份与恢复1. MySQL 多实例部署// 拉去安装包[root@localhost ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz//解压到/usr/local/[root@localhost ~]# tar xf mysql-5.7.35-linux-glibc2.12-x86_64.tar.g
复制链接

扫一扫