Linux操作文档——MySQL的备份与恢复（5.6.36）

文章目录

一、MySQL完全备份与恢复

1、数据库备份概述

1、数据库备份类型

从物理与逻辑的角度
冷备份	在数据库关闭状态下进行备份操作
热备份	在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件
温备份	数据库锁定表格（不可写入但可读）的状态下进行备份操作

从数据库的备份策略角度
完全备份	每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份
差异备份	备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起
增量备份	只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化

2、数据库完全备份操作

1、物理冷备份与恢复

备份数据库

[root@mysql ~]# systemctl stop mysqld
[root@mysql ~]# mkdir /backup
[root@mysql ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
tar: 从成员名中删除开头的“/”
[root@mysql ~]# ls -l /backup/
总用量 732
-rw-r--r-- 1 root root 747366 Jun  8 18:10 mysql_all-2020-06-08.tar.gz

恢复数据库

[root@mysql ~]# mkdir /restore
[root@mysql ~]# tar zxf /backup/mysql_all-2020-06-08.tar.gz -C /restore/
[root@mysql ~]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/
[root@mysql ~]# systemctl start mysqld

2、mysqldump备份与恢复

1、备份数据库
格式1：备份指定库中的部分表。
mysqldump ［选项］库名［表名1］［表名2］ … > /备份路径/备份文件名
将mysql库中的user表导出为mysql-user.sql

[root@mysql ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:

格式2：备份一个或多个完整的库（包括其中所有的表）。
mysqldump ［选项］–databases 库名1 ［库名2］…>/备份路径/备份文件名
将整个auth库导出为auth.sql 文件

[root@mysql ~]# mysqldump -u root -p --databases auth > auth.sql
Enter password:

格式3：备份MySQL服务器中所有的库。
mysqldump ［选项］–all-databases > /备份路径/备份文件名
创建备份文件all-data.sql，其中包括 MySQL服务器中的所有库

[root@mysql ~]# mysqldump -u root -p --all-databases > all-data.sql
Enter password:

2、查看备份文件

[root@mysql ~]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `xingming` char(16) DEFAULT NULL,
  `xingbie` char(4) DEFAULT NULL,
  `nianling` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `students` WRITE;
INSERT INTO `students` VALUES ('zhangsan','nan',25),('lisi','nv',30);
UNLOCK TABLES;

3、恢复数据库
命令格式：mysql ［选项］［库名］［表名］＜ /备份路径/备份文件名
从备份文件mysql-user.sql中将表导入auth库

[root@mysql ~]# mysql -u root -p -e 'show tables from auth;'
Enter password: 
+----------------+
| Tables_in_auth |
+----------------+
| students       |
+----------------+

备份文件auth.sql恢复auth库

[root@mysql ~]# mysql -u root -p < ~/auth.sql
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'show databases;;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

二、MySQL增量备份与恢复

1、基本概念

—般恢复	将所有备份的二进制日志内容全部恢复
命令格式	mysqlbinlog [–no-defaults] 增量备份文件

基于位置的恢复
格式1	恢复数据到指定位置
	mysqlbinlog --stop-position=‘操作id’ 二进制日志
格式2	从指定的位置开始恢复数据
	mysqlbinlog --start-position=‘操作id’ 二进制日志

基于时间点的恢复
格式1	从日志开头截止到某个时间点的恢复
	mysqlbinlog [–no-defaults] --stop-datetime=‘年-月-日小时：分钟：秒’ 二进制日志 I mysql -u 用户名 -p 密码
格式2	从某个时间点到日志结尾的恢复
	mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日小时：分钟：秒’ 二进制日志 I mysql -u 用户名 -p 密码
格式3	从某个时间点到某个时间点的恢复
	mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日小时:分钟:秒’ --stop-datetime=‘年-月-日小时：分钟：秒，二进制日志 I mysql -u 用户名 -p 密码

2、一般恢复

1、添加数据库、表，录入信息

[root@mysql ~]# mysql -uroot -p
Enter password: 
mysql> create database client;
mysql> use client;
Database changed
mysql> 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.01 sec)

mysql> insert into user_info values('000006','zhangsan','nan','016','10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_info values('000007','lisi','nv','017','91');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values('000008','wangwu','nv','018','23');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
+-----------+----------+--------+-------------+--------+
3 rows in set (0.00 sec)

2、开启二进制日志功能

[root@mysql ~]# vim /etc/my.cnf
[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=/usr/local/mysql/mysql-bin
[root@mysql ~]# systemctl restart mysqld

3、先进行一次完全备份

[root@mysql ~]# mkdir /mysql_bak
[root@mysql ~]# mysqldump -uroot -p client user_info > /mysql_bak/client_userinfo-$(date +%F).sql
Enter password: 
[root@mysql ~]# ls /mysql_bak/
client_userinfo-2020-06-08.sql
[root@mysql ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 167 Jun  8 19:02 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 Jun  8 19:02 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql  68 Jun  8 19:02 /usr/local/mysql/mysql-bin.index

4、继续录入新的数据并进行增量备份

[root@mysql ~]# mysql -uroot -p
Enter password: 
mysql> use client;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into user_info values('000009','zhaoliu','nan','019','37');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values('000010','sunqi','nan','020','36');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+
5 rows in set (0.00 sec)

mysql> exit
Bye

刷新二进制日志

[root@mysql ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 167 Jun  8 19:02 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 679 Jun  8 19:05 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql 120 Jun  8 19:05 /usr/local/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql 102 Jun  8 19:05 /usr/local/mysql/mysql-bin.index

模拟误操作删除user_info表

[root@mysql ~]# mysql -uroot -p -e 'drop table client.user_info;'
Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

5、恢复操作
恢复完全备份

[root@mysql ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2020-06-08.sql 
Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
+-----------+----------+--------+-------------+--------+

恢复增量备份

[root@mysql ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_bak/
[root@mysql ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+

3、基于位置恢复

1、模拟误操作删除user_info表

[root@mysql ~]# mysql -uroot -p -e 'drop table client.user_info;'Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

恢复完全备份

[root@mysql ~]# mysql -uroot -p client < /mysql_bak/client_userinfo-2020-06-08.sql Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
+-----------+----------+--------+-------------+--------+

查看二进制日志文件的具体内容

[root@mysql ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200608 19:02:44 server id 1  end_log_pos 120 CRC32 0x529ba9a5 	Start: binlog v 4, server v 5.6.36-log created 200608 19:02:44
BINLOG '
1BreXg8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaWp
m1I=
'/*!*/;
# at 120
#200608 19:04:15 server id 1  end_log_pos 203 CRC32 0x6bab226a 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1591614255/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 203
#200608 19:04:15 server id 1  end_log_pos 346 CRC32 0xae3036de 	Query	thread_id=3	exec_time=0	error_code=0
use `client`/*!*/;
SET TIMESTAMP=1591614255/*!*/;
insert into user_info values('000009','zhaoliu','nan','019','37')
/*!*/;
# at 346
#200608 19:04:15 server id 1  end_log_pos 377 CRC32 0xeb5a7bce 	Xid = 33
COMMIT/*!*/;
# at 377
#200608 19:04:32 server id 1  end_log_pos 460 CRC32 0x57bd8218 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1591614272/*!*/;
BEGIN
/*!*/;
# at 460                      //在操作ID为"460"的时候，user_info表中插入了 "sunqi"的用户数据
#200608 19:04:32 server id 1  end_log_pos 601 CRC32 0xcfb81de2 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1591614272/*!*/;
insert into user_info values('000010','sunqi','nan','020','36')
/*!*/;
# at 601
#200608 19:04:32 server id 1  end_log_pos 632 CRC32 0x4b0d9c26 	Xid = 34
COMMIT/*!*/;
# at 632
#200608 19:05:41 server id 1  end_log_pos 679 CRC32 0x86716eb7 	Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

恢复到操作ID为’'460"之前的数据，即不恢复"孙七"的信息

[root@mysql ~]# mysqlbinlog --no-defaults --stop-position='460' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
+-----------+----------+--------+-------------+--------+

恢复到操作ID为’'460"之后到结束的数据，即仅恢复"孙七"的信息，跳过 "赵六"的信息恢复

[root@mysql ~]# mysqlbinlog --no-defaults --start-position='460' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+

4、基于时间点恢复

恢复到22：56 : 15之前的数据，即不恢复"孙七"的信息

[root@mysql ~]# mysqlbinlog --no-defaults --stop-datetime='2020-06-08 19:04:32' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
+-----------+----------+--------+-------------+--------+

仅恢复"孙七"的信息，跳过"赵六"的信息恢复

[root@mysql ~]# mysqlbinlog --no-defaults --start-datetime='2020-06-08 19:04:32' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
[root@mysql ~]# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+

posted @ 2020-08-15 15:28 高中僧阅读(90) 评论(0) 收藏举报

刷新页面返回顶部

高中僧