MySQL备份和恢复具体实施(上)

古城微笑少年丶 2022-05-14 08:51 78阅读 0赞

##     ##

*MySQL备份类别，参考笔者上篇文章[MySQL备份类别][MySQL]*

## **冷备：** ##

对于InnoDB存储引擎的冷备非常简单，只需要备份MySQL数据库的frm文件、共享表空间文件、独立表空间文件（\*.ibd）、重做日志文件。--建议定期备份MySQL数据库的配置文件my.cnf

**冷备的优点：**

a) 备份简单，只要拷贝相关文件

b) 备份文件易于在不同操作系统，不同MySQL版本进行恢复

c) 恢复相当简单，只需要把文件恢复到指定位置即可

d) 恢复速度快，不需要执行任何SQL语句，也不需要重建索引

**冷备的缺点：**

a) InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着数据，如Undo段，插入缓冲等信息

b) 冷备不总是可以轻易地跨平台。操作系统、MySQL的版本、文件大小写敏感和浮点数格式都会成为问题

## **逻辑备份：** ##

### mysqldump备份工具 ###

mysqldump的语法如下：

shell>**mysqldump \[argument\] > file\_name**

备份全部数据库，使用--all-database选项：

shell>**mysqldump --all-databases > all.sql**

备份指定的数据库，--database：

shell> **mysqldump–database db1 db2 db3 > dump.sql**

mysqldump一些重要参数选项：

\--single-transaction：在备份开始前，先执行STARTTRANSACTION命令，以此来获得备份的一致性

\--lock-tables(-l):在备份中，以此锁住每个架构下的所有表。一般用于MyISAM存储引擎，备份时只能对数据库进行读取操作，不过备份依然可以保证一致性。对于InnoDB不需要使用该参数，用--single-transaction即可，并且--lock-tables和--single-transaction是互斥的，不能同时使用。如果你的MySQL数据库中既有MyISAM存储引擎表，又有InnoDB存储引擎表，那么这时你的选择只有--lock-tables了。--lock-tables选项是依次对每个架构中的表上锁的，因此只能保证每个架构下表备份的一致性，不能保证所有架构下表的一致性。

\--lock-all-tables（\-x）：在备份过程中，对所有架构中的所有表上锁

\--add-drop-database：在createdatabase之前先运行drop database。这个参数需要和--all-databases或者--database选项一起使用

备份指定的表：

\# **mysqldump--single-transaction --socket=/var/run/mysqld/mysql5.socket test1 test1 >test1.sql**

\# **cattest1.sql**

\-- MySQL dump 10.13 Distrib 5.1.61, for redhat-linux-gnu (i386)

\--

\-- Host: localhost Database: test1

\-- ------------------------------------------------------

\-- Server version 5.5.20-ndb-7.2.5-log

/\*!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: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 for table \`test1\`

\--

DROP TABLE IF EXISTS \`test1\`;

/\*!40101 SET @saved\_cs\_client = @@character\_set\_client \*/;

/\*!40101 SET character\_set\_client = utf8 \*/;

CREATE TABLE \`test1\` (

\`id\`int(11) NOT NULL,

\`name\`varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/\*!40101 SET character\_set\_client = @saved\_cs\_client\*/;

\--

\-- Dumping data for table \`test1\`

\--

LOCK TABLES \`test1\` WRITE;

/\*!40000 ALTER TABLE \`test1\` DISABLE KEYS \*/;

INSERT INTO \`test1\` VALUES(1,'wu'),(2,'terry'),(3,'tang'),(4,'jack'),(4,'cat'),(3,NULL),(3,'dog');

/\*!40000 ALTER TABLE \`test1\` ENABLE KEYS \*/;

UNLOCK TABLES;

/\*!40103 SET TIME\_ZONE=@OLD\_TIME\_ZONE \*/;

/\*!40101 SET SQL\_MODE=@OLD\_SQL\_MODE \*/;

/\*!40014 SETFOREIGN\_KEY\_CHECKS=@OLD\_FOREIGN\_KEY\_CHECKS \*/;

/\*!40014 SET UNIQUE\_CHECKS=@OLD\_UNIQUE\_CHECKS \*/;

/\*!40101 SET CHARACTER\_SET\_CLIENT=@OLD\_CHARACTER\_SET\_CLIENT\*/;

/\*!40101 SETCHARACTER\_SET\_RESULTS=@OLD\_CHARACTER\_SET\_RESULTS \*/;

/\*!40101 SETCOLLATION\_CONNECTION=@OLD\_COLLATION\_CONNECTION \*/;

/\*!40111 SET SQL\_NOTES=@OLD\_SQL\_NOTES \*/;

\-- Dump completed on 2012-11-10 1:39:53

### **select … into outfile** ###

select..into语句也是一种逻辑备份的方法，或者更准确地说是导出一张表中的数据

SELECT \[column 1\],\[column2\] … INTO OUTFILE'file\_name' from table where… …

mysql> select * into outfile '/root/test1.txt' from test1;  //必须是mysql用户可写
    
    ERROR 1 (HY000): Can't create/write to file'/root/test1.txt' (Errcode: 13)
    
    mysql> select * into outfile '/home/mysql/test1.txt' from test1;
    
    Query OK, 7 rows affected (0.00 sec)
    
    mysql> quit
    
    Bye

\# **cat/home/mysql/test1.tx**t

1 wu

2 terry

3 tang

4 jack

4 cat

3 \\N

3 dog

\# **mysql -e"select \* into outfile '/home/mysql/test\_bak' from test1.test1";**

\# **cat/home/mysql/test\_bak**

1 wu

2 terry

3 tang

4 jack

4 cat

3 \\N

3 dog

**逻辑备份的恢复:**

mysqldump的恢复操作比较简单，因为备份的文件就是导出的SQL语句

在shell命令行导入：

\# **mysqldump-uroot -p --socket=/var/run/mysqld/mysql5.socket >test1.sql test1**

使用source恢复：

mysql> **usetest1;**

mysql>**source /root/test1.sql;**

Query OK, 0 rows affected (0.00 sec)

… …

**注**：mysqldump可以导出存储过程、触发器、事件、数据，但是却不能导出视图

### **Load data infile** ###

若是通过mysqldump --tab或者select into outfile导出的数据需要恢复时，则需要通过Load data infile命令来进行导入

LOAD DATA \[LOW\_PRIORITY | CONCURRENT\]\[LOCAL\] INFILE 'file\_name'

\[REPLACE | IGNORE\]

INTO TABLE tbl\_name

\[CHARACTER SET charset\_name\]

\[\{FIELDS | COLUMNS\}

\[TERMINATED BY 'string'\]

\[\[OPTIONALLY\] ENCLOSED BY 'char'\]

\[ESCAPED BY 'char'\]

\[LINES

\[STARTING BY 'string'\]

\[TERMINATED BY 'string'\]

\[IGNORE number LINES\]

\[(col\_name\_or\_user\_var,...)\]

\[SET col\_name =expr,...\]

mysql> select * fromtest1;
    Empty set (0.00 sec)
    
    mysql> load datainfile '/home/mysql/test1.txt' into table test1;
    Query OK, 7 rows affected (0.05 sec)
    Records: 7  Deleted:0  Skipped: 0  Warnings: 0
    
    mysql> select * fromtest1;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | wu    |
    |  2 | terry |
    |  3 | tang  |
    ……
    
    7 rows in set (0.00 sec)
    
    mysql>

为了更快的导入，可以在导入过程忽略对外键的检查，可以使用：

![1354249273_5715.png][]

![1354249286_3306.png][]

### **mysqlimport** ###

mysqlimport是MySQL数据库提供的一个命令行程序，从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同

shell> **mysqlimport\[options\] db\_name testfile1 \[textfile2 …\]**

与LOAD DATA INFILE不同的是，mysqlimport命令是可以导入多张表的，并且通过**--user-thread**参数来并发导入不同的文件。这里的并发是指并发导入多个文件，并不是指mysqlimport可以并发地导入一个文件，这是有区别的，并且并发地对同一张表进行导入，效果一般不会比串行的方式好。

通过mysqlimport并发导入两张表：

\# **/usr/local/mysql/bin/mysqlimport--use-threads=2 -S /var/run/mysqld/mysql5.socket test1 /home/mysql/test1.txt/home/mysql/test3.txt**

test1.test1: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0

test1.test3: Records: 7 Deleted: 0 Skipped: 0 Warnings: 0

## **二进制日志备份和恢复** ##

二进制日志非常关键，我们可以通过它来完成point-in-time的恢复工作

开启二进制日志:

\[mysqld\]

bin-log

sync\_binlog=1

**sync\_binlog**

如果为正，当每个sync\_binlog’th写入该二进制日志后，MySQL服务器将它的二进制日志同步到硬盘上(fdatasync())。请注意如果在autocommit模式，每执行一个语句向二进制日志写入一次，否则每个事务写入一次。默认值是0，不与硬盘同步。值为1是最安全的选择，因为崩溃时，你最多丢掉二进制日志中的一个语句/事务；但是，这是最慢的选择(除非硬盘有电池备份缓存，从而使同步工作较快)。

二进制日志默认存在databasedir目录下,如下**mysql5-bin.000001**

使用mysqlbinlog查看二进制日志的内容：

\# **mysqlbinlogmysql5-bin.000001**

/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;

/\*!50003 SET@OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;

DELIMITER /\*!\*/;

\# at 4

\#121110 15:40:06 server id 1 end\_log\_pos 112 Start: binlog v4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup

\# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/\*!\*/;

BINLOG '

1gSeUA8BAAAAbAAAAHAAAAABAAQANS41LjIwLW5kYi03LjIuNS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADWBJ5QEzgNAAgAEgAEBAQEEgAAWQAEGggAAAAICAgCAAAACgoK

'/\*!\*/;

… …

**mysqlbinlog**有一些选项可以使用,简单说明常用选项:

Ø **-d,--database=name** :指定数据库名称,只列出指定数据库的操作.

Ø **-D, --disable-log-bin** :执行恢复的时候,禁止二进制日志.可以防止同一台MySQL加上-t时进入死循环

Ø **-o,--offset=n** :忽略掉日志前n行命令

Ø **-r,--result-file=name** :将输出日志到指定文件

Ø **-R,--read-from-remote-server** :从一个MySQL服务器上读取二进制

Ø **-s,--short-form** :显示简单格式,省略一些信息

Ø **-S, --socket=name**  :socket文件连接path.

Ø **-t, --to-last-log** :和-R一起使用,在二进制日志结束的时候并不会停止,而是在MySQL服务器最后生成的binlog结束,如果输出和输入都在一台MySQL上可能会导致死循环.

Ø **--set-charset=char-name** :在输出文本格式的时候,在第一行加上set names char-name.

Ø **--start-datetime=\#--stop-datetime=\#**  :指定输出起始日期的日志.

Ø **--start-position=\#--stop-position=\#** :指定起始日志的位置.

\# mysqlbinlog -d test2mysql5-bin.000001

/\*!40019 SET @@session.max\_insert\_delayed\_threads=0\*/;

/\*!50003 SET@OLD\_COMPLETION\_TYPE=@@COMPLETION\_TYPE,COMPLETION\_TYPE=0\*/;

DELIMITER /\*!\*/;

\# at 4

\#121110 15:40:06 server id 1 end\_log\_pos 112 Start:binlog v 4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup

\# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/\*!\*/;

BINLOG '

1gSeUA8BAAAAbAAAAHAAAAABAAQANS41LjIwLW5kYi03LjIuNS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADWBJ5QEzgNAAgAEgAEBAQEEgAAWQAEGggAAAAICAgCAAAACgoK

'/\*!\*/;

\# at 112

\#121110 16:07:51 server id 1 end\_log\_pos 197 Query thread\_id=3 exec\_time=0 error\_code=0

SET TIMESTAMP=1352534871/\*!\*/;

SET @@session.pseudo\_thread\_id=3/\*!\*/;

SET @@session.foreign\_key\_checks=1, @@session.sql\_auto\_is\_null=0,@@session.unique\_checks=1, @@session.autocommit=1/\*!\*/;

SET @@session.sql\_mode=0/\*!\*/;

SET @@session.auto\_increment\_increment=1,@@session.auto\_increment\_offset=1/\*!\*/;

/\*!\\C latin1 \*//\*!\*/;

SET@@session.character\_set\_client=8,@@session.collation\_connection=8,@@session.collation\_server=8/\*!\*/;

SET @@session.lc\_time\_names=0/\*!\*/;

SET @@session.collation\_database=DEFAULT/\*!\*/;

**createdatabase test2**

/\*!\*/;

… …

\# **mysqlbinlog -d test1--stop-position=870 mysql5-bin.000001 | mysql -uroot -p**

\--start-datetime=\# --stop-datetime=\# :指定输出起始日期的日志.

\--start-position=\# --stop-position=\# :指定起始日志的位置.

以上两个选项类似,一个日期,一个日志的位置

文章内容为笔者读《MySQL技术内幕Innodb引擎》笔记

[MySQL]: http://blog.csdn.net/kumu_linux/article/details/8229279
[1354249273_5715.png]: https://img-my.csdn.net/uploads/201211/30/1354249273_5715.png
[1354249286_3306.png]: https://img-my.csdn.net/uploads/201211/30/1354249286_3306.png