mysql 8.0.30数据备份与还原

一、mysqldump

mysqldump -u -p 

-r  --result-file

--events  导出数据库中的event

--routintes   导出数据库中的存储过程和存储函数

--triggers  导出数据库中的触发器

--no-data  只导出表结构，不导出数据

--add-drop-database  导出文件中 在create database前加  drop database

--add drop table

--add-locks:    导出文件中  在insert  加 lock tablel

--no-autocommit: 

--lock-all-tables 

--lock-tables

1、数据导入导出+binglog

mysql
use testdb
DROP TABLE IF EXISTS `tb1`;
 
CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;
 
INSERT INTO `tb1` VALUES ('1', '鲁班', '1', '10', '1000.00', '1');
INSERT INTO `tb1` VALUES ('2', '后裔', '1', '20', '2000.00', '1');
INSERT INTO `tb1` VALUES ('3', '孙尚香', '1', '20', '2500.00', '1');
INSERT INTO `tb1` VALUES ('4', '凯', '4', '20', '3000.00', '1');
INSERT INTO `tb1` VALUES ('5', '典韦', '4', '40', '3500.00', '2');
INSERT INTO `tb1` VALUES ('6', '貂蝉', '6', '20', '5000.00', '1');
INSERT INTO `tb1` VALUES ('7', '孙膑', '6', '50', '5000.00', '1');
INSERT INTO `tb1` VALUES ('8', '蔡文姬', '30', '35', '4000.00', '1');
 
select * from tb1;

1) 导出数据库中所有数据

mkdir /backup
mysqldump -uroot -pAdmin@123 --single-transaction --master-data -r /backup/alldb.sql --all-databases

2)数据库中写入新的数据

mysql
use testdb
create table tb10 (id int);
insert into tb10 values(1),(2);
flush logs;
insert into tb10 values(3),(4);
flush logs;
insert into tb10 values(5),(6);
commit;

3)备份binglog,关闭mysql   删除 /var/lib/mysql下所有文件  重新初始化mysql

systemctl stop mysqld
cp /var/lib/mysql/binlog.0000* /backup
ls /backup

rm -rf /var/lib/mysql/*
systemctl start mysqld
grep password /var/log/mysqld.log
mysql -uroot -p'5L5gj5yJOo*9'
alter user root@localhost identified by 'Admin@123';
exit

 4、导入数据并验证备份数据

mysql -uroot -pAdmin@123 < /backup/alldb.sql

 5、利用二进制恢复备份后新增的数据

less /backup/alldb.sql

 mysqlbinlog --start-position=157 /backup/binlog.000011 /backup/binlog.000012 /backup/binlog.000013 | mysql -uroot -pAdmin@123
mysql -uroot -pAdmin@123
use testdb
select * from tb1;
select * from tb10;

二、mysqlbackup

mysqlbackup -ubackup -pAdmin@123 --backup-image=/backup/fulldb.mbi --backup-dir=/backup-dir backup-to-image

--backup-image   指定image 文件的路径

--backup-dir  指定一个空目录，存储本次备份所产生的记录

--incremental 指定备份为增量备份

--incremantal-base    指定增量备份的基础

--with-timp-stamp   在--backup-dir 目录下，新建一个时间目录，存储所产生的记录

1、完全备份+增量备份+binglog

1)对mysql全备

mysql
create user backup@localhost identified by 'Admin@123';
grant reload on *.* to backup@localhost;
grant create,insert,drop,update on mysql.backup_process to backup@localhost;
grant create,insert,drop,update on mysql.backup_history to backup@localhost;
grant replication client on *.* to backup@localhost;
grant super on *.* to backup@localhost;
grant process on *.* to backup@localhost;
grant lock tables,select,create,drop,file on *.*  to backup@localhost;
grant backup_admin on *.* to backup@localhost;

mkdir /backup-dir
mkdir /backup
mysqlbackup -ubackup -pAdmin@123 --backup-image=/backup/full.mbi --backup-dir=/backup-dir \
--with-timestamp backup-to-image

 2)新增数据至数据库

update tb1 set salary=3000.00  where id=1;
insert into tb10 values(7),(8);

3）增量备份

mysqlbackup -ubackup -pAdmin@123 --backup-image=/backup/inc1.mbi --backup-dir=/backup-dir \
--with-timestamp --incremental --incremental-base=dir:/backup-dir/2022-09-19_17-39-31 backup-to-image

4)新增数据至数据库

mysql
use testdb
create table tb12 (id int);
insert into tb12 values(1),(2);
delete from  tb1 where id=8;
commit;

5)再次增量备份

mysqlbackup -ubackup -pAdmin@123 --backup-image=/backup/inc2.mbi --backup-dir=/backup-dir \
--with-timestamp --incremental --incremental-base=dir:/backup-dir/2022-09-19_18-33-51 backup-to-image

mysqlbackup --backup-image=/backup/inc2.mbi --incremental --incremental-backup-dir=/tmp/backup3 copy-back-and-apply-log

6)新增数据至数据库

mysql
use testdb
insert into tb12 values(3),(4);
insert into tb10 values(9),(10);
commit;

7)二进制日志复制，模拟故障

cp /var/lib/mysql/binlog.00*  /backup
systemctl stop mysqld
rm -rf /var/lib/mysql/*

8）全备恢复

mysqlbackup --backup-image=/backup/full.mbi  --backup-dir=/tmp/backup1 copy-back-and-apply-log

9)增量1恢复

mysqlbackup --backup-image=/backup/inc1.mbi --incremental --incremental-backup-dir=/tmp/backup2 copy-back-and-apply-log

10）增量2恢复

mysqlbackup --backup-image=/backup/inc2.mbi --incremental --incremental-backup-dir=/tmp/backup3 copy-back-and-apply-log

11）启动mysql,通过二进制日志恢复数据

chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
more /backup-dir/2022-09-19_18-40-52/meta/backup_variables.txt

mysqlbinlog --start-position=23828098 /backup/binlog.000001 | mysql -uroot -pAdmin@123
mysql
use testdb
select * from tb1;
select * from tb10;
select * from tb12;

验证数据

删除备份

rm -rf /backup/*

2、目录拷贝    完全备份+增量备份+binglog

 1）完整备份

mysqlbackup -ubackup -pAdmin@123 --backup-dir=/backup --with-timestamp backup

 2)新增数据至数据库

mysql
use testdb
update tb1 set salary=2022.00  where id=1;
insert into tb10 values(11),(12);

3)增量备份1

ls  /backup
mysqlbackup -ubackup -pAdmin@123 --incremental-backup-dir=/backup --with-timestamp \
--incremental --incremental-base=dir:/backup/2022-09-19_19-12-38 backup

4)新增数据至数据库

mysql
use testdb
create table tb14 (id int);
insert into tb14 values(1),(2);
delete from  tb1 where id=7;
commit;

5)增量备份2

mysqlbackup -ubackup -pAdmin@123 --incremental-backup-dir=/backup --with-timestamp \
 --incremental --incremental-base=dir:/backup/2022-09-19_19-17-10 backup

6)新增数据至数据库

mysql
use testdb
insert into tb14 values(3),(4);
insert into tb12 values(5),(6);
insert into tb10 values(13),(14);
commit;

7)二进制日志复制，模拟故障

cp /var/lib/mysql/binlog.00000* /backup
systemctl stop mysqld
rm -rf /var/lib/mysql/*

8)对完全备份进行apply-log

ls /backup
mysqlbackup --backup-dir=/backup/2022-09-19_19-12-38 apply-log

9)对增量备份1 更新完全备份

mysqlbackup --backup-dir=/backup/2022-09-19_19-12-38 --incremental-backup-dir=/backup/2022-09-19_19-17-10 apply-incremental-backup

10) 对增量备份2 更新完全备份

mysqlbackup --backup-dir=/backup/2022-09-19_19-12-38 --incremental-backup-dir=/backup/2022-09-19_19-20-20  apply-incremental-backup

11)  根据更新后的完全备份恢复

mysqlbackup --backup-dir=/backup/2022-09-19_19-12-38 copy-back

12) 启动mysql,通过二进制日志恢复数据

chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
more /backup/2022-09-19_19-20-20/meta/backup_variables.txt 

 验证数据