利用MySQL全備份（mysqldump），如何只恢復一個庫或者一個表？
mysql 按照備份恢復方式分為邏輯備份和物理備份。邏輯備份是備份 sql 語句，在恢復的時候執行備份的 sql 語句實現資料庫資料的重現，物理備份就是備份資料檔案了，比較形象點就是 cp 下資料檔案，但真正備份的時候自然不是的 cp 這麼簡單。
這 2 種備份各有優劣，一般來說，物理備份恢復速度比較快，佔用空間比較大，邏輯備份速度比較慢，佔用空間比較小。
官方地址： http://dev.mysql.com/doc/refman/5.6/en/backup-and-recovery.html
1.1 mysqldump 工具
Mysqldump 是 mysql 自帶的備份工具，目錄在 bin 目錄下面： /usr/local/mysql/bin/mysqldump ，支援基於 innodb 的熱備份。但是由於是邏輯備份，所以速度不是很快，適合備份資料比較小的場景。 Mysqldump 完全備份 + 二進位制日誌可以實現基於時間點的恢復。
對 myisam 儲存引擎的表，只能使用溫備份，這個時候要防止資料的寫入，所以先加上讀鎖。這個時候也可以進入資料庫手動加讀鎖，不過這樣比較麻煩，可以在 mysqldump 工具中直接有一個加鎖的選擇，就是 --lock-all-tables ，例如 mysqldump--databases test--lock-all-tables--flush-logs>/tmp/backup_test_`date+%F-%H-%M`.sql 。
如果是備份單張表，直接在庫名字 test 後面加上表名字即可。
對於 innodb 儲存引擎表，可以熱備，不必對資料庫進行加鎖的操作，加一個選項可以進行熱備份， --single-transaction ，例如： mysqldump --databases test --single-transaction --flush-logs--master-data=2> /tmp/backup_test_`date +%F-%H-%M`.sql 。
PS ：注意點，恢復的時候記得關閉二進位制日誌：
mysql> set sql_log_bin=0;
因為這是基於邏輯備份方式，所以執行 sql 會插入資料，會記錄到二進位制日誌裡面去，因為這事恢復，所以插入的二進位制日誌基本沒有啥意思，可以關閉掉，縮短恢復時間。
4.mysqldump 以及 xtranbackup 的實現原理，
Ømysqldump 是最簡單的邏輯備份方式。在備份 myisam 表的時候，如果要得到一致的資料，就需要鎖表，簡單而粗暴。而在備份 innodb 表的時候，加上– master-data=1 – single-transaction 選項，在事務開始時刻，記錄下 binlog pos 點，然後利用 mvcc 來獲取一致的資料，由於是一個長事務，在寫入和更新量很大的資料庫上，將產生非常多的 undo ，顯著影響效能，所以要慎用。
Ø 優點：簡單，可針對單表備份，在全量匯出表結構的時候尤其有用。
Ø 缺點：簡單粗暴，單執行緒，備份慢而且恢復慢，跨 IDC 有可能遇到時區問題
Øxtrabackup 它實際上是物理備份 + 邏輯備份的組合。在備份 innodb 表的時候，它拷貝 ibd 檔案，並一刻不停的監視 redo log 的變化， append 到自己的事務日誌檔案。在拷貝 ibd 檔案過程中， ibd 檔案本身可能被寫”花”，這都不是問題，因為在拷貝完成後的第一個 prepare 階段， Xtrabackup 採用類似於 innodb 崩潰恢復的方法，把資料檔案恢復到與日誌檔案一致的狀態，並把未提交的事務回滾。如果同時需要備份 myisam 表以及 innodb 表結構等檔案，那麼就需要用 flush tables with lock 來獲得全域性鎖，開始拷貝這些不再變化的檔案，同時獲得 binlog 位置，拷貝結束後釋放鎖，也停止對 redo log 的監視。
1.1.1 匯出表作為原始資料
mysqldump 是 mysql 用於轉儲存資料庫的實用程式。它主要產生一個 SQL 指令碼，其中包含從頭重新建立資料庫所必需的命令 CREATE TABLE INSERT 等。
使用 mysqldump 匯出資料需要使用 --tab 選項來指定匯出檔案指定的目錄，該目標必須是可寫的。
以下例項將資料表 tutorials_tbl 匯出到 /tmp 目錄中：
$ mysqldump -u root -p --no-create-info \ --tab=/tmp TUTORIALS tutorials_tbl password ******
1.1.2 mysqldump 匯出 SQL 格式的資料
[root@rhel6_lhr ~]# mysqldump -?
mysqldump Ver 10.13 Distrib 5.6.21, for Linux (x86_64)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file,
except for login file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--login-path=# Read this path from the login file.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
--add-drop-trigger Add a DROP TRIGGER before each create.
--add-locks Add locks around INSERT statements.
(Defaults to on; use --skip-add-locks to disable.)
--allow-keywords Allow creation of column names that are keywords.
--apply-slave-statements
Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START
SLAVE' to bottom of dump.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
-i, --comments Write additional information.
(Defaults to on; use --skip-comments to disable.)
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
-a, --create-options
Include all MySQL specific create options.
(Defaults to on; use --skip-create-options to disable.)
-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.
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.
--delayed-insert Insert rows with INSERT DELAYED.
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
(Defaults to on; use --skip-disable-keys to disable.)
--dump-slave[=#] This causes the binary log position and filename of the
master to be appended to the dumped data output. Setting
the value to 1, will printit as a CHANGE MASTER command
in the dumped data output; 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.
-E, --events Dump events.
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists.
(Defaults to on; use --skip-extended-insert to disable.)
--fields-terminated-by=name
Fields in the output file are terminated by the given
string.
--fields-enclosed-by=name
Fields in the output file are enclosed by the given
character.
--fields-optionally-enclosed-by=name
Fields in the output file are optionally enclosed by the
given character.
--fields-escaped-by=name
Fields in the output file are escaped by the given
character.
-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.
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
-f, --force Continue even if we get an SQL error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.
-h, --host=name Connect to host.
--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.,
--ignore-table=database.table.
--include-master-host-port
Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGE
MASTER TO..' in dump produced with --dump-slave.
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the output file are terminated by the given
string.
-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.
(Defaults to on; use --skip-lock-tables to disable.)
--log-error=name Append warnings and errors to given file.
--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.
--max-allowed-packet=#
The maximum packet length to send to or receive from
server.
--net-buffer-length=#
The buffer size for TCP/IP and socket communication.
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
normally is output for each dumped database if
--all-databases or --databases is given.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Same as --skip-set-charset.
--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.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.
-P, --port=# Port number to use for connection.
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-q, --quick Don't buffer query, dump directly to stdout.
(Defaults to on; use --skip-quick to disable.)
-Q, --quote-names Quote table and column names with backticks (`).
(Defaults to on; use --skip-quote-names to disable.)
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in systems (e.g., DOS, Windows) that use carriage-return
linefeed pairs (\r\n) to separate text lines. This option
ensures that only a single newline is used.
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
(Defaults to on; use --skip-set-charset to disable.)
--set-gtid-purged[=name]
Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
values for this option are ON, OFF and AUTO. If ON is
used and GTIDs are not enabled on the server, an error is
generated. If OFF is used, this option does nothing. If
AUTO is used and GTIDs are enabled on the server, 'SET
@@GLOBAL.GTID_PURGED' is added to the output. If GTIDs
are disabled, AUTO does nothing. If no value is supplied
then the default (AUTO) value will be considered.
--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.
(Defaults to on; use --skip-dump-date to disable.)
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name The socket file to use for connection.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol.
(Defaults to on; use --skip-secure-auth to disable.)
--ssl Enable SSL for connection (automatically enabled with
other flags).
--ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
--ssl).
--ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=name X509 cert in PEM format (implies --ssl).
--ssl-cipher=name SSL cipher to use (implies --ssl).
--ssl-key=name X509 key in PEM format (implies --ssl).
--ssl-crl=name Certificate revocation list (implies --ssl).
--ssl-crlpath=name Certificate revocation list path (implies --ssl).
--ssl-verify-server-cert
Verify server's "Common Name" in its cert against
hostname used when connecting. This option is disabled by
default.
-T, --tab=name Create tab-separated textfile for each table to given
path. (Create .sql and .txt files.) NOTE: This only works
if mysqldump is run on the same machine as the mysqld
server.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table.
(Defaults to on; use --skip-triggers to disable.)
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
(Defaults to on; use --skip-tz-utc to disable.)
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records. Quotes are mandatory.
-X, --xml Dump a database as well formed XML.
--plugin-dir=name Directory for client-side plugins.
--default-auth=name Default authentication client-side plugin to use.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-drop-trigger FALSE
add-locks TRUE
allow-keywords FALSE
apply-slave-statements FALSE
bind-address (No default value)
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
dump-slave 0
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
include-master-host-port FALSE
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max-allowed-packet 25165824
net-buffer-length 1046528
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 0
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket (No default value)
secure-auth TRUE
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
ssl-verify-server-cert FALSE
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)
plugin-dir (No default value)
default-auth (No default value)
[root@rhel6_lhr ~]#
匯出 SQL 格式的資料到指定檔案，如下所示：
$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt password ******
以上命令建立的檔案內容如下：
-- MySQL dump 8.23 -- -- Host: localhost Database: TUTORIALS --------------------------------------------------------- -- Server version 3.23.58 -- -- Table structure for table `tutorials_tbl` -- CREATE TABLE tutorials_tbl ( tutorial_id int(11) NOT NULL auto_increment, tutorial_title varchar(100) NOT NULL default '', tutorial_author varchar(40) NOT NULL default '', submission_date date default NULL, PRIMARY KEY (tutorial_id), UNIQUE KEY AUTHOR_INDEX (tutorial_author) ) TYPE=MyISAM; -- -- Dumping data for table `tutorials_tbl` -- INSERT INTO tutorials_tbl VALUES (1,'Learn PHP','John Poul','2007-05-24'); INSERT INTO tutorials_tbl VALUES (2,'Learn MySQL','Abdul S','2007-05-24'); INSERT INTO tutorials_tbl VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');
一、 備份單庫
如果你需要匯出 單 個資料庫的資料，可以使用以下命令：
$ mysqldump -u root -p TUTORIALS > database_dump.txt password ******
[root@rhel6_lhr ~]# mysqldump -u root -p viewdb > /tmp/viewdb_bk.sql
Enter password:
[root@rhel6_lhr ~]# more /tmp/viewdb_bk.sql
-- MySQL dump 10.13 Distrib 5.6.21, for Linux (x86_64)
--
-- Host: localhost Database: viewdb
-- ------------------------------------------------------
-- Server version 5.6.21-enterprise-commercial-advanced-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 `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('F','M','UN') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'TubeLiu',20,'F'),(2,'Kevin',20,'F'),(3,'Mark',30,'F'),(4,'July',40,'M');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `userinfo`
--
DROP TABLE IF EXISTS `userinfo`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userinfo` (
`fid` int(11) NOT NULL,
`phone` int(11) DEFAULT NULL,
`location` varchar(20) DEFAULT NULL,
KEY `fk_userinfo_fid` (`fid`),
CONSTRAINT `fk_userinfo_fid` FOREIGN KEY (`fid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `userinfo`
--
LOCK TABLES `userinfo` WRITE;
/*!40000 ALTER TABLE `userinfo` DISABLE KEYS */;
INSERT INTO `userinfo` VALUES (1,12345,'Shanghai'),(2,54321,'Beijing'),(3,32145,'Shenzhen'),(4,34521,'Dalian');
/*!40000 ALTER TABLE `userinfo` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Temporary view structure for view `view_userinfo`
--
DROP TABLE IF EXISTS `view_userinfo`;
/*!50001 DROP VIEW IF EXISTS `view_userinfo`*/;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `view_userinfo` AS SELECT
1 AS `uname`,
1 AS `mobile`*/;
SET character_set_client = @saved_cs_client;
--
-- Final view structure for view `view_userinfo`
--
/*!50001 DROP VIEW IF EXISTS `view_userinfo`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8 */;
/*!50001 SET character_set_results = utf8 */;
/*!50001 SET collation_connection = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `view_userinfo` AS select `user`.`name` AS `uname`,`userinfo`.`phone` AS `mobile` from (`user` join `userinfo`) where (`user`.`id` = `userinfo`.`fid`) */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
/*!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 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-03-26 10:29:26
[root@rhel6_lhr ~]#
二、 備份多庫
如果需要備份所有資料庫，可以使用以下命令：
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******
--all-databases 選項在 MySQL 3.23.12 及以後版本加入。
該方法可用於實現資料庫的備份策略。
[root@rhel6_lhr ~]# mysqldump --databases -uroot -p db1 db4 lhr_test mydb opensource opesource viewdb worlddb wyzc > /tmp/mysqldb.sql
Enter password:
[root@rhel6_lhr ~]# more /tmp/mysqldb.sql
-- MySQL dump 10.13 Distrib 5.6.21, for Linux (x86_64)
--
-- Host: localhost Database: db1
-- ------------------------------------------------------
-- Server version 5.6.21-enterprise-commercial-advanced-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 */;
--
-- Current Database: `db1`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `db1`;
--
-- Table structure for table `join_student`
--
DROP TABLE IF EXISTS `join_student`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `join_student` (
`stu_id` int(11) NOT NULL AUTO_INCREMENT,
`stu_no` char(10) DEFAULT NULL,
`class_id` int(11) NOT NULL,
`stu_name` varchar(10) DEFAULT NULL,
`stu_info` text,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
。。。。。。。
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
[root@rhel6_lhr ~]# less /tmp/mysqldb.sql
[root@rhel6_lhr ~]#
[root@rhel6_lhr ~]# tail /tmp/mysqldb.sql
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-03-26 14:08:28
[root@rhel6_lhr ~]# ll -h /tmp/mysqldb.sql
-rw-r--r-- 1 root root 65M Mar 26 14:08 /tmp/mysqldb.sql
[root@rhel6_lhr ~]#
三、 備份單表
[root@rhel6_lhr ~]# mysqldump -u root -p worlddb City > /tmp/worldb_City_bk.sql
Enter password:
[root@rhel6_lhr ~]# more /tmp/worldb_City_bk.sql
-- MySQL dump 10.13 Distrib 5.6.21, for Linux (x86_64)
--
-- Host: localhost Database: worlddb
-- ------------------------------------------------------
-- Server version 5.6.21-enterprise-commercial-advanced-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 `City`
--
DROP TABLE IF EXISTS `City`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `City`
--
LOCK TABLES `City` WRITE;
/*!40000 ALTER TABLE `City` DISABLE KEYS */;
INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000),(2,'Qandahar','AFG','Qandahar',237500),(3,'Herat','AFG','Herat',186800),(4,'Mazar-e-Sharif','AFG','Balkh',127800),(5,'Amsterdam','NLD','Noord-Holland'
,731200),(6,'Rotterdam','NLD','Zuid-Holland',593321),(7,'Haag','NLD','Zuid-Holland',440900),(8,'Utrecht','NLD','Utrecht',234323),(9,'Eindhoven','NLD','Noord-Brabant',201843),(10,'Tilburg','NLD','Noord-Brabant',
193238),(11,'Groningen','NLD','Groningen',172701),(12,'Breda','NLD','Noord-Brabant',160398),(13,'Apeldoorn','NLD','Gelderland',153491),(14,'Nijmegen','NLD','Gelderland',152463),(15,'Enschede','NLD','Overijssel'
,149544),(16,'Haarlem','NLD','Noord-Holland',148772),(17,'Almere','NLD','Flevoland',142465),(18,'Arnhem','NLD','Gelderland',138020),(19,'Zaanstad','NLD','Noord-Holland',135621),(20,'´s-Hertogenbosch','NLD','Noo
。。。。。。
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-03-26 10:38:46
1.1.3 將資料表及資料庫拷貝至其他主機
如果你需要將資料拷貝至其他的 MySQL 伺服器上 , 你可以在 mysqldump 命令中指定資料庫名及資料表。
在源主機上執行以下命令，將資料備份至 dump.txt 檔案中 :
$mysqldump -u root -p database_name table_name > dump.txt password*****
如果完整備份資料庫，則無需使用特定的表名稱。
如果你需要將備份的資料庫匯入到 MySQL 伺服器中，可以使用以下命令，使用以下命令你需要確認資料庫已經建立：
$mysql -u root -p database_name < dump.txt password*****
你也可以使用以下命令將匯出的資料直接匯入到遠端的伺服器上，但請確保兩臺伺服器是相通的，是可以相互訪問的： </p>
$mysqldump-u root-p database_name | mysql-h other-host.com database_name
以上命令中使用了管道來將匯出的資料匯入到指定的遠端主機上。
利用MySQL全備份（mysqldump），如何只恢復一個庫或者一個表？
在實際工作中，一個MySQL例項中可能有多個database。而我們備份時，通常採用完全備份，將所有database都備份到一個檔案中。
但是，偶爾會遇到只恢復一個database或者一個表的情況。怎麼解決呢？
一、利用全備恢復一個庫（database）的資料
案例：朋友在群裡問， MySQL全庫備份。如何只恢復一個庫？
1、採用--one-database 選項。不建議使用該方式，經常出現問題。
# mysql -uroot -pxx -D db1 -o
2、從全備份檔案中將需要的庫的建表語句和INSERT資料拿出來，然後再匯入
# sed -n '/^-- Current Database: `db1`/,/^-- Current Database: `/p' all.dmp > db1.sql # mysql -uroot -pxx -D db1
二、利用全備恢復一張表（table）的資料
生產中遇到開發同事更新表時未加上where條件，造稱 order_status 列所有資料都被更新為0.
通常，mysqldump對全庫進行備份。恢復時如果恢復全庫，太耗時間又沒有必要。所以，我們只恢復想要的表即可。
mysqldump備份中，既有表結構，又有INSERT INTO語句包含資料。所以獲得者兩部分內容即可。
1、獲得表結構
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `ecs_ugo_order_info`/!d;q' mysqldump_2017-05-23.sql
2、獲得INSERT INTO 語句，用於資料的恢復
# grep -i 'INSERT INTO `ecs_ugo_order_info`' mysqldump_2017-05-23.sql >data.sql &
3、根據得到的表結構建立表，並匯入資料
mysql -uroot -pxxx xx
4、拼接update語句
mysql> select concat('update xx.ecs_ugo_order_info set order_status=',order_status,' where order_id=',order_id,';') from ecs_ugo_order_info into outfile '/tmp/ecs_ugo_order_info_rercovery.sql';
結果如下：
update xx.ecs_ugo_order_info set order_status=6 where order_id=3254778;
update xx.ecs_ugo_order_info set order_status=6 where order_id=3254824;
update xx.ecs_ugo_order_info set order_status=6 where order_id=3254870;
5、在生產庫中將order_status恢復成正常值
# mysql -uroot -pxxx xx < /tmp/ecs_ugo_order_info_rercovery.sql
About Me
|
........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、部落格園（ http://www.cnblogs.com/lhrbest ）和個人weixin公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● weixin群：可加我weixin，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2019-04-01 06:00 ~ 2019-04-30 24:00 在魔都完成 ● 最新修改時間：2019-04-01 06:00 ~ 2019-04-30 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客戶端 掃描下面的二維碼來關注小麥苗的weixin公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗weixin， 學習最實用的資料庫技術。
........................................................................................................................ |
|
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2641620/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 從MySQL全庫備份中恢復某個庫和某張表MySql
- mysqldump如何只備份表結構MySql
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- mysql備份恢復mysqldump面面觀MySql
- 主庫模型調整需要做一個全庫備份，備份與恢復指令碼模型指令碼
- mysqldump 備份單張表和恢復MySql
- mysql之 mysqldump 備份恢復詳解MySql
- 【mysqldump】mysqldump及備份恢復示例MySql
- MySQL資料庫mysqldump命令備份異常的一個案例MySql資料庫
- MySQL 備份和恢復 一MySql
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 【備份恢復】上：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 備份與恢復--一個表空間能否被多個資料庫讀寫？資料庫
- RMAN備份恢復整個庫
- mysqldump使用方法(MySQL資料庫的備份與恢復)MySql資料庫
- mysqldump常用備份恢復方法MySql
- MySQL innobackupex全量備份恢復MySql
- 使用mysqldump對mysql進行備份和恢復MySql
- RAC環境利用備份恢復RAC資料庫（一）資料庫
- mysql之 Innobackupex（全備+增量）備份恢復MySql
- mysqldump 恢復單個資料庫MySql資料庫
- 如何從MySQL全庫備份的dump檔案中恢復某張表和表結構MySql
- RAC一個節點恢復另一個節點在帶庫上的備份
- 備份與恢復--一個表空間能否被多個資料庫同時開啟？資料庫
- mysql 利用binlog增量備份、恢復MySql
- 【備份恢復】 丟失一個控制檔案 之恢復操作
- MySQL入門--備份與恢復（一）MySql
- mysql備份每個庫下面每個表MySql
- 利用innobackupex備份集恢復指定庫
- Oracle12c多租戶資料庫備份與恢復 - 恢復一個PDBOracle資料庫
- 【Mysql】xbackup全量與增量備份恢復MySql
- 單例項環境利用備份恢復RAC資料庫（一）單例資料庫
- rman資料庫全庫備份與恢復資料庫
- MySQL備份恢復第一篇MySql
- mysql備份和恢復測試(一）--xtrabackupMySql
- mysql 備份資料庫 mysqldumpMySql資料庫
- 備份與恢復--利用備份的控制檔案恢復
- Mysql備份恢復MySql