欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述

程序员文章站 2023-01-02 11:29:09
MariaDB/MySQL备份恢复系列： 备份和恢复(一)：mysqldump工具用法详述 备份和恢复(二)：导入、导出表数据 备份和恢复(三)：xtrabackup用法和原理详述 本文目录：1.备份分类2.备份内容和备份工具3.mysqldump用法详述　3.1 语法选项 3.1.1 连接选项 3 ......

MariaDB/MySQL备份恢复系列：
备份和恢复(一)：mysqldump工具用法详述
备份和恢复(二)：导入、导出表数据
备份和恢复(三)：xtrabackup用法和原理详述

本文目录：
1.备份分类
2.备份内容和备份工具
3.mysqldump用法详述
　3.1 语法选项
　　3.1.1 连接选项
　　3.1.2 筛选选项
　　3.1.3 DDL选项
　　3.1.4 字符集选项
　　3.1.5 复制选项
　　3.1.6 格式化选项
　　3.1.7 性能选项
　　3.1.8 加锁和事务相关选项
　3.2 mysqldump导出示例
　　3.2.1 简单备份示例
　　3.2.2 使用DDL选项备份示例
　　3.2.3 使用字符集选项示例
　　3.2.4 使用格式化选项示例
　　3.2.5 使用筛选选项示例
　　3.2.6 使用事务选项示例
　3.3 mysqldump使用建议
　3.4 mysqldump + 二进制日志备份
　3.5 mysqldump工具的评价

1.备份分类

按照是否能够继续提供服务，将数据库备份类型划分为：

  • 热备份：在线备份，能读能写
  • 温备份：能读不能写
  • 冷备份：离线备份

按照备份数据库对象分类：

  • 物理备份：直接复制数据文件
  • 逻辑备份：将数据导出至文件中，必要时将其还原(也包括备份成sql语句的方式)

按照是否备份整个数据集分为：

  • 完全备份：备份从开始到某段时间的全部数据
  • 差异备份：备份自完全备份以来变化的数据
  • 增量备份：备份自上次增量备份以来变化的数据

分类方式不同，不同分类的备份没有冲突的关系，它们可以任意组合。

2.备份内容和备份工具

需要备份的内容：文件、二进制日志、事务日志、配置文件、操作系统上和MySQL相关的配置（如sudo，定时任务）。

物理备份和逻辑备份的优缺点：

  • 物理备份：直接复制数据文件，速度较快。
  • 逻辑备份：将数据导出到文本文件中或其他格式的文件中。有MySQL服务进程参与，相比物理备份而言速度较慢；可能丢失浮点数精度；但可以使用文本工具二次处理；可以跨版本和跨数据库系统进行移植。

备份策略：要考虑安全，也要考虑还原时长

  • 完全备份+增量
  • 完全备份+差异

备份工具：

  • mysqldump：逻辑备份工具。要求mysql服务在线。MyISAM(温备)，InnoDB（热备）
  • mysqlhotcopy：物理备份工具，温备份，实际上是冷备。加锁、flush table并进行cp或scp。即将废弃的工具
  • cp:冷备
  • lvm快照：几乎热备。注意点是：先flush table、lock table、创建快照、释放锁、复制数据。因为要先flush table和lock table，这对于MyISAM来说很简单很容易实现。但对于InnoDB来说，因为事务的原因，锁表后可能还有缓存中的数据在写入文件中，所以应该监控缓存中的数据是真的已经完全写入数据文件中，之后才能进行复制数据。
  • xtrabackup:开源。MyISAM（温备），InnoDB（热备），速度较快。

3.mysqldump用法详述

官方手册：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump默认会从配置文件中的mysqldump段读取选项，配置文件读取的顺序为：

/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf

3.1 语法选项

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

3.1.1 连接选项

-u, --user=name        指定用户名
-S, --socket=name      指定套接字路径 
-p, --password[=name]  指定密码
-P, --port=#           指定端口
-h, --host=name        指定主机名 

-r, --result-file=name
将导出结果保存到指定的文件中，在Linux中等同于覆盖重定向。在windows中因为回车符\r\n的原因，使用该选项比重定向更好

3.1.2 筛选选项

--all-databases, -A  
指定dump所有数据库。等价于使用--databases选定所有库
--databases, -B  
指定需要dump的库。该选项后的所有内容都被当成数据库名；在输出文件中的每个数据库前会加上建库语句和use语句
--ignore-table=db_name.tbl_name  
导出时忽略指定数据库中的指定表，同样可用于忽略视图，要忽略多个则多次写该选项
-d, --no-data       
不导出表数据，可以用在仅导出表结构的情况。
--events, -E  
导出事件调度器
--routines, -R   
导出存储过程和函数。但不会导出它们的属性值，若要导出它们的属性，可以导出mysql.proc表然后reload
--triggers  
导出触发器，默认已开启
--tables 
覆盖--databases选项，导出指定的表。但这样只能导出一个库中的表。格式为--tables database_name tab_list
--where='where_condition', -w 'where_condition'  
指定筛选条件并导出表中符合筛选的数据，如--where="user='jim'"

3.1.3 DDL选项

--add-drop-database 
在输出中的create database语句前加上drop database语句先删除数据库
--add-drop-table    
在输出的create table语句前加上drop table语句先删除表，默认是已开启的
--add-drop-trigger  
在输出中的create trigger语句前加上drop trigger语句先删除触发器
-n, --no-create-db   
指定了--databases或者--all-databases选项时默认会加上数据库创建语句，该选项抑制建库语句的输出
-t, --no-create-info 
不在输出中包含建表语句
--replace            
使用replace代替insert语句

3.1.4 字符集选项

--default-character-set=charset_name 
在导出数据的过程中，指定导出的字符集。很重要，客户端服务端字符集不同导出时可能乱码，默认使用utf8
--set-charset 
在导出结果中加上set names charsert_name语句。默认启用。

3.1.5 复制选项

--apply-slave-statements
--delete-master-logs
--dump-slave[=value]
--include-master-host-port

--master-data[=value]  
该选项主要用来建立一个replication，当值为1时，导出文件中记录change master语句；
当值为2时，change master语句被写成注释语句，默认值为空。
该选项自动忽略--lock-tables，当没有使用--single-transaction时自动启用--lock-all-tables。

3.1.6 格式化选项

--compact                   
简化输出导出的内容，几乎所有注释都不会输出

--complete-insert, -c       
在insert语句中加上插入的列信息

--create-options            
在导出的建表语句中，加上所有的建表选项

--tab=dir_name, -T dir_name 
将每个表的结构定义和数据分别导出到指定目录下文件名同表名的.sql和txt文件中，其中.txt
文件中的字段分隔符是制表符。要求mysqldump必须和MySQL Server在同一主机，且mysql用
户对指定的目录有写权限，并且连接数据库的用户必须有file权限。且指定要dump的表，不能和
--databases或--all-databases一起使用。它的实质是执行select into outfile。

--fields-terminated-by=name            
指定输出文件中的字段分隔符

--fields-enclosed-by=name              
指定输出文件中的字段值的包围符，如使用引号将字符串包围起来引用

--fields-optionally-enclosed-by=name   
指定输出文件中可选字段引用符

--fields-escaped-by=name               
指定输出文件中的转义符

--lines-terminated-by=name            
指定输出文件中的换行符   

-Q, --quote-names                       
引用表名和列名时使用的标识符，默认使用反引号"`" 

3.1.7 性能选项

--delayed-insert  
对于非事务表，在insert时支持delayed功能，但在MySQL5.6.6开始该选项已经废弃
--disable-keys, -K  
在insert语句前后加上禁用和启用索引语句，大量数据插入时该选项很适合。默认开启
--insert-ignore  
使用insert ignore语句替代insert语句
--quick, -q 
快速导出数据，该选项对于导出大表非常好用。默认导出数据时会一次性检索表中所有数据并加入
到内存中，而该选项是每次检索一行并导出一行

3.1.8 加锁和事务相关选项

--add-locks         
在insert语句前后加上lock tables和unlock tables语句，默认已开启。

--flush-logs, -F 
在开始dump前先flush logs，如果同时使用了--all-databases则依次在每个数据库dump前flush，
如果同时使用了--lock-all-tables,--master-data或者--single-transaction，则仅flush
一次，等价于使用flush tables with read lock锁定所有表，这样可以让dump和flush在完全精
确的同一时刻执行。

--flush-privileges   
在dump完所有数据库后在数据文件的结尾加上flush privileges语句，在导出的数据涉及mysql库或
者依赖于mysql库时都应该使用该选项

--lock-all-tables, -x  
为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁，一锁锁永久且锁所有。
该选项自动禁用--lock-tables和--single-transaction选项

--lock-tables, -l  
在dump每个数据库前依次对该数据库中所有表加read local锁(多次加锁，lock tables...read local)，
这样就允许对myisam表进行并发插入。对于innodb存储引擎，使用--single-transaction比
--lock-tables更好，因为它不完全锁定表。因为该选项是分别对数据库加锁的，所以只能保证每个数
据库的一致性而不能保证所有数据库之间的一致性。该选项主要用于myisam表，如果既有myisam又有
innodb，则只能使用--lock-tables，或者分开dump更好

--single-transaction 
该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction语句给
服务端。该选项对于导出事务表如innodb表很有用，因为它在发出start transaction后能保证导
出的数据库的一致性时而不阻塞任何的程序。该选项只能保证innodb表的一致性，无法保证myisam表
的一致性。在使用该选项的时候，一定要保证没有任何其他连接在使用ALTER TABLE,CREATE TABLE,
DROP TABLE,RENAME TABLE,TRUNCATE TABLE语句，因为一致性读无法隔离这些语句。
--single-transaction选项和--lock-tables选项互斥，因为lock tables会隐式提交事务。
要导出大的innodb表，该选项结合--quick选项更好

--no-autocommit  
在insert语句前后加上SET autocommit = 0，并在需要提交的地方加上COMMIT语句

--order-by-primary  
如果表中存在主键或者唯一索引，则排序后按序导出。对于myisam表迁移到innobd表时比较有用，但是
这样会让事务变得很长很慢

3.2 mysqldump导出示例

3.2.1 简单备份示例

创建示例数据库和表。

# 创建第一个数据库
CREATE DATABASE backuptest;
USE backuptest;
# 创建innodb表
CREATE TABLE `student` (
    `studentid` INT (11) NOT NULL,
    `sname` CHAR (30) NOT NULL,
    `gender` enum ('male', 'female') DEFAULT NULL,
    `birth` date DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE = INNODB DEFAULT CHARSET = latin1

INSERT INTO student
VALUES
    (1,'malongshuai','male',curdate()),
    (2,'gaoxiaofang','female',date_add(curdate(), INTERVAL - 2 YEAR)),
    (3,'longshuai','male',date_add(curdate(), INTERVAL - 5 YEAR)),
    (4,'meishaonv','female',date_add(curdate(), INTERVAL - 3 YEAR)),
    (5,'tun\'er','female',date_add(curdate(), INTERVAL - 4 YEAR));

# 创建myisam表，并且字符集设置为UTF8
CREATE TABLE teacher (
    tid INT NOT NULL PRIMARY KEY,
    tname VARCHAR (30),
    gender enum ('male', 'female'),
    classname CHAR (10)
) ENGINE = myisam DEFAULT charset = utf8;

INSERT INTO teacher
VALUES
    (1,'wugui','male','计算机网络'),
    (2,'woniu','female','C语言'),
    (3,'xiaowowo','female','oracle');

# 创建第二个数据库
CREATE DATABASE backuptest1;
USE backuptest1;
create table student1 as select * from backuptest.student;
create table teacher1 charset=utf8 engine=myisam as select * from backuptest.teacher;

mysqldump三种备份方式如下：

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

备份单个库，此处备份mysql库。重定向符号可以在Linux中等价于mysqldump的-r选项，所以下面的语句是等价的。

[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock mysql >/tmp/mysql.bak
[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -r /tmp/mysql1.bak mysql

查看备份文件，会发现dump单个库的时候不会在输出文件中记录建库语句和use语句。

[root@xuexi ~]# less /tmp/mysql.bak 
-- MySQL dump 10.13  Distrib 5.6.35, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.6.35-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 `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
......

备份多个库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest backuptest1 >/tmp/mutil_db.bak

备份所有库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --all-databases >/tmp/all_db.bak

备份多个库或所有库时，会在dump文件中加入建库语句和use语句。实际上，只要使用--databases选项，即使只备份一个库也会加上建库语句和use语句。

[root@xuexi ~]# grep -C 2 -i 'use' /tmp/mutil_db.bak  
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest`;

--
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest1`;

--

所以使用mysqldump备份的时候，无论何时都建议--databases或者--all-databases选项二选一，这样就免去了连进数据库的过程。

3.2.2 使用DDL选项备份示例

DDL选项如下：

--add-drop-database
--add-drop-table   
--add-drop-trigger 
-n, --no-create-db  
-t, --no-create-info
--replace

--no-create-db将抑制建库语句，所以不建议使用。

--no-create-info将抑制建表语句。使用和不使用的对比如下：

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --no-create-info --databases backuptest >/tmp/backuptest1.bak
vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述

--replace将会把insert语句替换为replace语句。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --replace --databases backuptest >/tmp/backuptest2.bak
shell> grep -i 'replace' /tmp/backuptest2.bak                                                                     
REPLACE INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun\'er','female','2013-03-31');
REPLACE INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

3.2.3 使用字符集选项示例

dump数据的时候，客户端和数据库的字符集不一致的话会进行字符集转换，转换的过程是不可逆的，所以有可能会导致乱码。

例如，插入一个带有中文字符的记录到字符集为latin1的表student中。

insert INTO backuptest.`student` VALUES (6,'马','male','2017-03-31');

如果提示无法插入，则设置客户端字符集和连接字符集为latin1，character_set_client、character_set_connection、character_set_results，使用set names latin1即可，它会设置它们3个。

插入成功之后，其他会话连接数据库查询将会是乱码的。dump的时候也是乱码的，因为dump默认会使用utf8字符集，在latin1转码为utf8的过程中出现了乱码。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun\'er','female','2013-03-31'),(6,'马','male','2017-03-31');
INSERT INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

再使用乱码的文件来恢复的话，肯定是乱码的结果。

这时可以指定dump时的字符集为latin1来使得dump数据时无需转换字符集。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --default-character-set=latin1 --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun\'er','female','2013-03-31'),(6,'马','male','2017-03-31');

测试完成之后，将新插入的含有中文字符的记录删除。

delete from backuptest.student where studentid=6;

3.2.4 使用格式化选项示例

格式化选项如下：

--compact                   
--complete-insert, -c       
--create-options            
--tab=dir_name, -T dir_name 
--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name            
-Q, --quote-names

选项--compact可以极大的简化输出，让输出变得更加简单明晰。但是却不安全，因为它不止会简化注释语句，还会简化部分非注释语句，如insert前后的lock语句。所以该选项仅用来调试使用。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --compact --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述

"--complete-insert, -c"选项会在每个insert语句中列出插入列列表，而默认情况下mysqldump备份时是不加的。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -c --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述

--tab选项是将表的结构定义和数据分开dump，结构定义语句dump到表的同名.sql文件中，数据dump到表的同名.txt文件中。--tab指定这些文件的输出目录。但要求mysqldump和MySQL服务器必须在同一台服务器上，且mysql系统用户对指定的输出目录有写权限，连接数据库的用户还需要有file权限。而且该选项不能和--databases或--all-databases一起使用。因为它只能定义表的结构。

一般和该选项一起使用的有：该部分内容见select ... into outfile。

--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name  

3.2.5 使用筛选选项示例

--all-databases, -A  
--databases, -B  
--ignore-table=db_name.tbl_name  
-d, --no-data       
--events, -E  
--routines, -R   
--triggers  
--tables 
--where='where_condition', -w 'where_condition'

筛选数据库此处不再赘述。

--ignore-table表示忽略数据库中的某张表不被dump。要忽略多张表的时候多次写该选项即可。例如忽略backuptest.student和backuptest1.student1。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --ignore-table backuptest.student --ignore-table backuptest1.student1 --databases backuptest backuptest1 >/tmp/backuptest.bak
shell> grep -i 'student' /tmp/backuptest.bak |wc -l
0

--no-data表示只dump表的结构，不dump数据。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -d backuptest>/tmp/backuptest.bak

grep -i 'insert' /tmp/backuptest.bak | wc -l
0

--tables指定数据库来导出其中的某些表。只支持指定一个数据库。该选项会覆盖--databases选项，所以不会建库语句和use语句。该选项可以用在分开备份innodb表和myisam表。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --tables backuptest student teacher>/tmp/backuptest.bak

--where指定某个表的筛选条件，格式为--where="condition"。如果指定了筛选列，则要导出的表中必须要含有该列。另外，显式指定表时，格式为"db_name tab_list"。以下格式都正确。

# 导出某数据库下的一个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student>/tmp/backuptest.bak
# 导出某数据库下多个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student teacher>/tmp/backuptest.bak
# 导出多个数据库中的筛选数据
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --where="gender='male'" --databases backuptest backuptest1>/tmp/backuptest.bak

3.2.6 使用事务选项示例

--add-locks选项是在insert语句前后加上lock tables和unlock tables语句。

MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述

--no-autocommit选项是在insert语句前后加上set autocommit=0，并在事务结束的地方加上commit语句。这样插入数据时只需一次提交，可以大幅提升大量插入时的性能。

--flush-logs, -F

在开始dump前先flush logs，如果同时使用了--all-databases，则依次在每个数据库dump前flush；如果同时使用了--lock-all-tables,--master-data或者--single-transaction，则仅flush一次，因为这几个选项是dump前开启一个长事务或者全局锁定。等价于使用flush tables with read lock锁定所有表，这样可以让dump和flush在完全精确的同一时刻执行。

--flush-privileges  

在dump完所有数据库后在数据文件的结尾加上flush privileges语句，在导出的数据涉及mysql数据库或者依赖于mysql数据库时都应该使用该选项。

--lock-all-tables, -x 

为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁，一锁锁永久且锁所有。该选项自动禁用--lock-tables和--single-transaction选项。

--lock-tables, -l 

在dump每个数据库前依次对该数据库中所有表加上read local锁(多次加锁，lock tables ... read local)，这样就允许对myisam表进行并发插入。对于innodb存储引擎，使用--single-transaction比--lock-tables更好，因为它不完全需要锁定表。因为该选项是分别对数据库加锁的，所以只能保证每个数据库之间的一致性而不能保证在所有数据库之间的一致性。 该选项主要用于myisam表，如果既有myisam又有innodb，则只能使用--lock-tables了，或者分开dump更好。

--single-transaction

该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction语句给服务端。该选项对于导出事务表如innodb表很有用，因为它在发出start transaction后能保证导出的数据库的一致性时而不阻塞任何的程序。当使用该选项只能保证innodb表的一致性，对于myisam表是无法保证的。但是在使用该选项的时候，一定要保证没有任何其他数据库的连接在使用ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE语句，因为一致性读无法隔离这些语句。--single-transaction选项和--lock-tables选项互斥，因为lock tables会隐式提交事务，要导出大的innodb表，该选项结合--quick选项更好。

3.3 mysqldump使用建议

1.从性能考虑：在需要导出大量数据的时候，使用--quick选项可以加速导出，但导入速度不变。如果是innodb表，则可以同时加上--no-autocommit选项，这样大量数据量导入时将极大提升性能。

2.一致性考虑：对于innodb表，几乎没有理由不用--single-transaction选项。对于myisam表，使用--lock-all-tables选项要好于--lock-tables。既有innodb又有myisam表时，可以分开导出，又能保证一致性，还能保证效率。

3.方便管理和维护性考虑：在导出时flush log很有必要。加上--flush-logs选项即可。而且一般要配合--lock-all-tables选项或者--single-transaction选项一起使用，因为同时使用时，只需刷新一次日志即可，并且也能保证一致性。同时，还可以配合--master-data=2，这样就可以方便地知道二进制日志中备份结束点的位置。

4.字符集考虑：如果有表涉及到了中文数据，在dump时，一定要将dump的字符集设置的和该表的字符集一样。

5.杂项考虑：备份过程中会产生二进制日志，但是这是没有必要的。所以在备份前可以关掉，备份完后开启。set sql_log_bin=0关闭，set sql_log_bin=1开启。

以下是备份不同存储引擎类型表的示例：

备份myisam表：需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --lock-all-tables --flush-logs --master-data=2 --tables backuptest teacher >/tmp/myisam.sql ;

set sql_log_bin=1

备份innodb表：需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

set sql_log_bin=1

3.4 mysqldump + 二进制日志备份

mysqldump可以实现全备份，在mysqldump之后再二进制日志备份就相当于增量备份，这样就可以实现全备份之后的定时点还原。

假设要备份的是一张innodb表。使用下面的语句：

mysqldump  -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

因为dump前会flush二进制日志，所以之后对该表的操作会记录到新的滚动日志中。然后只需备份新的二进制日志即可。

然后在该表中插入一行记录。

insert into student select 10,'xiaolonglong','male','2015-01-02';

备份新的二进制日志。

mysqlbinlog mysql-bin.000002 >/tmp/new_binlog.sql

设计刚才备份的表误操作，如删除该表。

drop table student;

使用该表的完全备份和二进制日志恢复。因为备份时使用的是--tables选项，所以要恢复需要进入数据库指定数据库，然后使用source来加载sql文件。

use backuptest;
source /tmp/innodb.sql;
source /tmp/new_binlog.sql;

3.5 mysqldump工具的评价

mysqldump备份的文件是逻辑SQL语句，总体来说，简单，便捷，在有些时候迁移数据的时候比较有用。另外，它的功能也很多，例如导出数据，导出表结构等。

但是缺点是恢复速度太慢，因为恢复数据时是通过insert不断插入记录的，它的恢复速度远不及load data infile导入数据。

在备份方式上，mysqldump备份myisam表时因为要加--lock-all-tables，这时要备份的数据库全部被上锁，可读不可写，所以实现的是温备。mysqldump备份innodb表时因为要加--single-transaction，会自动将隔离级别设置为repeatable read并开启一个事务，这时mysqldump将获取dump执行前一刻的行版本，并处于一个长事务中直到dump结束。所以不影响目标数据库的使用，可读也可写，即实现的是热备。

 

回到Linux系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲：http://www.cnblogs.com/f-ck-need-u/p/7586194.html
转载请注明出处：http://www.cnblogs.com/f-ck-need-u/p/9013458.html

注：若您觉得这篇文章还不错请点击右下角推荐，您的支持能激发作者更大的写作热情，非常感谢！

上一篇： 空调病的症状有哪些?

下一篇： 春季养生饮食小常识 赶走春困呵护健康

推荐阅读