MySQL备份与恢复(PDF版)
MySQL备份与恢复（PDF版）
MySQL备份与恢复（PDF版）下面文章摘录的主题：mysql日志文件，使用mysqld加相应选项来启用某种日志。
Mysql 完全备份及恢复：mysqldump对MyISAM或InnoDB完全备份，mysqlhotcopy对MyISAM完全备份。
增量备份：使用二进制日志增量备份，使用mysqlbinlog命令恢复二进制日志。
SQL语法备份及恢复。
拷贝数据文件备份（对Innodb还需拷贝日志文件）。
MyISAM表的检查与修复（另见《MySql存储引擎》）。
Innodb表的碎片整理和模糊检查点。
MySQL备份和恢复作/译者：叶金荣本文讨论MySQL的备份和恢复机制，以及如何维护数据表，包括最主要的两种表类型：MyISAM和Innodb，文中设计的MySQL版本为 5.0.22。
目前MySQL支持的免费备份工具有：mysqldump、mysqlhotcopy，还可以用SQL语法进行备份：BACKUP TABLE或者SELECT INTO OUTFILE，又或者备份二进制日志（binlog），还可以是直接拷贝数据文件和相关的配置文件。
MyISAM表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。
Innodb所有的表都保存在同一个数据文件ibdata1中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份binlog，或者用mysqldump。
1、mysqldump1.1备份mysqldump是采用SQL级别的备份机制，它将数据表导成SQL 脚本文件，在不同的MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。
现在来讲一下mysqldump的一些主要参数：1.--compatible=name它告诉mysqldump，导出的数据将和哪种数据库或哪个旧版本的MySQL服务器相兼容。
值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，要使用几个值，用逗号将它们隔开。
MySQL数据库备份和恢复教程
MySQL数据库备份和恢复教程引言MySQL是一种开源的关系型数据库管理系统，被广泛应用于各种Web应用程序和服务器端开发中。
作为一种数据库管理系统，备份和恢复是非常重要的环节。
本文将为您讲解MySQL数据库备份和恢复的方法和步骤，以帮助您保护和恢复数据库中的重要数据。
一、MySQL数据库备份方法1. 手动备份手动备份是最简单的备份方法之一。
您只需要登录到MySQL服务器，使用命令行或者图形界面工具，执行相应的备份命令即可。
以下是手动备份的步骤：步骤一：登录到MySQL服务器您可以使用命令行工具，如MySQL命令行客户端或者PuTTY等，输入相应的主机名、用户名和密码。
例如：```mysql -h localhost -u root -p```步骤二：选择要备份的数据库使用以下命令选择要备份的数据库：```use database_name;```步骤三：执行备份命令使用以下命令执行备份：```mysqldump -h localhost -u root -p database_name > backup_file.sql```这将把数据库的数据和结构导出到一个名为backup_file.sql的文件中。
2. 自动备份手动备份虽然简单，但是可能会忘记执行备份操作，因此自动备份是更为常见的备份方式。
以下是几种自动备份的常见方法：a. Shell脚本您可以编写一个Shell脚本来定期执行备份操作。
脚本可以使用crontab等工具来设置定时任务，例如每天凌晨执行一次备份操作。
以下是一个使用Shell脚本的备份示例：```#!/bin/bashmysqldump -h localhost -u root -p database_name > /path/to/backup_file.sql```您可以将这个脚本保存为backup.sh，并通过crontab设置每天凌晨执行：```0 0 * * * /bin/bash /path/to/backup.sh```b. MySQL事件MySQL还提供了事件功能，可以定时执行一些SQL语句，包括备份命令。
第六章 MYSQL备份与恢复
MYSQL安装后默认仅启动错误日志（且不能被禁止），其它日志则需要通过配 安装后默认仅启动错误日志（且不能被禁止），其它日志则需要通过配 安装后默认仅启动错误日志 ）， 置服务器来启动。 的日志文件一般放在默认的数据目录中（ ）。默 置服务器来启动。MYSQL的日志文件一般放在默认的数据目录中（datadir）。默 的日志文件一般放在默认的数据目录中 ）。 认数据目录通过查看配置文件my.ini进行查看。 进行查看。 认数据目录通过查看配置文件 进行查看
第8页 页
★数据库技术与应用★ 数据库技术与应用★
MYSQL备份与恢复 第六章 MYSQL备份与恢复
MYSQL日志小结 MYSQL日志小结
日志文件 错误日志 记入文件中的信息类型 记录启动、运行或停止mysqld时出现的问题。 mysqld时出现的问题 记录启动、运行或停止mysqld时出现的问题。
第2页 页
★数据库技术与应用★ 数据库技术与应用★
MYSQL备份与恢复 第六章 MYSQL备份与恢复
MYSQL日志概述 MYSQL日志概述
MySQL日志记录了数据库工作的各种信息， MySQL日志记录了数据库工作的各种信息，以帮助数据库管理 日志记录了数据库工作的各种信息 员追踪数据库曾经发生过的各种事件， 员追踪数据库曾经发生过的各种事件，从而实现对数据库系统的各 种维护和优化。MYSQL日志类型如下表所示 日志类型如下表所示： 种维护和优化。MYSQL日志类型如下表所示：
1.2.6 ٭备份/恢复策略 备份/ 2.2.6 ٭逻辑备份和恢复 3.2.6 ٭物理备份和恢复 4.2.6 ٭表的导入导出
第10页 页
★数据库技术与应用★ 数据库技术与应用★
MYSQL备份与恢复 第六章 MYSQL备份与恢复
MySQL备份与恢复
MySQL备份与恢复xtrabackup2011-12-071.1 MySQL Workbeach备份•原理：采用mysql自带的mysqldump工具来实现。
•可以在服务器上用mysqldump –help来查看其所有参数。
•优点：简洁方便灵活快捷快捷。
•缺点：在大数据量的时候，效率很低。
汇报的标题写这里。
可以开始做汇报了。
前途无量的邦购2.1 Xtrabackup开源工具•支持在线热备与恢复•大数据量的时候，备份恢复都比较快快•支持复制模式下的从机备份恢复备份恢复•缺点：在增量备份的时候，作为备份基础的全备文件不能压缩，否则备份失效；增量的时候，表结构变更的话，变更部分备份无效。
•安装rpm版本•rpm -ivh xtrabackup-1.6-245.rhel5.x86_64.rpm2.2 压缩备份与解压•备份：innobackupex --user=root --port=3306 --defaults-file=/etc/f --no-lock --socket=/data/mbdata/open/mysql.sock --stream=tar /root/backup/full | gzip1>/root/backup/full/alldb.tar.gz (20G 2个小时)•解压缩：[root@localhost ~]# tar -izxvf alldb.tar.gz -C backup ( 25分钟)2.3 恢复•先停止数据库service mysqld stop;•(1)：innobackupex --apply-log --user=root --defaults-file=/etc/f --no-lock /root/backup •(2)：备份并删除原目录/open/mbdata/open/下所有文件•(3)：innobackupex --copy-back --user=root --defaults-file=/etc/f --no-lock /root/backup •(4)：chown -R mysql:mysql /data/mbdata/open •(5)：service mysqld start;2.4 增量备份•必须要有未压缩的全量备份文件目录•(1)：全量备份，innobackupex --user=root --port=3306 --defaults-file=/etc/f --slave-info --socket=/data/mbdata/open/mysql.sock/root/backup/full•(2)：在全量备份完毕之后，对test库的表t1添加一条记录，新增加表test.t2;•(3)：然后进行增量备份，innobackupex --defaults-file=/etc/f --no-timestamp --socket=/data/mbdata/open/mysql.sock --user=root --incremental --incremental-basedir=/root/backup/full /root/backup/daily2.5 增量恢复(1)•先全量恢复，然后增量恢复•对test库的数据test.t1删除掉前面新增加的数据，然后在test.t2表上面新加一个字段。
MySQL备份与恢复
MySQL的备份与恢复一、MySQL的备份：MySQL通过在全量备份基础上保证完整的二进制日志文件来达到增量备份的目的。
因此MySQL实现增量备份主要有以下几个步骤：1.开启二进制日志（bin-log）：mysql默认没有启二进制日志。
首先我们需要在my.ini中修改参数：在[mysqld]下添加：log-bin=log-bin这是设置开启二进制日志的参数，日志名为log-bin.index和log-bin.000001等。
默认路径为：MySQL安装目录\data。
expire_logs_day=7这是设置二进制日志过期时间的参数。
目前二进制日志在flush log时自动删除7天之前的日志。
然后重启MySQL服务。
可以在E:\MySQL Server5.5\data目录下看到“log-bin.数字编号”的文件，如log-bin.000001。
以后每次flush log时都会从当前时间开始生成新的log-bin，文件名中的数字编号依次递增。
2.MySQL的自动全量备份：这里使用mysqldump备份数据库。
mysqldump是MySQL用于转存储数据库的实用程序。
它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令，在不同的MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。
这里编写了一个批处理程序，通过任务计划定时运行来完成自动备份功能。
代码如下：MySQLBackup.bat：@echo offtitle "数据库备份程序"set MYSQLPATH=E:\"MySQL Server5.5"set DT=%date:~0,4%-%date:~5,2%-%date:~8,2% //获得当前日期if %time:~0,2% leq 9 (set hour=0%time:~1,1%) else (set hour=%time:~0,2%)//小时数前自动添0 set TM=%hour%-%time:~3,2%-%time:~6,2% //获得当前时间set USERNAME=rootset PASSWORD=google //数据库用户名和密码@echo onmkdir E:\iqe_data_backup //数据库备份的保存目录%MYSQLPATH%\bin\mysqladmin -u%USERNAME% -p%PASSWORD% flush-logs//重写日志（新建日志文件储存当前时间之后的日志，其文件名的编号自动增加）//同时删除7天前的日志%MYSQLPATH%\bin\mysqldump -u%USERNAME% iqe -p%PASSWORD% >E:\iqe_data_backup\"%DT%_%TM%_iqe".sql//备份数据库到目标路径，其文件名形式为“日期_时间_iqe.sql”目前数据库比较小，大小157M，备份时间6s。
MySQL 数据库基础与应用 第11章 备份和恢复
MySQL 数据库基础与应用
5
11.2 导出表数据和备份数据
ESCAPED BY子句用来指定转义字符，例如，“ESCAPED BY '*'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。
● LINES子句：在LINES子句中使用TERMINATED BY指定一行 结束的标志，如“LINES TERMINATED BY '?'”表示一行以“?”作为 结束标志。
语法格式：
SELECT columnist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]
其中，OPTIONS的语法格式：
FIELDS TERMINATED BY 'value' FIELDS [OPTIONALLY] ENCLOSED BY 'value' FIELDS ESCAPED BY 'value' LINES STARTING BY 'value' LINES TERMINATED BY 'value'
MySQL 数据库基础与应用
6
11.2 导出表数据和备份数据
【例11.1】将sales数据库中的goods表中数据备份到指定目录： C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/，要求字段值如果是字 符就用“”””标注，字段值之间用“,”隔开，每行以“?”为结束标志。
MySQL 数据库基础与应用
3
11.2 导出表数据和备份数据
11.2.1 导出表数据
使用SELECT…INTO OUTFILE语句可以导出表数据的文本文 件。可以使用LOAD DATA INFILE语句恢复先前导出的表数据。 但SELECT…INTO OUTFILE只能导出或导入表的数据内容，而不 能导出表结构。
MySQL数据库备份与恢复
MySQL 数据库备份与恢复MySQL 数据库备份与恢复数据备份介绍在⽣产环境中我们数据库可能会遭遇各种各样的不测从⽽导致数据丢失, ⼤概分为以下⼏种.硬件故障软件故障⾃然灾害⿊客攻击误操作 (占⽐最⼤)须知在⽣产环境中，服务器的硬件坏了可以维修或者换新，软件崩溃可以修复或重新安装, 但是如果数据没了那可就毁了，⽣产环境中最重要的应该就是数据了。
所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据。
备份什么⼀般情况下, 我们需要备份的数据分为以下⼏种⼆进制⽇志, InnoDB 事务⽇志代码(存储过程、存储函数、触发器、事件调度器)服务器配置⽂件备份的类型按照备份时数据库的运⾏状态，可以分为三种，分别是：冷备、温备、热备。
、冷备：停库、停服务来备份，即当数据库进⾏备份时, 数据库不能进⾏读写操作, 即数据库要下线。
温备：不停库、不停服务来备份，会(锁表)阻⽌⽤户的写⼊，即当数据库进⾏备份时, 数据库的读操作可以执⾏, 但是不能执⾏写操作 。
热备：不停库、不停服务来备份，也不会(锁表)阻⽌⽤户的写⼊ 即当数据库进⾏备份时, 数据库的读写操作均不是受影响 。
MySQL中进⾏不同类型的备份还要考虑存储引擎是否⽀持？逻辑备份与物理备份按照备份的内容分，可以分为两种，分别是逻辑备份与物理备份1、物理备份：直接将底层物理⽂件备份2、逻辑备份：通过特定的⼯具从数据库中导出sql 语句或者数据，可能会丢失数据精度备份⽅式之全量、差异、增量按照每次备份的数据量，可以分为全量备份、差异备份以及增量备份。
全量备份/完全备份（Full Backup ）：备份整个数据集( 即整个数据库 )部分备份：备份部分数据集(例如: 只备份⼀个表的变化)差异备份增量备份# 1、差异备份（Differential Backup ）每次备份时，都是基于第⼀次完全备份的内容，只备份有差异的数据(新增的、修改的、删除的)，例如第⼀次备份：完全备份第⼆次备份：以当前时间节点的数据为基础，备份与第⼀次备份内容的差异第三次备份：以当前时间节点的数据为基础，备份与第⼀次备份内容的差异第四次备份：以当前时间节点的数据为基础，备份与第⼀次备份内容的差异第五次备份：以当前时间节点的数据为基础，备份与第⼀次备份内容的差异。
MYSQL备份和恢复
并考虑系统可以承受的恢复时间 2.确保 mysql 打开 log-bin，mysql 才可以再必要的时候做完整恢复，或基于时间点的恢复， 或基于位置的恢复 3.要经常做备份恢复测试，确保备份是有效，并且是可以恢复的
冷备份
备份：
1.停掉 mysql 服务，再操作系统级别备份 mysql 的数据文件 2.重启 mysql 服务，备份重启以后生成的 binlog
恢复
1.方法
terminated-by=',';
mysql> load data [local] infile '/tmp/order_tab' into table order_tab fields-
2.方法
terminated-by=',';
mysqlimport -u root -p*** [--local] pointcatd order_tab.txt --fields-
单个表的备份：
备份
1.方法
from order_tab;
mysql> select * into outfile '/tmp/orader_tab' fields-terminated-by=','
2.方法
terminated-by=',';
mysqldump -u root -p*** -T /tmp pointcard orader_tab --fields-
恢复
1.停掉应用，执行 mysql 导入备份文件 mysql -u root -p**** pointcart < pointcart.sql
2.使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binlog mysqlbinlog $HOME/data/mysql-bin.123456 | mysql -u root -p***
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
下面文章摘录的主题：mysql日志文件，使用mysqld加相应选项来启用某种日志。
Mysql 完全备份及恢复：mysqldump对MyISAM或InnoDB完全备份，mysqlhotcopy对MyISAM完全备份。
增量备份：使用二进制日志增量备份，使用mysqlbinlog命令恢复二进制日志。
SQL语法备份及恢复。
拷贝数据文件备份（对Innodb还需拷贝日志文件）。
MyISAM表的检查与修复（另见《MySql存储引擎》）。
Innodb表的碎片整理和模糊检查点。
MySQL备份和恢复作/译者：叶金荣本文讨论MySQL的备份和恢复机制，以及如何维护数据表，包括最主要的两种表类型：MyISAM和Innodb，文中设计的MySQL版本为 5.0.22。
目前MySQL支持的免费备份工具有：mysqldump、mysqlhotcopy，还可以用SQL语法进行备份：BACKUP TABLE或者SELECT INTO OUTFILE，又或者备份二进制日志（binlog），还可以是直接拷贝数据文件和相关的配置文件。
MyISAM表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。
Innodb所有的表都保存在同一个数据文件ibdata1中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份binlog，或者用mysqldump。
1、mysqldump1.1备份mysqldump是采用SQL级别的备份机制，它将数据表导成SQL脚本文件，在不同的MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。
现在来讲一下mysqldump的一些主要参数：1.--compatible=name它告诉mysqldump，导出的数据将和哪种数据库或哪个旧版本的MySQL服务器相兼容。
值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，要使用几个值，用逗号将它们隔开。
当然了，它并不保证能完全兼容，而是尽量兼容。
2.--complete-insert，-c导出的数据采用包含字段名的完整INSERT方式，也就是把所有的值都写在一行。
这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
因此，需要谨慎使用该参数，至少我不推荐。
3.--default-character-set=charset指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
4.--disable-keys告诉mysqldump在INSERT语句的开头和结尾增加/*!40000ALTER TABLE table DISABLE KEYS*/;和/*!40000ALTER TABLE table ENABLE KEYS*/;语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。
该选项只适合MyISAM表。
5.--extended-insert=true|false默认情况下，mysqldump开启--complete-insert模式，因此不想用它的的话，就使用本选项，设定它的值为false即可。
6.--hex-blob使用十六进制格式导出二进制字符串字段。
如果有二进制数据就必须使用本选项。
影响到的字段类型有BINARY、VARBINARY、BLOB。
7.--lock-all-tables，-x在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。
这是一个全局读锁，并且自动关闭--single-transaction和--lock-tables选项。
8.--lock-tables它和--lock-all-tables类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。
本选项只适用于MyISAM表，如果是Innodb表可以用--single-transaction选项。
9.--no-create-info，-t只导出数据，而不添加CREATE TABLE语句。
10.--no-data，-d不导出任何数据，只导出数据库表结构。
11.--opt这只是一个快捷选项，等同于同时添加--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables--quick--set-charset选项。
本选项能让mysqldump很快的导出数据，并且导出的数据能很快导回。
该选项默认开启，但可以用--skip-opt禁用。
注意，如果运行mysqldump没有指定--quick或--opt选项，则会将整个结果集放在内存中。
如果导出大数据库的话可能会出现问题。
12.--quick，-q该选项在导出大表时很有用，它强制mysqldump从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
13.--routines，-R导出存储过程以及自定义函数。
14.--single-transaction该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
它只适用于事务表，例如InnoDB和BDB。
本选项和--lock-tables选项是互斥的，因为LOCK TABLES会使任何挂起的事务隐含提交。
要想导出大表的话，应结合使用--quick选项。
15.--triggers同时导出触发器。
该选项默认启用，用--skip-triggers禁用它。
其他参数详情请参考手册，我通常使用以下SQL来备份MyISAM表：/usr/local/mysql/bin/mysqldump-uyejr-pyejr\--default-character-set=utf8--opt--extended-insert=false\--triggers-R--hex-blob-x db_name>db_name.sql使用以下SQL来备份Innodb表：/usr/local/mysql/bin/mysqldump-uyejr-pyejr\--default-character-set=utf8--opt--extended-insert=false\--triggers-R--hex-blob--single-transaction db_name>db_name.sql另外，如果想要实现在线备份，还可以使用--master-data参数来实现，如下：/usr/local/mysql/bin/mysqldump-uyejr-pyejr\--default-character-set=utf8--opt--master-data=1\--single-transaction--flush-logs db_name>db_name.sql它只是在一开始的瞬间请求锁表，然后就刷新binlog了，而后在导出的文件中加入CHANGE MASTER语句来指定当前备份的binlog位置，如果要把这个文件恢复到slave里去，就可以采用这种方法来做。
1.2还原用mysqldump备份出来的文件是一个可以直接倒入的SQL脚本，有两种方法可以将数据导入。
1.直接用mysql客户端例如：/usr/local/mysql/bin/mysql-uyejr-pyejr db_name<db_name.sql2.用SOURCE语法其实这不是标准的SQL语法，而是mysql客户端提供的功能，例如：SOURCE/tmp/db_name.sql;这里需要指定文件的绝对路径，并且必须是mysqld运行用户(例如nobody)有权限读取的文件。
2、mysqlhotcopy2.1备份mysqlhotcopy是一个PERL程序，最初由Tim Bunce编写。
它使用LOCK TABLES、FLUSH TABLES和cp 或scp来快速备份数据库。
它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上。
mysqlhotcopy只能用于备份MyISAM，并且只能运行在类Unix和NetWare系统上。
mysqlhotcopy支持一次性拷贝多个数据库，同时还支持正则表达。
以下是几个例子：root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr\db_name/tmp(把数据库目录db_name拷贝到/tmp下)root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr\db_name_1...db_name_n/tmproot#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr\db_name./regex//tmp更详细的使用方法请查看手册，或者调用下面的命令来查看mysqlhotcopy的帮助：perldoc/usr/local/mysql/bin/mysqlhotcopy注意，想要使用mysqlhotcopy，必须要有SELECT、RELOAD(要执行FLUSH TABLES)权限，并且还必须要能够有读取datadir/db_name目录的权限。
2.2还原mysqlhotcopy备份出来的是整个数据库目录，使用时可以直接拷贝到mysqld指定的datadir(在这里是/usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：root#cp-rf db_name/usr/local/mysql/data/root#chown-R nobody:nobody/usr/local/mysql/data/(将db_name目录的属主改成mysqld运行用户)3、SQL语法备份3.1备份BACKUP TABLE语法其实和mysqlhotcopy的工作原理差不多，都是锁表，然后拷贝数据文件。
它能实现在线备份，但是效果不理想，因此不推荐使用。
它只拷贝表结构文件和数据文件，不同时拷贝索引文件，因此恢复时比较慢。
例子：BACK TABLE tbl_name TO'/tmp/db_name/';注意，必须要有FILE权限才能执行本SQL，并且目录/tmp/db_name/必须能被mysqld用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。