企业级MySQL备份原理

云中漫步

　　企业级MYSQL备份恢复原理
　　1. 全量备份
　　全量数据就是数据库中所有的数据，全量备份就是把数据库中所有的数据进行备份。
　　例如：
　　备份所有库：
　　# mysqldump  -uroot -poldboy -F -B -A | gzip >/mysqlbak_$(date+%F).sql.gz
　　备份一个库：
　　#mysqldump  -uroot -poldboy  -F -B oldboy|gzip > mysqldump  -uroot -poldboy
　　2. 增量备份
　　增量备份时从上次全量备份之后，更新的新数据。对于mysql来说，binlog日志就是Mysql的增量数据。

　　优点：恢复时间：短
　　维护成本：低
　　缺点：占用空间：多
　　占用资源多，经常锁表影响用户体验

　　优点：占用空间小，占用系统资源少，无需锁表，用户体验好。
　　缺点：维护成本高，恢复麻烦，时间长。对技术要求比较高。
　　------------------------------------------------------------------------------------------
　　企业场景全量和增量的频率是怎么做的呢？
　　1）中小公司，全量一般是每天一次，业务流量低谷执行全备，备份时锁表。
　　2）单台数据库，如何增量。用rsync（配合定时任务或主从复制把所有binlog备份到远程服务器，尽量做主从复制）
　　例如: rsync -avz /mysql-bin.000*rsync_backup@10.1.1.1:backup--password-file=/etc/rsync.password
　　3) 大公司周备，每周六00点一次全备，下周日-下周六00点前都是增量。
　　优点：节省备份时间，减小备份压力。
　　缺点：增量的binlog文件副本多，还原会很麻烦。
　　4）一主多从，会有一个从库做备份，延迟同步。
　　Mysql的mysqldump备份应用场景
　　1. 迁移后者升级数据库时
　　2. 增加从库的时候
　　3. 因为硬件或者特殊异常情况，主库或者从库宕机，主从可以相互切换，无需备份。
　　4. 人为的DDL，DML语句，主从库没办法了，所有库都会执行，此时需要备份。
　　5. 跨机房灾备，需要备份到异地。
　　1. 什么情况下需要增量恢复？
　　我们在生产工作中一般常用一主多从的数据库架构，常见的备份方案是在某一个不对外服务的从库上开启binlog，然后实施定时全备和时时增量备份。
　　什么是增量备份？
　　利用二进制日志和全备进行的恢复过程，被称为增量备份。
　　那么，到底什么情况下才需要数据库增量恢复呢？
　　1）主或者从库宕机（硬件损坏）是否需要增量恢复？
　　答：不需要增量恢复，主库宕机，只需要把其中一个同步最快的从库切换为主库即可。
　　主库宕机，只要选择更新最快的从库，提升为主库。
　　从库宕机，直接不用就好了。或者正常恢复。
　　2）人为操作数据库SQL语句破坏主库是否需要增量恢复？
　　在数据库主库内部命令行误操作，会导致所有的数据库（包括主从库）数据丢失，例如：在主库执行了drop database test;这样的删除语句，这时所有的从库也会执行这个drop database test;语句，从而导致所有的数据库上的oldboy库数据丢失。这样的场景是需要增量恢复的。
　　3）只有一个主库是否需要增量恢复？
　　如果公司里只有一个主库的情况，首先应该做定时全量备份（一天每天一次）及增量备份（每隔1-10分对binlog日志做切割然后备份到其它服务器上，或者本地其他的硬盘里）或者写到网络文件系统（备份服务器）里。
　　如果不允许数据丢失，最后的办法就是做从库，通过drbd（基于磁盘块的）同步。
　　正常情况：
　　主从同步：除了分担读写分离压力外，还可以防止物理设备损坏数据丢失的恢复。
　　从库备份：在从库进行全量和增量的方式备份，可以防止人为对主库的误操作导致数据丢失。确保备份的从库实时和主库是同步状态。
　　小结：一般由人为（或程序）逻辑的方式在数据库执行的sql语句等误操作，才能要增量恢复，因为此时，所有的从库也执行了误操作语句。
　　Mysql增量恢复必备条件
　　1. 开启mysql  log-bin日志功能
　　Mysql数据库开启log-bin参数记录日志功能如下：
　　#cat  /etc/my.cnf
　　log-bin=/data/3306/mysql-bin
　　小结：增量备份的条件
　　存在一份全备加上全备之后的时刻到出问题时刻的所有增量binlog文件备份。

　　查看字符集
　　mysql> show variables like'%character_set%';
　　+--------------------------+----------------------------+
　　|Variable_name            | Value                  |
　　+--------------------------+----------------------------+
　　|character_set_client     | latin1                     |
　　|character_set_connection | latin1                     |
　　|character_set_database   | latin1                    |
　　| character_set_filesystem| binary                     |
　　|character_set_results    | latin1                     |
　　|character_set_server     | latin1                    |
　　|character_set_system     | utf8                     |
　　|character_sets_dir       | /usr/share/mysql/charsets/   |
　　查看数据库：
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　|information_schema  |
　　| mysql              |
　　| oldboy             |
　　| test                |
　　使用数据库：
　　mysql> useoldboy;
　　Database changed
　　查看数据库中有哪些表：
　　mysql> showtables;
　　+------------------+
　　| Tables_in_oldboy|
　　+------------------+
　　| xin              |
　　| ying             |
　　更改系统时间：
　　[root@data-1-2 ~]#date -s '2014/11/06'
　　Thu Nov  6 00:00:00 CST 2014
　　Mysql全备：
　　[root@data-1-2mysql]# mysqldump -uroot -p12345qwert -F -B --master-data=2 oldboy | gzip >/server/back/bak_$(date +%F).sql.gz
　　写入新数据，建立一个baba表
　　mysql> createtable baba (id int ,name varchar(30));
　　模拟用户破坏数据库：
　　mysql> dropdatabase oldboy;
　　增量恢复全过程（重点）
　　通过防火墙禁止web等应用向主库写数据或者锁表，让主库暂时停止更新。然后在进行恢复。
　　检查全备备份:
　　[root@data-1-2back]# ll /server/back/
　　-rw-r--r-- 1 rootroot 919 Nov  6 00:13bak_2014-11-06.sql.gz
　　解包命令：
　　[root@data-1-2back]# gzip -d bak_2014-11-06.sql.gz
　　在全备中，查找增量备份的日志
　　[root@data-1-2back]# grep -i "change" bak_2014-11-06.sql
　　-- CHANGE MASTERTO MASTER_LOG_FILE='mysqld-bin.000002',MASTER_LOG_POS=106;
　　刷新binlog日志
　　[root@data-1-2mysql]# mysqladmin -uroot -p12345qwert flush-logs
　　把二进制日志放到全备目录
　　[root@data-1-2mysql]# cp mysqld-bin.000002 /server/back/
　　[root@data-1-2back]# ll /server/back/
　　-rw-r--r-- 1 rootroot 2737 Nov  6 00:13 bak_2014-11-06.sql
　　-rw-r----- 1 rootroot  546 Nov  6 01:20 mysqld-bin.000002
　　把二进制日志倒成sql语句
　　[root@data-1-2back]# mysqlbinlog -d oldboy mysqld-bin.000002 > bin.sql
　　编辑bin.sql文件，删除dropdatabase oldboy;
　　关闭sql_log_bin ，恢复时日志就不会写入到binlog日志中。此时禁止用户写入。因为用户写入的信息也不写入到binlog中。
　　mysql> showvariables like '%log_bin%';
　　+---------------------------------+-------+
　　|Variable_name                   | Value |
　　+---------------------------------+-------+
　　| log_bin                         | ON   |
　　|log_bin_trust_function_creators | OFF   |
　　|log_bin_trust_routine_creators  |OFF   |
　　| sql_log_bin                     | ON    |
　　+---------------------------------+-------+
　　mysql> setsql_log_bin=off；
　　恢复全备：
　　[root@data-1-2back]# mysql -uroot -p12345qwert < bak_2014-11-06.sql
　　恢复增量备份：
　　[root@data-1-2back]# mysql -uroot -p12345qwert oldboy < bin.sql
　　登陆到Mysql数据库验证：
　　mysql> showtables;
　　+------------------+
　　| Tables_in_oldboy|
　　+------------------+
　　| baba             |
　　| xin              |
　　| ying             |
　　+------------------+
　　开启写入到二进制文件的sql
　　mysql> setsql_log_bin=on;