mysql学习：备份和恢复

1、基础概念

备份：存储的数据副本；但是原始数据持续改变；

恢复：把副本应用到线上系统；仅能恢复至备份操作时刻的数据状态；

为什么备份？

灾难恢复：硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击、误操作、…

测试；

虽然raid10可以防止磁盘出问题，但是防止不了人为失误操作

备份数据最好不要和原始数据在同一台机器上面，不要在同一个机房，假如机房失火了。数据是最重要的

备份时应该注意事项：

能容忍最多丢失多少数据；

恢复数据需要在多长时间内完成；

需要恢复哪些数据；

做恢复演练：

测试备份的可用性；

增强恢复操作效率；

备份类型：

根据备份的数据的集范围：完全备份和部分备份

完全备份：整个数据集；

部分备份：数据集的一部分，比如部分表，部分库；

根据备份方式：完全备份、增量备份、差异备份：有一个完全备份A

完全备份：每到一个时间点，比如12点，全部备份一遍

差异备份：每次备份都是备份自上一次完全备份A以来变化的那部数据；数据有冗余

增量备份：后一天备份自上一次完全备份A以来变化的那部数据B；后后一天备份从B开始变化的数据；必须要每一天的数据才能恢复所有的数据，省空间，但是还原难度非常大

最好的方式是：完全+差异+二进制日志来进行备份恢复数据

物理备份、逻辑备份：

物理备份：复制数据文件进行备份；

逻辑备份：从数据库导出数据另存在一个或多个文件中；

根据数据服务是否在线：

热备：读写操作均可进行的状态下所做的备份；

温备：可读但不可写状态下进行的备份；

冷备：读写操作均不可进行的状态下所做的备份；

备份需要考虑因素：

锁定资源多长时间？

备份过程的时长？一般在访问量最少的情况下备份数据

备份时的服务器负载？

恢复过程的时长？

备份什么？

数据文件

二进制日志、InnoDB的事务日志；

代码（存储过程、存储函数、触发器、事件调度器）

服务器的配置文件，不同数据库对应的配置文件不同

备份工具：

mysqldump：mysql服务自带的备份工具；逻辑备份工具；

支持完全、部分备份；

InnoDB：热备；

MyISAM：温备；

cp/tar

lvm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份；

注意：不能仅备份数据文件；要同时备份事务日志；

前提：要求数据文件和事务日志位于同一个逻辑卷；

xtrabackup：由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具；

完全备份、部分备份；

完全备份、增量备份；

完全备份、差异备份；

mysqlhotcopy：冷备工具

select：

备份：SELECT cluase INTO OUTFILE 'FILENAME';

恢复：CREATE TABLE 

导入：LOAD DATA 

MySQL备份恢复(01)

2、mysqldump使用

1、mysqldump参数 

-A (--all-databases) ：备份所有库，也会有创建数据库的语句和use库语句
-B (--databases) ：连接多个数据库,备份的数据中有增加建库和use库语句 (方便导入数据)
-d (--no-data) ：只导出表结构
-t (--no-create-info) ：只导出表数据
-x (--lock-all-tables) ：锁表(无法对数据库进行操作，影响大)
-l (--lock-tables) : 只读锁表
-F (--flush-logs) : 刷新binlog日志(备份数据时可能会用上)也可以在mysql客户端执行reset master; 
--master-date=1/2 ：默认等于1，将dump起始（change master to）binlog和pos值写到结果中，等于2是将change master to写到结果中并注释。

gzip ：指定gzip进行备份sql压缩

--single-transaction ：适合InnoDB事务数据库备份（InnoDB表在备份时,通常启用该参数来保证备份的一致性，
#他的工作原理是设定本次回话的隔离级别为: repeatable read,以确保本次会话dump时，不会看到其他会话已经提交了的数据）

--compact ：参数优化备份文件大小减少输出注释(debug调试时使用)

--default-chatacter-set=utf8 ：指定默认字符集

• mysql数据库自带一个很好用的备份命令mysqldump，它的语法为:

mysqldump -u 用户名 -p 密码 数据库名 [表名] > 备份的文件名

• Myisam表常规备份(参数)，自带压缩

mysqldump -uroot -paaaaaa -A -F --flush-privileges --triggers --routines --events --hex-blob --master-data=1 -x|gzip > ./all.sql.gz

• InnoDB表常规备份(推荐使用的存储引擎)，测试结果：加上-F后binlog会刷新

mysqldump -uroot -paaaaaa -A -F --flush-privileges --master-data=2 --triggers --routines --events --hex-blob --single-transaction|gzip > ./all.sql.gz

• 通过socket文件备份，没有创建数据库的语句

mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock database > ~/database.sql

• 多实例指定sock文件备份数据库test并使用gzip压缩

mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock test|gzip > ~/test.gz.sql

• 使用egrep查看备份的文件的sql内容，这样看到的sql语句不会有注释干扰，全是可执行的文件

egrep -v "#|\*|--|^$" ~/test.sql

• -B参数 备份多个库，有创建数据库的语句

mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock -B test mysql|gzip >~/mutil_db.sql.gz

• 备份库下的某个表，没有创建数据库的语句，还原的时候必须要指定还原到哪个库中

mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbNAME tableName>~/dbName.sql

• 备份库下的多个表

mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbName tableName tableName ..>~/dbName.sql

• -d参数 备份表结构，只会有创建表结构的sql语句

mysqldump -uroot -paaaaaa -d -S /data/3306/mysql.sock dbName tableName

• -t参数 备份表数据，不会有创建表结构的sql语句

mysqldump -uroot -paaaaaa -t -S /data/3306/mysql.sock dbName tableName

-F参数 刷新二进制日志，一般是备份的时候加上这个选项，备份开始的时候将数据库二进制文件重新启动为一个文件，表明有效数据为（当前备份的数据+新的二进制文件），便于恢复

–master-data参数 备份记录点，可以导出sql语句查看，一个被注释掉了，一个没有被注释掉

具体作用后面说：http://asmboy001.blog.51cto.com/340398/197750

3、脚本实现(多实例情况)

分库备份的意义 

感觉不会用到只恢复某一个库的情况，因为备份一般是在半夜备份，即使备份的是分库，假如白天12点数据库hellodb库出错，需要恢复。但是还有其他数据库，今天的二进制日志文件中记录的都是所有库按照时间记录的语句，把二进制日志里面某一个单独库的二进制语句提取出来不容易。一般是将所有库按照二进制日志恢复到当前。所以，这里备份整个库了。

又想到假如恢复的时候数据库很大，需要的时间很多，假如所有的库都放在一个文件中，恢复数据，某一个库导入的时候，因为某个原因出错了，这时候删库，重新导入很费时间，如果将每个库单独为一个文件，哪个库出问题，只用重新导入这个库就可以，节约恢复的时间。

需要备份的数据库根据实际情况来指定，不一定就是全部都要备份，如果要备份指定列出的几个库，for循环in后面不能加引号，不然几个文件名会被当做一个文件名来处理，出错。

备份的时候要么要指定–master-data选项，要做个标记，可以从备份的sql语句中看到当前二进制文件位置。恢复的时候从哪里开始恢复。要么要指定-F选项，知道从备份这一刻起，二进制日志为新的文件。

mysqldump的执行文件路径最好看下默认的路径和自己要的路径是否相同，不同的话要指定全路径

[root@zj-dx-fzgl-37 shell]# cat mysql_back_db.sh 
#!/bin/bash
#filename mysql_back_db.sh

MYUSER=root #数据库用户
MYPASS=  #用户密码
#SOCKET=/var/lib/mysql/mysql.sock #多实例sock文件地址
IPADDR=127.0.0.1
BASEDIR=/data/mysql_backup #保存的文件地址

BAKDIR=${BASEDIR}/$(date +%Y%m%d) #保存的文件地址
MYCMD="/usr/local/mysql/bin/mysql -u${MYUSER} -p${MYPASS} -h ${IPADDR}"  #mysql连接
MYDUMP="/usr/local/mysql/bin/mysqldump -u${MYUSER} -p${MYPASS} -h ${IPADDR}" #mysqldump连接
mkdir -p $BAKDIR # 创建文件夹

fun_back() {
echo "$(date +%F_%T) start back"

for db in adxv3 adxhost dspv5 dsphost mysql performance_schema information_schema ;do
    ${MYDUMP} --flush-privileges --triggers --routines --events --hex-blob --master-data=2 -x -B $db|gzip > ${BAKDIR}/${db}_$(date +%Y%m%d).sql.gz
done

${MYCMD} -e "flush logs;"

#${MYDUMP} -A -F --flush-privileges --triggers --routines --events --hex-blob --master-data=2 -x|gzip > ${BAKDIR}/full_back_$(date +"%Y%m%d").sql.gz

echo "$(date +%F_%T) stop back"
}

scp_back(){
    echo "$(date +%F_%T) start scp"
    scp -P 2222 -r ${BAKDIR} admin@192.168.10.91:/home/mysql_backup
    [[ $? -eq 0 ]] && echo "$(date +%F_%T) scp ok"

}

fun_back >> ${BASEDIR}/back.log 2>&1
scp_back >> ${BASEDIR}/back.log 2>&1

4、利用source命令恢复数据库

进入到mysql数据库客户端，mysql -uroot -p登录后，使用source命令，后面跟脚本文件

• source all.sql # 默认的路径为msyql登录之前的系统路径，也可以通过绝对定位指定路径，如果只有某个表的数据，当前要use到目标库里面，在导入

• 利用mysql命令恢复(标准)，备份时使用了-B参数导出的情况(备份中存在建库和选库语句)，使用如下语句：

mysql -uroot -p'aaaaaa' < all.sql (无需指定库名)

• 备份时未使用-B参数导出的情况,使用如下语句：

mysql -uroot -p'aaaaaa' dbName < all.sql (必须指定库名)

5、备份恢复实验

下面从头开始模拟整个备份删库恢复的过程，比较重要，相当于消防演练，出问题解决问题的时候很关键。

[root@localhost ~]#yi mariadb-server
[root@localhost ~]#cat /etc/my.cnf
innodb_file_per_table = ON
skip_name_resolve = ON
ssl = ON
log_bin=master_bin_log	#开启二进制日志功能

[root@localhost ~]#systemctl start mariadb.service

[root@localhost ~]#mysql < hellodb.sql    #将已有的库脚本导入

mysql> insert into courses (course) values ('ansible'),('math');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
|        8 | ansible        |
|        9 | math           |
+----------+----------------+
9 rows in set (0.00 sec)

mysql> delete from courses where CourseID=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from courses;
+----------+---------------+
| CourseID | Course        |
+----------+---------------+
|        1 | Hamo Gong     |
|        3 | Jinshe Jianfa |
|        4 | Taiji Quan    |
|        5 | Daiyu Zanghua |
|        6 | Weituo Zhang  |
|        7 | Dagou Bangfa  |
|        8 | ansible       |
|        9 | math          |
+----------+---------------+
8 rows in set (0.00 sec)

上面是以前的正常操作，然后当前是半夜3点访问最少的情况，备份整个库，同时手动刷新二进制日志，相当于如果这个备份正确，之前的所有二进制日志都没有用了，如果遇到问题，从这个二进制日志开始恢复。

备份hellodb整个库，会有创建数据库的语句，下面的master-log.000027是新的二进制日志

[root@localhost ~]#mysqldump -B hellodb -F > back1.sql

[root@localhost /tmp]#ls
abc.sql            master-log.000006  master-log.000012  master-log.000018  master-log.000024
all.sql.gz         master-log.000007  master-log.000013  master-log.000019  master-log.000025
hellodb.sql        master-log.000008  master-log.000014  master-log.000020  master-log.000026
master-log.000003  master-log.000009  master-log.000015  master-log.000021  master-log.000027
master-log.000004  master-log.000010  master-log.000016  master-log.000022  master-log.index
master-log.000005  master-log.000011  master-log.000017  master-log.000023

然后下面的操作是今天发生的，假如是正常操作了一早上，然后数据库被误操作drop掉了，所有的操作都被记录到二进制日志中了。

mysql> insert into courses (course) values ('english'),('teacher');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from courses;
+----------+---------------+
| CourseID | Course        |
+----------+---------------+
|        1 | Hamo Gong     |
|       10 | english       |
|        3 | Jinshe Jianfa |
|        4 | Taiji Quan    |
|        5 | Daiyu Zanghua |
|        6 | Weituo Zhang  |
|        7 | Dagou Bangfa  |
|        8 | ansible       |
|        9 | math          |
|       11 | teacher       |
+----------+---------------+
10 rows in set (0.00 sec)

mysql> delete from courses where CourseID=5;
Query OK, 1 row affected (0.00 sec)

mysql> select * from courses;
+----------+---------------+
| CourseID | Course        |
+----------+---------------+
|        1 | Hamo Gong     |
|       10 | english       |
|        3 | Jinshe Jianfa |
|        4 | Taiji Quan    |
|        6 | Weituo Zhang  |
|        7 | Dagou Bangfa  |
|        8 | ansible       |
|        9 | math          |
|       11 | teacher       |
+----------+---------------+
9 rows in set (0.00 sec)

mysql> drop database hellodb;
Query OK, 7 rows affected (0.00 sec)

然后开始恢复

+——|——–now（二进制日志）

+——|——-now（备份的数据库）

要还原到now的状态，要用备份的数据库+从备份时开始的二进制日志文件

[root@localhost /tmp]#mysqlbinlog master-log.000027
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170602 23:03:13 server id 1  end_log_pos 106 	Start: binlog v 4, server v 5.1.73-log created 170602 23:03:13
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
MX4xWQ8BAAAAZgAAAGoAAAABAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#170602 23:05:05 server id 1  end_log_pos 134 	Intvar
SET INSERT_ID=10/*!*/;
# at 134
#170602 23:05:05 server id 1  end_log_pos 259 	Query	thread_id=11	exec_time=0	error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1496415905/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into courses (course) values ('english'),('teacher')
/*!*/;
# at 259
#170602 23:06:11 server id 1  end_log_pos 361 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1496415971/*!*/;
delete from courses where CourseID=5
/*!*/;
# at 361
#170602 23:20:25 server id 1  end_log_pos 448 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1496416825/*!*/;
drop database hellodb
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

因为二进制日志会记录所有能修改数据库的操作，所以最后一个drop database不能要，不然恢复到最后又会drop掉，将取出的内容保存在文件中，这其实就是sql脚本，可以直接导入就可以

[root@localhost /tmp]#mysqlbinlog --start-position=4 --stop-position=361 master-log.000027 > /root/binlog.sql

然后将两个sql语句依次导入即可，导入的时候会将hellodb里面原有的表清空。

mysql> set sql_log_bin=off;    #暂时将binlog功能关闭，这里恢复数据不用记录日志
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> \. /root/back1.sql    #因为这个语句中已经有了创建数据库和use到数据库的语句，这里不管在哪个库里都可以操纵
mysql> select * from courses;	#恢复到备份数据库时的内容了
+----------+---------------+
| CourseID | Course        |
+----------+---------------+
|        1 | Hamo Gong     |
|        3 | Jinshe Jianfa |
|        4 | Taiji Quan    |
|        5 | Daiyu Zanghua |
|        6 | Weituo Zhang  |
|        7 | Dagou Bangfa  |
|        8 | ansible       |
|        9 | math          |
+----------+---------------+

mysql> \. binlog.sql
mysql> select * from courses;    #恢复到drop库之前的状态
+----------+---------------+
| CourseID | Course        |
+----------+---------------+
|        1 | Hamo Gong     |
|        3 | Jinshe Jianfa |
|        4 | Taiji Quan    |
|        6 | Weituo Zhang  |
|        7 | Dagou Bangfa  |
|        8 | ansible       |
|        9 | math          |
|       10 | english       |
|       11 | teacher       |
+----------+---------------+
9 rows in set (0.00 sec)

最后将binlog功能打开，并且手动刷新一次binlog，重新启动一个文件

mysql> set sql_log_bin=on;   

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000028 |      106 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

恢复某个表

当研发人员不小心将某个表操作错了，需要还原这张表，但是其他数据不动的时候，先从备份的数据里面将这个库的sql语句拿出来，找出对应要恢复的表的开始和结束位置（用vim，每个表的开头有关键字可以查找到为第多少行），将这个表的数据重定向导出为sql语句，tail -n +10 dspv5.sql | head -n 5  > tmp.sql，将线上环境的该表备份，然后将线上的该表drop掉（注意在哪个库），小心谨慎，确认，然后将刚才导出的表数据恢复（注意在哪个库）。

6、基于lvm2的备份

就是利用lvm的快照功能来进行备份数据库文件，不常用

前提：数据目录位于逻辑卷，包含了数据文件和事务日志；

(1) 请求锁定所有表；

mysql> FLUSH TABLES WITH READ LOCK;

(2) 记录二进制文件事件位置；

mysql> FLUSH LOGS;

mysql> SHOW MASTER STATUS;

mysql  -e  'SHOW MASTER STATUS;' >> /PATH/TO/SOME_POS_FILE

(3) 创建快照卷

lvcreate  -L # -s -p r – SNAM-NAME /dev/VG-NAME/LV-NAME 

(4) 释放锁

mysql> UNLOCK TABLES

(5) 挂载快照卷，并执行备份，备份完成后删除快照卷；

(6) 周期性备份二进制日志；

MySQL备份恢复(02)

参考文档：mysqldump备份命令

–

–

–