首页 > 代码库 > 为mysql寻找最佳备份方法和备份

为mysql寻找最佳备份方法和备份

 

 

 

 

 

 

 

 

 

一、备份

   

    需求改

    测试

 

二、事先考问题

    可以容忍失多长时间据?

    要在多长时间内完成?

    是否需要持提供服

    需要恢,整个数器?单个数?一或多表?某个语句?

 

三、备份类

    根据是否需要线

      :coldbackup,关闭mysql,或不允许读写请

      温备:warmbackup,备份的同时仅支持读请

      热备:hotbackup,备份的同业务功能不受影,需要工具和引擎支持

    根据要备份据范可分

      完全备份:fullbackup,备份全部据集,服器、等根据注点

      增量备份:incrementalbackup,上次完全备份或增量备份了的据;

      异备份:differentialbackup,上次完全备份了的

    根据备份数据或是文件,可以分

      物理备份:直接备份数据文件

      逻辑备份备份表中据和

 

四、备份对象:

    据:

    配置文件:备份/etc

    :存储过程、存触发器等;

    OS的配置文件:

    制日志:

 

例一:使用SELECT INTO OUTFILE备份复单

1

2

3

  mysql> SELECT * FROM test1 INTO OUTFILE ‘/tmp/sql1.backup‘

  mysql> DELETE FROM TABLE 

  myslq> LOAD DATA INFILE ‘/tmp/sql1.backup‘ INTO TABLE test1

 

例二:mysqldump的MySQL客端工具使用

  mysqldump可以备份器,单个或部分单个或部分表,表中某些行,存储过程,存函   触发

  能自动记录备份时的二制日志文件及相position

 

    -uusername -hhostname  -ppassword

    -B, --databasesdbname 备份指定

    -x,--lock-all-tables 定所有表

    -l,--lock-tables 单张

    --master-data=http://www.mamicode.com/[0|1|2] 0用,1用,2记录正在访问的二制日志并记录访问

    --single-transaction 基于此选项实现InnoDB热备,不需要同使用--lock-all-tables;

    -A,--all-databases;备份器上的所有

1

2

3

4

5

6

  mysql> FLUSH TABLES WITH READ LOCK;关闭所有表

  mysql> SHOW ENGINE INNODB STATUS; 看INNODB状态,确保不再有

  [root@localhost ~]# mysqldump -uroot -pmysql --databases TESTDB --lock-all-tables >         /tmp/backupdb.sql

  mysql> DROP DATABESE TESTDB;

  mysql> source /tmp/backupdb.sql

  mysql> UNLOCK TABLES; 

  注:恢复时注意前所在,如果原不存在新建use.

 

 

  myslqdump热备-->   完全备份 + 增量备份 + 制 

1

2

3

4

5

6

  # mysqldump -uroot -pmypass --single-transaction --master-data=http://www.mamicode.com/2 --all-databases >

    /backup/`date +%F`.sql     完全备份

  # mysqlbinlog --start-position=4579 --stop-position=4868 mysql-bin.000021 > 

    /backup/`date +%F_%H`.sql   增量备份

  # mysqlbinlog --start-position=4868 --stop-position=5062 mysql-bin.000021 > 

    /tmp/backup.sql   最近一次增量备份之后的制文件

 备份需要mysql服器离线

1

2

3

4

5

    mysql> SET sql_log_bin=0; 停止二制日志

    mysql> flush logs;  滚动日志

    mysql> source /backup/filename.sql   完全备份文件恢

    mysql> source /backup/filename.sql   增量备份文件恢

    mysql> source /backup/filename.sql   制文件恢

  注:也可以使用mysql -uroot -pmypass < /backup/filename.sql 入重定向恢

 

 

 

例三:使用二制日志操作

1

2

3

4

5

  mysql> SHOW {BINARY| MASTER} LOGS; 看二制和主服器日志

  mysql> SHOW MASTER STATUS;前正在使用的二制日志及前所位置

  mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000021‘ FROM 3321(指定看位置); 看日志

  mysql> FLUSH LOGS; 滚动日志,可以理解建立使用新的二制日志

  mysql> PURGE BINARY LOGS TO ‘mysql-bin.000010‘;除10以前的日志(不建议清除或备份后再     除)

  操作恢

1

2

3

  mysql> drop database test;

  [root@charce data]# mysqlbinlog mysql-bin.000021 看二制日志的操作位置

  [root@charce data]# mysqlbinlog --stop-position=3447 mysql-bin.000021 > /tmp/a.sql

1

2

3

  mysql> source /tmp/a.sql;

  mysql> SHOW DATABASES;

  mysql> SHOW TABLE test;

  注:在恢程中如果继续有二制日志入,需FLUSH LOGS之后再恢

 

 

 备份方式

  MySQL本身提供了mysqldump、mysqlbinlog备份工具,percona也提供了强大的Xtrabackup, 加上源的mydumper,有基于主同步的延迟备份从库等方式,以及基于文件系快照的备份,其实选择多到眼花缭乱。而备份本身是了恢 ,所以能够让在出故障后迅速、准确恢备份方式,就是最适合我的,然,同、省事,那就非常完美。下面就我理解的几种备份工具行 一些比,探它们各自的适用景。

   1. mysqldump& mydumper

  mysqldump是最简单逻辑备份方式。在备份myisam表的候,如果要得到一致的据,就需要表,简单而粗暴。而在备份innodb表 的候,加上–master-data=http://www.mamicode.com/1–single-transaction 选项,在事务开刻,记录下binlog pos点,然后利用mvcc来获取一致的据,由于是一个长,在入和更新量很大的上,将产生非常多的undo,著影性能,所以要用。

    2. 基于文件系的快照

  基于文件系的快照,是物理备份的一。在备份前需要行一些复杂置,在备份开得快照并记录下binlog pos点,然后采用似copy-on-write的方式,把快照转储转储快照本身消耗一定的IO源,而且在大的例上,保存被更改 的前印象也消耗IO,最现为整体性能的下降。而且服copy-on-write快照多的磁本身对资源也是一。因此这种备份方式我使用的不多。

   3.Xtrabackup

是最广泛的备份方式。percona之所以家喻户晓,Xtrabackup应该功不可它实际上是物理备份+逻辑备份合。在备份 innodb表的候,ibd文件,一刻不停的监视redo log化,append到自己的事日志文件。在拷ibd文件程中,ibd文件本身可能被花”,都不是问题,因在拷完成后的第一 prepare段,Xtrabackup采用似于innodb崩的方法,把据文件恢日志文件一致的状态把未提交的事。如果同 需要备份myisam表以及innodb表结构等文件,那就需要用flush tables with lock来获得全局始拷贝这些不再化的文件,同时获得binlog位置,拷贝结束后,也停止redo log监视

 

  由于mysql中不可避免的含有myisam表,同innobackup备份结构等文件,因此想要完整的备份mysql例,就少不了要行 flush tables with read lock,而这个语被任何查询(包括select)阻塞,在阻塞程中,又反过来阻塞任何查询(包括select)。如果备份实例上有长查询先 于flush tables with read lock行,hang住。而flush tables with read lock得全局后,查询可以行,但是仍阻塞更新,所以,我希望flush tables withread lock从发起到束,持时间越短越好。

了解决这个问题,有两种有效的方法

  1. 量不用myisam表。

  2. Xtrabackup增加了–rsync选项,通过两次rsync来减少持有全局时间

  点:在线热备,全++,支持限速,支持压缩,支持加密。

  缺点:需要取全局,如果遇到长查询,等待时间将不可控,因此要做好控,必要时杀长查询或自;遇到超大的例,备份过较长,redo log太大速度,这种下最好采用延迟备份

  4. mysqlbinlog 5.6

  上述所有的备份方式,都只能把备份的某个时间点:mysqldump和mydumper,以及snapshot是备份开始的时间 点;Xtrabackup是备份结束的时间点。要想实现point in time的恢须备份binlog。同binlog也是实现宝贵资源。

    blackhole备份binlog好的。一方面可以久的备份binlog用于恢复数,另一方面,在其上配置半同步制,可以有效防止主的binlog失。

  备份策略一:直接拷贝数文件(不推荐)

备份策略二:使用mysqlhotcopy备份数(完全备份,适合小型库备份

备份策略三:使用mysqldump备份数(完全+增量备份,适合中型库备份

备份策略四:使用主从复制机制(replication)(实现数库实时备份

 

 

考?mysqldump做的备份,是否会产生二制日志?

     mysql> SET SESSION sql_log_bin=0;

     mysql> SOURCE /path/from/somefile.sql;

     mysql> SET SESSION sql_log_bin=1;


本文出自 “正则表达式和fgrep” 博客,请务必保留此出处http://9025736.blog.51cto.com/9015736/1554657

为mysql寻找最佳备份方法和备份