Mysql 备份

~]# mysqldump -hlocalhost -uroot -ppo –database hellodb > hellodb.sql.2 #会创建相应的数据库 , 然后再创建表

~]# mysqldump -hlocalhost -uroot -ppo –all-database > backup.sql.3 #–all-database 则会备份该数据库服务器上的所有服务器 , 此处不作展开查看

假如不小心删除了库 (此前必须要定期做备份 可以是完全备份+增量+binlog  或者是 完全备份+差异+binlog  ,  但是此处先跳过增量和差异的恢复)

         

如果只有完全备份 , 则抽取完全备份的那一刻的binlog日志时间点的binlog数据 (有增量或者差异备份的话 , 就抽取最后的增量备份或者最后的差异备份那一刻的binlog日志时间点的binlog数据)

但是由于此前备份的时候 , 使用了选项 –master-data=2 带注释的记录binlog位置 以及刷新了二进制日志

mysqldump -uroot -ppo -hlocalhost –database hellodb -R –triggers -E –master-data=1 –flush-logs > /root/hello.sql

所以只需要照出备份的时候二进制日志的名字 , 之后的二进制日志文件就行了

 此处一共有6 个二进制日志文件

 备份的文件中显示 , 备份的时候maser_log已经在master_log.000006开始记录了 , 但是要记住将drop hellodb 的误操作语句给屏蔽 

 由上图可见 , 是本分后才删除的 (加入不是备份后才删除 , 以及记录 pos位置   再使用    mysqldump –stop-position= POS 然后再重定向输出)

先登录mysql , 将二进制日志记录功能关闭 (sql文件恢复是大量写语句的操作 , 如果开启则会记录没必要记录的写操作)

 

再执行sql命令MariaDB [(none)]> \. /root/hello.sql

 

然后再按时间点恢复 , 恢复从二进制日志文件导出来的sql文件

lsn 日志序列号

innodb是使用表空间存储数据 , 而表空间自身就是一个建立在操作系统的文件系统之上的一个独立的文件系统 , 专用于保存关系型数据的文件系统 , 它拥有与操作系统的文件系统的特性。

在表空间中 , 存储引擎会事前规划好一个个的dateblock , 每一个dateblock在表空间中都是一个自制的数据块（其中元数据包每一个dateblock的括版本号，如有在其datablock中的真实数据发生变化，则会自动增加；行id号，存储引擎自建的id号，并非使用mysql客户端登陆所看到的行id号；等等） 

而日志序列号（lsn）则是记录所有数据块的版本号

Usage: [innobackupex [–defaults-file=#] –backup | innobackupex [–defaults-file=#] –prepare] [OPTIONS]

             $ innobackupex –user=USER –password=PASSWD /path/to/backup/dir/ #完全备份数据库至本地/path/to/backup/dir/ 目录下

$ innobackupex –user=USER –password=PASSWD –stream=tar ./ | bzip2 – #完全备份数据库至本地当前目录下 , 并使用zip压缩

$ xtrabackup –user=USER –password=PASSWD –backup –target-dir=/data/bkps/

(1)RELOAD  and  LOCK  TABLES (2)REPLICATION CLIENT (3)CREATE TABLESPACE (4)PROCESS (5)SUPER (6)INSERT (7)SELECT

 相应的sql语句如下 :

mysql> CREATE USER ‘bkpuser’@’localhost’ IDENTIFIED BY ‘s3cret’;

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ‘bkpuser’@’localhost’;

mysql> FLUSH PRIVILEGES;

 

运行的时候会提醒，当程序运行结束的时候，如果运行成功，则会有completed OK!的字样显示；接下来会连接服务端，检查服务端的各种信息（版本号，程序的版本号，二者是否兼容，获取innodb的各种信息）

获取innodb的各种信息：

 如果没有开启每表使用独立的表空间的话（innodb_file_per_table = ON），innobackupex的高级功能部分备份是没办法用的

记录事务日志的大小以及日志序列号（lsn）然后就开始备份

先复制事务引擎的数据库文件，最后才是非事务存储引擎的数据库文件

工作的具体流程：

           对于事务性存储引擎：先记录lsn，然后复制表空间文件，继而是数据文件，复制完之后会再次检查lsn是否发生变化，以及检查是否有数据写入binlog文件，如果有，则会同步到备份文件中，同步完的那一刻，会立即执行FLUSH TABLES WITH READ LOCK，刷写非事务性存储引擎在内存中的缓存到磁盘上和请求读锁

           对于非事务存储引擎：在事务性存储引擎结束及请求完读锁之后，就将所有有关的表（.frm表格式定义； .myd数据文件；.myi索引文件）复制到备份文件中，复制完之后会立即释放所有非事务性表读锁

 

 最后：会记录当前使用的二进制日志文件的名称及位置；报告lsn号码；及报告备份了lsn长度的日志（0-#位增量备份；#-#为完全备份）

备份后所生产的文件：

 

backup-my.cnf ： 数据还原时，指明数据格式的文件

xtrabackup补充文件：

xtrabackup_binlog_info #备份完那一刻的二进制日志文件的名字及pos位置

xtrabackup_checkpoints #备份的时候xtrabackup的检查点

xtrabackup_info #备份时xtrabackup 自身的信息

xtrabackup_logfile #二进制格式文件，xtrabackup复制的事务日志文件           

将数据文件复制到另外一台服务器上，由于是物理还原，目标主机不能启用mysql服务，以及清空数据目录，该服务器也需要安装xtrabackup程序

恢复前的准备：

在prepare这个过程中，xtrabackup使用之前复制的事务日志，将该提价的事务进行提交，将该回滚的事务进行回滚。当这个过程结束后，数据库就可以做恢复还原了。

 ~]# rm -rf /var/lib/mysql/*

 ~]# innobackupex –apply-log /root/2016-11-20_17-21-12/

成功运行完之后也会出现 completed OK ！字样 

然后再运行恢复操作，将备份目录中的数据复制回数据目录中

~]# innobackupex –copy-back /root/2016-11-20_17-21-12/

 当然，成功运行完之后也会提示completed OK !

 由于使用root账号恢复，复制完之后需要修改文件属性才能使用，而且在启动前，还需要注意的是，表空间文件的大小不再是5M了，所以还需要在my.cnf中重新定义innodb_log_file_size

如果数据从备份那一刻开始有数据发生变化时，需要从放二进制日志

到此，恢复已经完成