1、mysqldump
1.1 备份
mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比
较合适,这也是最常用的备份方法。
现在来讲一下 mysqldump 的一些主要参数:
--compatible=name
它告诉 mysqldump,导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、
mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、
no_field_options 等,要使用几个值,用逗号将它们隔开。当然了,它并不保证能完全兼容,而是尽量兼容。
--complete-insert,-c
导出的数据采用包含字段名的完整 INSERT 方式,也就是把所有的值都写在一行。这么做能提高插入效率,但是
可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此,需要谨慎使用该参数,至少我不推荐。
--default-character-set=charset
指定导出数据时采用何种字符集,如果数据表不是采用默认的 latin1 字符集的话,那么导出时必须指定该选项
,否则再次导入数据后将产生乱码问题。
--disable-keys
告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!
40000 ALTER TABLE table ENABLE KEYS */; 语句,这能大大提高插入语句的速度,因为它是在插入完所有数据
后才重建索引的。该选项只适合 MyISAM 表。
--extended-insert = true|false
默认情况下,mysqldump 开启 --complete-insert 模式,因此不想用它的的话,就使用本选项,设定它的值为
false 即可。
--hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、
VARBINARY、BLOB。
--lock-all-tables,-x
在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关
闭 --single-transaction 和 --lock-tables 选项。
--lock-tables
它和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项只适用
于 MyISAM 表,如果是 Innodb 表可以用 --single-transaction 选项。
--no-create-info,-t
只导出数据,而不添加 CREATE TABLE 语句。
--no-data,-d
不导出任何数据,只导出数据库表结构。
--opt
这只是一个快捷选项,等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys
--extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据,并
且导出的数据能很快导回。该选项默认开启,但可以用 --skip-opt 禁用。注意,如果运行 mysqldump 没有指定
--quick 或 --opt 选项,则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。
--quick,-q
该选项在导出大表时很有用,它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓
存到内存中。
--routines,-R
导出存储过程以及自定义函数。
--single-transaction
该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性
状态。它只适用于事务表,例如 InnoDB 和 BDB。
本选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。
要想导出大表的话,应结合使用 --quick 选项。
--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 脚本,有两种方法可以将数据导入。
直接用 mysql 客户端
例如:
/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql
用 SOURCE 语法
其实这不是标准的 SQL 语法,而是 mysql 客户端提供的功能,例如:
SOURCE /tmp/db_name.sql;
这里需要指定文件的绝对路径,并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。
2、 mysqlhotcopy
2.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 /tmp
root#/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 用户可写,导出的文件
不能覆盖已经存在的文件,以避免安全问题。
SELECT INTO OUTFILE 则是把数据导出来成为普通的文本文件,可以自定义字段间隔的方式,方便处理这些数据
。
例子:
SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
注意,必须要有 FILE 权限才能执行本SQL,并且文件 /tmp/db_name/tbl_name.txt 必须能被 mysqld 用户可写
,导出的文件不能覆盖已经存在的文件,以避免安全问题。
3.2 恢复
用 BACKUP TABLE 方法备份出来的文件,可以运行 RESTORE TABLE 语句来恢复数据表。
例子:
RESTORE TABLE FROM '/tmp/db_name/';
权限要求类似上面所述。
用 SELECT INTO OUTFILE 方法备份出来的文件,可以运行 LOAD DATA INFILE 语句来恢复数据表。
例子:
LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;
权限要求类似上面所述。倒入数据之前,数据表要已经存在才行。如果担心数据会发生重复,可以增加 REPLACE
关键字来替换已有记录或者用 IGNORE 关键字来忽略他们。
4、 启用二进制日志(binlog)
采用 binlog 的方法相对来说更灵活,省心省力,而且还可以支持增量备份。
启用 binlog 时必须要重启 mysqld。首先,关闭 mysqld,打开 my.cnf,加入以下几行:
server-id = 1
log-bin = binlog
log-bin-index = binlog.index
然后启动 mysqld 就可以了。运行过程中会产生 binlog.000001 以及 binlog.index,前面的文件是 mysqld 记
录所有对数据的更新操作,后面的文件则是所有 binlog 的索引,都不能轻易删除。关于 binlog 的信息请查看
手册。
需要备份时,可以先执行一下 SQL 语句,让 mysqld 终止对当前 binlog 的写入,就可以把文件直接备份,这样
的话就能达到增量备份的目的了:
FLUSH LOGS;如果是备份复制系统中的从服务器,还应该备份 master.info 和 relay-log.info 文件。
备份出来的 binlog 文件可以用 MySQL 提供的工具 mysqlbinlog 来查看,如:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
该工具允许你显示指定的数据库下的所有 SQL 语句,并且还可以限定时间范围,相当的方便,详细的请查看手册
。
恢复时,可以采用类似以下语句来做到:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
把 mysqlbinlog 输出的 SQL 语句直接作为输入来执行它。
如果你有空闲的机器,不妨采用这种方式来备份。由于作为 slave 的机器性能要求相对不是那么高,因此成本低
,用低成本就能实现增量备份而且还能分担一部分数据查询压力,何乐而不为呢?
5、 直接备份数据文件
相较前几种方法,备份数据文件最为直接、快速、方便,缺点是基本上不能实现增量备份。为了保证数据的一致
性,需要在靠背文件前,执行以下 SQL 语句:
FLUSH TABLES WITH READ LOCK;也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不
会有新的数据写入。这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。
注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件。因为当 Innodb 表损坏时,就
可以依靠这些日志文件来恢复。
6、 备份策略
对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做一次全
量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份
,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制
(replication),在 slave 机器上做备份。
7、 数据维护和灾难恢复
作为一名DBA(我目前还不是,呵呵),最重要的工作内容之一是保证数据表能安全、稳定、高速使用。因此,需要
定期维护你的数据表。以下 SQL 语句就很有用:
CHECK TABLE 或 REPAIR TABLE,检查或维护 MyISAM 表
OPTIMIZE TABLE,优化 MyISAM 表
ANALYZE TABLE,分析 MyISAM 表
当然了,上面这些命令起始都可以通过工具 myisamchk 来完成,在这里不作详述。
Innodb 表则可以通过执行以下语句来整理碎片,提高索引速度:
ALTER TABLE tbl_name ENGINE = Innodb;
这其实是一个 NULL 操作,表面上看什么也不做,实际上重新整理碎片了。
通常使用的 MyISAM 表可以用上面提到的恢复方法来完成。如果是索引坏了,可以用 myisamchk 工具来重建索引
。而对于 Innodb 表来说,就没这么直接了,因为它把所有的表都保存在一个表空间了。不过 Innodb 有一个检
查机制叫 模糊检查点,只要保存了日志文件,就能根据日志文件来修复错误。可以在 my.cnf 文件中,增加以下
参数,让 mysqld 在启动时自动检查日志文件:
innodb_force_recovery = 4
关于该参数的信息请查看手册。
相关推荐
接下来,我们将详细解析`mysqldump`的一些关键参数及其使用场景。 ### 1. 基础参数 - **-h** 或 **--host**: 指定MySQL服务器的主机名或IP地址。 - **-u** 或 **--user**: 指定登录数据库的用户名。 - **-p** 或 *...
LNH_MySQL 12-mysqldump-master-data参数答疑详解.mp4
本文主要探讨了如何使用PHP结合mysqldump命令实现MySQL数据库的定时备份,并详细解析了mysqldump的相关语法参数。 首先,让我们了解mysqldump的基本用法。mysqldump是一个用于备份MySQL数据库的命令行工具,它能将...
#### 常用命令及参数详解 1. **基本备份命令** - `mysqldump [选项] [数据库名] > [文件名]` 2. **连接选项** - `-u username`: 指定登录MySQL服务器的用户名。 - `-p[password]`: 指定密码,如果密码直接跟在-...
一种常见方法是先使用 `mysqldump` 导出 SQL,然后通过 Python、Perl 或其他编程语言读取 SQL 文件并解析为数据结构,最后再将这些数据写入 XML 文件。例如,Python 中可以使用 `pymysql` 和 `xml.etree.ElementTree...
LNH_MySQL 02-mysqldump-master-data参数答疑详解.mp4
这里的`database`是指你要操作的数据库名称,`tables`则是可选参数,指定需要导出的表。如果不指定`tables`,那么整个数据库的所有表都会被导出。 ### 选项详解 #### 数据库操作选项 - `--add-locks`: 在导出每个...
根据提供的文件信息,本文将详细解析“mysqldump在线备份”的相关知识点,包括mysqldump工具的基本介绍、在线备份的过程与注意事项等。 ### 一、mysqldump工具概述 `mysqldump`是MySQL数据库管理系统自带的一个...
这里,`选项`是可选的参数,用于定制备份行为,而`数据库名`是你要备份的数据库,`表名`则是要备份的特定表(如果需要的话)。 以下是一些常用的`mysqldump`选项: 1. `--add-drop-table`:在每个创建数据表的SQL...
一、mysqldump 简介 mysqldump 是 MySQL 自带的逻辑备份工具。 它的备份原理是通过协议连接到 MySQL 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert 语句,当我们需要还原这些数据时,...参数名
mysqldump工具是MySQL官方提供的一种实用程序,用于创建数据库或表的结构、数据、视图、存储过程等的SQL脚本,以便于备份和恢复数据。本文将详细介绍如何使用mysqldump进行MySQL数据库的备份。 首先,让我们了解...
本文将深入解析mysqldump的各种参数及其使用场景,帮助你更好地理解和运用这些命令。 首先,mysqldump的基本语法是用于备份数据库,如下所示: ```bash mysqldump -u[username] -p[password] -h[hostname] ...
**参数详解** - `lock-all-tables`:此参数用于锁定所有表,防止在导出期间发生写操作,但可能导致长时间的锁定时间。 - `single-transaction`:开启事务模式,保证导出的数据在事务开始时的一致性,而不会阻塞其他...
### mysqldump工具基本用法详解 #### 引言 `mysqldump`是MySQL数据库管理系统中一个非常强大的工具,主要用于数据库的备份、迁移和恢复操作。它能够将数据库的内容导出成文本文件,形成SQL脚本,便于在不同环境中...