mysql备份与恢复.docx
- 文档编号:5075651
- 上传时间:2022-12-13
- 格式:DOCX
- 页数:29
- 大小:749.11KB
mysql备份与恢复.docx
《mysql备份与恢复.docx》由会员分享,可在线阅读,更多相关《mysql备份与恢复.docx(29页珍藏版)》请在冰豆网上搜索。
mysql备份与恢复
在mysql.exe中执行的
SELECT*INTOOUTFILE'C:
\\log1.txt'FIELDSTERMINATEDBY','FROMlog.log1
--将刚刚导出的文件log1.txt导入到表log1相同结构的LOG2中
LOADDATAINFILE'C:
\\log1.txt'INTOTABLEaa.log2FIELDSTERMINATEDBY',';
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--使用select*intooutfile导出
SELECT*INTOOUTFILE'/tmp/fi.txt'FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'LINESTERMINATEDBY'\n'FROMtest.fi;
"2013-04-0900:
06:
56","100000198108800","EXPORT_INFO","BUY_ITEM_INNER",65,10,1004,10,"11","2",100
"2013-04-0900:
06:
59","1068029027","EXPORT_INFO","BUY_ITEM_INNER",16,7,304,7,"11","2",70
"2013-04-0900:
08:
27","100000198108800","EXPORT_INFO","BUY_ITEM_INNER",65,2,1004,2,"11","2",20
--导入
LOADDATAINFILE'/tmp/fi.txt'INTOTABLEtest.fiiFIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'LINESTERMINATEDBY'\n';
注意:
常规的,所有的记录,应该通过行来显示
例外是保存二进制数据:
Blobbinary
使用intodumpfile
select*intodumpfile'e:
/amp/six'fromteacher_classwheret_name='韩信'limit1;
总结:
FIELDSTERMINATEDBY','字段间分割符
OPTIONALLYENCLOSEDBY'"'将字段包围对数值型无效
LINESTERMINATEDBY'\n'换行符
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysqldump也有同样功能
#mysqldump-uroot-p-T/tmptestfi--fields-enclosed-by=\"--fields-terminated-by="\t
不过一般情况下mysqldump是这样用的
导出
1,备份整个数据内的表:
Mysqldump-uroot-pdb_name>bak.sql
2,备份数据库内的某张表:
mysqldump-uroot-pphp_oneteacher_class>e:
/php_one_teacher_class.sql
mysqldump-uroot-pphp_oneteacher_classtbl_name1tbl_name2tbl_name3>e:
/php_one_teacher_class.sql
导入
mysql-uroot–password=root密码数据库名<备份文件.sql
这个没命令也可以导入数据库
(mysql-e“这里可以输入sql指令”)
备份MySQL数据库的命令
mysqldump-hhostname-uusername-ppassworddatabasename>backupfile.sql
备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
mysqldump-–add-drop-table-uusername-ppassworddatabasename>backupfile.sql
直接将MySQL数据库压缩备份
mysqldump-hhostname-uusername-ppassworddatabasename|gzip>backupfile.sql.gz
备份MySQL数据库某个(些)表
mysqldump-hhostname-uusername-ppassworddatabasenamespecific_table1specific_table2>backupfile.sql
同时备份多个MySQL数据库
mysqldump-hhostname-uusername-ppassword–databasesdatabasename1databasename2databasename3>multibackupfile.sql
仅仅备份数据库结构
mysqldump–no-data–databasesdatabasename1databasename2databasename3>structurebackupfile.sql
备份服务器上所有数据库
mysqldump–all-databases>allbackupfile.sql
还原MySQL数据库的命令
mysql-hhostname-uusername-ppassworddatabasename 还原压缩的MySQL数据库 gunzip 将数据库转移到新服务器 mysqldump-uusername-ppassworddatabasename|mysql–host=*.*.*.*-Cdatabasename 增量备份: 增量备份 定义: mysql数据库会以二进制的形式,自动把用户对mysql数据库的操作,记录到文件. 当用户希望恢复的时候可以使用备份文件,进行恢复. ☞增量备份会记录(dml语句,创建表的语句,创建数据库不会记录select) ☞记录的(a.操作语句本身b.操作的时间cposition) 步骤: 1: 开启binlog日志记录 修改mysql配置文件mysql.ini(f),在[mysqld]节点下添加 #log-bin log-bin=E: /log/logbin.log 路径中不要包含中文和空格。 重启mysql服务。 通过命令行停止和启动mysql服务 c: \>netstopmysql; c: \>netstartmysql; 进入命令行进入mysql并查看二进制日志是否已经启动 Sql代码 mysql>showvariableslike'log_%'; 日志成功开启后,会在E: /log/目录下创建 logbin.index和logbin.000001两个文件。 Logbin.index索引文件,有哪些增量备份文件 logbin.000001就是数据库的备份文件, 以后就可以通过此文件对数据库进行恢复操作。 2: 查看备份的二进制文件 Sql代码 c: \mysql\bin\>mysqlbinloge: /log/logbin.000001 可以使用将日志导出文件的方式来查看日志内容 2.1导出 c: \mysql\bin\>mysqlbinloge: /log/logbin.000001>e: /log/log.txt ">": 导入到文件中;">>": 追加到文件中 如果有多个日志文件 Sql代码 c: \mysql\bin\>mysqlbinloge: /log/logbin.000001>e: /log/log.sql c: \mysql\bin\>mysqlbinloge: /log/logbin.000002>>e: /log/log.sq 2.2按指定位置导出: Sql代码 c: \mysql\bin\>mysqlbinlog--start-position=185--stop-position=338e: /log/logbin.000001>e: /log/log3.txt 2.3按指定时间导出: c: \mysql\bin\>mysqlbinlog--start-datetime="2010-01-0711: 25: 56"--stop-datetime="2010-01-0713: 23: 50"e: /log/logbin.000001>e: /log/log_by_date22.txt 3.1恢复: Sql代码 c: \mysql\bin\>mysqlbinloge: /log/logbin.000001|mysql-uroot-p 3.2按指定位置恢复: Sql代码 c: \mysql\bin\>mysqlbinlog--start-position=185--stop-position=338e: /log/logbin.000001|mysql-uroot-p 3.3按指定时间恢复: c: \mysql\bin\>mysqlbinlog--start-datetime="2010-01-0711: 25: 56"--stop-datetime="2010-01-0713: 23: 50"e: /log/logbin.000001|mysql-uroot-p 3.4通过导出的脚本文件恢复 Sql代码 c: \mysql\bin\>mysql-e"sourcee: /log/log.sql" (说明一下mysql-e“这里可以写sql命令”) 4.其他常用操作 4.1查看所有日志文件 Sql代码 mysql>showmasterlogs; 4.2当前使用的binlog文件 Sql代码 mysql>showbinlogevents\g; 4.3产生一个新的binlog日志文件 Sql代码 mysql>flushlogs; 4.4删除所有二进制日志,并从新开始记录(注意: resetmaster命令会删除所有的二进制日志) Sql代码 mysql>resetmaster; 2)PURGEMASTERLOGSTO&PURGEMASTERLOGSBEFORE 执行PURGEMASTERLOGSTO'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除 执行PURGEMASTERLOGSBEFORE'yyyy-mm-ddhh: mm: ss'命令,是将在'yyyy-mm-ddhh: mm: ss'时间之前的所有日志进行删除 3)-EXPIRE_LOGS_DAYS 此参数是设置日志的过期天数,过期的日志将会被自动删除,这有利于减少我们管理日志的工作量,需要修改f 这里我们设定保存日志为3天,3天之后过期的日志将被自动删除 4.5快速备份数据到sql文件 Sql代码 c: \mysql\bin>mysqldump-uroot-p--opt--quickinteractive>e: /log/mysqldump.sql 为了方便查看,把从脚本恢复的命令在写一次 Sql代码 c: \mysql\bin\>mysql-e"sourcee: /log/mysqldump.sql" c),根据数据库名来进行还原-d 在这里是小写的d,请不要把它和mysqldump中的-D搞混了。 哈哈。 [root@BlackGhostmysql]#/usr/local/mysql/bin/mysqlbinlog-dtest/var/lib/mysql/mysql-bin.000002 查看内容,请参考a) d),根据数据库所在IP来分-h [root@BlackGhostmysql]#/usr/local/mysql/bin/mysqlbinlog-h192.1681.102/var/lib/mysql/mysql-bin.000002 查看内容,请参考a) e),根据数据库所占用的端口来分-P 有的时候,我们的mysql用的不一定是3306端口,注意是大写的P [root@BlackGhostmysql]#/usr/local/mysql/bin/mysqlbinlog-P13306/var/lib/mysql/mysql-bin.000002 查看内容,请参考a) f),根据数据库serverid来还原–server-id 在数据库的配置文件中,都有一个serverid并且同一集群中serverid是不能相同的。 [root@BlackGhostmysql]#/usr/local/mysql/bin/mysqlbinlog–server-id=1/var/lib/mysql/mysql-bin.000002 查看内容,请参考a) 注意: 上面的几个例子,我都是一个一个说的,其实可以排列组合的。 例如 [root@BlackGhostmysql]#/usr/local/mysql/bin/mysqlbinlog–start-position=”2010-09-2918: 00: 00″-dtest-h127.0.0.1/var/lib/mysql/mysql-bin.000002|mysql-uroot-p 五,后续 增量备份的时候,有一点让人不爽,就是mysql-bin这样的文件,每启动一次mysql就会增加一些,如果你不去管他的话,时间长了,他会把你的磁盘占满。 ./mysqldump–flush-logs-urootmyblog>/tmp/myblog.sql 备份myblog数据库,清除增量备份里面的有关myblog的数据 ./mysqldump–flush-logs-uroot–all-databases>/tmp/alldatabase.sql 备份所有数据库,清除增量备份 mysql-bin.index的起索引作用,因为增量的数据不一定在一个mysql-bin000这样的文件里面,这个时候,我们就要根据mysql-bin.index来找mysql-bin这样的增量文件了。 如果mysql里面做了这样的配置binlog-do-db=test1,增量备份里面只会有test1这个数据库的数据 MySQLbinlog的其他介绍 1.前言 日志是把数据库的每一个变化都记载到一个专用的文件里,这种文件就叫做日志文件。 Mysql默认只打开出错日志,因为过多的日志将会影响系统的处理性能。 在5.0前支持文本格式和二进制格式,5.0后只支持二进制格式,因为二进制日志在性能、信息处理方面有更多的优点。 2.基础知识 2.1、二进制日志的启用 二进制日志由配置文件的log-bin选项负责启用,Mysql服务器将在数据根目录创建两个新文件XXX-bin.001和XXX-bin.index,若配置选项没有给出文件名,Mysql将使用主机名称命名这两个文件,其中.index文件包含一份全体日志文件的清单。 Mysql会把用户对所有数据库的内容和结构的修改情况记入XXX-bin.n文件,而不会记录SELECT和没有实际 2.2、更新的UPDATE语句。 日志文件的扩展 当停止或重启时,服务器会把日志文件记入下一个日志文件,Mysql会在重启时生成一个新的日志文件,文件序号递增,此外,如果日志文件超过max_binlog_size系统变量配置的上限时,也会生成新的日志文件。 2.3、日志文件的查看 Mysql提供了mysqlbinlog命令来查看日志文件,如mysqlbinlogxxx-bin.001|more。 在记录每条变更日志的时候,日志文件都会把当前时间给记录下来,以便进行数据库恢复。 2.4、日志文件的停用 可以使用SETSQL_LOG_BIN=0命令停止使用日志文件,然后可以通过SETSQL_LOG_BIN=1命令来启用。 2.5、使用日志进行数据库恢复 如果遇到灾难事件,应该用最近一次制作的完整备份恢复数据库,然后使用备份之后的日志 文件把数据库恢复到最接近现在的可用状态。 使用日志进行恢复时需要依次进行,即最早生成的日志文件要最先恢复: mysqlbinlogxxx-bin.00001|mysql-uroot-p mysqlbinlogxxx-bin.00002|mysql-uroot-p 3.日志跟换策略 使用索引来循环文件,在以下条件将循环至下一个索引 a.服务器重启 b.服务器被更新 c.日志达到了最大日志长度max_binlog_size d.日志被刷新mysql>flushlogs; 4.日志格式 从官网文档中看到,之前的MySQL一直都只有基于statement的复制模式,直到5.1.5版本的MySQL才开始支持rowlevel的复制。 从5.0开始,MySQL的复制已经解决了大量老版本中出现的无法正确复制的问题。 但是由于存储过程的出现,给MySQLReplication复制又带来了更大的新挑战。 另外,看到官方文档说,从5.1.8版本开始,MySQL提供了除StatementLevel和RowLevel之外的第三种复制模式: Mixed,实际上就前两种模式的结合。 在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。 新版本中的StatementLevel还是和以前一样,仅仅记录执行的语句。 而新版本的MySQL中对rowlevel模式也被做了优化,并不是所有的修改都会以rowlevel来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。 --基于SQL语句的复制(statement-basedreplication,SBR), --基于行的复制(row-basedreplication,RBR), --混合模式复制(mixed-basedreplication,MBR)。 静态设置binlog格式: vif log-bin=mysql-bin #binlog_format="STATEMENT" #binlog_format="ROW" binlog_format="MIXED" 动态修改binlog格式: mysql>SETSESSIONbinlog_format='STATEMENT'; mysql>SETSESSIONbinlog_format='ROW'; mysql>SETSESSIONbinlog_format='MIXED'; mysql>SETGLOBALbinlog_format='STATEMENT'; mysql>SETGLOBALbinlog_format='ROW'; mysql>SETGLOBALbinlog_format='MIXED'; 5.binarylog相关变量和参数 5.1、命令行参数 --log-bin[=file_name] 设置此参数表示启用binlog功能,并制定路径名称。 --log-bin-index[=file] 设置此参数是指定二进制索引文件的路径与名称。 --max_binlog_size Binlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时, 为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束。 --binlog-do-db=db_name 此参数表示只记录指定数据库的二进制日志 --binlog-ignore-db=db_name 此参数表示不记录指定的数据库的二进制日志 5.2、系统变量 log_bin binlog_cache_size 此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。 max_binlog_cache_size 此参数表示binlog使用的内存最大的尺寸 binlog_cache_use 使用二进制日志缓存的事务数量 binlog_cache_disk_use 使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。 binlog_do_db binlog_ignore_db sync_binlog 这个参数直接影响mysql的性能和完整性。 sync_binlog=0: 当事务提交后,Mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘,同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。 sync_binlog=0,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,通知文件系统将Binlog文件缓存刷新到磁盘。 Mysql中默认的设置是sync_binlog=0,即不做任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。 一旦系统Crash,在文件系统缓存中的所有Binlog信息都会丢失。 6.常见问题 6.1、如何清除binlog --使用下面的两个命令 PURGE{MASTER|BINARY}LOGSTO
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 备份 恢复