mysql数据库备份、恢复文档

78yuhi

说明:

为了加强线上数据库安全，避免研发人员误操作造成数据的丢失，制作本文档。一线运维人员可以参考!


一、数据备份：
专用数据库备份服务器，定时对数据库进行热备、冷备，即主从设置、mysqldump冷备、mysql-bin-log日志备份。
1.主从设置及主备切换：
2.数据库冷备：使用mysqldump进行冷备份
mysqldump -h 10.124.x.x -u user -p Passwd --all-databases > /opt/data/all_databases.sql
#mysqldump --host=x.x.x.x --port=Port --user=user --password Passwd --all-databases --events --routines --master-data=2 > dump.sql
3.bin-log日志备份：
1)开启binlog功能：
vim /etc/my.cnf
=================================
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
=================================
service mysql restart
2)binlog日志备份：
从数据库：停止slave状态：
mysql -h[从库IP] -u user -p
mysql>stop slave;
主数据库:tar czf mysql_M_binlog_$date.tar.gz /home/mysql/data/mysql-bin*
scp -P port mysql_M_binlog_$date.tar.gz user@[备份服务器]:/data/mysql/
3)利用mysqlbinlog工具进行binlog的备份工作
mysqlbinlog  --read-from-remote-server --host=x.x.x.x --port=Port --user=user --password=$passwd --result-file=/backup/ --raw --stop-never mysql-bin.000001
备注:--read-from-remote-serve参数表明要连接至该server请求其二进制日志;
--raw参数表明输出二进制格式的文件而非文本格式;
--stop-never参数使备份命令保持连接进行持续备份(默认是备份完毕接着断开);
--result-file=/backup/参数指定文件存储到指定目录下面/backup/mysql-bin.000001,日志文件名称默认是不改变的，如果使用--result-file=x则会输出xmysql-bin.000001,所以这个参数还具有修改文件前缀的功能;
4)自动化定期备份脚本:
...后续更新
二、数据恢复：
数据恢复涉及到先后顺序，并且涉及到程序写入数据的问题，所以在数据恢复的时候一定要注意。另外，mysqldump备份的sql文件只能恢复数据库指定时间段的数据，而且当数据库的结构遭到损坏的时候，数据是恢复不了的。bin-log备份恢复的时候可以精确到秒级的数据，而且整个数据库结构都是可以保留完整的。
binlog数据恢复有成‘日志回滚’,如果bin-log日志也不甚丢失，那么数据库恢复的可能性基本上不大了。所以对于bin-log日志的备份工作一定要重视起来!
1.停止程序：
该步骤访问用户调用程序往数据库中写数据，造成数据库前后出现很大的误差，或者造成数据库的结构出现问题
2.使用mysqldump的备份文件恢复:
若发生数据丢失则使用最近的备份来恢复数据
mysql --host=x.x.x.x -uuser -p -P port < dump.sql
根据dump.sql备份文件中-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=27284语句中的二进制日志文件名和位置重新执行该坐标之后的二进制日志备份文件中的事件。
3.使用mysqlbinlog工具恢复最新binlog日志数据:
若最新的二进制日志备份文件为mysql-bin.000020，如下重新执行日志事件
mysqlbinlog --start-position=27284 mysql-bin.000018 mysql-bin.000019 mysql-bin.000020| mysql --host=x.x.x.x -uuser -p -P port
4.若使用mysqlbinlog持续备份的方法，则使用下面的命令:
执行如下命令恢复到2015年7月1日13点30开始的数据库状态
mysqlbinlog  --start-date=“2015-07-01 13:30” mysqlbinlogxxx |mysql -uuser -p
执行如下命令恢复到2015年7月1日13点30截至的数据库状态
mysqlbinlog  --stop-date=“2015-07-01 13:30” mysqlbinlogxxx |mysql -uuser -p
执行如下命令恢复到截至点为368312数据库状态
mysqlbinlog --stop-position="368312" /home/mysql/mysql-bin.000020 | mysql -u root -pmypwd
执行如下命令恢复到开始点为368315数据库状态
mysqlbinlog --start-position="368315" /home/mysql/mysal-bin.000020 | mysql -u root -pmypwd