简述：数据备份保证数据的安全性。
下面简要说明数据库的备份操作。
逻辑备份工具：mysqldump
备份类型
完全备份：整个数据集
部分备份：只备份数据子集
增量备份：仅备份最近一次完全备份或增量备份后变化的数据
差异备份：仅备份最近一次完成备份以来变化的数据。
演示数据库中备份机制 ，备份策略为完全备份+增量备份（复制binlog //复制二进制）
数据库二进制文件记录了所有的增删改查操作，也就是说当数据丢失时我们可以重读二进制文件进行恢复
模拟数据库备份，两台数据库服务器A,B
A：10.5.100.171
B：10.5.100.185
假设服务器A是主用服务器，B是备份数据库服务器。
服务器A做完全备份。
[root@centos6 ~]# mysqldump -uroot --all-databases --lock-all-tables --master-data=2 > hellodb.sql
--all-databases //备份全部数据库
--lock-all-tables //表示备份时锁定所有库的所有表。
--master-data //表示注释备份此时二进制文件和位置。
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[root@centos6 ~]# ls
anaconda-ks.cfg hellodb.sql hostname.sh install.log install.log.syslog
[root@centos6 ~]# less hellodb.sql
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
*-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;*
//上述表示备份时帮我们注释的二进制日志和位置为106
--
-- Current Database: `hellodb`
连入服务器A数据库，进行修改操作
[root@centos6 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.73-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use hellodb;
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_hellodb |
+-------------------+
| classes |
| courses |
| students |
| teachers |
+-------------------+
4 rows in set (0.00 sec)
mysql> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 101 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
25 rows in set (0.00 sec)
mysql> update students set Age=200 where StuID=25;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 200 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
//修改将25号的年龄修改成了200
模拟假设刚做完数据库修改操作，某些原因数据库崩溃了。
因为之前我们服务器A做过全量备份，我们只需将备份的数据库（hellodb.sql）传给备份服务器，导入到备份服务器中，我们的数据可以恢复，但是我们备份之后做的修改操作数据怎么恢复呢？
解决：这时我们就可以根据二进制文件进行恢复，我们备份时指定了二进制日志和位置。
我们只需将当时的二进制文件位置后所写入的修改操作备份出来就行。
例如：
有一个客户端工具mysqlbinlog可以检索二进制文件。查看二进制事件
[root@centos6 ~]# mysqlbinlog --start-position 106 /var/lib/mysql/mysql-bin.000001 > sal.sql
You have new mail in /var/spool/mail/root
//通过这个命令可以查看在mysql-bin.000001文件中106位置后发生的事件，我们把它备份到sql文件中
[root@centos6 ~]# scp hellodb.sql sal.sql 10.5.100.185:/root
我们将这两个sql数据备份传到10.5.100.185中，进行数据库导入实现数据恢复。
B服务器实行数据恢复。
[root@Centos7 ~]# mysql < hellodb.sql
[root@Centos7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| myDB |
| mysql |
| performance_schema |
| test |
| zabbix |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| courses |
| students |
| teachers |
+-------------------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 101 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
25 rows in set (0.00 sec)
//这是我们的数据库恢复了，但是全量备份之后的没有恢复 25号年龄还是101
这是我们再导入sal.sql
[root@Centos7 ~]# mysql < sal.sql
[root@Centos7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb;
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
MariaDB [hellodb]> select * from students;
+-------+---------------+------+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+------+--------+---------+-----------+
| 1 | shi zhongyu | 22 | M | 2 | 3 |
| 2 | shi potian | 22 | M | 1 | 7 |
| 3 | xie yangke | 53 | M | 2 | 16 |
| 4 | ding dian | 32 | M | 4 | 4 |
| 5 | yu yutong | 26 | M | 3 | 1 |
| 6 | shi qing | 46 | M | 5 | 5 |
| 7 | xie ren | 19 | F | 3 | NULL |
| 8 | lin daiyu | 17 | F | 7 | NULL |
| 9 | ren yingying | 20 | F | 6 | NULL |
| 10 | yue lingshan | 19 | F | 3 | NULL |
| 11 | yuan chengzhi | 23 | M | 6 | NULL |
| 12 | wei qingqing | 19 | F | 1 | NULL |
| 13 | tian boguang | 33 | M | 2 | NULL |
| 14 | lu wushuang | 17 | F | 3 | NULL |
| 15 | duan yu | 19 | M | 4 | NULL |
| 16 | xu zhu | 21 | M | 1 | NULL |
| 17 | lin chong | 25 | M | 4 | NULL |
| 18 | hua rong | 23 | M | 7 | NULL |
| 19 | xue baochai | 18 | F | 6 | NULL |
| 20 | huang yueying | 19 | F | 7 | NULL |
| 21 | diao chan | 22 | F | 6 | NULL |
| 22 | xiao qiao | 20 | F | 1 | NULL |
| 23 | ma chao | 23 | M | 4 | NULL |
| 24 | xu xian | 27 | M | NULL | NULL |
| 25 | su dasheng | 200 | M | NULL | NULL |
+-------+---------------+------+--------+---------+-----------+
//这时数据库已经完全恢复了。
25 rows in set (0.00 sec)
扫一扫
503
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
