mysql数据库备份操作

数据库备份

---

一、mysqldump备份

mysqldump是在数据库外在服务器内进行备份

1、备份所有数据库

mysqldump -uroot -p --all-databases > /var/lib/mysql/all.db

2、备份指定数据库

mysqldump -uroot -p test2 > /var/lib/mysql/test2.db

3、备份指定数据库指定表

mysqldump -uroot -p mysql db event > /var/lib/mysql/2table.db

二、mysqldump还原

1、mysql外还原

mysqladmin -uroot -p create test3
mysql -uroot -p test3 < /var/lib/mysql/test2.db

2、soure方法

在mysql内还原

create database test3;
use test3;
source /var/lib/mysql/test2.db

三、使用Gtid通过二进制日志进行数据还原

1、gtid（Global Transaction ID）简介

全局唯一的事务编号。

幂等性。

GtID包括两部分：

​ Server_uuid：

​ Tx_id：

2、gtid配置

（1）查看gtid

show variables like '%gtid%';

（2）修改配置

vim /etc/my.cnf

gtid_mode=on						#开启gtid
enforce_gtid_consistency=true		#强制GTID一致性
log_slave_updates=1	   #主从复制中从库记录 binlog，并统一GTID信息

systemcat restart mysqld

3、基于gtid截取日志

对于 DDL和 DCL 一个操作就是一个 GTID。

对于 DML，一个完整的事务就是已给 GTID。

show variables like '%gtid%';

show master status;

我这日志到了9

show binlog events in 'mysql-bin.000009';

（1）基于 gtid 截取日志

• –include-gtids= 包含
• –exclude-gtids= 排除
• –skip-gtids= 跳过

截取1-3号事务

mysqlbinlog --include-gtids='57c3d0f1-1f99-11ee-a697-000c29938b7f:1-3' /var/lib/mysql/mysql-bin.000001>/var/lib/mysql/gtid1-3.sql

（2）gtid 截取日志实例

• 准备环境

create database gtid charset utf8mb4;
use gtid;
create table t1(id int) engine=innodb charset=utf8mb4;
insert into t1 values(1),(2),(3);
commit;
insert into t1 values(11),(12),(13);
commit;
select * from t1;

• 删除表

drop database gtid;
show databases;

• 找起点和gtid

show master status;
show binlog events in 'mysql-bin.000009';

• 截取日志

mysqlbinlog --skip-gtids --include-gtids='57c3d0f1-1f99-11ee-a697-000c29938b7f:16-21' /var/lib/mysql/mysql-bin.000009 > /var/lib/mysql/gtid.sql

• 恢复数据

set sql_log_bin=0;
source /var/lib/mysql/gtid1.sql
show databases;

四、MySQL物理备份 xtrabackup

1、xtrabackup 介绍

Xtrabackup 是一个开源的免费的热备工具，在 Xtrabackup 包中主要有 Xtrabackup 和innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能。

Xtrabackup备份时不能备份表结构、触发器等等，也不能智能区分.idb 数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备的功能。

2、xtrbackup安装

(1)依赖包安装

依赖包下载，下载地址

导入到服务器中安装

rpm -ivh libev-4.04-2.el6.x86_64.rpm

（2）xtrbackup下载安装

下载地址

导入到服务器中安装

tar -xf Percona-XtraBackup-8.0.32-26-r34cf2908-el9-x86_64-bundle.tar
yum -y install percona-xtrabackup-80.x86_64

（3）配置文件

修改配置文件/etc/my.cnf，保证[mysqld]模块存在参数datadir=/var/lib/mysql（指向数据目录），因xtrbackup是根据/etc/my.cnf配置文件来获取需要备份的文件。

3、xtrbackup使用

（1）用户权限说明

备份数据库时会涉及到两个用户：系统用户与数据库内部的用户。

• 系统用户

需要在 datadir（配置文件内设置的目录）上具有读写执行权限（rwx）

• 数据库内部用户

RELOAD 和 LOCK TABLES 权限，执行 FLUSH TABLES WITH READ LOCK；

REPLICATION CLIENT 权限，获取 binary log（二进制日志文件）位置；

CREATE TABLESPACE权限，导入表，用户表级别的恢复；

SUPER权限，在slave环境下备份用来启动和关闭slave线程。

（2）常用参数

--user			# 以什么用户身份进行操作	
--password		# 数据库用户的密码
--port			# 数据库的端口号，默认3306
--stream		# 打包（数据流）
--defaults-file	# 指定默认配置文件，默认读取/etc/my.cnf
--no-timestamp	# 不创建时间戳文件，而改用目的地址以自动创建）
--copy-back		# 备份还原的主要选项
--incremental	# 使用增量备份，默认使用的 整备份
--incremental-basedir	# 与--incremental选项联合，该参数指定上一级备份的地址来做增量备份

4、xtrbackup备份和还原

（1）完整备份

xtrabackup --host=127.0.0.1 --user=root --password=Admin@123.. --port=3306 --backup --target-dir=/var/lib/mysql/backup  --no-server-version-check

查看备份信息

cat xtrabackup_checkpoints

（2）全量还原

xtrabackup --prepare --target-dir=/var/lib/mysql/backup --incremental-dir=/var/lib/mysql/backup