性能资料mysql_备份恢复

1、MySQL备份恢复篇搜狐畅游mysql DBA王洪权新浪微博foreverreturnsumary 1 DBA守则 2 备份恢复原理 3 常见的备份参数 4 记一次误删除的恢复 5 xtrabackup简介DBA四条守则 1.备份重于一切 2.三思而后行 3.rm是危险的 4.你来制定规范 5 做任何重大操作的时候都要经过测试备份的原理备份软件按照最初设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关 联格式一条一条生成相关的文本文件，达到备份的目的。逻辑备份 主要有两种方法（1） 将数据生成为可以完全重现当时数据库中数据的IN

2、SERT语句；（2） 通过软件，将数据库表的数据以特定的分隔符进行分隔后记录在文本文件中。逻辑备份 生成INSERT语句备份：问题：mysqldump不停机备份时，可能有数据变更的请求产生，这样可能造成mysqldump备份出来的数据不一致，在对一致性和完整性要求比较严格的系统中，该备份可能是无效的备份。逻辑备份 生成INSERT语句备份：解决方法：（1）可以把整个备份过程控制在一个事务中；mysqldump通过-single-transaction选项支持此功能而不影响数据库的服务。（2）通过锁表：-lock-tables每次锁定一个数据库的表；逻辑备份 生成特定格式的纯文本备份数据文件：优

3、点：使用存储空间小，数据格式清晰，编辑方便；缺点：每个备份文件只能备份一个表，没有数据库结构的重建命令（不过可以通过其他方式生成）；逻辑备份 生成特定格式的纯文本备份数据文件：（1）select . from. into outfile fields terminated bylines terminated by（2）通过mysqldump，即 T选项：结构与数据分别导出备份；例如：mysqldump -u root -p -T /tmp/test backname;mysqldump：数据库备份程序mysqldump的作用：mysqldump客户端可用来转储数据库或搜集数据库进行备份或将

4、数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。mysqldumpmysqldump的调用方式：shell mysqldump options db_name tablesshell mysqldump options -database DB1 DB2 DB3.shell mysqldump options -all-databaseMysqldump的重要选项。Options(参数)说明-add-drop-database在每个CREATE DATABASE语句前添加DROP DATABASE语句-add-drop-tables在每个C

5、REATE TABLE语句前添加DROP TABLE语句-all-database转储所有数据库中的所有表，与-database相同-default-character- set=charset使用charsetas默认字符集。-database，-B转储几个数据库-quick该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中Mysqldump的重要选项Options(参数)说明-add-locks在插入数据开始的时候加上LOCK TABLES-R, -routines导出函数和存储过程-triggers导出触发器-n, -n

6、o-create-db不提供建库语句-t, -no-create-info不写重新创建每个转储表的CREATE TABLE语句-d, -no-data不提供导出数据库数据记录-T, -tab=name分别导出数据库结构和数据，生成.sql和.txt文件-tables用表格式显示输出Mysqldump的重要-选项-Options(-选项)设置选项但无=时的默认值未设置选项时的默认值-add-drop- databaseFALSE不启用-add-drop-tablesTRUE启用-all-databaseFALSE-default-character- setutf8UTF-8-databaseF

7、ALSE不启用-quickTRUE-add-locksTRUE启用Mysqldump的重要-选项-Options(-选项)设置选项但无=时的默认值未设置选项时的默认值-routinesFALSE5.0不启用，5.1启用-triggersTRUE启用-no-create-dbFALSE启用-no-create-infoFALSE不启用-no-dataFALSE不启用-tab=nameNo default value不启用mysqldump常见操作1 导出全库(包括存储过程和触发器)Mysqldump single-transaction A -triggers R all.sql2 导出test

8、库Mysqldump single-transaction B test test.sql3 导出全库的表结构Mysqldump single-transaction A -no-data all.sqlmysqldump4 导出所有的数据，不导出表结构Mysqldump single-transaction A-no-create-info all.sql5 恢复全库Mysql-f all.sql恢复单个库Mysql ftest zoo_test.sqlmore zoo_test.sql结果是怎么样的？- MySQL dump 10.11- Host: localhostDatabase:

9、zoo- Server version5.0.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 S

10、ET TIME_ZONE=+00: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 s

11、tructure 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 defau

12、lt NULL,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,dog

13、,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),(

14、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 */;/*!

15、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 on 2010-01

16、-18 0:55:11mysqldump mysqldump最常用于备份一个整个的数据库：shell mysqldump -opt db_name backup-file.sql可以这样将转储文件读回到服务器：shell mysql db_name mysqldump -opt db_name | mysql -host=remote_host -C db_name其中，-C表示压缩在客户端和服务器之间发送的所有信息mysqldump 可以用一个命令转储几个数据库：shell mysqldump -database db_name1 db_name2 . my_databases.sql如果想

17、要转储所有数据库，使用-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恢复数据 案例，早上10点的时候研发一人员误删除test库的t1表，11点的时候发现 解决思路 1 检查全备份是否正常。 2 检查binlog是否开启，且正常 3 使用mysqlbinlo

18、g恢复。use testinsert into t1 values(1)insert into t1 values(2)insert into t1 values(3)delete from t1 where id=2insert into t1 values(4)tc_cnc mysql# ll mysql-*-rw-rw- 1 mysql mysql 117 12-29 15:36 mysql-bin.000001-rw-rw- 1 mysql mysql 1010 12-29 15:38 mysql-bin.000002-rw-rw- 1 mysql mysql38 12-29 15:3

19、6 mysql-bin.indexmysqlbinlog mysql-bin.000001|grep t1tc_test_cnc mysql# mysqlbinlog mysql-bin.000002|grep i t1 use test/*!*/;delete fromt1insert into t1 values(1)insert into t1 values(2)insert into t1 values(3)delete from t1 where id=2insert into t1 values(4)# at 894#111229 15:38:58 server id 1 end_

20、log_pos 983error_code=0Querythread_id=2exec_time=0SET TIMESTAMP=1325144338/*!*/;delete from t1 where id=2/*!*/;# at 983#111229 15:38:58 server id 1 end_log_pos 1010Xid = 20COMMIT/*!*/;# at 1010#111229 15:46:16 server id 1 end_log_pos 1079error_code=0Querythread_id=3exec_time=0SET TIMESTAMP=1325144776/*!*/;BEGIN/*!*/;# at 1079#111229 15:46:16 server id 1 end_log_pos 1167error_code=0Querythread_id=3exec_time=0SET TIMESTAMP=1325144776/*!*/;insert into t1