为了能够在数据丢失之后能够恢复数据, 我们需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略：
- 可以容忍丢失多长时间的数据；
- 恢复数据要在多长时间内完；
- 恢复的时候是否需要持续提供服务；
- 恢复的对象，是整个库，多个表，还是单个库，单个表。
数据的备份类型
1、根据要备份的数据集合的范围
-
完全备份：是备份整个数据集( 即整个数据库 )
-
增量备份：是备份自上一次备份(增量或完全)以来变化的数据;
特点: 节约空间、还原麻烦 -
差异备份：是备份自上一次完全备份以来变化的数据 ；
特点: 浪费空间、还原比增量备份简单
建议的恢复策略：
完全+增量+二进制日志
完全+差异+二进制日志
2、根据是否需要数据库离线
冷备：需要关mysql服务，读写请求均不允许状态下进行；
温备： 服务在线，但仅支持读请求，不允许写请求；
热备：备份的同时，业务不受影响。
1、这种类型的备份，取决于业务的需求，而不是备份工具
2、MySQL中进行不同方式的备份还要考虑存储引擎是否支持
-
MyISAM
热备 ×
温备 √
冷备 √
-
InnoDB
热备 √
温备 √
冷备 √
3、根据备份数据或文件
物理备份：通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
优点：
备份和恢复操作都比较简单，能够跨mysql的版本，
恢复速度快，属于文件系统级别的
建议：
不要假设备份一定可用，要测试
mysql>check tables；检测表是否可用
逻辑备份: 通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)
优点：
恢复简单、
备份的结果为ASCII文件，可以编辑
与存储引擎无关
可以通过网络备份和恢复
缺点：
备份或恢复都需要mysql服务器进程参与
备份结果占据更多的空间，
浮点数可能会丢失精度
还原之后，缩影需要重建
备份的对象
-
数据
-
二进制日志, InnoDB事务日志
-
代码(存储过程、存储函数、触发器、事件调度器)
-
服务器配置文件
备份工具
常用的几种备份工具 ：
cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份 mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备 lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份 mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎 xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份
合适的备份策略
针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种
-
直接cp,tar复制数据库文件
-
mysqldump+复制BIN LOGS
-
lvm2快照+复制BIN LOGS
-
xtrabackup
以上的几种解决方案分别针对于不同的场景
-
如果备份表中部分数据，可以利用select into outfile实现数据的备份与load data infile 恢复还原
-
如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
-
如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
-
如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用
lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果 -
如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用
xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
具体操作
1、select into outfile方式
导出文件进行备份：mysql> select * from users where Age > 30 into outfile ‘/tmp/user.txt' ;
备份的目录路径必须让当前运行mysql服务器的用户mysql具有访问权限
备份完成之后需要把备份的文件从tmp目录复制走，要不就失去备份的目的了
模拟数据丢失：mysql> delete from users where Age > 30;
导入文件进行恢复：mysql> load data infile '/tmp/user.txt' into table users;
2、cp方式
查看数据库的信息
mysql> SHOW DATABASES; #查看当前的数据库, 我们的数据库为employees
mysql> USE employees;
mysql> SHOW TABLES; #查看当前库中的表
mysql> SELECT COUNT(*) FROM employees; #由于篇幅原因, 我们这里只看一下employees的行数
向数据库施加读锁
mysql> FLUSH TABLES WITH READ LOCK;
备份数据文件
[root@node1 ~]# mkdir /backup #创建文件夹存放备份数据库文件
[root@node1 ~]# cp -a /var/lib/mysql/* /backup #保留权限的拷贝源数据文件
[root@node1 ~]# ls /backup #查看目录下的文件
employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test
模拟数据丢失并恢复
[root@node1 ~]# rm -rf /var/lib/mysql/* #删除数据库的所有文件
[root@node1 ~]# service mysqld restart #重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库
mysql> SHOW DATABASES;
[root@node1 ~]# rm -rf /var/lib/mysql/* #这一步可以不做
[root@node1 ~]# cp -a /backup/* /var/lib/mysql/ #将备份的数据文件拷贝回去
[root@node1 ~]# service mysqld restart #重启MySQL
#重新连接数据并查看
mysql> SHOW DATABASES; #数据库已恢复
mysql> USE employees;
mysql> SELECT COUNT(*) FROM employees; #表的行数没有变化
3、mysqldump+bin log
通过mysqldump进行一次完全备份, 再修改表中的数据, 然后再通过binary log进行恢复。
二进制日志需要在mysql配置文件中添加 log_bin=on 开启。
mysqldump是一个客户端的逻辑备份工具, 可以生成一个重现创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备。
方法一：
1）全量备份：
[root@www ~]# mysql -uroot -p -e 'SHOW MASTER STATUS' #查看当前二进制文件 mysql-bin.000003的状态, 并记录下position的数字106
[root@www ~]# mysqldump --all-databases --lock-all-tables > backup.sql #备份数据库到backup.sql文件中
2）、更新数据
mysql> CREATE DATABASE TEST1; #创建一个数据库
mysql> SHOW MASTER STATUS; #记下现在的position：191
从备份文件里边记录的位置到我们此时的位置，即为增量的部分
3）、备份二进制文件
cp /var/lib/mysql/mysql-bin.000003 /root
4）、模拟数据丢失
service mysqld stop #停止MySQL
rm -rf /var/lib/mysql/* #删除所有的数据文件
service mysqld start #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则
会重新初始化数据库
mysql> SHOW DATABASES; #查看数据库, 数据丢失!
5）、恢复数据
mysql> SET sql_log_bin=OFF; #暂时先将二进制日志关闭
mysql> source backup.sql #恢复数据，所需时间根据数据库时间大小而定
mysql> SET sql_log_bin=ON; 开启二进制日志
mysql> SHOW DATABASES; #数据库恢复, 但是缺少新数据TEST1
mysqlbinlog --start-position=106 --stop-position=191 mysql-bin.000003 | mysql employees #通过二进制日志增量恢复数据
mysql> SHOW DATABASES; #现在TEST1出现了！
方法二：
完全备份+增量备份+二进制日志
1、完全备份：
[root@www ~]# mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql
--single-transaction: 基于此选项能实现热备InnoDB表；因此，不需要同时使用--lock-all-tables；
--master-data=2 记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的
--databases hellodb 指定备份的数据库
然后回到mysql服务器端，
2、更新数据：
mysql> create table tb1(id int); 创建表
mysql> insert into tb1 values (1),(2),(3); 插入数据，这里只做演示，随便插入了几个数据
3、查看：
1、先查看完全备份文件里边记录的位置：
[root@www backup]# cat hellodb_2020-08-08.sql | less
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=15684; 记录了二进制日志的位置
2、
mysql> show master status;
显示此时的二进制日志的位置，从备份文件里边记录的位置到我们此时的位置，即为增量的部分
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 15982 | | |
+------------------+----------+--------------+------------------+
4、增量备份：
[root@www backup]# mysqlbinlog --start-position=15684 --stop-position=15982
/mydata/data/mysql-bin.000013 > /backup/hellodb_`date +$F_%H`.sql
5、更新数据：
mysql> insert into tb1 values (4),(5); 在插入一些数值
mysql> drop database hellodb; 删除hellodb库
6、二进制日志备份：
mysqlbinlog --start-position=15982 /mydata/data/mysql-bin.000013
查看删除操作时二进制日志的位置
# mysqlbinlog --start-position=15982 --stop-position=16176 /mydata/data/mysql-bin.000013 > /tmp/hellodb.sql
//导出二进制日志
7、恢复数据：
mysql> set sql_log_bin=0; 关闭二进制日志
mysql> flush logs; 滚动下日志
[root@www ]# mysql < /backup/hellodb_2020-08-08.sql
//导入完全备份文件
[root@www ]# mysql < /backup/hellodb_2020-08-08_05.sql
//导入增量备份文件
[root@www ]# mysql< hellodb.sql
//导入二进制文件
8、验证
4、lvm2快照
LVM快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存，如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件，如果源文件发生改变，则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。
5、Xtrabackup
特点：
-
备份过程快速、可靠；
-
备份过程不会打断正在执行的事务；
-
能够基于压缩等功能节约磁盘空间和流量；
-
自动实现备份检验；
-
还原速度快；
前提应该确定采用的是单表一个表空间，否则不支持单表的备份与恢复，在配置文件中添加 innodb_file_per_table = ON 来开启。
安装Xtrabackup
准备个目录用于存放备份数据
mkdir /extrabackup
完全备份
使用xtrabackup的前端配置工具innobackupex来实现对数据库的完全备份。
使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.
[root@node1 ~]# mkdir /extrabackup #创建备份目录
[root@node1 ~]# innobackupex --user=root /extrabackup/ #备份数据
###################提示complete表示成功*********************
[root@node1 ~]# ls /extrabackup/ #看到备份目录
一般情况, 备份完成后, 数据不能用于恢复操作, 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此, 此时的数据文件仍不一致, 所以我们需要”准备”一个完全备份
[root@node1 ~]# innobackupex --apply-log /extrabackup/2020-08-08_07-30-48/ #指定备份文件的目录
#一般情况下下面三行结尾代表成功*****************
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 369661462
160427 07:40:11 completed OK!
[root@www 2020-08-08_07-30-48]# ls
backup-my.cnf ibdata1 performance_schema xtrabackup_binary xtrabackup_checkpoints
hellodb mysql test xtrabackup_binlog_info xtrabackup_logfile
[root@www 2020-08-08_07-30-48]#
xtrabackup_checkpoints ：备份类型、备份状态和LSN(日志序列号)范围信息；
xtrabackup_binlog_info ：mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
xtrabackup_logfile ：非文本文件，xtrabackup自己的日志文件
xtrabackup_binlog_pos_innodb ：二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
backup-my.cnf ：备份时数据文件中关于mysqld的配置
更新数据
mysql> use hellodb;
mysql> delete from students where StuID>=24;
增量备份
innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2020-08-08_07-30-48/
--incremental 指定备份类型
--incremental-basedir= 指定这次增量备份是基于哪一次备份的，这里是完全备份文件,这样可以把增量备份的数据合并到完全备份中去
BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/extrabackup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。
需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。
第二次增量备份
mysql> insert into students (Name,Age,Gender,ClassID,TeacherID) values ('tom',33,'M',2,4);
innobackupex --user=root --password=mypass --incremental /extrabackup/ --incremental-basedir=/extrabackup/2020-08-08_11-37-01/
这里只须要把最后的目录改为第一次增量备份的数据目录即可
恢复准备
1、最后一次对数据更改但是没做增量备份
mysql> delete from coc where id=14;
2、把二进制日志文件备份出来，(因为最后一次修改，没做增量备份，要依赖二进制日志做时间点恢复)
[root@www data]# cp mysql-bin.000003 /tmp/
3、模拟数据库崩溃
[root@www data]# service mysqld stop
[root@www data]# rm -rf *
恢复前准备
4、对完全备份做数据同步
[root@www ~]# innobackupex --apply-log --redo-only /extrabackup/2020-08-08_07-30-48/
5、对第一次增量做数据同步
innobackupex --apply-log --redo-only /extrabackup/2020-08-08_07-30-48/ --incremental-basedir=/extrabackup/2020-08-08_11-37-01/
6、对第二次增量做数据同步
innobackupex --apply-log --redo-only /extrabackup/2020-08-08_07-30-48/ --incremental-basedir=/extrabackup/2020-08-08_11-45-53/
--apply-log 的意义在于把备份时没commit的事务撤销，已经commit的但还在事务日志中的应用到数据库
注：
对于xtrabackup来讲，它是基于事务日志和数据文件备份的，备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据库文件中的事务，还应该对其做预处理，把已提交的事务同步到数据文件，未提交的事务要回滚。因此其备份的数据库，不能立即拿来恢复。
预处理的过程：
首先对完全备份文件只把已提交的事务同步至数据文件，要注意的是有增量的时候，不能对事务做数据回滚，不然你的增量备份就没有效果了。
然后把第一次的增量备份合并到完全备份文件内，
以此类推，把后几次的增量都合并到前一次合并之后的文件中，这样的话，我们只要拿着完全备份+二进制日志，就可以做时间点恢复。
数据恢复
[root@www ~]# service mysqld stop
[root@www data]# rm -rf * 模拟数据库崩溃
[root@www ~]# innobackupex --copy-back /extrabackup/2020-08-08_07-30-48/
--copy-back数据库恢复，后面跟上备份目录的位置
[root@www ~]# cd /mydata/data/
[root@www data]# chown mysql:mysql *
[root@www data]#service mysqld start
关于xtrabackup还有很多强大的功能。
6、MYSQL Master-Slave
一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更 新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。所有对表的更新必须在主服务器上进行。
复制类型
(１)基于语句的复制： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。
(２)基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
(３)混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
原理
(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2) slave将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave重做中继日志中的事件，将改变反映它自己的数据。
有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器，初始状态时，Master和slave中的数据信息相同，当Master 中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制 日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。
配置
1、创建复制帐号
在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中
命令如下： mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@’10.100.0.200’ IDENTIFIED BY ‘1234’;
建立一个帐户backup，并且只能允许从10.100.0.200这个地址上来登陆，密码是1234。
2、拷贝数据
确保master和slave服务器上的数据一致
3、配置master:/etc/mysql/my.cnf
server-id=1 log-bin=mysql-bin
binlog-do-db=test #(test是需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可)
binlog-ignore-db=test1 #（不需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可）
#bind-address=127.0.0.1 （这行需要注销，否则不允许主机外的机器连接本机器的mysql）
#server-id：为主服务器A的ID值
#log-bin：二进制变更日值
重启master，运行SHOW MASTER STATUS\G;
Mysql> show master status\G;
File: master-bin.000024
Position: 106 (记住 file与 position的值，后面要用到)
Binlog_Do_DB: test
Binlog_Ignore_DB:
4、配置slave
server_id = 2
master-host=10.100.0.200
master-user=backup （即主服务器上为从服务器分配的用户名与密码）
master-password=1234
master-port=3306
master-connect-retry=60 重新连接的时间间隔
replicate-do-db=test （需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可）
replicate-ignore-db= （需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可）
log_bin = mysql-bin
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1
server_id 是必须的，而且唯一,不要与master重复。
slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置 bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。
relay_log配置中继日志
log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。
有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量 使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only不是很实用，特别是那些需要在slave上创建表的应用。
5、启动slave
让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.100.0.200',
-> MASTER_USER='backup',
-> MASTER_PASSWORD='1234',
-> MASTER_LOG_FILE=' master-bin.000001',
-> MASTER_LOG_POS=’0’;
mysql> start slave; #开始复制
mysql> show slave status\G
Slave_IO_State:
Master_Host: 10.100.0.200
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: b62-System-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: zpj
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
在这里主要是看: Slave_IO_State 、Slave_IO_Running 、Slave_SQL_Running
slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。
你可查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接：
在master上输入show processlist\G;
在slave服务器上运行该语句： show processlist \G。
基本原则
(1) 每个slave只能有一个master；
(2) 每个slave只能有一个唯一的服务器ID；
(3) 每个master可以有很多slave；
(4) 如果你设置log_slave_updates，slave可以是其它slave的master，从而扩散master的更新。
扫一扫
3494
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
