摘要：本文主要學(xué)習(xí)了如何備份和還原數(shù)據(jù)庫。

部分內(nèi)容來自以下博客：

https://www.cnblogs.com/chenmh/p/5300370.html

常用命令

導(dǎo)出數(shù)據(jù)

導(dǎo)出2019年1月的數(shù)據(jù)，數(shù)據(jù)庫是ide，表是user，要求不需要導(dǎo)出建庫語句，要求使用完成的插入語句，并且合并插入：

1 [root@localhost ~]# mysqldump -h 127.0.0.1 -P 3306 -uroot -p'123456' --no-create-info --databases ide --tables user 2 > --where=" date >= '2019-01-01' and date < '2019-02-01' " --complete-insert --extended-insert > '/data/dump/201901.txt'

導(dǎo)入數(shù)據(jù)

1 [root@localhost ~]# mysql -uroot -p'123456' --database ide < db_back.sql

導(dǎo)出數(shù)據(jù)

導(dǎo)出數(shù)據(jù)一般使用mysqldump命令在Linux系統(tǒng)執(zhí)行。

語法

導(dǎo)出全部數(shù)據(jù)庫：

1 mysqldump [OPTIONS] --all-databases [OPTIONS]

導(dǎo)出指定數(shù)據(jù)庫：

1 mysqldump [OPTIONS] --databases DB1 [DB2 DB3...] [OPTIONS]

導(dǎo)出指定數(shù)據(jù)庫的指定表：

1 mysqldump [OPTIONS] --database DB --tables TB [TB2 TB3...] [OPTIONS]

說明

用戶名：

登錄數(shù)據(jù)庫的用戶名，當(dāng)前用戶可省略該配置，“-u”后面緊跟用戶名，或者使用“--user=”后面緊跟用戶名的方式。

1 --user, -u

密碼：

登錄數(shù)據(jù)庫的密碼，“-p”后面緊跟密碼，或使用“--password=”后面緊跟密碼的方式。

1 --password, -p

主機(jī)地址：

設(shè)置數(shù)據(jù)庫所在的主機(jī)地址，默認(rèn)是localhost。

1 --host, -h

端口號：

設(shè)置用于連接的端口號。

1 -P, --port

連接協(xié)議：

設(shè)置使用的連接協(xié)議，取值有TCP、SOCKET、PIPE、MEMORY四種。

1 --protocol

套接字文件：

指定當(dāng)連接localhost時的套接字文件位置，默認(rèn)路徑是/tmp/mysql.sock。

1 -S, --socket

導(dǎo)出全部數(shù)據(jù)庫：

1 --all-databases, -A

導(dǎo)出指定數(shù)據(jù)庫：

1 --databases, -B

導(dǎo)出指定表：

只能針對一個數(shù)據(jù)庫進(jìn)行導(dǎo)出，并且導(dǎo)出的內(nèi)容中沒有創(chuàng)建數(shù)據(jù)庫的判斷語句。

1 --tables

不導(dǎo)出指定表：

指定忽略多個表時，需要重復(fù)多次，每次一個表，每個表必須同時指定數(shù)據(jù)庫和表名。

1 --ignore-table

導(dǎo)出全部表空間：

1 --all-tablespaces, -Y

不導(dǎo)出表空間信息：

1 --no-tablespaces, -y

不添加創(chuàng)建數(shù)據(jù)庫語句：

默認(rèn)添加。

1 --no-create-db, -n

添加刪除數(shù)據(jù)庫語句：

默認(rèn)不添加。

1 --add-drop-database

不添加創(chuàng)建表語句：

默認(rèn)添加。

1 --no-create-info, -t

添加刪除表語句：

默認(rèn)添加，使用“--skip-add-drop-table”參數(shù)取消。

1 --add-drop-table
2 –-skip-add-drop-table

不導(dǎo)出數(shù)據(jù)只導(dǎo)出表結(jié)構(gòu)：

1 --no-data, -d

導(dǎo)出存儲過程以及自定義函數(shù)：

1 --routines, -R

在每個表導(dǎo)出前鎖表并在導(dǎo)出后解鎖：

默認(rèn)為打開狀態(tài)，使用“--skip-add-locks”參數(shù)取消。

1 --add-locks
2 --skip-add-locks

設(shè)置默認(rèn)的字符集：

默認(rèn)是utf8。

1 --default-character-set

在客戶端和服務(wù)器之間啟用壓縮傳遞所有信息：

1 --compress, -C

處理換行：

直接輸出到指定文件中。該選項應(yīng)該用在使用回車換行對（\\r\\n）換行的系統(tǒng)上（例如：DOS，Windows）。該選項確保只有一行被使用。

1 --result-file, -r

添加導(dǎo)出時間：

將導(dǎo)出時間添加到輸出文件中。默認(rèn)為打開狀態(tài)，使用“--skip-dump-date”參數(shù)取消。

1 --dump-date
2 --skip-dump-date

只導(dǎo)出符合指定條件的記錄：

如果條件包含命令解釋符專用空格或字符，一定要將條件引用起來。

1 --where, -w

合并多個插入語句：

默認(rèn)開啟，使用“--skip-extended-insert”關(guān)閉。

1 --extended-insert
2 --skip-extended-insert

使用完整的插入語句：

使用包含列名稱的完整插入語句。這么做能提高插入效率，但是可能會受到max_allowed_packet參數(shù)的影響而導(dǎo)致插入失敗。

1 --complete-insert, -c

忽略插入錯誤：

默認(rèn)不添加，在插入語句中添加忽略錯誤語句。

1 --insert-ignore

服務(wù)器發(fā)送和接受的最大包長度：

客戶端/服務(wù)器之間通信的緩存區(qū)的最大大小，最大為1GB。

1 --max_allowed_packet

強(qiáng)制插入：

在表轉(zhuǎn)儲過程中，即使出現(xiàn)SQL錯誤也繼續(xù)。

1 --force, -f

實例

導(dǎo)出全部數(shù)據(jù)庫：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases > db_back.sql

導(dǎo)出指定數(shù)據(jù)庫：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --databases test demo > db_back.sql

導(dǎo)出指定表：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --database test --tables demo user > db_back.sql

不導(dǎo)出指定表：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases 2 > --ignore-table=database.table1 --ignore-table=database.table2 > db_back.sql

設(shè)置默認(rèn)字符集：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --default-character-set=latin1 > db_back.sql

處理換行：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --result-file=/tmp/mysqldump_result_file.txt

只導(dǎo)出符合指定條件的記錄：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --where=" id=1 limit 10"

使用完整的插入語句：

1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --complete-insert

導(dǎo)入數(shù)據(jù)

導(dǎo)入數(shù)據(jù)有兩種方式，一種是在Linux系統(tǒng)使用<命令導(dǎo)入，另一種是在MySQL數(shù)據(jù)庫使用source命令導(dǎo)入。

語法

在Linux系統(tǒng)使用<命令：

1 mysql -u用戶名 -p密碼 數(shù)據(jù)庫名 < 文件路徑

在MySQL數(shù)據(jù)庫使用source命令：

1 source 文件路徑

實例

在Linux系統(tǒng)使用<命令：

1 [root@localhost ~]# mysql -uroot -p123456 database < db_back.sql

在MySQL數(shù)據(jù)庫使用source命令：

1 mysql> source /home/user/data/db_back.sql

效率比較

在文件較小的情況下，source速度比mysql高。在導(dǎo)入大的文件時，建議使用mysql命令。

優(yōu)化導(dǎo)入速度

對于百M(fèi)級以上文件，根據(jù)MySQL官方建議，有幾個措施可以極大提高導(dǎo)入的速度。

修改配置文件

配置如下：

1 [mysqld]
2 bulk_insert_buffer_size=2G;
3 innodb_log_buffer_size=2G;
4 innodb_autoinc_lock_mode=2;

說明：

對于MyISAM，調(diào)整系統(tǒng)參數(shù)：bulk_insert_buffer_size。改為至少單個文件大小的2倍以上。

對于InnoDB，調(diào)整系統(tǒng)參數(shù)：innodb_log_buffer_size。改為至少單個文件大小的2倍以上，導(dǎo)入完成后可以改回默認(rèn)的8M，注意不是innodb_buffer_pool_size。

有自增列的，設(shè)置：innodb_autoinc_lock_mode的值為2。

刪除索引

除主鍵外，刪除其他索引，導(dǎo)入完成后重建索引。

執(zhí)行批量腳本

bash腳本內(nèi)容：

1 for SQL in *.sql;
2 do
3 echo $SQL;
4 sed -i '1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;' $SQL
5 sed -i '$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;' $SQL
6 done

說明：

關(guān)閉自動提交：autocommit=0。

關(guān)閉唯一索引檢查：unique_checks=0。

關(guān)閉外鍵檢查：foreign_key_checks=0。