失眠网 > 理论+实验详解MySQL全量增量备份与恢复

理论+实验详解MySQL全量增量备份与恢复

时间：2022-12-02 17:59:00

一数据库备份的分类1.1造成数据丢失的原因1.2 数据库备份的分类1.3 常见的备份方法1.3.1 物理备份1.3.2 专用备份工具mydump或者mysqlhotcopy1.3.3 启用二进制日志进行增量备份1.3.4 第三方工具备份二 MySQL完全备份与恢复2.1 MySQL完全备份2.1.1 优点2.1.2 缺点2.2 数据库完全备份分类2.2.1 物理冷备份与恢复2.2.2 mysqldump备份与恢复2.3 示例三 MySQL增量备份与恢复3.1 MySQL增量备份3.2 MySQL数据库增量恢复

一数据库备份的分类

1.1造成数据丢失的原因

程序错误人为操作错误运算错误磁盘故障灾难（如火灾，地震）和盗窃

1.2 数据库备份的分类

完全备份：每次对数据库进行完整的备份差异备份：备份自从上次完全备份之后被修改过的文件增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

1.3 常见的备份方法

1.3.1 物理备份

● 备份时数据库处于关闭状态，直接打包数据库文件

● 备份速度快，恢复时也是最简单的

1.3.2 专用备份工具mydump或者mysqlhotcopy

● mysqldump常用的逻辑备份工具

● mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

1.3.3 启用二进制日志进行增量备份

● 进行增量备份，需要刷新二进制日志

1.3.4 第三方工具备份

● 免费的MySQL热备份软件Percona XtraBackup

二 MySQL完全备份与恢复

2.1 MySQL完全备份

● 是对整个数据库，数据库结构和文件结构的备份

● 保存的是备份完成时刻的数据库

● 是差异备份与增量备份的基础

2.1.1 优点

● 备份与恢复操作简单方便

2.1.2 缺点

● 数据存在大量的重复

● 占用大量的备份空间

● 备份与恢复时间长

2.2 数据库完全备份分类

2.2.1 物理冷备份与恢复

● 关闭MySQL数据库

● 使用tar命令直接打包数据库文件夹

● 直接替换现有MySQL目录即可

2.2.2 mysqldump备份与恢复

● MySQL自带的备份工具，可方便实现对MySQL的备份

● 可以将指定的库，表导出为SQL脚本

● 使用命令mysql导入备份的数据

2.3 示例

1. 物理冷备份与恢复

[root@localhost ~]# systemctl stop mysqld ##关闭数据库[root@localhost ~]# mkdir /backup ##在根下新建backup目录[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/ ##/usr/local/mysql/data备份压缩[root@localhost ~]# cd /backup/ ##进入/backup/[root@localhost backup]# ll ##查看备份total 1384-rw-r--r-- 1 root root 1414007 Sep 13 15:57 mysql_all--09-13.tar.gz##模拟故障##[root@localhost backup]# mkdir /bak ##在根下新建bak目录[root@localhost backup]# mv /usr/local/mysql/data/ /bak/ ##将目录data数据移动到bak 里面去[root@localhost backup]# cd /usr/local/mysql/ 进mysql目录查看data[root@localhost mysql]# ll ##查看后data目录被移走，模拟故障成功total 64drwxr-xr-x 2 mysql mysql 4096 Sep 7 16:15 bin-rw-r--r-- 1 mysql mysql 17987 Sep 13 COPYING-rw-r--r-- 1 mysql mysql 17987 Sep 13 COPYING-testdrwxr-xr-x 2 mysql mysql 55 Sep 7 16:15 docsdrwxr-xr-x 3 mysql mysql 4096 Sep 7 16:15 includedrwxr-xr-x 4 mysql mysql 191 Sep 7 16:15 libdrwxr-xr-x 4 mysql mysql 30 Sep 7 16:15 mandrwxr-xr-x 10 mysql mysql 4096 Sep 7 16:15 mysql-test-rw-r--r-- 1 mysql mysql 2478 Sep 13 README-rw-r--r-- 1 mysql mysql 2478 Sep 13 README-testdrwxr-xr-x 28 mysql mysql 4096 Sep 7 16:15 sharedrwxr-xr-x 2 mysql mysql 90 Sep 7 16:15 support-filesdrwxr-xr-x 3 mysql mysql 17 Sep 7 16:15 usr[root@localhost mysql]# mkdir /restore##根目录新建restore 目录[root@localhost mysql]# tar xzvf /backup/mysql_all--09-13.tar.gz -C /restore/ ##将备份数据库解压到 /restore目录下[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/ ##将备份数据移动到到/usr/local/mysql中[root@localhost mysql]# systemctl start mysqld.service ##启动mysql数据库[root@localhost mysql]# systemctl status mysqld.service ##查看mysql数据库启动状态，日志文件显示正常。● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sun -09-13 16:04:27 CST; 5s agoDocs: man:mysqld(8)/doc/refman/en/using-systemd.htmlProcess: 50222 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)Process: 50201 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 50225 (mysqld)Tasks: 27CGroup: /system.slice/mysqld.service└─50225 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysql...Sep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.643615Z 0 [Note] ...e.Sep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.643628Z 0 [Note] ...';Sep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.643639Z 0 [Note] ...'.Sep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.653986Z 0 [Note] ...tsSep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.655178Z 0 [Note] ...s.Sep 13 16:04:27 localhost.localdomain mysqld[50222]: Version: '5.7.20' socket: '/usr/loca...onSep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.655204Z 0 [Note] ...k.Sep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.655208Z 0 [Note] ...esSep 13 16:04:27 localhost.localdomain mysqld[50222]: -09-13T08:04:27.681836Z 0 [Note] ...esSep 13 16:04:27 localhost.localdomain systemd[1]: Started MySQL Server.Hint: Some lines were ellipsized, use -l to show in full.

2. mysqldump备份与恢复

##备份恢复数据表##[root@localhost mysql]# mysql -uroot -p ##登录数据库Enter password: mysql> show databases; ##查看数据库+--------------------+| Database |+--------------------+| information_schema || bbs|| mysql || performance_schema || sys|+--------------------+5 rows in set (0.00 sec)mysql> use mysql; Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables; ##查看数据表+---------------------------+| Tables_in_mysql |+---------------------------+| columns_priv || db || engine_cost|| event || func || general_log|| gtid_executed || help_category || help_keyword || help_relation || help_topic|| innodb_index_stats || innodb_table_stats || ndb_binlog_index|| plugin|| proc || procs_priv|| proxies_priv || server_cost|| servers || slave_master_info || slave_relay_log_info|| slave_worker_info || slow_log || tables_priv|| time_zone || time_zone_leap_second|| time_zone_name || time_zone_transition|| time_zone_transition_type || user |+---------------------------+31 rows in set (0.00 sec)mysql> exit ##退出数据库Bye[root@localhost mysql]# mysqldump -u root -p mysql user > mysql-user.sql ##备份数据路mysql中的user表，文件保存在当前操作目录下面Enter password: [root@localhost mysql]# grep -v "^--" mysql-user.sql | grep -v "^/" | grep -v "^$" ##查看备份文件[root@localhost mysql]# mysql -uroot -pEnter password: mysql> create database text; ##新建数据库textQuery OK, 1 row affected (0.00 sec)mysql> exitBye[root@localhost mysql]# mysql -u root -p text < mysql-user.sql##导入数据库Enter password: [root@localhost mysql]# mysql -u root -p -e 'show tables from text'##验证输出结果Enter password: +----------------+| Tables_in_text |+----------------+| user |+----------------+##备份恢复数据库##[root@localhost mysql]# mysqldump -u root -p --databases bbs >bbs.sql##备份bbs数据库Enter password: [root@localhost mysql]# mysql -u root -p -e 'drop database bbs'##模拟故障删除bbs数据库Enter password: [root@localhost mysql]# mysql -u root -p -e 'show databases'##查看databases所有数据库有没有bbs，发现没有了Enter password: +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys|| text|+--------------------+[root@localhost mysql]# mysql -u root -p < bbs.sql##导入数据库bbsEnter password: [root@localhost mysql]# mysql -u root -p -e 'show databases' ##查看databases所有数据库有没有auth，发现有了 Enter password: +--------------------+| Database |+--------------------+| information_schema || bbs|| mysql || performance_schema || sys|| text|+--------------------+

三 MySQL增量备份与恢复

3.1 MySQL增量备份

[root@localhost mysql]# vi /etc/f[client]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysql]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1log-bin=/usr/local/mysql/data/mysql-bin##这个地方添加命令，开启二进制日志功能sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES[root@localhost mysql]# systemctl restart mysqld ##重启数据库[root@localhost mysql]# ll /usr/local/mysql/data/total 122924-rw-r----- 1 mysql mysql 56 Sep 7 16:16 fdrwxr-x--- 2 mysql mysql 20 Sep 13 16:24 bbs-rw-r----- 1 mysql mysql329 Sep 13 16:28 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 Sep 13 16:28 ibdata1-rw-r----- 1 mysql mysql 50331648 Sep 13 16:28 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Sep 7 16:16 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Sep 13 16:28 ibtmp1drwxr-x--- 2 mysql mysql4096 Sep 7 16:16 mysql-rw-r----- 1 mysql mysql154 Sep 13 16:28 mysql-bin.000001 ##二进制日志文件-rw-r----- 1 mysql mysql 39 Sep 13 16:28 mysql-bin.indexdrwxr-x--- 2 mysql mysql8192 Sep 7 16:16 performance_schemadrwxr-x--- 2 mysql mysql8192 Sep 7 16:16 sysdrwxr-x--- 2 mysql mysql 68 Sep 13 16:16 text

3.2 MySQL数据库增量恢复

1. 一般恢复

[root@localhost mysql]# mysql -uroot -pEnter password:mysql> create database client; ##创建数据库clientQuery OK, 1 row affected (0.01 sec)mysql> use client; ##进入数据库Database changedmysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,姓别 char(4),用户ID号 char(10) not null,资费 int(10)); ##创建表结构Query OK, 0 rows affected (0.00 sec)mysql> insert into user_info values('000006','张三','男','016','10'); ##插入数据Query OK, 1 row affected (0.02 sec)mysql> insert into user_info values('000007','李四','女','017','91');Query OK, 1 row affected (0.00 sec)mysql> insert into user_info values('000008','王五','女','018','23');Query OK, 1 row affected (0.00 sec)mysql> select * from user_info; ##查看表内容+-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 |+-----------+--------+--------+-------------+--------+3 rows in set (0.00 sec)mysql> flush tables with read lock; ##备份前需要将数据库加读锁，防止数据在备份时写入Query OK, 0 rows affected (0.00 sec)mysql> exitBye[root@localhost ~]# mkdir /mysql_bak ##创建/mysql_bak 目录[root@localhost ~]# mysqldump -u root -p client user_info > /mysql_bak/client_userinfo-$(date +%F).sql ##完全备份Enter password: [root@localhost ~]# ll /mysql_bak/total 4-rw-r--r-- 1 root root 2055 Sep 13 16:37 client_userinfo--09-13.sql[root@localhost ~]# mysqladmin -u root -p flush-logsEnter password: [root@localhost ~]# ll /usr/local/mysql/data/total 122928-rw-r----- 1 mysql mysql 56 Sep 7 16:16 fdrwxr-x--- 2 mysql mysql 20 Sep 13 16:24 bbsdrwxr-x--- 2 mysql mysql 62 Sep 13 16:33 client-rw-r----- 1 mysql mysql329 Sep 13 16:28 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 Sep 13 16:33 ibdata1-rw-r----- 1 mysql mysql 50331648 Sep 13 16:33 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Sep 7 16:16 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Sep 13 16:37 ibtmp1drwxr-x--- 2 mysql mysql4096 Sep 7 16:16 mysql-rw-r----- 1 mysql mysql1539 Sep 13 16:39 mysql-bin.000001-rw-r----- 1 mysql mysql154 Sep 13 16:39 mysql-bin.000002-rw-r----- 1 mysql mysql 78 Sep 13 16:39 mysql-bin.indexdrwxr-x--- 2 mysql mysql8192 Sep 7 16:16 performance_schemadrwxr-x--- 2 mysql mysql8192 Sep 7 16:16 sysdrwxr-x--- 2 mysql mysql 68 Sep 13 16:16 text##继续录入新的内容并进行增量备份##[root@localhost ~]# mysql -uroot -pEnter password: mysql> unlock tables;##解锁表锁Query OK, 0 rows affected (0.00 sec)mysql> use client;mysql> insert into user_info values('000009','赵六','男','019','37');Query OK, 1 row affected (0.00 sec)mysql> insert into user_info values('000010','孙七','男','020','36');Query OK, 1 row affected (0.00 sec)mysql> select * from user_info;+-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 |+-----------+--------+--------+-------------+--------+5 rows in set (0.00 sec)mysql> exitBye[root@localhost ~]# mysqladmin -u root -p flush-logsEnter password: [root@localhost ~]# ll /usr/local/mysql/data/total 122932-rw-r----- 1 mysql mysql 56 Sep 7 16:16 fdrwxr-x--- 2 mysql mysql 20 Sep 13 16:24 bbsdrwxr-x--- 2 mysql mysql 62 Sep 13 16:33 client-rw-r----- 1 mysql mysql329 Sep 13 16:28 ib_buffer_pool-rw-r----- 1 mysql mysql 12582912 Sep 13 16:42 ibdata1-rw-r----- 1 mysql mysql 50331648 Sep 13 16:42 ib_logfile0-rw-r----- 1 mysql mysql 50331648 Sep 7 16:16 ib_logfile1-rw-r----- 1 mysql mysql 12582912 Sep 13 16:37 ibtmp1drwxr-x--- 2 mysql mysql4096 Sep 7 16:16 mysql-rw-r----- 1 mysql mysql1539 Sep 13 16:39 mysql-bin.000001-rw-r----- 1 mysql mysql797 Sep 13 16:43 mysql-bin.000002-rw-r----- 1 mysql mysql154 Sep 13 16:43 mysql-bin.000003-rw-r----- 1 mysql mysql117 Sep 13 16:43 mysql-bin.indexdrwxr-x--- 2 mysql mysql8192 Sep 7 16:16 performance_schemadrwxr-x--- 2 mysql mysql8192 Sep 7 16:16 sysdrwxr-x--- 2 mysql mysql 68 Sep 13 16:16 text[root@localhost ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info' ##模拟误操作删除 user_info表Enter password: [root@localhost ~]# mysql -u root -p -e 'select * from client.user_info' ##查看数据表，发现不存在，删除成功Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo--09-13.sql##数据恢复Enter password: [root@localhost ~]# mysql -u root -p -e 'select * from client.user_info' ##查看恢复的数据表内容Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 |+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -pEnter password: [root@localhost ~]# mysql -u root -p -e 'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 |+-----------+--------+--------+-------------+--------+

补充：

锁定，解锁的两种方法

第一种

show processlist;

找到锁进程，kill id ;

第二种

mysql>UNLOCK TABLES;

###锁表###

锁定数据表，避免在备份过程中，表被更新

mysql>LOCK TABLES tbl_name READ;

为表增加一个写锁定：

mysql>LOCK TABLES tbl_name WRITE;

2. 基于位置恢复

##完全备份##[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo--09-13.sql Enter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 |+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 ...... ##省略内容[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -pEnter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 |+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults --start-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -pEnter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 |+-----------+--------+--------+-------------+--------+

说明：基于位置恢复的操作，节点不要选择太靠近操作的节点，否则容易恢复失败。

3. 基于时间点恢复

##选择这个时间段：13 16:42:20##[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='-9-13 16:42:20' /mysql_bak/mysql-bin.000002 |mysql -u root -pEnter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 || 000009 | 赵六 | 男| 019 |37 |+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='-9-13 16:42:20' /mysql_bak/mysql-bin.000002 |mysql -u root -pEnter password: [root@localhost ~]# mysql -u root -p -e'select * from client.user_info'Enter password: +-----------+--------+--------+-------------+--------+| 身份证 | 姓名 | 姓别 | 用户ID号 | 资费 |+-----------+--------+--------+-------------+--------+| 000006 | 张三 | 男| 016 |10 || 000007 | 李四 | 女| 017 |91 || 000008 | 王五 | 女| 018 |23 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 || 000009 | 赵六 | 男| 019 |37 || 000010 | 孙七 | 男| 020 |36 |+-----------+--------+--------+-------------+--------+

如果觉得《理论+实验详解MySQL全量增量备份与恢复》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。