mysql dba系统学习（16）mysql的mysqldump备份

最新推荐文章于 2025-08-08 14:10:14 发布

GarfieldEr007

最新推荐文章于 2025-08-08 14:10:14 发布

阅读量767

点赞数

分类专栏： Database 文章标签： mysql dba 学习 mysqldump 备份

Database 专栏收录该内容

149 篇文章

订阅专栏

本文详细介绍使用mysqldump工具进行MySQL数据库备份与恢复的方法。包括如何锁定所有表以确保数据一致性，如何在备份期间允许数据库写操作，以及如何记录日志文件名和位置等关键配置选项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql数据库的备份恢复

mysqldump备份数据库

-B, --databases Dump several databases. Note the difference in usage; in

this case no tables are given. All name arguments are

regarded as database names. 'USE db_name;' will be

included in the output.

-e, --extended-insert

Use multiple-row INSERT syntax that include several

VALUES lists. 多行插入数据

为了保证数据的一致性，我们要把表锁起来在dump

-F, --flush-logs Flush logs file in server before starting dump. Note that

if you dump many databases at once (using the option

--databases= or --all-databases), the logs will be

flushed for each database dumped. The exception is when

using --lock-all-tables or --master-data: in this case

the logs will be flushed only once, corresponding to the

moment all tables are locked. So if you want your dump

and the log flush to happen at the same exact moment you

should use --lock-all-tables or --master-data with

--flush-logs.

-x, --lock-all-tables

Locks all tables across all databases. This is achieved

by taking a global read lock for the duration of the

whole dump. Automatically turns --single-transaction and

--lock-tables off.

-l, --lock-tables Lock all tables for read.

--master-data[=#] This causes the binary log position and filename to be

appended to the output. If equal to 1, will print it as a

CHANGE MASTER command; if equal to 2, that command will

be prefixed with a comment symbol. This option will turn

--lock-all-tables on, unless --single-transaction is

specified too (in which case a global read lock is only

taken a short time at the beginning of the dump; don't

forget to read about --single-transaction below). In all

cases, any action on logs will happen at the exact moment

of the dump. Option automatically turns --lock-tables

off.

-t, --no-create-info

Don't write table creation info.

-d, --no-data No row information.

-N, --no-set-names Suppress the SET NAMES statement

--opt Same as --add-drop-table, --add-locks, --create-options,

--quick, --extended-insert, --lock-tables, --set-charset,

and --disable-keys. Enabled by default, disable with

--skip-opt.

-q, --quick Don't buffer query, dump directly to stdout. 不缓存

-R, --routines Dump stored routines (functions and procedures).

--single-transaction

Creates a consistent snapshot by dumping all tables in a

single transaction. Works ONLY for tables stored in

storage engines which support multiversioning (currently

only InnoDB does); the dump is NOT guaranteed to be

consistent for other storage engines. While a

--single-transaction dump is in process, to ensure a

valid dump file (correct table contents and binary log

position), no other connection should use the following

statements: ALTER TABLE, DROP TABLE, RENAME TABLE,

TRUNCATE TABLE, as consistent snapshot is not isolated

from them. Option automatically turns off --lock-tables.

--dump-date Put a dump date to the end of the output.

--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,

--create-options, --quick, --extended-insert,

--lock-tables, --set-charset, and --disable-keys.

实践之非事务性一直备份(备份期间数据库不可写)

mysql> use test

mysql> create table tt(id int,name varchar(12));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt values(1,'zz');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tt values(2,'yy');

Query OK, 1 row affected (0.00 sec)

[root@test4 Desktop]# mysqldump --databases test --skip-opt --quick --extended-insert=false --lock-all-tables --master-data=2 -u root -p123456 > /tmp/test.sql

这就是dump的结果

[root@test4
 Desktop]# cat /tmp/test.sql

--
 MySQL dump 10.13  Distrib 5.1.70, for unknown-linux-gnu
 (x86_64)

--

--
 Host: localhost    Database: test

--
 ------------------------------------------------------

--
 Server version   5.1.70-log

/*!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 */;

--

--
 Position to start replication or point-in-time
 recovery from

--

--
 CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000166',
 MASTER_LOG_POS=798;

--

--
 Current Database: `test`

--

CREATE
 DATABASE /*!32312
 IF NOT EXISTS*/ `test` /*!40100
 DEFAULT CHARACTER SET utf8 */;

USE
 `test`;

--

--
 Table structure for table
 `tt`

--

/*!40101
 SET @saved_cs_client     = @@character_set_client */;

/*!40101
 SET character_set_client = utf8 */;

CREATE
 TABLE `tt` (

  `id` int(11)
 DEFAULT NULL,

  `name` varchar(12)
 DEFAULT NULL

);

/*!40101
 SET character_set_client = @saved_cs_client */;

--

--
 Dumping data for table
 `tt`

--

INSERT
 INTO `tt` VALUES (1,'zz');

INSERT
 INTO `tt` VALUES (2,'yy');

/*!40103
 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101
 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014
 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014
 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111
 SET SQL_NOTES=@OLD_SQL_NOTES */;

--
 Dump completed on 2013-09-05 20:15:38

由于我们dump的时候用了master-data参数，这个时候记录了日志位置和日志文件名

-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000166', MASTER_LOG_POS=798;

实践之事务性一直备份（备份期间数据库可写）

mysql> use test

mysql> create table tt(id int,name varchar(12)) engine=innodb ;

Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt values(1,'zz');

Query OK, 1 row affected (0.01 sec)

mysql> insert into tt values(2,'yy');

Query OK, 1 row affected (0.00 sec)

[root@test4 Desktop]# mysqldump --databases test --skip-opt --quick --extended-insert=false --single-transaction --master-data=2 -u root -p123456 > /tmp/test.sql