mysql备份

查询集备份

• 查询集合备份默认只允许备份在指定文件夹
  show variables like '%secure%';
  `
• 执行备份

mysql> use hellodb;     　　　　　　//打开hellodb库
mysql> select * from students;  　　查看students的属性
mysql> select * from students where Age > 30 into outfile ‘/var/lib/mysql-files/stud.txt' ;  

• 模拟删除数据，然后恢复

mysql> delete from students where Age > 30;
mysql> load data infile '/tmp/stud.txt' into table students;

完全备份+增量备份

• 备份前需要开启mysql日志

MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的
MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语
句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据
的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作

• 查看二进制日志开启状态
  show variables like 'log_bin';
  
• 修改my.cnf，在[mysqld]下面增加

server_id=1
log_bin=mysql_bin_log

• 如果有，则查看日志指向位置
  show master status;

+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql_bin_log.000001 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+

• 然后进行完全备份

mysqldump --single-transaction  -h127.0.0.1 -uroot -p'123456' --databases qianzhang > /mysql_backup/alldatas_`date +%F`.sql

• 完全备份后对数据库创建表模拟数据操作,然后查看日志指向变化

+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql_bin_log.000001 |      500 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看日志文件位置
show variables like 'general_log_file';

+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log_file | /var/lib/mysql/bruse-X550VC.log |
+------------------+---------------------------------+

• 进行增量备份

mysqlbinlog --start-position=154 --stop-position=500 /var/lib/mysql/mysql_bin_log.000001 > /mysql_backup/zengliang_`date +$F_%H`.sql

• 添加一些数据再进行增量备份

+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql_bin_log.000001 |     1113 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+

开始进行恢复

mysql> set sql_log_bin=0;  关闭二进制日志
mysql> flush logs; 滚动下日志 

删除数据库模拟损坏
mysql> drop database hellodb;

依次导入相关sql文件

[root@www ]# mysql -u root -p'' < /mysql_backup/alldatas_2019-09-19.sql
//导入完全备份文件
[root@www ]# mysql < /backup/hellodb_2013-09-08_05.sql 
//导入增量备份文件
[root@www ]# mysql< hellodb.sql 
//导入二进制文件

最后数据恢复如初
注：

1、真正在生产环境中，我们应该导出的是整个mysql服务器中的数据，而不是单个库，因此应该使用--all-databases
2、在导出二进制日志的时候，可以直接复制文件即可，但是要注意的是，备份之前滚动下日志。
3、利用lvm快照实现几乎热备的数据备份与恢复