一、为什么要备份

在日常运维工作中,对于mysql数据库的备份是至关重要的!数据库对于网站的重要性使得我们对mysql数据的管理不容有失!
然后,是人总难免会犯错误,说不定哪天大脑短路了来个误操作把数据库给删除了,怎么办???
再者,领导需要查看过去某一天的数据,怎么办?
所以我们备份的重要性,不言而喻

 

二、备份的方式和类型选择

(1)根据数据库状态划分
        1)冷备(cold backup): 服务器离线,读写操作都不能进行 ,相当于要停止mysql服务

               最简单的备份方式就是,关闭MySQL服务器,然后将data目录下面的所有文件进行拷贝保存,需要恢复时,则将目录拷贝到需要恢复的机器即可。

               这种方式确实方便,但是在生产环境中基本没什么作用。
        2)温备: 全局加锁,数据库的读可以执行,但是不能写;
        3)热备(hot backup):数据库在线,读写照样进行 ,读写操作均不受影响

(2)根据备份方式
      1)物理备份(physical backup):直接复制数据文件 ,打包归档

             特点: 
             不需要额外工具,直接通过cp,tar等直接打包复制数据库的数据文件达到备份的效果 ,但是跨平台能力比较差;一般都不使用

      2)逻辑备份(logical backup):通过特定工具从数据库中导出数据并另存备份把数据抽取出来保存在sql脚本中
            特点: 
            可以使用文本编辑器编辑;导入方便,直接读取sql语句即可;逻辑备份恢复时间慢,占据空间大;无法保证浮点数的精度;恢复完数据库后需要重建索引。

 (3)根据备份整个数据还是变化数据 
       1) 全量备份 full backup
       2) 增量备份 incremental backup 
              在不同时间点起始备份一段数据,比较节约空间;针对的是上一次备份后有变化的数据,备份数据少,备份快,恢复慢
       3) 差异备份 differential backup 
              备份从每个时间点到上一次全部备份之间的数据,随着时间增多二增多;比较容易恢复;对于很大的数据库,可以考虑主从模型,备份从服务器的内容。针对的是上一次全量备份后有变化的数据,备份数据多,备份慢,恢复快。

 

 (4) Mysql常用的备份工具和选择

        1)cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份

             适用场景数据量很小,比如个人博客,在生产环境中基本没什么作用

        2)mysqldump: 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备       

             innodb: 热备,温备
             MyISAM, Aria: 温备 
             单线程备份恢复比较慢

             适用场景:  数据量还行,比如咱们公司的数据,20G左右,可以先使用mysqldump对数据库进行完全备份, 然后定期备份binlog达到增量备份的效果

        3)lvm-snapshot:

             接近于热备的工具:因为要先请求全局锁,而后创建快照,并在创建快照完成后释放全局锁;
             使用cp、tar等工具进行物理备份;
             备份和恢复速度较快;
             很难实现增量备份,并且请求全局需要等待一段时间,在繁忙的服务器上尤其如此;

              适用场景:  如果数据量一般, 而又不过分影响业务运行, 可以使用lvm2的快照对数据文件进行备份, 而后定期备份binlog达到增量备份的效果

       4)Xtrabackup(通常用innobackupex工具,由percona提供):
               备份mysql大数据
               InnoDB热备,增量备份;
               MyISAM温备,不支持增量,只有完全备份 
               属于物理备份,速度快;

               适用场景:   如果数据量很大, 比如数据50G以上,而又不过分影响业务运行, 可以使用这种方式,使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

 

 

  • 今天我主要介绍mysqldump的方式 

 

mysqldump工具基本使用

1. mysqldump [OPTIONS] database [tables…]

还原时库必须存在,不存在需要手动创建,除了全库备份之外

    --all-databases: 备份所有库 
    --databases db1 db2 ...: 备份指定的多个库,如果使用此命令,恢复时将不用手动创建库。或者是-B db1 db2 db3 ....
    --lock-all-tables:请求锁定所有表之后再备份,对MyISAM、InnoDB、Aria做温备
    --lock-table: 对正在备份的表加锁,但是不建议使用,如果其它表被修改,则备份后表与表之间将不同步
    --single-transaction: 能够对InnoDB存储引擎实现热备,保证数据一致,启动一个很大的大事物,基于MOCC可以保证在事物内的表版本一致;工作原理是设定本次会话的隔离级别为:REPEATABLE READ,以确保本次会话(dump)时,不会看到其他会话已经提交了的数据,自动加锁不需要再加--lock-table, 可以实现热备

备份代码:
   --events: 备份事件调度器代码
   --routines: 备份存储过程和存储函数
   --triggers:备份触发器
备份时滚动日志:
   --flush-logs: 备份前、请求到锁之后滚动日志;
方恢复备份时间点以后的内容
复制时的同步位置标记:主从架构中的,主服务器数据。效果相当于标记一个时间点。 

   --master-data=[0|1|2]           #只能在master上备份加这个参数有效
   0: 不记录
   1:记录为CHANGE MASTER语句
   2:记录为注释的CHANGE MASTER语句

 

2. 使用mysqldump备份大体过程:
1) 请求锁:--lock-all-tables或使用–singe-transaction进行innodb热备;
2) 滚动日志:--flush-logs
3) 选定要备份的库:--databases
4) 记录二进制日志文件及位置:--master-data=
FLUSH TABLES5 WITH READ LOCK;

3. 恢复注意点:
1).恢复过程无需写到二进制日志中 
    建议:关闭二进制日志,关闭其它用户连接;

2).误操作之后,应该立即采取措施,避免数据混乱

      比如设置只读

      对于master库或单实例数据库,需要进行如下操作和设置,讲数据库设置为只读状态

mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;       #限制所有用户,包括root用户 只读
mysql> set global read_only=1;            #限制普通权限的用户,只读
mysql> show global variables like "%read_only%";

将MySQL从只读设置为读写状态的命令:
mysql> unlock tables;  
mysql> set global read_only=0;

对于需要保证master-slave主从同步的salve库,如果要设置为只读状态,需要执行的命令为:
mysql> set global read_only=1;
     将salve库从只读状态变为读写状态,需要执行的命令是:
mysql> set global read_only=0;


说明:
1. read_only=1 ,不会影响slave同步复制的功能,所以在MySQL slave库中设定了read_only=1后,通过 show slave status\G 命令查看salve状态,可以看到salve仍然会读取master上的日志,并且在slave库中应用日志,保证主从数据库同步一致;
2.read_only=1只读模式,可以限定普通用户进行数据修改的操作,但不会限定具有super权限的用户的数据修改操作;在MySQL中设置read_only=1后,普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时,都会报出数据库处于只读模式不能发生数据变化的错误,但具有super权限的用户,例如在本地或远程通过root用户登录到数据库,还是可以进行数据变化的DML操作;
 比如root, 和授权的all privileges等是可以操作的
3.为了确保所有用户,包括具有super权限的用户也不能进行读写操作,就需要执行给所有的表加读锁的命令 “flush tables with read lock;”,这样使用具有super权限的用户登录数据库,想要发生数据变化的操作时,也会提示表被锁定不能修改的报错。

      但是我们一般不会设置所有权限给其他有用户,所以我们需要的时候,在master服务器上执行 set global read_only=1;    

   保证root用户可以进行恢复

  

4. 备份策略:基于mysqldump

备份:mysqldump+二进制日志文件;(“mysqldump >”)
周日做一次完全备份:备份的同时滚动日志
周一至周六:备份二进制日志;

 

我们公司是在mysql从服务器上,每天凌晨对在用的数据库进行备份,传输到内网,线上保留最近7天的,内网保存所有的数据

不定期进行全库备份和二进制日志的备份传输
恢复:(“mysql < ”)或在mysql数据库中直接执行“source sql备份文件;”进行恢复。如果sql执行语句比较多,可以将sql语句放在一个文件内,将文件名命名为.sql结尾,然后在mysql数据库中使用"source 文件.sql;"命令进行执行即可!
完全备份+各二进制日志文件中至此刻的事件

 

5.实例说明

mysql利用binlog恢复数据的详细案例