数据库备份
一、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