mysql數(shù)據(jù)庫的備份恢復

mysqldump備份數(shù)據(jù)庫

?-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. ?多行插入數(shù)據(jù)

? ? 為了保證數(shù)據(jù)的一致性，我們要把表鎖起來在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.

實踐之非事務(wù)性一直備份(備份期間數(shù)據(jù)庫不可寫)

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的結(jié)果

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

[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參數(shù)，這個時候記錄了日志位置和日志文件名

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

實踐之事務(wù)性一直備份（備份期間數(shù)據(jù)庫可寫）

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

總結(jié)

以上是生活随笔為你收集整理的mysql dba系统学习（16）mysql的mysqldump备份的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。