linux 下mysql的备份和恢复

什么时候需要对数据库进行备份：
    为了数据安全
    用于测试环境

备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。
冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。

mysql的数据库备份：
逻辑备份(热备)： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。一般在数据库正常提供服务的前提下进行的
物理备份（冷备）： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份
在线热备： AB复制  （实时备份）

全量备份：
增量备份：
差异备份：
常用的备份工具：
    1、mysqldump  逻辑备份工具 。适用中小型数据库，无法做增量备份和累计增量备份
    2、xtrabackup  物理备份工具。它是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地在线热备份。
    3、mysql enterprise backup mysql官方的备份工具；可以实现增量备份和累计增量备份

备份过程中必须考虑因素：
    1、必须制定详细的备份策略（备份频率、时间点、周期）
    2、必须做好数据恢复的演练
    每个一段时间，对备份的数据在测试环境中进行模拟恢复，保证当出现数据灾难的时候能够及时恢复数据。
    3、根据数据应用的场合、特点选择正确的备份工具。
    4、数据的一致性
        5、服务的可用性

逻辑备份（逻辑导入导出）：
逻辑数据导出（备份）：
用法：
select  xxx  into outfile '/path' from table_name; 
无论是什么存储引擎，本身是一种数据导出的方法，同时可以用来辅助备份，它可以对一个表的其中一列或者某几列做备份
逻辑数据导入（恢复）：
方法一：
load data local infile '/path/file'  into  table table_name;   
说明：该方法要求在编译数据库时要加上--enable-local-infile参数才可以使用
方法二：
# mysqlimport   dbname /path/file
它就是一个load data local infile的一个功能的打包实现

demo1：把db01库的emp表导出成文本，然后误删掉数据后进行恢复
1、导出
select * into outfile '/tmp/backup/emp.txt' from emp;
2、删除
truncate emp;
3、恢复
load data local infile '/tmp/backup/emp.txt' into table db01.emp;

# mysqlimport db01 /tmp/backup/aaa.txt -p
Enter password: 
mysqlimport: Error: 1146, Table 'db01.aaa' doesn't exist, when using table: aaa
注意：如果使用外部导入那么所备份的文件的文件名必须和表名一致

demo2：创建一个表，把你系统里的/etc/passwd导入到数据库
1、创建表password
mysql> create table password (
    -> username varchar(20),
    -> pass char(1),
    -> uid int (10),
    -> gid int (10),
    -> comment varchar(100),
    -> home_dir varchar(100),
    -> shell varchar(100)
    -> );
Query OK, 0 rows affected (0.03 sec)

2、创建password.txt文件(文件内容必须是\t分割的多列)并导入到数据库
方法一：使用sed或者awk处理成新文本
sed -i 's/:/\t/g' /tmp/passwd
方法二：直接用mysqlimport指定分隔符
# mysqlimport db01 --fields-terminated-by=':' --lines-terminated-by='\n' /tmp/mysqlbak/password -p

\n 代表linux系统回车键的行结束符号
windows默认为\r\n

总结：
通过以上例子可以看出，文本文件和数据库都可以存放数据，如果数据量大，放到数据库里要明显比文本文件里有优势。方便查询方便管理。

课堂练习：
需求：把用户登录系统的信息存放到数据库里
要求如下显示：
用户名     登录终端        来源IP


mysqldump工具备份:导出的是sql语句文件
优点：
无论是什么存储引擎，都可以用mysqldump备成sql语句
缺点:
速度较慢,导入时可能会出现格式不兼容的突发状况.无法做增量备份和累计增量备份.

提供三种级别的备份，表级，库级和全库级
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
说明：
    如果备份对象下的数据库绝大多数都是myisam类型表，为了保证数据的一致性，备份时需要锁定表：
    --lock-tables   如果备份的数据库里的表与其他库没有关系的话，那么只需要锁定该库下的表就可以了
    --lock-all-tables   如果备份的数据库里的表与其他库有关系的话，那么需要锁定整个mysql数据库的所有库下的所有表；如果是针对innodb的表进行备份由于innodb是事务型的引擎，会话与会话之间是隔离的，所以备份的时候不影响数据库的正常能够，无需锁表

表级备份：
# mysqldump -p123 db01 emp > /tmp/mysqlbak/emp.sql  备份单个表
# mysqldump -p123 db01 emp dept > /tmp/mysqlbak/emp.sql 备份多个表

表级恢复：
# mysql -p db01 </tmp/mysqlbak/emp.sql
或者在mysql数据库内使用source命令来执行外部的sql文件
mysql> source /tmp/mysqlbackup/emp.sql  

库级备份:
# mysqldump --databases db01 -p > /tmp/mysqlbak/db01.sql        备份单个库
# mysqldump --databases db01 db02 -p > /tmp/mysqlbak/db01.sql   备份多个库

库级恢复：
# mysql -p </tmp/mysqlbak/db01.sql 
mysql> source /tmp/mysqlbak/db01.sql 

全库级备份：
考虑到数据库里有innodb，也有其他类型的表，那么就只能锁表备份
# mysqldump -p --lock-tables  --all-databases > /tmp/mysqlbak/alldb.sql

全库恢复演示：     注意mysqldump的恢复速度较慢，所以数据太大需要的时间较长
rm /mysqldata56/*  -rf      这样删除数据是需要初始化的

恢复步骤：
1、初始化  
2、把数据库先启起来
3、恢复


完全备份(mysqldump)+增量备份(binlog)
适用于中小型数据库；通过结合二进制日志文件，把数据库恢复到最新的状态

二进制日志文件默认会记录下所有对数据库数据变化的操作
二进制日志文件中会记录某个操作的详细sql语句，还有执行的时候环境、时间、以及该记录在二进制日志文件的起始和结束点pos值

1、error log     错误日志    记录mysql服务端在运行时产生的错误信息，以及mysql启动和关闭时的日志信息（排错）
2、slow log      慢查询日志   慢查询时间阀值，以秒为单位，如果超过这个阀值就是慢查询  （调优）
3、bin log        二进制日志  记录对数据库增、删、改的SQL操作，可以使用这个日志做增量备份  （备份）
4、Relay log     中继日志（主从复制日志）  从机器上从主机器复制过来日志，根据日志来同步数据（复制）

查看二进制日志是否开启：
mysql> show variables like '%bin%';
...
log_bin                                 | OFF            关闭

mysql> set global log_bin=on;     不能直接更改
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable       

修改配置文件：
vim /etc/my.cnf
.....
log-bin=/var/lib/mysql/mysql56-bin.log

重启服务：
service mysql restart
再次查看开启：
mysql> show variables like '%bin%';
...
log_bin                                 | ON            开启

mysqlbinlog
--start-datetime=name   开始的时间
--stop-datetime=name    结束的时间
--start-position=#  开始的位置(POS)
--stop-position=#   结束的位置

demo1：先做全量备份，然后更新数据并误操作，数据恢复
1.备份
# mysqldump -p -S /mysql56/mysql56.sock --flush-logs --master-data=2 --all-databases > /tmp/all.sql
    --flush-logs        --备份时先将内存中日志写回磁盘，然后截断日志,并产生新的日志文件
    --master-data=2 --该选项将二进制日志的位置和文件名写入到备份文件，等于2表示CHANGE MASTER语句被写成SQL注释；1表示没有注释，默认是1.

查看完整备份文件中的字段
# vim /tmp/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000003', MASTER_LOG_POS=120;

2、更新相关数据

3.还原
# mysql -p -S /mysql56/mysql56.sock < /tmp/all2.sql 

# at 199
#160822  6:16:14 server id 1  end_log_pos 307 CRC32 0x9f22d696  Query   thread_id=6    exec_time=0      error_code=0
use `db01`/*!*/;
SET TIMESTAMP=1471817774/*!*/;
insert into t2 set id=6,name='tom'
......
# at 307
#160822  6:16:14 server id 1  end_log_pos 338 CRC32 0xb69e555d  Xid = 16541
COMMIT/*!*/;
# at 338    误操作点
#160822  6:16:33 server id 1  end_log_pos 430 CRC32 0xa371739a  Query   thread_id=6    exec_time=0      error_code=0
SET TIMESTAMP=1471817793/*!*/;
drop database db01

# mysqlbinlog --start-position=120 --stop-position=338 mysqld-bin.000003|mysql -S /mysql56/mysql56.sock -p
Enter password: 


课堂练习：使用mysqldump备份整个数据库，做一些更新后在新的机器上进行恢复
1、备份整个数据库
2、备份之后，在原有的数据库上进行一些数据更新
可以直接在论坛上发帖模拟
3、在同一台机器上重新再跑一个全新的数据库（模拟新的机器）
4、在这个全新的数据库上进行恢复


由于恢复的是整库，包括了数据库的授权信息，要让授权信息生效，必须刷新
mysql> flush privileges;
强烈建议重启数据库，这样肯定是应用了恢复后的数据进行启动

5、结合二进制让数据库恢复到最新的状态


mysql> flush privileges;

XtraBackup

https://www.percona.com/