1、,MySQL备份恢复篇,搜狐畅游mysql DBA 王洪权 新浪微博foreverreturn,sumary,1 DBA守则 2 备份恢复原理 3 常见的备份参数 4 记一次误删除的恢复 5 xtrabackup简介,DBA四条守则,1.备份重于一切 2.三思而后行 3.rm是危险的 4.你来制定规范 5 做任何重大操作的时候都要经过测试,备份的原理,备份软件按照最初设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，达到备份的目的。,逻辑备份,主要有两种方法（1）将数据生成为可以完全重现当时数据库中数据的INSERT语句；（2）
2、通过软件，将数据库表的数据以特定的分隔符进行分隔后记录在文本文件中。,逻辑备份,生成INSERT语句备份：问题：mysqldump不停机备份时，可能有数据变更的请求产生，这样可能造成mysqldump备份出来的数据不一致，在对一致性和完整性要求比较严格的系统中，该备份可能是无效的备份。,逻辑备份,生成INSERT语句备份：解决方法：（1）可以把整个备份过程控制在一个事务中；mysqldump通过-single-transaction选项支持此功能而不影响数据库的服务。（2）通过锁表：-lock-tables每次锁定一个数据库的表；,逻辑备份,生成特定格式的纯文本备份数据文件：优点：使用存储空间
3、小，数据格式清晰，编辑方便；缺点：每个备份文件只能备份一个表，没有数据库结构的重建命令（不过可以通过其他方式生成）；,逻辑备份,生成特定格式的纯文本备份数据文件：（1）select . from. into outfile fields terminated by lines terminated by（2）通过mysqldump，即 T选项：结构与数据分别导出备份；例如：mysqldump -u root -p -T /tmp/test backname;,mysqldump：数据库备份程序,mysqldump的作用：mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到
4、另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。,mysqldump,mysqldump的调用方式：shell mysqldump options db_name tablesshell mysqldump options -database DB1 DB2 DB3.shell mysqldump options -all-database,Mysqldump的重要选项,Mysqldump的重要选项,Mysqldump的重要-选项,Mysqldump的重要-选项,mysqldump,常见操作 1 导出全库(包括存储过程和触发器) Mysqldump
5、single-transaction A -triggers R all.sql 2 导出test库 Mysqldump single-transaction B test test.sql 3 导出全库的表结构 Mysqldump single-transaction A -no-data all.sql,mysqldump,4 导出所有的数据，不导出表结构 Mysqldump single-transaction A -no-create-info all.sql 5 恢复全库 Mysql -f all.sql 恢复单个库 Mysql f test test.sql,重要参数介绍,mysq
6、ldump的几个主要参数的实际工作方式。 -q 很简单，什么都不做，只是导出时加了一个 SQL_NO_CACHE 来确保不会读取缓存里的数据。 -lock-tables 跟上面类似，不过多加了一个 READ LOCAL LOCK，该锁不会阻止读，也不会阻止新的数据插入。 3. -lock-all-tables 这个就有点不太一样了，它请求发起一个全局的读锁，会阻止对所有表的写入操作，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。,重要参数介绍,4. -master-data 除了和刚才的 -lock-all-tables 多了个 SHOW MASTER STATUS 之外，没有别
7、的变化。 5. -single-transaction InnoDB 表在备份时，通常启用选项-single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据。,mysqldump,执行命令：mysqldump uroot p zoo zoo_test.sqlmore zoo_test.sql结果是怎么样的？,- MySQL dump 10.11 - - Host: localhost Database: zoo - - - Server version 5.0
8、.89-community /*!40101 SET OLD_CHARACTER_SET_CLIENT=CHARACTER_SET_CLIENT */; /*!40101 SET OLD_CHARACTER_SET_RESULTS=CHARACTER_SET_RESULTS */; /*!40101 SET OLD_COLLATION_CONNECTION=COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET OLD_TIME_ZONE=TIME_ZONE */; /*!40103 SET TIME_ZONE=+00
9、:00 */; /*!40014 SET OLD_UNIQUE_CHECKS=UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET OLD_FOREIGN_KEY_CHECKS=FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET OLD_SQL_MODE=SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; /*!40111 SET OLD_SQL_NOTES=SQL_NOTES, SQL_NOTES=0 */;,- - Table structure
10、for table pet -DROP TABLE IF EXISTS pet; /*!40101 SET saved_cs_client = character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE pet (name varchar(20) default NULL,owner varchar(20) default NULL,species varchar(20) default NULL,sex char(1) default NULL,birth date default NU
11、LL,death date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = saved_cs_client */;,- - Dumping data for table pet -LOCK TABLES pet WRITE; /*!40000 ALTER TABLE pet DISABLE KEYS */; INSERT INTO pet VALUES (Puffball,Diane,hamster,f,1999-03-30,NULL),(Bower,Benny,d
12、og,m,1989-08-31,1995-07-29),(Fluffy,Harold,cat,f,1993-02-04,NULL),(Claws,Gwen,bird,m,1994-03-17,NULL),(rold,cat,f,1,0000-00-00,NULL),(Claws,Gwen,cat,m,1994-03-17,0000-00-00),(Buffy,Harold,dog,f,1989-05-13,0000-00-00),(Fang,Benny,dog,m,1990-08-27,0000-00-00),(Bowser,Diane,dog,m,1979-08-31,1995-07-29)
13、,(Chirpy,Gwen,bird,f,1998-09-11,0000-00-00),(Slim,Benny,snake,m,1996-04-29,NULL),(,NULL,NULL,NULL,NULL,NULL); /*!40000 ALTER TABLE pet ENABLE KEYS */; UNLOCK TABLES;,/*!40103 SET TIME_ZONE=OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=OLD_FOREIGN_KEY_CHECKS
14、*/; /*!40014 SET UNIQUE_CHECKS=OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=OLD_SQL_NOTES */;- Dump completed
15、 on 2010-01-18 0:55:11,mysqldump,mysqldump最常用于备份一个整个的数据库：shell mysqldump -opt db_name backup-file.sql 可以这样将转储文件读回到服务器：shell mysql db_name backup-file.sql,mysqldump,mysqldump也可用于从一个MySQL服务器向另一个服务器复制数据时装载数据库： shell mysqldump -opt db_name | mysql -host=remote_host -C db_name其中，-C表示压缩在客户端和服务器之间发送的所有信息,m
16、ysqldump,可以用一个命令转储几个数据库：shell mysqldump -database db_name1 db_name2 . my_databases.sql 如果想要转储所有数据库，使用-all-database选项：shell mysqldump -all-databases all_databases.sql,数据恢复,纯数据文本备份的恢复，利用load命令、 Shell mysql -u root -p 数据库名 备份文件目录例如： mysqldump -uroot -p -tables zoo pet /root/est1/pet_dump.sql,通过binlog恢复
17、数据,案例，早上10点的时候研发一人员误删除test库的t1表，11点的时候发现 解决思路 1 检查全备份是否正常。 2 检查binlog是否开启，且正常 3 使用mysqlbinlog恢复。,use test insert into t1 values(1) insert into t1 values(2) insert into t1 values(3) delete from t1 where id=2 insert into t1 values(4) tc_cnc mysql# ll mysql-* -rw-rw- 1 mysql mysql 117 12-29 15:36 mysql
18、-bin.000001 -rw-rw- 1 mysql mysql 1010 12-29 15:38 mysql-bin.000002 -rw-rw- 1 mysql mysql 38 12-29 15:36 mysql-bin.index,mysqlbinlog mysql-bin.000001|grep t1 tc_test_cnc mysql# mysqlbinlog mysql-bin.000002|grep i t1 use test/*!*/; delete from t1 insert into t1 values(1) insert into t1 values(2) inse
19、rt into t1 values(3) delete from t1 where id=2 insert into t1 values(4),# at 894 #111229 15:38:58 server id 1 end_log_pos 983 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1325144338/*!*/; delete from t1 where id=2 /*!*/; # at 983 #111229 15:38:58 server id 1 end_log_pos 1010 Xid = 20 COM
20、MIT/*!*/;,# at 1010 #111229 15:46:16 server id 1 end_log_pos 1079 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1325144776/*!*/; BEGIN /*!*/; # at 1079 #111229 15:46:16 server id 1 end_log_pos 1167 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1325144776/*!*/; insert into t1 va
21、lues(4) /*!*/;,开始恢复,OK该确定的都确定了，开始恢复吧 恢复的时候记得在测试库上先测试呀，测试OK的话再把test库导入到线上。 1.mysql -f all.sql 恢复全备份。 2.依次应用binlog文件。 mysqlbinlog mysql-bin.000001|mysql 3.应用到删除表信息时候的binlog时候，指定相关的-stop-position= 894; mysqlbinlog -D -stop-position=894 mysql-bin.000002|mysql -uroot -p,4开始恢复后边的吧 mysqlbinlog -D -start-po
22、sition= 1079 mysql-bin.000002|mysql -uroot p,验证数据 select * from t1 order by id; +-+ | id | +-+ | 1 | | 2 | | 3 | | 4 | +-+,恢复,最后让我们把test库导入到线上库吧。 导出库 Mysql -B test test.sql 导入线上库 mysql test.sql 大家记得实验呀 试试这两个参数 -start-datetime= -start-position= 效果是一样的,Xtrabackup原理,1 首先拷贝日志文件线程开启，找到最后一次检查点LSN,找到后从这个位置
23、开始拷贝，开始等待拷贝，这个是实时的，因为如果日志文件时会被覆盖写的 2 拷贝所有的数据文件包括ibdata1 和所有的表文件 3 拷贝结束后，日志拷贝线程结束，产生xtrabackup_logfile文件，和xtrabackup_checkpoints 这里边记录了开始的lsn（检查点得时候）和结束的lsn,Xtrabackup原理,xtrabackup以read-write的方式打开innodb的文件，然后对其进行复制之所以是采用read-write的方式，是因为xtrabackup采用了内置的innodb库打开 文件，而innodb库打开文件的时候是rw xtraback要从文件系统中读
24、取大量的数据采用了posix_fadvise()，方式，来告诉操作系统，不要缓存读取到的数据，从而提高性能，因为这些数据不会在重用了，OS是没有这么聪明的，如果想一下，几个G的数据。会对OS的虚拟内存造成巨大的压力，其他进程，比如mysql很肯呢过会，swap出去，这样系统就受到影响了,Xtrabackup原理,在备份innodb page的时候，xtrabackup每次读写1M的数据，也就是64个页，这个不可以配置的，读1M后,xtrabackup会一个页一个页得遍历这1M的数据，通过其自己的使用innodb的buf_page_is_corrupted()函数检查此页的数据是否正常， 如果数
25、据不正常，就重新读取这一页，最多重新读取10次，如果还是失败，备份就失败了，退出。在复制transactions log的时候，每次读写512KB的数据。同样不可以配置。,备份实验,+ xtrabackup -defaults-file=/etc/f -target-dir=/data/backups/base/ -backup xtrabackup version 1.6.4 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined) xtrabackup: uses posix_fadvise(
26、). xtrabackup: cd to /home/mysql xtrabackup: Target instance is assumed as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:1024M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_s
27、ize = 268435456 111228 20:38:03 InnoDB: Warning: allocated tablespace 17, old maximum was 9,备份实验,01 Copying ./test/t1.ibd to /data/backups/base/test/t1.ibd 01 .done 01 Copying ./test/t3.ibd to /data/backups/base/test/t3.ibd 01 .done xtrabackup: The latest check point (for incremental): 473881 log sc
28、anned up to (473881) xtrabackup: Stopping log copying thread. xtrabackup: Transaction log of lsn (473881) to (473881) was copied.,备份实验,tc_cnc base# ll 总计 1049616 -rw-r-r- 1 root root 1073741824 12-28 20:38 ibdata1 drwx- 2 root root 4096 12-28 20:38 test -rw-r-r- 1 root root 75 12-28 20:38 xtrabackup_checkpoints -rw-r-r- 1 root root 2560 12-28 20:38 xtrabackup_logfile,备份实验,xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 473881(复制时候的检查点) last_lsn = 473881（结束时候时候的lsn,是日志中Log sequence number 0 2943620）,课后实验,学会mysqldump使用尝试备份和恢复操作，写成文档。 学会操作xtrabackup工具，学会全备份和增量备份,
