美文网首页纵横研究院数据库技术专题社区
利用 mysqldump 实现数据库的备份与恢复

利用 mysqldump 实现数据库的备份与恢复

作者: 正在加载更多 | 来源:发表于2019-07-14 21:24 被阅读8次

    创建备份账号:

    使用 mysqldump 进行数据备份,得有一个至少拥有 select,reload,lock tables,replication client,show view,process 权限的账号

    • 创建一个专门用于备份账号: create user 'backup'@'localhost' identified by '123456'
    • 给该账号进行授权:grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost'

    mysqldump 常用的一些导出命令

    • mysqldump 导出整个数据库实例
      mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers --all-databases>alldatabase.sql

    • mysqldump 导出某个数据库
      mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers --databases dbName >dbName.sql

    • mysqldump 导出某个数据库下的某个表
      mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers dbName tableName >dbName_tableName.sql

    • mysqldump 使用 --tab 导出某个数据库
      mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers dbName --tab=/var/lib/mysql-files/

    • mysqldump 使用 --where 导出某个数据库下某个表中满足where条件的数据
      mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers dbName tableName --where="id=1">where.sql

    mysqldump 全备恢复

    • linux 命令行下:
      mysql -uroot -p db_name < 全备.sql
    • mysql 命令行下
      source 全备.sql
    • 对于使用 --tab 导出的数据库
      1.先使用某一个表的sql建表 source tableName.sql
      2.然后使用 load data infile 命令来导入
      load data into table tableName infile '/${path}/tableName.txt'

    mysqldump 利用bin log 实现数据的增量恢复

    前提条件:具有指定时间点前的mysqldump的全备以及既有全备到指定时间点的 MySQL 二进制日志

    • 测试数据如下


      testdata.png

    模拟过程:

    • 对backupdata数据库进行一次全备,命令如下
      mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers --databases backupdata >backupdata.sql
      backupdata.sql的文件内容如下图
      image1.png
    image2.png
    • 模拟一些数据操作
      INSERT INTO `tb_bakdata` VALUES (5,'zhang',23),(6,'ming',24),(7,'cao',23),(8,'jia',23);
      INSERT INTO `tb_bakdata2` VALUES (66,'liu',24),(77,'jia',23),(88,'yi',24);

      DELETE FROM tb_bakdata WHERE id IN (1,2);
      DELETE FROM tb_bakdata2 WHERE id IN (33,44,55);

    我们想要恢复delete操作之前的数据:
    • 先利用全备数据进行恢复
      source /home/db_backup/backupdata.sql;
    • 利用 mysqlbinlog 命令查找删除命令之前的bin log的点,结果如下图,可以看出 ,删除命令之前的 bin log 的点为 8889
      mysqlbinlog --base64-output=decode-rows -vv --start-position=8066 --database=backupdata mysql-bin.000003|grep -B3 DELETE | more

    start-position 为全备sql中的MASTER_LOG_POS 的值,database表示想要恢复那个数据库 ,mysql-bin.000003 为全备sql中 MASTER_LOG_FILE 的值

    image.png
    • 获取两个bin log 点之间的信息,并保持到文件中
      mysqlbinlog --start-position=8066 --stop-position=8889 --database=backupdata mysql-bin.000003 > backupdata_diff.sql
      -然后导入 diff.sql 文件
      mysql -uroot -p backupdata < backupdata_diff.sql

    怎样备份 bin log

    既然bin log 是如此的重要,那么怎样备份bin log 呢?
    在 MySQL5.6 之后,可以实时备份 Binlog 文件,步骤如下:

    • 创建一个用于备份的账号并赋予 replication slave 权限
      grant replication slave on *.* to 'repl'@'localhost' identified by '123456'
    • 然后执行备份命令
      mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -urepl -p123456 mysql-bin.000001

    下期预告:关于 mysqlbinlog 命令的详细介绍

    相关文章

      网友评论

        本文标题:利用 mysqldump 实现数据库的备份与恢复

        本文链接:https://www.haomeiwen.com/subject/llrikctx.html