MySQL备份恢复（生产）

备份机制

增量备份：
按天备份（中小公司）：
优点：恢复时间短、 维护成本低
缺点：占用空间大、占用系统资源多

按周备份（大公司）：
优点：占用空间小、占用系统资源少
缺点：恢复时间长、 维护成本高

一主多从。（生产环境必备）

备份用途

1、迁移或者升级数据库
2、增加从库
3、误操作数据库
4、灾备

备份恢复

锁表或者禁止写入（停止应用等）
mysqladmin flush-logs （恢复前，切割下日志。保障新数据写到切割后的日志中）
sql_log_bin（恢复时关闭，恢复后开启）

---

MySQL备份恢复和sql_log_bin参数

--备份数据库（db）
[root@mysql data]# mysqldump -S /data/3306/mysql.sock -uroot -p123456 --master-data=2 --single-transaction -F -B db|gzip >/opt/bak/mysql_backup_db_$(date +%F).sql.gz
[root@mysql bak]# gzip -d /opt/bak/mysql_backup_db_2015-11-17.sql.gz 

--查看备份后的日志开始位置
[root@mysql bak]# grep -i "change" /opt/bak/mysql_backup_db_2015-11-17.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000024', MASTER_LOG_POS=107;

--误操作，删除数据库
mysql> drop database db;

--恢复数据库
[root@mysql 3306]# mysql -S /data/3306/mysql.sock  -uroot -p123456 </opt/bak/mysql_backup_db_2015-11-17.sql 

--处理备份后操作的日志数据
[root@mysql 3306]# mysqlbinlog mysql-bin.000024 -d db >/opt/bak/bin.log

--注释掉误操作SQL，恢复日志
[root@mysql 3306]# mysql -S /data/3306/mysql.sock  -uroot -p123456 </opt/bak/bin.log 

--查看恢复数据库产生的日志信息
[root@mysql 3306]# mysqlbinlog  mysql-bin.000024 -d db > /opt/bak/bin1.log
[root@mysql 3306]# cat /opt/bak/bin1.log

--恢复数据库时关闭写日志功能
mysql> show variables like '%log_bin%';
 +---------------------------------+-------+
 | Variable_name                   | Value |
 +---------------------------------+-------+
 | log_bin                         | ON    |
 | log_bin_trust_function_creators | OFF   |
 | sql_log_bin                     | ON    |
 +---------------------------------+-------+
mysql> set  sql_log_bin = 0;
mysql> show variables like '%log_bin%';
 +---------------------------------+-------+
 | Variable_name                   | Value |
 +---------------------------------+-------+
 | log_bin                         | ON    |
 | log_bin_trust_function_creators | OFF   |
 | sql_log_bin                     | OFF   |
 +---------------------------------+-------+

--关闭写日志功能后查看恢复数据库产生的日志
[root@mysql 3306]# mysqladmin -S /data/3306/mysql.sock  -uroot -p123456 flush-logs
[root@mysql 3306]# mysql -S /data/3306/mysql.sock  -uroot -p123456 </opt/bak/mysql_backup_db_2015-11-17.sql 
[root@mysql 3306]# mysql -S /data/3306/mysql.sock  -uroot -p123456 </opt/bak/bin.log 
[root@mysql 3306]# mysqlbinlog  mysql-bin.000025 -d db > /opt/bak/bin2.log
[root@mysql 3306]# cat /opt/bak/bin2.log

--恢复完成后，开启写日志功能
mysql> set  sql_log_bin = 1;