MySQL全量备份的实现

create database school;
use school

create table student (
id int(10) not null unique primary key ,
name varchar(20) not null ,
sex varchar(4) ,
birth year,
department varchar(20) ,
address varchar(50)
);

create table score (
id int(10) not null unique primary key auto_increment ,
stu_id int(10) not null ,
c_name varchar(20) ,
grade int(10)
);

insert into student values( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
insert into student values( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
insert into student values( 903,'张三', '女',1990,'中文系', '湖南省永州市');
insert into student values( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
insert into student values( 905,'王五', '女',1991,'英语系', '福建省厦门市');
insert into student values( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

insert into score values(null,901, '计算机',98);
insert into score values(null,901, '英语', 80);
insert into score values(null,902, '计算机',65);
insert into score values(null,902, '中文',88);
insert into score values(null,903, '中文',95);
insert into score values(null,904, '计算机',70);
insert into score values(null,904, '英语',92);
insert into score values(null,905, '英语',94);
insert into score values(null,906, '计算机',90);
insert into score values(null,906, '英语',85);

[root@node7 ~]# echo 'export mysql_pwd=mysql@123' >> ~/.bashrc #将密码写入环境变量
[root@node7 ~]# source ~/.bashrc

[root@node7 ~]# mkdir /backup
[root@node7 ~]# mysqldump -b school > /backup/school1_date +%f.sql
[root@node7 ~]# ll /backup/
total 4
-rw-r–r-- 1 root root 3558 feb 19 16:51 school_2024-02-19.sql
[root@node7 ~]# vim /backup/school1_2024-02-19.sql #可以看到内容
[root@node7 ~]# grep -i drop /backup/school1_2024-02-19.sql
drop table if exists score;
drop table if exists student;

[root@node7 ~]# mysqldump --add-drop-table school > /backup/school_drop_date +%f.sql
#恢复时，如果表存在，它将被删除，然后新的表会被创建
[root@node7 ~]# vim /backup/school_drop_2024-02-19.sql
[root@node7 ~]# grep -i drop /backup/school_drop_2024-02-19.sql
drop table if exists score;
drop table if exists student;

[root@node7 ~]# mysqldump -b school | gzip > /backup/school2_date +%f.sql
[root@node7 ~]# vim /backup/school2_2024-02-19.sql
[root@node7 ~]# zcat /backup/school2_2024-02-19.sql

[root@node7 ~]# mysqldump school student > /backup/school3_date +%f.sql
[root@node7 ~]# cat /backup/school3_2024-02-19.sql #只有student表的数据

 mysql dump 10.13  distrib 8.0.36, for linux (x86_64)
--
-- host: localhost    database: school
-- ------------------------------------------------------
-- server version       8.0.36

/*!40101 set @old_character_set_client=@@character_set_client */;
/*!40101 set @old_character_set_results=@@character_set_results */;
/*!40101 set @old_collation_connection=@@collation_connection */;
/*!50503 set names utf8mb4 */;
/*!40103 set @old_time_zone=@@time_zone */;
/*!40103 set time_zone='+00:00' */;
/*!40014 set @old_unique_checks=@@unique_checks, unique_checks=0 */;
/*!40014 set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0 */;
/*!40101 set @old_sql_mode=@@sql_mode, sql_mode='no_auto_value_on_zero' */;
/*!40111 set @old_sql_notes=@@sql_notes, sql_notes=0 */;

--
-- table structure for table `student`
--

drop table if exists `student`;
/*!40101 set @saved_cs_client     = @@character_set_client */;
/*!50503 set character_set_client = utf8mb4 */;
create table `student` (
  `id` int not null,
  `name` varchar(20) not null,
  `sex` varchar(4) default null,
  `birth` year default null,
  `department` varchar(20) default null,
  `address` varchar(50) default null,
  primary key (`id`),
  unique key `id` (`id`)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;
/*!40101 set character_set_client = @saved_cs_client */;

--
-- dumping data for table `student`
--

lock tables `student` write;
/*!40000 alter table `student` disable keys */;
insert into `student` values (901,'张老大','男',1985,'计算机系','北京市海淀区'),(902,'张老二','男',1986,'中文系','北京市昌平区'),(903,'张三','女',1990,'中文系','湖南省永州市'),(904,'李四','男',1990,'英语系','辽宁省阜新市'),(905,'王五','女',1991,'英语系','福建省厦门市'),(906,'王六','男',1988,'计算机系','湖南省衡阳市');
/*!40000 alter table `student` enable keys */;
unlock tables;
/*!40103 set time_zone=@old_time_zone */;

/*!40101 set sql_mode=@old_sql_mode */;
/*!40014 set foreign_key_checks=@old_foreign_key_checks */;
/*!40014 set unique_checks=@old_unique_checks */;
/*!40101 set character_set_client=@old_character_set_client */;
/*!40101 set character_set_results=@old_character_set_results */;
/*!40101 set collation_connection=@old_collation_connection */;
/*!40111 set sql_notes=@old_sql_notes */;

-- dump completed on 2024-02-19 17:21:31

create database db1;
use db1
create table `stu` (
`sno` int(10) not null comment '学号', `sname` varchar(16) not null comment
'姓名',
`ssex` char(2) not null comment '性别', `sage` tinyint(2) not null default '0'
comment '学生年龄',
`sdept` varchar(16) default 'null' comment '学生所在系别', primary key (`sno`)
);

insert into `stu` values (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男',
26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');

[root@node7 ~]# mysqldump --databases school db1 > /backup/school4_date +%f.sql

[root@node7 ~]# grep school /backup/school4_2024-02-19.sql
– host: localhost database: school
– current database: school
create database /!32312 if not exists/ school /*!40100 default character set utf8mb4 collate utf8mb4_0900_ai_ci / /!80016 default encryption=‘n' */;
use school;

[root@node7 ~]# grep db1 /backup/school4_2024-02-19.sql
– current database: db1
create database /!32312 if not exists/ db1 /*!40100 default character set utf8mb4 collate utf8mb4_0900_ai_ci / /!80016 default encryption=‘n' */;
use db1;

[root@node7 ~]# mysqldump -d school > /backup/school5_date +%f.sql
[root@node7 ~]# grep -i insert /backup/school5_2024-02-19.sql #没有insert语句
[root@node7 ~]# echo $?

[root@node7 ~]# mysqldump --all-databases > /backup/school6_date +%f.sql
[root@node7 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

[root@node7 ~]# grep -i ‘use db1' /backup/school6_2024-02-19.sql
use db1;
[root@node7 ~]# grep -i ‘use mysql' /backup/school6_2024-02-19.sql
use mysql;
[root@node7 ~]# grep -i ‘use school' /backup/school6_2024-02-19.sql
use school;

[root@node7 ~]# mysql -e 'drop database school'
[root@node7 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#mysql命令恢复
[root@node7 ~]# mysql < /backup/school1_2024-02-19.sql
[root@node7 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

#source恢复
[root@node7 ~]# mysql -e 'drop database school'
[root@node7 ~]# mysql -e 'source /backup/school1_2024-02-19.sql'
[root@node7 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

[root@node7 ~]# mysqldump -b school | gzip > /backup/school2_date +%f.sql
[root@node7 ~]# mysql -e 'drop database school'
[root@node7 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@node7 ~]# zcat /backup/school2_2024-02-19.sql | mysql
[root@node7 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

[root@node7 ~]# wget -c https://github.com/mydumper/mydumper/releases/download/v0.15.1-3/mydumper-0.15.1-3.el8.x86_64.rpm
[root@node7 ~]# yum install mydumper-0.15.1-3.el8.x86_64.rpm
[root@node6 ~]# mydumper -b school -o /backup/
[root@node6 ~]# ll /backup/

total 32
drwx------ 2 root root 4096 feb 19 19:01 2024-02-19_19-01-26
drwx------ 2 root root 4096 feb 19 19:01 2024-02-19_19-01-44
-rw-r--r-- 1 root root  323 feb 19 19:13 metadata
-rw-r--r-- 1 root root  155 feb 19 19:13 school-schema-create.sql
-rw-r--r-- 1 root root    0 feb 19 19:13 school-schema-triggers.sql
-rw-r--r-- 1 root root  352 feb 19 19:13 school.score.00000.sql
-rw-r--r-- 1 root root  384 feb 19 19:13 school.score-schema.sql
-rw-r--r-- 1 root root  503 feb 19 19:13 school.student.00000.sql
-rw-r--r-- 1 root root  435 feb 19 19:13 school.student-schema.sql

[root@node6 ~]# mysql -e 'drop database school'
[root@node6 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@node6 ~]# myloader -d /backup/ -o -b school
[root@node6 ~]# mysql -e 'show databases'

+--------------------+
| database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+