网站建设哪家好，找创新互联公司！专注于网页设计、网站建设、微信开发、小程序制作、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了西湖免费建站欢迎大家使用！
在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
程序错误
人为错误（常事）
计算机失败
磁盘失败
灾难
物理备份
对数据库操作系统的物理文件（如数据文件，日志文件等）的本份
物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）
冷备份：实在关闭数据库的时候进行的
热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件
逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份
从数据库的备份策略角度，备份可分为
完全备份：每次对数据进行完整的备份
差异备份：备份那些自从上次完全备份之后被修改的文件
增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份
增量备份，第一点在完全备份基础之上，a,b,c增量修改之后会就b+,c+增量备份。在这两个文件增量备份之后，再备份修改，b+b+
完全备份是对整个数据库的备份，数据库结构和文件结构的备份
完全备份保存的是备份完成时刻的数据库
完全备份是增量备份的基础
完全备份的优缺点
优点：备份与恢复操作简单方便
缺点：数据存在大量的重复，占用大量的备份空间，备份与恢复时间长
Mysql数据库的备份可以采用多种方式
直接打包数据库文件夹，如/usr/local/mysal/data
使用专用备份工具mysqldump
mysqldump命令
mysql自带的备份工具，相当方便对mysql进行备份
通过命令工具可以将指定额库，表或全部的库导出为SQL脚本，在需要恢复时可进行数据恢复
特点
没有重复数据，备份量不大，时间短
恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
可以通过mysql提供的二进制日志（binary logs) 间接实现增量备份
一般恢复
添加数据——进行完全备份——录入新的数据——进行增量备份——模拟故障——恢复操作基于位置恢复
就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复基于时间点恢复
使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式
mysql> create database shcool; #创建shcool库
Query OK, 1 row affected (0.51 sec)
mysql> use shcool; #进入shcool库中
Database changed
mysql> create table info ( #创建info表
-> id int(4) not null primary key auto_increment,
-> name varchar(10) not null,
-> score decimal(4,1) not null);
Query OK, 0 rows affected (0.21 sec)
mysql> desc info; #查看表结构
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(4,1) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into info (name,score) values ('stu01',88),('stu02',77); #插入数据
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> select * from info limit 1; #查看数据表的开头的一行
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
+----+-------+-------+
1 row in set (0.00 sec)
mysql> quit #退出来[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# ls
bin COPYING-test docs lib mysqld.pid mysql.sock.lock README share usr
COPYING data include man mysql.sock mysql-test README-test support-files
[root@localhost mysql]# cd data/
[root@localhost data]# ls
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 mysql performance_schema shcool sys
[root@localhost data]# cd shcool/
[root@localhost shcool]# cd ../../
[root@localhost mysql]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/
tar: Removing leading `/' from member names
/usr/local/mysql/data/
/usr/local/mysql/data/ibdata1
/usr/local/mysql/data/ib_logfile1
[root@localhost mysql]# ls /opt/
mysql-2019-11-24.tar.xz mysql-5.7.20 rh[root@localhost mysql]# cd data/
[root@localhost data]# mysqldump -u root -p shcool > /opt/shcool.sql
Enter password:
[root@localhost data]# ls /opt/
mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql[root@localhost opt]# mysqldump -u root -p123123 --databases shcool mysql > /opt/db_shcool_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls
db_shcool_mysql.sql mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql[root@localhost opt]# mysqldump -u root -p123123 --opt --all-databases > /opt/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls
all.sql db_shcool_mysql.sql mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql[root@localhost opt]# mysqldump -u root -p123123 shcool info > /opt/shcool_info.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# mysqldump -u root -p123123 -d shcool info > /opt/shcool_info_secret.sqlmysql> use shcool;
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> drop table info;
Query OK, 0 rows affected (0.03 sec)
mysql> source /opt/shcool.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_shcool |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> select * from info;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)mysql> drop table info;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> quit
[root@localhost opt]# mysql -u root -p123123 shcool < /opt/shcool.sql
mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# mysql -u root -p123123
mysql> use shcool;
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> show tables;
+------------------+
| Tables_in_shcool |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> select * from info;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)[root@localhost opt]# rm -rf *.sql
[root@localhost opt]# ls
mysql-2019-11-24.tar.xz mysql-5.7.20 rh[root@localhost opt]# vim /etc/my.cnf
log-bin=mysql-bin
[root@localhost opt]# systemctl restart mysqld.service
[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.index shcool
ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 performance_schema sys
[root@localhost data]# mysqldump -u root -p123123 shcool > /opt/shcool.sql #增量备份是建立在完整备份的基础上
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ls /opt/
mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql
[root@localhost data]# mysqladmin -uroot -p123123 flush-logs #产生增量备份的日志文件
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ls #02就是我们的二进制日志文件，你接下来的操作会生成到02当中
auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.000002 performance_schema sys
ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 mysql-bin.index shcool
[root@localhost data]# mysql -u root -p123123
mysql> use shcool;
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> select * from info;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> insert into info (name,score) values ('test01',66); #正常插入数据
Query OK, 1 row affected (0.01 sec)
mysql> delete from info where name='stu01'; #这是一个误操作，我们不小心删了一行数据
Query OK, 1 row affected (0.01 sec)
mysql> insert into info (name,score) values ('test02',99); #正常插入数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 2 | stu02 | 77.0 |
| 3 | test01 | 66.0 |
| 4 | test02 | 99.0 |
+----+--------+-------+
3 rows in set (0.00 sec)[root@localhost data]# mysqladmin -uroot -p123123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ls #我们刚才所有的操作都在02中包括误操作
auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.000002 mysql-bin.index shcool
ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 mysql-bin.000003 performance_schema sys
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt
要用64解码器输出，按行进行读取，显示出来生成到bak.txt文件中
[root@localhost data]# cd /opt/
[root@localhost opt]# ls
bak.txt mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql
以时间点进行恢复191124 19:18:21 --stop-datetime 代表从日志文件头部操作开始到这个时间点，后面不在执行操作
191124 19:18:33 --start-datatime 代表从这个时间点开始继续进行操作[root@localhost opt]# mysql -u root -p123123
mysql> drop table info;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from info;
ERROR 1146 (42S02): Table 'shcool.info' doesn't exist
mysql> source /opt/shcool.sql;
mysql> show tables;
+------------------+
| Tables_in_shcool |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
mysql> select * from info;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)
mysql> quit
Bye[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2019-11-24 19:18:21' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123[root@localhost opt]# mysql -u root -p123123
mysql> use shcool;
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> select * from info;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
| 3 | test01 | 66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)
mysql> quit[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2019-11-24 19:18:33' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123[root@localhost opt]# mysql -u root -p123123
mysql> use shcool;
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> select * from info;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
| 3 | test01 | 66.0 |
| 4 | test02 | 99.0 |
+----+--------+-------+
4 rows in set (0.00 sec)mysql> delete from info where name='test01';
Query OK, 1 row affected (0.00 sec)
mysql> delete from info where name='test02';
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+-------+-------+
| id | name | score |
+----+-------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
+----+-------+-------+
2 rows in set (0.00 sec)
[root@localhost opt]# cd /opt/
[root@localhost opt]# ls
bak.txt mysql-2019-11-24.tar.xz mysql-5.7.20 rh shcool.sql
[root@localhost opt]# vim bak.txt
at 568 --stop-position #跟前面一样，568是从日志头部到568结束
at 672 --start-position #从672开始到结束[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='568' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# mysql -u root -p123123
mysql> use shcool;
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> select * from info;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
| 3 | test01 | 66.0 |
+----+--------+-------+
3 rows in set (0.00 sec)
mysql> quit
Bye[root@localhost opt]# mysqlbinlog --no-defaults --start-position='672' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123123[root@localhost opt]# mysql -u root -p123123
mysql> use shcool;
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> select * from info;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 1 | stu01 | 88.0 |
| 2 | stu02 | 77.0 |
| 3 | test01 | 66.0 |
| 4 | test02 | 99.0 |
+----+--------+-------+
4 rows in set (0.00 sec)
