mysqldump 逻辑备份

1、mysqldump简介

• mysqldump是MySQL自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
• 它的备份原理是通过协议连接到MySQL数据库 ，将数据库备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

2、备份命令

1、命令格式

mysqldump [选项] 数据库名 [表名] > 脚本名

或

mysqldump [选项] --数据库名 [选项 表名] > 脚本名

或

mysqldump [选项] --all-databases [选项] > 脚本名

2、选项说明

在这就不一 一解释了详细信息请点击：https://www.cnblogs.com/qq78292959/p/3637135.html

参数名	缩写	含义
–host	-h	服务器IP地址
–port	-p	连接数据库端口号
–user	-u	MySQL 用户名
–password	-p	MySQL 密码
–databases	-B	指定要备份的数据库
–all-databases	-A	备份mysql服务器上的所有数据库
–compact		压缩模式，产生更少的输出
–comments		附加注释信息。默认为打开，可以用–skip-comments取消
–complete-insert	-c	使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。

3、备份、还原示例

备份示例

备份MySQL所有的库

[root@k8s-master01 ~]# mysqldump -uroot -p -A > /home/mysqlbackup/mysql-all.sql

注：mysql-all.sql：备份文件存储路径

mysql备份指定数据库（mysql）

[root@k8s-master01 ~]# mysqldump -uroot -p mysql > 命名.sql

mysql只备份表结构不导出数据

[root@k8s-master01 ~]# mysqldump -uroot -p --opt -d 数据库名 > 命名.sql

mysql指定备份表

[root@k8s-master01 ~]# mysqldump -uroot -p 数据库名 --table 表名 > 命名.sql

mysql备份防止锁表

[root@k8s-master01 ~]# mysqldump --skip-opt -uroot -p 数据库名 --table 表名 > 命名.sql

还原

导入数据

由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了

mysql　-uroot -p 数据库名　<　文件名
source　/home/mysqlbackup/xxx.sql

3、MySQL逻辑备份

3.1、全量备份

1）系统环境

系统版本	MySQL版本	安装方式
CentOS 7.x	5.7.x	YUM安装

2）完整备份与恢复
2.1）修改配置文件开启二进制日志

[root@k8s-master01 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 2		# id是做标识，随便填写
log-bin=/var/log/mysql/bin-log		# 设置二进制日志存放的位置

2.2）创建存放二进制日志文件的目录并赋予权限

[root@k8s-master01 ~]# mkdir -p /var/log/mysql
[root@k8s-master01 ~]# chown -R mysql.mysql /var/log/mysql

2.3）创建全量备份文件存放目录并赋予权限

[root@k8s-master01 ~]# mkdir /home/mysqlbackup/ -p
[root@k8s-master01 ~]# chown -R mysql.mysql /home/mysqlbackup/

2.4）重启数据库

[root@k8s-master01 ~]# systemctl restart mysqld

2.5）进入数据库创建一些数据用于演示备份还原

mysql> create database test1;

2.6）进行全量备份

[root@k8s-master01 ~]# mysqldump -uroot -p -P3306 --all-databases --triggers --routines --events --single-transaction --master-data=2 --flush-logs --set-gtid-purged=OFF > /home/mysqlbackup/$(date +%F%H)-mysql-all.sql	

注：
-u：指定用户
-h：连接数据地址
-P：端口号
–all-databases：备份所有数据库
–triggers：触发器
–routines：存储过程
–events：事件
–single-transaction：数据要求一致性，完整性
–master-data：将二进制的信息写入到输出文件中
–flush-logs：刷新，重新生成二进制
–set-gtid-purged：在会记录binlog日志，如果不加，不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加–set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。

3.2、全量恢复

1）为了模拟我们数据库损坏现象我们先把数据库目录删除

[root@k8s-master01 ~]# systemctl stop mysqld
[root@k8s-master01 ~]# rm -rf /var/lib/mysql/*

2）向全量备份文件里面追加不记录二进制日志的命令

[root@k8s-master01 ~]# sed -i '23a SET sql_log_bin=0;' /home/mysqlbackup/2021-01-2715-mysql-all.sql

• 向全量备份文件里面追加不记录二进制日志的命令的原因是因为我们在恢复的时候要重新执行一次SQL语句，这个语句没有记录的必要，如果记录的话还可能会导致恢复失败。

3）重启初始化数据库，启动数据库，并修改密码

[root@k8s-master01 ~]# systemctl restart mysqld
mysql> alter user USER() identified by '123456';
mysql> show databases;		# 可以看到我们创建的test1数据库已经不见了，现在我们来进行还原
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

4）导入全量的数据，还原

方法一：

[root@k8s-master01 ~]# mysql -uroot -p < /home/mysqlbackup/2021-01-2715-mysql-all.sql 

方法二：

mysql> source /home/mysqlbackup/2021-01-2715-mysql-all.sql

5）验证还原是否成功

mysql> show databases;		# 可以看到已经还原成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

3.3、增量备份

• 备份与恢复环境
• 数据库完整备份+数据库增量备份
• 新建数据表，进行全量备份，随着时间推移，数据库突然崩溃

1）备份之前

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> create table test2.t1 (id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test2.t1 values (1,"test21");
Query OK, 1 row affected (0.02 sec)

mysql> insert into test2.t1 values (2,"test22");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2.t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | test21 |
|    2 | test22 |
+------+--------+
2 rows in set (0.00 sec)

2）基于当前状态进行一次全备

[root@k8s-master01 ~]# mysqldump -uroot -p -P3306 --all-databases --triggers --routines --events --single-transaction --master-data=1 --flush-logs --set-gtid-purged=OFF > /home/mysqlbackup/$(date +%F%H)-mysql-all.sql

3）进入数据库在插入数据

mysql> insert into test2.t1 values (3,"test23");
Query OK, 1 row affected (0.01 sec)

mysql> insert into test2.t1 values (4,"test24");
Query OK, 1 row affected (0.01 sec)

mysql> select * from test2.t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | test21 |
|    2 | test22 |
|    3 | test23 |
|    4 | test24 |
+------+--------+
4 rows in set (0.00 sec)

4）模拟数据库崩溃

• 重启初始化、启动数据库，更改密码

---

[root@k8s-master01 ~]# systemctl stop mysqld
[root@k8s-master01 ~]# rm -rf /var/lib/mysql/*
[root@k8s-master01 ~]# systemctl restart mysqld
[root@k8s-master01 ~]# mysql -uroot -p
mysql>alter user USER() identified by '123456';
mysql> show databases;		# 可以看到我们创建的test1、test2都没了，现在开始恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

5）恢复全量数据

[root@k8s-master01 ~]# sed -i '23a SET sql_log_bin=0;' /home/mysqlbackup/2021-01-2716-mysql-all.sql 
[root@k8s-master01 ~]# mysql -uroot -p < /home/mysqlbackup/2021-01-2716-mysql-all.sql
[root@k8s-master01 ~]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql> select * from test2.t1;		# 可以看到还是缺少数据，现在我们通过binlog进行恢复
+------+--------+
| id   | name   |
+------+--------+
|    1 | test21 |
|    2 | test22 |
+------+--------+
2 rows in set (0.01 sec)

3.4、增量恢复

1）恢复增量备份
查看一下全量备份，备份到那个点了，如下所示是154这个点，000006这个文件

[root@k8s-master01 ~]# sed -n '22p' /home/mysqlbackup/2021-01-2716-mysql-all.sql 
CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000006', MASTER_LOG_POS=154;

• 全量仅被分到154这个点，154后面的点全备份文件里面就没有了，需要去000006以后的二进制文件里面找

2） 根据MASTER_LOG_POS恢复增量的数据

[root@k8s-master01 ~]# cd /var/log/mysql/
[root@k8s-master01 mysql]# ls
bin-log.000001  bin-log.000003  bin-log.000005  bin-log.000007  bin-log.000009  bin-log.index
bin-log.000002  bin-log.000004  bin-log.000006  bin-log.000008  bin-log.000010
[root@k8s-master01 mysql]# mysqlbinlog --start-position=154 bin-log.000006 bin-log.000007 bin-log.000008 bin-log.000009 bin-log.000010 | mysql -uroot -p
[root@k8s-master01 mysql]# mysql -uroot -p
mysql> select * from test2.t1;		# 可以看到后面我们插入的数据已经恢复了
+------+--------+
| id   | name   |
+------+--------+
|    1 | test21 |
|    2 | test22 |
|    3 | test23 |
|    4 | test24 |
+------+--------+
4 rows in set (0.00 sec)