DBA成长之路---mysql数据备份与恢复

数据备份与恢复

备份方式： 

    物理备份：直接拷贝备份库和表对应的文件 

        cp -r /var/lib/mysql/mysql  /mysql.bak

        tar -zcvf /mysql.tar.gz  /varlib/mysql/mysql/*

    逻辑备份：执行备份时，根据已有的库和表生成对应的sql命令,把生成的sql命令存储到指定的备份文件里

备份策略：

    完全备份： 备份所有数据(表，库，服务器)

    差异备份：备份自完全备份后所新产生（备份新产生的数据）

    增量备份：备份自上一次备份后，所有新产生（备份新产生的数据）

备份数据时要考虑的问题

备份频率 备份时间 备份策略 存储空间 备份文件的命名 xx.sql

备份策略使用方式

    完全 + 增量

    完全 + 差异

执行数据备份的方式：周期性计划任务crond 执行备份脚本

完全备份数据mysqldump

    mysqldump -uroot -pabc123 库名 > 目录/名.sql

    库名的表示方式

    -B 库名1 库名2      #一起备份多各库里的所有数据

    [root@mysql4-1 admin]# mkdir /bakdir

    [root@mysql4-1 admin]# mysqldump -uroot -pabc123 mysql > /bakdir/mysql.sql

    mysqldump: [Warning] Using a password on the command line interface can be insecure.

    mysql> create database test;

    [root@mysql4-1 admin]# mysql -uroot -pabc123 test  < /bakdir/mysql.sql 

    mysql: [Warning] Using a password on the command line interface can be insecure.

增量备份与增量恢复

一，启用binlog日志 实现实时增量备份

 binlog日志介绍：又被称为二进制日志 是mysql数据库服务日志文件的一种，记录连接服务器后，执行的除查询之外的sql命令

启用binlog日志

自定义 binlog日志

查看日志当前记录格式

有三种记录格式：

 查看binlog日志内容

    mysqlbinlog binlog日志文件名

 binlog日志文件记录sql命令的方式：

什么情况下会生成新的binlog日志文件 （默认>500M后自动创建新的）

删除binlog日志文件方法

练习增量备份并恢复

二，使用第三方软件提供的命令做增量备份

percona 

 安装软件包

     yum -y install perl-DBD-mysql

     yum -y install perl-Digest-MD5

     rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 

     rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 

 命令格式

备份文件夹必须为空

要求 /var/lib/mysql/ 为空

xtrabackup只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表；

innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份。

支持事务 和 事务回滚

ls /var/lib/mysql

事务日志文件

ibdata1

LSN 日志序列号

ib_logfile1

备份过程

备份开始时首先会开启一个后台检测进程，实时检测mysql redo的变化，一旦发现redo中有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中。之后复制innodb的数据文件和系统表空间文件ibdata1，待复制结束后，执行flush tables with read lock操作，复制.frm，MYI，MYD，等文件（执行flush tableswith read lock的目的是为了防止数据表发生DDL操作，并且在这一时刻获得binlog的位置）最后会发出unlock tables，把表设置为可读可写状态，最终停止xtrabackup_log

完全备份

恢复

先恢复日志 在恢复数据

增量备份

增量恢复

--incremental-dir=目录名 

1 恢复日志文件

2 恢复数据

3 重启服务

具体操作

使用完全备份文件恢复单个表----innobackupex

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>