MySQL数据备份与恢复

42311

一、简介
    数据在当今的互联网行业中非常重要，掌握了数据能够从中发现价值，所以数据对于公司来书再重要不过了，当我们面临数据的丢失时，比如数据意外删除、自然灾害等，然而数据的备份和恢复就显得尤为重要。   
   
    二、MySQL/MariaDB数据备份与恢复

   常见的备份类型：
  根据备份时数据库是否在线：
    冷备：cold backup,服务器离线，线上业务终止，无法执行读写操作，但是此方法备份数据最为靠谱，丢数据少      热备：hot backup,服务器在线，线上业务正常进行，读写操作都可进行，此方法备份要求复杂
    温备：warn backup,服务器在线，但是对数据库施加全局锁，只读操作正常进行，不可写
    根据备份时的接口：
    物理备份：physical backup，直接从数据库的数据目录进行cp复制归档的方式
    逻辑备份：logical backup，把数据从库中提取出来保存为文本文件，主要用到的工具是mysqldump
    注意：在数据量很大时使用逻辑备份很实用，当数据量小于10G时可以考虑使用mysqldump实现逻辑备份
    根据备份的数据集：

    完全备份：full backup,指的是备份整个库

    部分备份：partial backup,指的是备份数据库中的部分数据  

    根据备份时是否备份整个 数据还是仅仅备份变化的数据

    完全备份： full backup,备份整个数据库

    增量备份：incremental backup,仅仅备份上一次完全备份之后所改变的数据做备份的为增量备份
    差异备份：differential backup,比如说周一做一次备份，到周二了就把周一和周二这两天的做一次备份，到周三就把周一周二周三的做一次备份，这就叫差异备份。比较容易恢复。
    MYISAM与InnoDB区别：

    MYISAM存储引擎不支持热备，而InnoDB支持 InnoDB记录数据时都会给数据一个序列号，所以在备份时基于MVCC(多版本并发控制)的机制自动加快照，每启动一个事务都会创建当前集的一个快照，而后基于MVCC的机制把每一个序列号都给它记录一份下来，备份时只备份序列号或序列号之前的数据，往后发生的将不做备份，如果事务的隔离级别不是特别高的话，它并不会影响事务的读写操作，而这样备份出来的数据一定是时间点一致的数据，所以要完成热备份，通常是基于事务的存储引擎才能够完成的。           
     Innobase：提供了商业备份工具为Innobackup，可以实现InnoDB的热备支持增量备份；但是对于MyISAM不支持增量备份，只能实现完全备份，属于物理备份，速度比较快。
   Xtrabackup：由Percona组织提供的开源备份工具，物理备份，速度快；

    mysqldump：常用的备份工具，也是个逻辑备份工具，用于小数据备份，一般都是在10G以下的小数据进行备份；可以使用文本进行二次处理；相当于MySQL的客户端工具   

   
    三、数据备份与恢复实战
    常见备份工具：
    mysqldump:逻辑备份工具，备份和恢复过程都比较慢
    mysqldumper:多线程，的mysqldump，很难实现差异或增量备份
    lvm-snapshot:
    接近于热备的工具，因为要事先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁。使用cp,tar等工具进行物理备份；备份和恢复速度快，很难实现增量备份，并且请求全局需要一段时间，  
    逻辑备份工具，快于mysqldump  
    Innobase:商业备份工具，innobackup
    xtrabackup:由percona提供的开源备份工具
    innodb热备，增量备份
    myisam温备，不支持增量  
    物理备份，速度快
    mysqlhotcopy:几乎冷备
    mysqldump:仅适用于数据集较小的数据库，数据量达到10G以上时建议不使用，备份太慢

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysqldump: mysqldump [options] [db_name [tbl_name ...]]    mysqldump -uroot -hlocalhost -p testdb>/tmp/testdb.sql #备份单个表 mysql -uroot -hlocalhost -p testdb</tmp/testdb.sql #恢复时要事先创建数据库 mysqldump -uroot -hlocalhost -p --databases db1 db2 db2 >/tmp/dbs.sql #使用此备份方法恢复时无需连入mysql创建数据库 mysqldump -uroot -hlocalhost -p --all-databases >/tmp/all.sql #备份所有数据库，适用于冷备 备份单库或多库：--databases    备份所有数据库：--all-databases 注意：线上备份时，在实施温备时需施加锁对单表或多表施加锁，让用户只能读而不能写 mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs>/tmp/testdb.sql mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs >/tmp/testdb1.sql #仅对Innodb存储引擎实现热备，不支持myisam引擎 mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs --master-data=2 >/tmp/testdb2.sql mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs --master-data=2 >/tmp/testdb3.sql --lock-all-tables #请求锁定所有表之后备份    --single-transaction #能够对innodb存储引擎实现热备 --flush-logs #备份时滚动日志    --master-data=[0|1|2]设置为1:保存为一个change master语句 设置为2：注释掉的change master语句，设置为0：不记录 数据恢复：建议关闭二进制日志,关闭其他用户连接：set session sql_log_bin=0

使用mysqldump实现备份及其使用二进制日志做恢复：               
使用mysqldump实现备份,用二进制日志恢复数据，这里我们以当前系统上的数据库hellodb为例：
   第一步：先把hellodb这个数据库做一次完全备份，当数据库的数据很大，比如说大于10G的话不建议使用mysqldump这个工具做备份，而使用物理备份更为合理：

1	mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --flush-logs --master-data=2 >/tmp/hellodb.sql

   第二步：在数据库hellodb中修改或创建一些表或数据，使得之前备份的数据跟现有的数据库中的数据存在差别，以完后后面的通过二进制日志进行数据恢复：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

MariaDB [(none)]> use hellodb; Database changed MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | t1                | | teachers          | | toc               | +-------------------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> drop table t1; Query OK, 0 rows affected (0.01 sec) MariaDB [hellodb]> create table tb1(id int,name varchar(20)); Query OK, 0 rows affected (0.02 sec) MariaDB [hellodb]> insert into tb1 values(1,'alren'),(2,'tbname'); Query OK, 2 rows affected (0.01 sec) Records: 2  Duplicates: 0  Warnings: 0 MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | tb1               | | teachers          | | toc               | +-------------------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> \q Bye

   第三步：使用另外一台服务器做恢复测试，将hellodb.sql,及发生改变的二进制日志导入出来拷贝至另一台主机

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

[iyunv@centos6 binlog]# mysqlbinlog --start-position=245 master-bin.000005 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #161117  6:43:00 server id 1  end_log_pos 245 Start: binlog v 4, server v5.5.32-MariaDB-log created 161117  6:43:00 # Warning: this binlog is either in use or was not closed properly. BINLOG ' 9OAsWA8BAAAA8QAAAPUAAAABAAQANS41LjMyLU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAALfrHBw== '/*!*/; # at 245 #161117  7:01:18 server id 1  end_log_pos 352 Querythread_id=23exec_time=0error_code=0 use `hellodb`/*!*/; SET TIMESTAMP=1479337278/*!*/; SET @@session.pseudo_thread_id=23/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; DROP TABLE `t1` /* generated by server */ /*!*/; # at 352 #161117  7:01:51 server id 1  end_log_pos 459 Querythread_id=23exec_time=0error_code=0 SET TIMESTAMP=1479337311/*!*/; create table tb1(id int,name varchar(20)) /*!*/; # at 459 #161117  7:02:43 server id 1  end_log_pos 530 Querythread_id=23exec_time=0error_code=0 SET TIMESTAMP=1479337363/*!*/; BEGIN /*!*/; # at 530 #161117  7:02:43 server id 1  end_log_pos 642 Querythread_id=23exec_time=0error_code=0 SET TIMESTAMP=1479337363/*!*/; insert into tb1 values(1,'alren'),(2,'tbname') /*!*/; # at 642 #161117  7:02:43 server id 1  end_log_pos 669 Xid = 1515 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [iyunv@centos6 binlog]# mysqlbinlog --start-position=245 master-bin.000005 >inre.sql

第四步：恢复数据，进入到mysql的命令行模式下或在命令行，把二进制日志关掉，恢复数据时不需要把恢复信息记录到二进制日志中去；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

MariaDB [(none)]>set sql_log_bin=0 MariaDB [hellodb]>source /root/hellodb1.sql MariaDB [hellodb]> source /root/inre.sql MariaDB [hellodb]>set sql_log_bin=1 MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes           | | coc               | | courses           | | scores            | | students          | | tb1               | | teachers          | | toc               | +-------------------+ 8 rows in set (0.00 sec)

   

如上诉结果说明数据已经恢复到原来最初始的状态，基于mysqldump+二进制日志的备份和恢复完成。