设为首页 收藏本站
查看: 555|回复: 0

[经验分享] mysql数据库备份还原

[复制链接]

尚未签到

发表于 2018-10-7 12:40:41 | 显示全部楼层 |阅读模式
  1 简介
  1.1 增量备份简介
  增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份与前一次相比增加或者被修改的文件。这就意味着,第一次增量备份的对象是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件,如此类推。这种备份方式最显著的优点就是:没有重复的备份数据,因此备份的数据量不大,备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。您必须具有上一次全备份和所有增量备份磁带(一旦丢失或损坏其中的一个增量,就会造成恢复的失败),并且它们必须沿着从全备份到依次增量备份的时间顺序逐个反推恢复,因此这就极大地延长了恢复时间。
  假如我们有一个数据库,有20G的数据,每天会增加10M的数据,数据库每天都要全量备份一次,这样的话服务器的压力比较大,因此我们只需要备份增加的这部分数据,这样减少服务器的负担。
  1.2 binlog简介
  binlog日志由配置文件的log-bin选项负责启用,Mysql服务器将在数据根目录创建两个新文 件XXX-bin.001和xxx-bin.index,若配置选项没有给出文件名,Mysql将使用主机名称命名这两个文件,其中.index文件包含一份全体日志文件的清单。  Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件,而不会记录 SELECT和没有实际更新的UPDATE语句。
  当MySQL数据库停止或重启时,服务器会把日志文件记入下一个日志文件,Mysql会在重启时生成一个新的binlog日志文件,文件序号递增,此外,如果日志文件超过max_binlog_size系统变量配置的上限时,也会生成新的日志文件。
  
  2 binlog日志操作
  2.1 开启binlog日志
  在my.cnf或my.ini中增加
  [sql] view plain copy

  •   [mysqld]
  •   log-bin=mysql-bin
  •   binlog_format=row
  其中:log-bin若不显示指定存储目录,则默认存储在mysql的data目录下
  binlog_format的几种格式:(STATEMENT,ROW和MIXED)
  STATEMENT:基于SQL语句的复制(statement-based replication, SBR)
  ROW:基于行的复制(row-based replication, RBR)
  MIXED:混合模式复制(mixed-based replication, MBR)
  启动后会产生mysql-bin.*这样的文件,每启动一次,就会增加一个或者多个。
  [sql] view plain copy

  •   [root@localhost data]# ls -l| grep mysql-bin
  •   -rw-rw----. 1 mysql mysql       107 Jul  5 11:19 mysql-bin.000001
  •   -rw-rw----. 1 mysql mysql        19 Jul  5 11:19 mysql-bin.index
  •   [root@localhost data]#
  查看binlog开启情况
  [sql] view plain copy

  •   mysql> show variables like 'log_bin%';
  •   +---------------------------------+-------+
  •   | Variable_name                   | Value |
  •   +---------------------------------+-------+
  •   | log_bin                         | ON    |
  •   | log_bin_trust_function_creators | OFF   |
  •   +---------------------------------+-------+
  •   mysql> show variables like 'binlog%';
  •   +-----------------------------------------+-------+
  •   | Variable_name                           | Value |
  •   +-----------------------------------------+-------+
  •   | binlog_cache_size                       | 32768 |
  •   | binlog_direct_non_transactional_updates | OFF   |
  •   | binlog_format                           | ROW |
  •   | binlog_stmt_cache_size                  | 32768 |
  •   +-----------------------------------------+-------+
  2.2 查看binlog日志内容
  [sql] view plain copy

  •   [root@localhost data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 ;
  •   mysqlbinlog: unknown variable 'default-character-set=utf8'
  这里我们碰到了mysqlbinlog的一个bug,解决方法有两个:
  方法一:使用--no-defaults选项
  [sql] view plain copy

  •   [root@localhost data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001
  方法二:将my.cnf中[client]选项组中default-character-set=utf8选项临时屏蔽掉(该选项即时生效,不用重启数据库),使用完mysqlbinlog命令时在恢复。因为使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加载进内存的配置文件。
  输出格式如下:
  [sql] view plain copy

  •   # at 188
  •   #140705 11:23:55 server id 1  end_log_pos 271   Query   thread_id=1 exec_time=0 error_code=0
  •   SET TIMESTAMP=1404573835/*!*/;
  •   create database test
  •   /*!*/;
  •   DELIMITER ;
  •   # End of log file
  •   ROLLBACK /* added by mysqlbinlog */;
  •   /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  包含以下要素:
  Position:位于文件中的位置,即第一行的(#at 4)和第二行的(log_pos 4),说明该事件记录从文件第4字节开始。
  Timestamp:事件发生的时间戳,即第二行的(#070813 14:16:36)
  Exec_time:事件的执行花费时间
  Error_code:错误码
  Type 事件类型:

  Master>  Master Pos:事件在原始二进制文件中的位置
  Flags:标志信息
  2.3 一些常用操作
  [sql] view plain copy

  •   mysql> show master logs;  #查看数据库所有日志文件。
  •   mysql> show binlog events \g;  #查看当前使用的binlog文件信息。
  •   mysql> show binlog events in 'mysql-bin.000016';  #查看指定的binlog文件信息。
  •   mysql> flush logs;  #将内存中log日志写磁盘,保存在当前binlog文件中,并产生一个新的binlog日志文件。
  •   mysql> flush logs; reset master;  #删除所有二进制日志,并重新(mysql-bin.000001)开始记录。
  3 mysql备份实例分析(全备+基于binlog的增备)
  实例采用小数据量进行模拟,包含一份全备及两份增备,主要演示下备份还原过程,工程中可根据数据实际情况进行备份还原策略调整。
  3.1 查看当前数据库binlog文件
  通过mysql客户端查看
  [sql] view plain copy

  •   mysql> show master logs;
  •   +------------------+-----------+
  •   | Log_name         | File_size |
  •   +------------------+-----------+
  •   | mysql-bin.000001 |       107 |
  •   +------------------+-----------+
  通过linux命令行直接查看(mysql数据目录data)
  [plain] view plain copy

  •   [root@localhost data]# ll -h
  •   total 5.1G
  •   -rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ibdata1
  •   -rw-rw---- 1 mysql mysql 2.0G Jul 18 14:12 ibdata2
  •   -rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile0
  •   -rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile1
  •   drwxr-xr-x 2 mysql mysql 4.0K Jul 18 13:52 mysql
  •   -rw-rw---- 1 mysql mysql  107 Jul 23 13:29 mysql-bin.000001
  •   -rw-rw---- 1 mysql mysql   19 Jul 23 13:29 mysql-bin.index
  •   srwxrwxrwx 1 mysql mysql    0 Jul 18 14:14 mysql.sock
  •   drwx------ 2 mysql mysql 4.0K Jul 18 14:01 performance_schema
  •   -rw-rw---- 1 mysql mysql  483 Jul 23 13:29 R820-08.err
  •   -rw-rw---- 1 mysql mysql    5 Jul 18 14:14 R820-08.pid
  •   drwx------ 2 mysql mysql   19 Jul 22 23:15 test
  目前只有一个binlog文件mysql-bin.000001。
  3.2 准备全量数据
  [plain] view plain copy

  •   mysql> create database backup_full;
  •   mysql> create table full (c1 int(10), c2 varchar(20)) engine=innodb;
  •   mysql> insert into full values (1, 'full1'),(2, 'full2'),(3, 'full3'),(4, 'full4'),
  •   (5, 'full5'),(6, 'full6'),(7, 'full7'),(8, 'full8'),(9, 'full9'),(10, 'full10');
  3.3 将全量数据进行备份
  步骤如下:
  a.备份前需要将数据库加读锁,防止数据在备份时写入。
  [plain] view plain copy

  •   mysql> flush tables with read lock;
  b.通过命令flush logs;将log日志刷盘,写入当前binlog(mysql-bin.000001),在生成一个新的binlog(mysql-bin.000002)为增备做准备。
  [plain] view plain copy

  •   mysql> flush logs;
  c.进行数据备份。在linux命令行下执行:
  [plain] view plain copy

  •   mysqldump  -u用户名 -p密码 -hIP地址 -P端口 数据库名 > /tmp/backup_full.sql
  •   mysqldump  -uroot -pjesse -h127.0.0.1 -P3355 buckup_full > /tmp/backup_full.sql
  d.解除表锁。
  [sql] view plain copy

  •   mysql> unlock tables;
  至此全量备份全部结束,将全量数据文件buckup_full.sql保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000002)里面。
  3.4 准备第一份增量数据
  [sql] view plain copy

  •   mysql> create database backup_increment;
  •   mysql> use backup_increment;
  •   mysql> create table increment (c1 int(10), c2 varchar(20)) engine=innodb;
  •   mysql> insert into increment values (11, 'increment1'),(12, 'increment2'),(13, 'increment3'),(14, 'increment4'),(15, 'increment5');
  3.5 将第一份增量数据进行备份
  步骤如下:
  a.备份前需要将数据库加读锁,防止数据在备份时写入。
  [sql] view plain copy

  •   mysql> flush tables with read lock;
  b.通过命令flush logs;将log日志刷盘,写入当前binlog(mysql-bin.000002),在生成一个新的binlog(mysql-bin.000003)为下次增备做准备。
  [sql] view plain copy

  •   mysql> flush logs;
  c.将binlog第一个增备文件mysql-bin.000002直接复制保存即可。
  也可以将二进制文件导出到文本文件保存,在linux命令行下执行
  [sql] view plain copy

  •   mysqlbinlog  mysql-bin.000002 > /tmp/increment1.txt
  d.解除表锁。
  [sql] view plain copy

  •   mysql> unlock tables;
  至此第一个增量备份全部结束,将增量binlog文件mysql-bin.000002或者有binlog文件导出的文本文件/tmp/increment1.txt保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000003)里面。
  3.6 准备第二份增量数据
  [sql] view plain copy

  •   mysql> use backup_increment;
  •   mysql> insert into increment values (16, 'increment16'),(17, 'increment17'),(18, 'increment18'),(19, 'increment19'),(20, 'increment20');
  3.7 将第二份增量数据进行备份
  步骤如下:
  a.备份前需要将数据库加读锁,防止数据在备份时写入。
  [sql] view plain copy

  •   mysql> flush tables with read lock;
  b.通过命令flush logs;将log日志刷盘,写入当前binlog(mysql-bin.000003),在生成一个新的binlog(mysql-bin.000004)为下次增备做准备。
  [sql] view plain copy

  •   mysql> flush logs;
  c.将binlog第一个增备文件mysql-bin.000003直接复制保存即可。
  也可以将二进制文件导出到文本文件保存,在linux命令行下执行
  [sql] view plain copy

  •   mysqlbinlog  mysql-bin.000003 > /tmp/increment2.txt
  d.解除表锁。
  [sql] view plain copy

  •   mysql> unlock tables;
  至此第二个增量备份全部结束,将增量binlog文件mysql-bin.000003或者有binlog文件导出的文本文件/tmp/increment2.txt保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000004)里面。
  
  4 mysql还原实例分析(全备还原+基于binlog的增备还原)
  模拟数据库故障,即删除全备数据及增备数据库。
  [sql] view plain copy

  •   mysql> drop table backup_full.full;
  •   mysql> drop database backup_increment;
  此时数据库数据被清空。
  [sql] view plain copy

  •   mysql> show databases;
  •   +--------------------+
  •   | Database           |
  •   +--------------------+
  •   | information_schema |
  •   | backup_full        |
  •   | mysql              |
  •   | performance_schema |
  •   | test               |
  •   +--------------------+
  •   5 rows in set (0.00 sec)

  •   mysql> select * from backup_full.full;
  •   ERROR 1146 (42S02): Table 'backup_full.full' doesn't exist
  •   mysql> select * from backup_increment.increment;
  •   ERROR 1146 (42S02): Table 'backup_increment.increment' doesn't exist
  4.1 还原全备数据
  方法1:进入数据库,通过source
  [sql] view plain copy

  •   mysql> use backup_full;
  •   mysql> source /tmp/backup_full.sql;
  •   mysql> select * from backup_full.full;
  •   +------+--------+
  •   | c1   | c2     |
  •   +------+--------+
  •   |    1 | full1  |
  •   |    2 | full2  |
  •   |    3 | full3  |
  •   |    4 | full4  |
  •   |    5 | full5  |
  •   |    6 | full6  |
  •   |    7 | full7  |
  •   |    8 | full8  |
  •   |    9 | full9  |
  •   |   10 | full10 |
  •   +------+--------+
  全量数据还原成功
  方法2:直接还原数据文件。
  [sql] view plain copy

  •   mysql -u用户名 -p密码  -hIP地址    -P端口 数据库名     < /tmp/buckup_full.sql
  •   mysql -uroot   -pjesse -h127.0.0.1 -P3355 backup_full  < /tmp/buckup_full.sql
  •   mysql> select * from backup_full.full;
  •   +------+--------+
  •   | c1   | c2     |
  •   +------+--------+
  •   |    1 | full1  |
  •   |    2 | full2  |
  •   |    3 | full3  |
  •   |    4 | full4  |
  •   |    5 | full5  |
  •   |    6 | full6  |
  •   |    7 | full7  |
  •   |    8 | full8  |
  •   |    9 | full9  |
  •   |   10 | full10 |
  •   +------+--------+
  4.2 还原第一个增备文件
  方法一:通过文本文件还原
  [sql] view plain copy

  •   mysql> source /tmp/increment1.txt;
  •   mysql> select * from backup_increment.increment;
  •   +------+------------+
  •   | c1   | c2         |
  •   +------+------------+
  •   |   11 | increment1 |
  •   |   12 | increment2 |
  •   |   13 | increment3 |
  •   |   14 | increment4 |
  •   |   15 | increment5 |
  •   +------+------------+
  方法二:通过binlog直接还原
  在linux命令行下执行:
  [sql] view plain copy

  •   mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口
  •   mysqlbinlog  mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3355
  查看数据:
  [sql] view plain copy

  •   mysql> select * from backup_increment.increment;
  •   +------+------------+
  •   | c1   | c2         |
  •   +------+------------+
  •   |   11 | increment1 |
  •   |   12 | increment2 |
  •   |   13 | increment3 |
  •   |   14 | increment4 |
  •   |   15 | increment5 |
  •   +------+------------+
  第一份增量数据还原成功!
  4.3 还原第二个增备文件(方法同上)
  在linux命令行下执行:
  [sql] view plain copy

  •   mysqlbinlog  binlog文件名 | mysql -u用户名 -p密码 -hIP地址 -P端口
  •   mysqlbinlog  mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355
  查看数据:
  [sql] view plain copy

  •   mysql> select * from backup_increment.increment;
  •   +------+-------------+
  •   | c1   | c2          |
  •   +------+-------------+
  •   |   11 | increment1  |
  •   |   12 | increment2  |
  •   |   13 | increment3  |
  •   |   14 | increment4  |
  •   |   15 | increment5  |
  •   |   16 | increment16 |
  •   |   17 | increment17 |
  •   |   18 | increment18 |
  •   |   19 | increment19 |
  •   |   20 | increment20 |
  •   +------+-------------+
  至此数据全部还原成功!
  ****************************************************************************************
  原文地址:http://blog.csdn.net/jesseyoung/article/details/37106035
  博客主页:http://blog.csdn.net/jesseyoung
  ****************************************************************************************


运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-614344-1-1.html 上篇帖子: Linux彻底删除mysql5.6 下篇帖子: python3 安装mysql-client 报错 mysql_config not found (py3 centos7)
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表