MySQL怎么备份和恢复文件

本篇内容介绍了“MySQL怎么备份和恢复文件”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！MySQL备份与恢复数据库分逻辑备份物理备份；物理备份又分冷备和热备A.直接拷贝数据文件到安全地方进行保存B.使用MYSQLHOSTCOPY备分数据C.使用MYSQLDUMP备份数据D.使用MYSQL的同步复制，实现数据实时数据同步备份1）
逻辑备份常用的逻辑备份主要就是两种：l
一种是将数据生成为可以完全重现当前数据库中的数据的insert语句l
一种是将数据通过逻辑备份软件，将数据库表的数据以特定分隔符进行分割后记录在文本中。对于第一种生成insert语句来说我们可以直接使用mysql自带的工具mysqldump来完成。这种方式不好之处在于可能导致数据的不一致，或是不完整。解决办法：一个是通过在数据库系统中加入写入锁，只提供数据库的查询服务；第二种是对于支持事务的存储引擎来说，INNODB
BDB可以通过将整个备份过程控制在一个事务中，来达到备份数据的一致性和完整性；并且可以不用影响到数据库的正常运行。如何保证数据备份的一致性？要想保证数据的一致性可以通过以下两种方法做到：第一、同一时刻取出所有数据对于事务支持的存储引擎，如Innodb或者BDB 等，可以通过控制将整个备份过程在同一个事务中，使用“–single-transaction”选项。例如：# mysqldump –single-transaction
test > test_backup.sql第二、数据库中的数据处于静止状态通过锁表参数–lock-tables 每次锁定一个数据库的表，此参数是默认为true(见上面备份内容实例)；–lock-all-tables 一次锁定所有的表，适用于dump的表分别处于各个不同的数据库中的情况恢复方式则是通过mysql

例如：mysql -uroot -p mydb1

第二种直接生成数据格式。占用的空间小，数据格式清晰。可是没有数据库结构的脚本。不容易控制

实现方式：通过select******* to
outfile from***命令来实现。

例如：mysql>
select
* into outfile ‘/tmp/tb1.txt’

->
fields terminated by ‘,’

->
optionally enclosed by ‘”‘

->
lines terminated by ‘n’ –默认

->
from tb1 limit 50;

恢复方式就是通过，load data infile和mysqlimport命令来做。

这一过程相当的复杂需要实时的进行恢复测试，保证备份数据是可用的。

2）
物理备份

数据库的物理备份，主要的对象是数据库的物理数据文件，日志文件以及配置文件等。物理数据文件有哪些呢？

一是日志文件6大类：错误日志error Log、二进制日志binary
Log、更新日志
update log（5.0后被bin
log代替）、查询日志
query log、慢查询日志
slow query log、innodb的redo日志。

二是数据文件：对于myisam来说的话，.frm表结构信息、.myd数据信息、.myi数据的索引信息。对于Innodb来说的话.ibd文件（独享表空间）和.ibdata（共享表空间）文件。

三是replication文件：master.info存储在slave端的数据目录下，存放了slave和master的相关信息，relay
log和
relay log index主要存储了I/O进程从Master端读取到的binary
log信息，然后由slave端的SQL线程从该binary
log中读取解析过的日志信息，转化成master所能执行的query语句。index则是存放binarylog的路径也就是目录文件。

四是系统文件：如my.cnf、pid文件是mysqld应用程序中的一个进程文件存放自己的进程id，还有就是socket文件它只有在linux下才有的，可以不通过tcp/ip网络协议直接连接mysql

l
如果是做冷备的话，直接复制所有的数据文件和日志文件到备份集存放的地方，

l
热备的方法针对不多的数据库有不同的方案

对于MYISAM存储引擎来说，做法就是给数据库表加锁来阻止写操作，可以直接复制物理文件，或者也可以通过mysql专门的mysqlhotcopy（原理就是现锁住表，然后进行操作）程序来完成相应的备份任务。

flush tables with read lock

cp -R test /tmp/backup/test

unlock tables;

innodb数据库引擎来说，有一款商业软件ibbackup，在线物理备份功能。还有一款开源的工具xtrabackup；

如果在备份过程中，把INNODB数据文件备份完成后，会锁住整个库，并开始复制MYISAM等非事务引擎的数据和.frm；所以如果你拥有比较多的MYISAM表，锁库的时候会持续很长。如果是在主库上运行，千万注意。

同样也只能通过xtrabackup进行增量的备份，其实这一工具只是备份innobd的日志信息。

4 xtrabackup/ibbackup

xtrabackup –backup
–datadir=/var/lib/mysql/ –target-

dir=/data/backups/mysql/

xtrabackup –backup
–defaults-file=/etc/my.cnf –target-

dir=/data/backups/mysql/

物理备份就是备份数据文件了，比较形象点就是cp下数据文件，但真正备份的时候自然不是的cp这么简单。

（1）使用 xtrabackup 工具

是一个用来备份 MySQL数据库的开源工具。

主要特点：

. 在线热备份。可以备份innodb和myisam。innodb主要应用recovery原理。myisam直接拷贝文件。

. 支持流备份。可以备份到disk，tape和remote
host。–stream=tar
./ | ssh user@remotehost cat “>”
/backup/dir/

. 支持增量备份。可以利用lsn和基础备份目录来进行增量备份。

. 支持记录slave上的master log和master position信息。

. 支持多个进程同时热备份，xtrabackup的稳定性还是挺好的。

（2）LVM

特点：热备、支持所有基于本地磁盘的存储引擎、快速备份、低开销、容易保持完整性、快速恢复等。

（3）cp + tar

使用直接拷贝数据库文件的方式进行打包备份，需要注意的是执行步骤：锁表、备份、解表。

恢复也很简单，直接拷贝到之前的数据库文件的存放目录即可。

注意：对于Innodb引擎的表来说，还需要备份日志文件，即ib_logfile*文件。因为当Innodb表损坏时，就可以依靠这些日志文件来恢复。

（4）mysqlhotcopy

mysqlhotcopy是一个perl程序，是lock tables、flush
tables 和cp或scp来快速备份数据库。

它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表文件、数据文件、索引文件）所在的机器上。

mysqlhotcopy只能用于备份MyISAM。

（5）使用mysql主从复制

mysql的复制是指将主数据库的DDL和DML操作通过二进制文件（bin-log）传送到从服务器上，然后在从服务器上对这些日志做重新执行的操作，从而使得从服务器和主服务器保持数据的同步。

3）
mysql的备份方式

1.mysqldump

效率比较低，备份和还原的速度都很慢,任何数据插入和更新操作都会被挂起

2.mysqlhotcopy

mysqlhotcopy 是专门针对myisam 数据表进行备份,备份的过程中，任何数据插入和更新操作都会被挂起

3.准备一台从服务器,专门做备份（master-slave方式）

4.xtrabackup 是 percona 的一个开源项目，可热备份innodb
,XtraDB,MyISAM(会锁表)

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表

innobackupex-1.5.1则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁

?xtra备份原理

记住开始时候的LSN号，然后开始拷备文件，同时运行一个后台进程监视重做日志，并将变化拷备下来到xtrabackup_logfile。

innobackupex可以备份myisam表和frm文件。当xtrabackup结束后，执行flush
tables with read lo开发云主机域名ck，避免数据更改，然后刷新所有myisam表到磁盘。拷备结束后，释放锁。

4）
mysqlbinlog

mysqlbinlog也是一个恢复工具，是基于时间点来处理二进制文件的

?备份：直接拷备

?还原：

时间点还原：mysqlbinlog
–stop-date=”2005-04-20 9:59:59″

/var/log/mysql/bin.123456 | mysql
-u root –pmypwd

mysqlbinlog
–start-date=”2005-04-20 10:01:00″

/var/log/mysql/bin.123456| mysql
-u root –pmypwd

?日志点还原：

mysqlbinlog
–stop-position=”368312″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

mysqlbinlog
–start-position=”368315″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

LVM是 Logical Volume Manager（逻辑卷管理）的简写，它是Linux环境下对磁盘分区进行管理的一种机制。

LSN定义：

5）
日志序号

日志序号 (LSN) 标识特定日志文件记录在日志文件中的位置。

LSN 由 DB2? 产品中的许多组件用来维护数据库一致性和完整性。除其他作用外，LSN
还对于分区数据库环境中的落实和回滚操作、崩溃和前滚恢复以及数据库操作同步起非常重要的作用。

日志文件中 LSN 的增长率与数据库活动直接相关联。也就是说，随着事务发生并且条目被写入日志文件，LSN
会不断增大。数据库中的活动越多，LSN
增长得越快。

日志序号的上限

在 DB2 V9.5 和较早版本中，日志序号 (LSN) 是 6 字节数字。从
FP3 开始，LSN
的范围在
0x0000 0000 0000（首次创建数据库时）到
0xFFFF 0000 0000（约
256 太字节）之间。在
FP3 之前，上限为
0xFFFF FFFF FFFF。随着记录被添加到日志文件中，LSN
在数据库生命期不断增长。

“MySQL怎么备份和恢复文件”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注开发云网站，小编将为大家输出更多高质量的实用文章！

例如：mysql -uroot -p mydb1

第二种直接生成数据格式。占用的空间小，数据格式清晰。可是没有数据库结构的脚本。不容易控制

实现方式：通过select******* to
outfile from***命令来实现。

例如：mysql>
select
* into outfile ‘/tmp/tb1.txt’

->
fields terminated by ‘,’

->
optionally enclosed by ‘”‘

->
lines terminated by ‘n’ –默认

->
from tb1 limit 50;

恢复方式就是通过，load data infile和mysqlimport命令来做。

这一过程相当的复杂需要实时的进行恢复测试，保证备份数据是可用的。

2）
物理备份

数据库的物理备份，主要的对象是数据库的物理数据文件，日志文件以及配置文件等。物理数据文件有哪些呢？

l
如果是做冷备的话，直接复制所有的数据文件和日志文件到备份集存放的地方，

l
热备的方法针对不多的数据库有不同的方案

flush tables with read lock

cp -R test /tmp/backup/test

unlock tables;

innodb数据库引擎来说，有一款商业软件ibbackup，在线物理备份功能。还有一款开源的工具xtrabackup；

同样也只能通过xtrabackup进行增量的备份，其实这一工具只是备份innobd的日志信息。

4 xtrabackup/ibbackup

xtrabackup –backup
–datadir=/var/lib/mysql/ –target-

dir=/data/backups/mysql/

xtrabackup –backup
–defaults-file=/etc/my.cnf –target-

dir=/data/backups/mysql/

物理备份就是备份数据文件了，比较形象点就是cp下数据文件，但真正备份的时候自然不是的cp这么简单。

（1）使用 xtrabackup 工具

是一个用来备份 MySQL数据库的开源工具。

主要特点：

. 在线热备份。可以备份innodb和myisam。innodb主要应用recovery原理。myisam直接拷贝文件。

. 支持流备份。可以备份到disk，tape和remote
host。–stream=tar
./ | ssh user@remotehost cat “>”
/backup/dir/

. 支持增量备份。可以利用lsn和基础备份目录来进行增量备份。

. 支持记录slave上的master log和master position信息。

. 支持多个进程同时热备份，xtrabackup的稳定性还是挺好的。

（2）LVM

特点：热备、支持所有基于本地磁盘的存储引擎、快速备份、低开销、容易保持完整性、快速恢复等。

（3）cp + tar

使用直接拷贝数据库文件的方式进行打包备份，需要注意的是执行步骤：锁表、备份、解表。

恢复也很简单，直接拷贝到之前的数据库文件的存放目录即可。

注意：对于Innodb引擎的表来说，还需要备份日志文件，即ib_logfile*文件。因为当Innodb表损坏时，就可以依靠这些日志文件来恢复。

（4）mysqlhotcopy

mysqlhotcopy是一个perl程序，是lock tables、flush
tables 和cp或scp来快速备份数据库。

它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表文件、数据文件、索引文件）所在的机器上。

mysqlhotcopy只能用于备份MyISAM。

（5）使用mysql主从复制

3）
mysql的备份方式

1.mysqldump

效率比较低，备份和还原的速度都很慢,任何数据插入和更新操作都会被挂起

2.mysqlhotcopy

mysqlhotcopy 是专门针对myisam 数据表进行备份,备份的过程中，任何数据插入和更新操作都会被挂起

3.准备一台从服务器,专门做备份（master-slave方式）

4.xtrabackup 是 percona 的一个开源项目，可热备份innodb
,XtraDB,MyISAM(会锁表)

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表

innobackupex-1.5.1则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁

?xtra备份原理

记住开始时候的LSN号，然后开始拷备文件，同时运行一个后台进程监视重做日志，并将变化拷备下来到xtrabackup_logfile。

innobackupex可以备份myisam表和frm文件。当xtrabackup结束后，执行flush
tables with read lock，避免数据更改，然后刷新所有myisam表到磁盘。拷备结束后，释放锁。

4）
mysqlbinlog

mysqlbinlog也是一个恢复工具，是基于时间点来处理二进制文件的

?备份：直接拷备

?还原：

时间点还原：mysqlbinlog
–stop-date=”2005-04-20 9:59:59″

/var/log/mysql/bin.123456 | mysql
-u root –pmypwd

mysqlbinlog
–start-date=”2005-04-20 10:01:00″

/var/log/mysql/bin.123456| mysql
-u root –pmypwd

?日志点还原：

mysqlbinlog
–stop-position=”368312″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

mysqlbinlog
–start-position=”368315″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

LVM是 Logical Volume Manager（逻辑卷管理）的简写，它是Linux环境下对磁盘分区进行管理的一种机制。

LSN定义：

5）
日志序号

日志序号 (LSN) 标识特定日志文件记录在日志文件中的位置。

日志序号的上限

第二种直接生成数据格式。占用的空间小，数据格式清晰。可是没有数据库结构的脚本。不容易控制实现方式：通过select******* to
outfile from***命令来实现。例如：mysql>
select
* into outfile ‘/tmp/tb1.txt’ ->
fields terminated by ‘,’ ->
optionally enclosed by ‘”‘ ->
lines terminated by ‘n’ –默认 ->
from tb1 limit 50;恢复方式就是通过，load data infile和mysqlimport命令来做。这一过程相当的复杂需要实时的进行恢复测试，保证备份数据是可用的。2）
物理备份数据库的物理备份，主要的对象是数据库的物理数据文件，日志文件以及配置文件等。物理数据文件有哪些呢？一是日志文件6大类：错误日志error Log、二进制日志binary
Log、更新日志
update log（5.0后被bin
log代替）、查询日志
query log、慢查询日志
slow query log、innodb的redo日志。二是数据文件：对于myisam来说的话，.frm表结构信息、.myd数据信息、.myi数据的索引信息。对于Innodb来说的话.ibd文件（独享表空间）和.ibdata（共享表空间）文件。三是replication文件：master.info存储在slave端的数据目录下，存放了slave和master的相关信息，relay
log和
relay log index主要存储了I/O进程从Master端读取到的binary
log信息，然后由slave端的SQL线程从该binary
log中读取解析过的日志信息，转化成master所能执行的query语句。index则是存放binarylog的路径也就是目录文件。四是系统文件：如my.cnf、pid文件是mysqld应用程序中的一个进程文件存放自己的进程id，还有就是socket文件它只有在linux下才有的，可以不通过tcp/ip网络协议直接连接mysqll
如果是做冷备的话，直接复制所有的数据文件和日志文件到备份集存放的地方，l
热备的方法针对不多的数据库有不同的方案对于MYISAM存储引擎来说，做法就是给数据库表加锁来阻止写操作，可以直接复制物理文件，或者也可以通过mysql专门的mysqlhotcopy（原理就是现锁住表，然后进行操作）程序来完成相应的备份任务。flush tables with read lockcp -R test /tmp/backup/testunlock tables;innodb数据库引擎来说，有一款商业软件ibbackup，在线物理备份功能。还有一款开源的工具xtrabackup；如果在备份过程中，把INNODB数据文件备份完成后，会锁住整个库，并开始复制MYISAM等非事务引擎的数据和.frm；所以如果你拥有比较多的MYISAM表，锁库的时候会持续很长。如果是在主库上运行，千万注意。同样也只能通过xtrabackup进行增量的备份，其实这一工具只是备份innobd的日志信息。4 xtrabackup/ibbackupxtrabackup –backup
–datadir=/var/lib/mysql/ –target-dir=/data/backups/mysql/xtrabackup –backup
–defaults-file=/etc/my.cnf –target-dir=/data/backups/mysql/物理备份就是备份数据文件了，比较形象点就是cp下数据文件，但真正备份的时候自然不是的cp这么简单。（1）使用 xtrabackup 工具是一个用来备份 MySQL数据库的开源工具。主要特点：. 在线热备份。可以备份innodb和myisam。innodb主要应用recovery原理。myisam直接拷贝文件。. 支持流备份。可以备份到disk，tape和remote
host。–stream=tar
./ | ssh user@remotehost cat “>”
/backup/dir/. 支持增量备份。可以利用lsn和基础备份目录来进行增量备份。. 支持记录slave上的master log和master position信息。. 支持多个进程同时热备份，xtrabackup的稳定性还是挺好的。（2）LVM特点：热备、支持所有基于本地磁盘的存储引擎、快速备份、低开销、容易保持完整性、快速恢复等。（3）cp + tar使用直接拷贝数据库文件的方式进行打包备份，需要注意的是执行步骤：锁表、备份、解表。恢复也很简单，直接拷贝到之前的数据库文件的存放目录即可。注意：对于Innodb引擎的表来说，还需要备份日志文件，即ib_logfile*文件。因为当Innodb表损坏时，就可以依靠这些日志文件来恢复。（4）mysqlhotcopymysqlhotcopy是一个perl程序，是lock tables、flush
tables 和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表文件、数据文件、索引文件）所在的机器上。mysqlhotcopy只能用于备份MyISAM。（5）使用mysql主从复制mysql的复制是指将主数据库的DDL和DML操作通过二进制文件（bin-log）传送到从服务器上，然后在从服务器上对这些日志做重新执行的操作，从而使得从服务器和主服务器保持数据的同步。3）
mysql的备份方式1.mysqldump效率比较低，备份和还原的速度都很慢,任何数据插入和更新操作都会被挂起2.mysqlhotcopymysqlhotcopy 是专门针对myisam 数据表进行备份,备份的过程中，任何数据插入和更新操作都会被挂起3.准备一台从服务器,专门做备份（master-slave方式）4.xtrabackup 是 percona 的一个开源项目，可热备份innodb
,XtraDB,MyISAM(会锁表)Xtrabackup有两个主要的工具：xtrabackup、innobackupexxtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表innobackupex-1.5.1则封装了xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁?xtra备份原理记住开始时候的LSN号，然后开始拷备文件，同时运行一个后台进程监视重做日志，并将变化拷备下来到xtrabackup_logfile。innobackupex可以备份myisam表和frm文件。当xtrabackup结束后，执行flush
tables with read lock，避免数据更改，然后刷新所有myisam表到磁盘。拷备结束后，释放锁。4）
mysqlbinlog
mysqlbinlog也是一个恢复工具，是基于时间点来处理二进制文件的?备份：直接拷备?还原：时间点还原：mysqlbinlog
–stop-date=”2005-04-20 9:59:59″/var/log/mysql/bin.123456 | mysql
-u root –pmypwdmysqlbinlog
–start-date=”2005-04-20 10:01:00″/var/log/mysql/bin.123456| mysql
-u root –pmypwd?日志点还原：mysqlbinlog
–stop-position=”368312″ /var/log/mysql/bin.123456 | mysql -u root -pmypwdmysqlbinlog
–start-position=”368315″ /var/log/mysql/bin.123456 | mysql -u root -pmypwd LVM是 Logical Volume Manager（逻辑卷管理）的简写，它是Linux环境下对磁盘分区进行管理的一种机制。LSN定义：5）
日志序号日志序号 (LSN) 标识特定日志文件记录在日志文件中的位置。LSN 由 DB2? 产品中的许多组件用来维护数据库一致性和完整性。除其他作用外，LSN
还对于分区数据库环境中的落实和回滚操作、崩溃和前滚恢复以及数据库操作同步起非常重要的作用。日志文件中 LSN 的增长率与数据库活动直接相关联。也就是说，随着事务发生并且条目被写入日志文件，LSN
会不断增大。数据库中的活动越多，LSN
增长得越快。日志序号的上限在 DB2 V9.5 和较早版本中，日志序号 (LSN) 是 6 字节数字。从
FP3 开始，LSN
的范围在
0x0000 0000 0000（首次创建数据库时）到
0xFFFF 0000 0000（约
256 太字节）之间。在
FP3 之前，上限为
0xFFFF FFFF FFFF。随着记录被添加到日志文件中，LSN
在数据库生命期不断增长。“MySQL怎么备份和恢复文件”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注开发云网站，小编将为大家输出更多高质量的实用文章！

相关推荐: MySQL Explain使用详解

在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explai…