备份的作用
我们不能保证服务器永远都在工作之中，所以我们就必须要做备份，尤其是数据库服务器。而且数据的重要性不言而喻，数据是最重要的资源之一，如果数据库遭到黑客入侵等等造成数据丢失，在没有备份的情况下那么对于公司来讲是不可逆的。
Mysql备份目的
发生故障损失降到最低
保证快速数据恢复，数据库系统和数据稳定运行
数据库可能造成故障的原因
人为误操作：权限过大误操作
程序逻辑故障：数据库系统故障
物理故障：磁盘坏道
不可抗力自然灾害：海啸、地震、台风
数据库常见的备份方法
物理冷备份
停止数据库服务，使用tar或者cp对数据库和日志文件备份
专用备份工具
mysqldump：Mysql数据库系统自带的备份工具简单方便
mysqlhotcopy：开发人员使用广泛支持二次开发
二进制日志
需要开启二进制日志功能
日志记录mysql数据变化用于恢复数据使用
做数据库增量备份恢复使用
第三方工具
Xtrabackup
innobackupex
xbstream
实验图：
实验步骤：
一，数据库备份恢复（Mysql配置创建步骤可看第二篇文章）
1.mysqldump 对表 数据库 所有数据备份
1）对magua数据库的student表备份
[root@Centos01 ~]# mysqldump -uroot -p123 magua student > ./student.sql
2）备份magua数据库
[root@Centos01 ~]# mysqldump -uroot -p123 --databases magua > ./db_magua.sql
3）备份所有的数据
[root@Centos01 ~]# mysqldump -uroot -p123 --all-databases > ./all_database.sql
2.将student表恢复到chongzi库
1）创建accp数据库
[root@Centos01 ~]# mysql -uroot -p123
mysql> create database chongzi;
2）查看创建的chongzi数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chongzi |
| magua |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> exit
3）将备份的student表恢复到虫子数据库
[root@Centos01 ~]# mysql -uroot -p123 chongzi < ./student.sql
4）查询恢复的数据
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from chongzi.student;'
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+--------+--------------------+
4.模拟magua数据库丢失恢复数据
1）删除数据库magua
[root@Centos01 ~]# mysql -uroot -p -e 'drop database magua;'
Enter password:123
2）查看数据库是否被删除
[root@Centos01 ~]# mysql -uroot -p123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| chongzi |
| mysql |
| performance_schema |
| test |
+--------------------+
3）恢复误删除的数据库
[root@Centos01 ~]# mysql -uroot -p123 < ./db_magua.sql
4）查看麻瓜的student表的数据
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from magua.student;'
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+--------+--------------------+
二，增量二进制日志备份恢复
1，开启数据库的二进制功能
1）开启数据库的二进制功能（默认开启，不需要配置）
[root@Centos01 ~]# vim /etc/my.cnf
49 log-bin=mysql-bin
2）重启mysql服务
[root@Centos01 ~]# systemctl restart mysqld.service
3）切割日志
[root@Centos01 ~]# ls /usr/local/mysql/data/ //查看日志编号
Centos01.err ib_logfile0 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009
Centos01.pid ib_logfile1 mysql-bin.000002 mysql-bin.000006 mysql-bin.index
chongzi magua mysql-bin.000003 mysql-bin.000007 performance_schema
ibdata1 mysql mysql-bin.000004 mysql-bin.000008 test
[root@Centos01 ~]# mysqladmin -uroot -p123 flush-log //生成新的日志
4）数据库插入新的数据
[root@Centos01 ~]# mysql -uroot -p123
mysql> use magua
mysql> insert into magua.student values ('赵四','男','278977897999787912',14);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into magua.student values ('李四','女','278978978978977987',15);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into magua.student values ('张冠','女','278978978978977123',16);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into magua.student values ('李戴','女','278978978978912323',17);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> exit
5）切割日志
[root@Centos01 ~]# mysqladmin -uroot -p123 flush-log
6）备份有新建记录的二进制日志
[root@Centos01 ~]# cp /usr/local/mysql/data/mysql-bin.000009 ./
7）阅读二进制日志
[root@Centos01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000009
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
2.通过二进制日志恢复误删除的数据
1）模拟数据丢失
[root@Centos01 ~]# mysql -uroot -p123 magua < ./student.sql
[root@Centos01 ~]#
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from magua.student;'
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+--------+--------------------+
2）使用二进制日志恢复误删除的数据
[root@Centos01 ~]# mysqlbinlog --no-defaults ./mysql-bin.000009 | mysql -uroot -p123
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from magua.student'
+--------+--------+------------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+------------+--------------------+
| 赵四 | 男 | 2147483647 | 14 |
| 李四 | 女 | 2147483647 | 15 |
| 张冠 | 女 | 2147483647 | 16 |
| 李戴 | 女 | 2147483647 | 17 |
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+------------+--------------------+
3.通过二进制日志恢复数据
1）模拟数据丢失
[root@Centos01 ~]# mysql -uroot -p123 magua < ./student.sql
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from magua.student;'
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+--------+--------------------+
2）基于起始位置从前往后恢复数据
[root@Centos01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000009
/*!*/;
# at 1161 //恢复id位置
#230319 4:23:54 server id 1 end_log_pos 1298 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1679171034/*!*/;
insert into magua.student values ('赵四','男','278977897999787912',14)
[root@Centos01 ~]# mysql -uroot -p123 -e 'secel * from magua.student;'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'secel * from magua.student' at line 1
3）查看恢复的数据
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from magua.student'
+--------+--------+------------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+------------+--------------------+
| 赵四 | 男 | 2147483647 | 14 |
| 李四 | 女 | 2147483647 | 15 |
| 张冠 | 女 | 2147483647 | 16 |
| 李戴 | 女 | 2147483647 | 17 |
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+------------+--------------------+
4.通过二进制日志恢复数据
1）模拟数据丢失
[root@Centos01 ~]# mysql -uroot -p123 magua < ./student.sql
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from magua.student;'
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+--------+--------------------+
2）通过起始时间和恢复时间恢复(这里只恢复张冠)
[root@Centos01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000009
/*!*/;
# at 1627
#230319 4:24:16 //恢复的起始时间 server id 1 end_log_pos 1764 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1679171056/*!*/;
insert into magua.student values ('张冠','女','278978978978977123',16)
/*!*/;
# at 1764
#230319 4:24:16 server id 1 end_log_pos 1791 Xid = 124
COMMIT/*!*/;
# at 1791
#230319 4:24:20 //恢复的结束时间 server id 1 end_log_pos 1860 Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1679171060/*!*/;
BEGIN
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from select'
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select' at line 1
[root@Centos01 ~]# mysql -uroot -p123 -e 'select * from magua.student'
+--------+--------+------------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+------------+--------------------+
| 张冠 | 女 | 2147483647 | 16 |
| magu | NULL | 18 | 222222222222222111 |
| chon | NULL | 17 | 222222222222222222 |
| Yyy | NULL | 19 | 222222222222222333 |
| whh | NULL | 20 | 222222222222222444 |
+--------+--------+------------+--------------------+
[root@Centos01 ~]#