MySQL學習——備份和還原

摘要：本文主要學習了如何備份和還原資料庫，

部分內容來自以下博客：

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

常用命令

匯出資料

匯出2019年1月的資料，資料庫是ide，表是user，要求不需要匯出建庫陳述句，要求使用完成的插入陳述句，并且合并插入：

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'

匯入資料

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

匯出資料

匯出資料一般使用mysqldump命令在Linux系統執行，

語法

匯出全部資料庫：

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

匯出指定資料庫：

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

匯出指定資料庫的指定表：

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

說明

用戶名：

登錄資料庫的用戶名，當前用戶可省略該配置，“-u”后面緊跟用戶名，或者使用“--user=”后面緊跟用戶名的方式，

1 --user, -u

密碼：

登錄資料庫的密碼，“-p”后面緊跟密碼，或使用“--password=”后面緊跟密碼的方式，

1 --password, -p

主機地址：

設定資料庫所在的主機地址，默認是localhost，

1 --host, -h

埠號：

設定用于連接的埠號，

1 -P, --port

連接協議：

設定使用的連接協議，取值有TCP、SOCKET、PIPE、MEMORY四種，

1 --protocol

套接字檔案：

指定當連接localhost時的套接字檔案位置，默認路徑是/tmp/mysql.sock，

1 -S, --socket

匯出全部資料庫：

1 --all-databases, -A

匯出指定資料庫：

1 --databases, -B

匯出指定表：

只能針對一個資料庫進行匯出，并且匯出的內容中沒有創建資料庫的判斷陳述句，

1 --tables

不匯出指定表：

指定忽略多個表時，需要重復多次，每次一個表，每個表必須同時指定資料庫和表名，

1 --ignore-table

匯出全部表空間：

1 --all-tablespaces, -Y

不匯出表空間資訊：

1 --no-tablespaces, -y

不添加創建資料庫陳述句：

默認添加，

1 --no-create-db, -n

添加洗掉資料庫陳述句：

默認不添加，

1 --add-drop-database

不添加創建表陳述句：

默認添加，

1 --no-create-info, -t

添加洗掉表陳述句：

默認添加，使用“--skip-add-drop-table”引數取消，

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

不匯出資料只匯出表結構：

1 --no-data, -d

匯出存盤程序以及自定義函式：

1 --routines, -R

在每個表匯出前鎖表并在匯出后解鎖：

默認為打開狀態，使用“--skip-add-locks”引數取消，

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

設定默認的字符集：

默認是utf8，

1 --default-character-set

在客戶端和服務器之間啟用壓縮傳遞所有資訊：

1 --compress, -C

處理換行：

直接輸出到指定檔案中，該選項應該用在使用回車換行對（\\r\\n）換行的系統上（例如：DOS，Windows），該選項確保只有一行被使用，

1 --result-file, -r

添加匯出時間：

將匯出時間添加到輸出檔案中，默認為打開狀態，使用“--skip-dump-date”引數取消，

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

只匯出符合指定條件的記錄：

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

1 --where, -w

合并多個插入陳述句：

默認開啟，使用“--skip-extended-insert”關閉，

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

使用完整的插入陳述句：

使用包含列名稱的完整插入陳述句，這么做能提高插入效率，但是可能會受到max_allowed_packet引數的影響而導致插入失敗，

1 --complete-insert, -c

忽略插入錯誤：

默認不添加，在插入陳述句中添加忽略錯誤陳述句，

1 --insert-ignore

服務器發送和接受的最大包長度：

客戶端/服務器之間通信的快取區的最大大小，最大為1GB，

1 --max_allowed_packet

強制插入：

在表轉儲程序中，即使出現SQL錯誤也繼續，

1 --force, -f

實體

匯出全部資料庫：

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

匯出指定資料庫：

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

匯出指定表：

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

不匯出指定表：

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

設定默認字符集：

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

只匯出符合指定條件的記錄：

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

匯入資料

匯入資料有兩種方式，一種是在Linux系統使用<命令匯入，另一種是在MySQL資料庫使用source命令匯入，

語法

在Linux系統使用<命令：

1 mysql -u用戶名 -p密碼 資料庫名 < 檔案路徑

在MySQL資料庫使用source命令：

1 source 檔案路徑

實體

在Linux系統使用<命令：

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

在MySQL資料庫使用source命令：

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

效率比較

在檔案較小的情況下，source速度比mysql高，在匯入大的檔案時，建議使用mysql命令，

優化匯入速度

對于百M級以上檔案，根據MySQL官方建議，有幾個措施可以極大提高匯入的速度，

修改組態檔

配置如下：

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

說明：

對于MyISAM，調整系統引數：bulk_insert_buffer_size，改為至少單個檔案大小的2倍以上，

對于InnoDB，調整系統引數：innodb_log_buffer_size，改為至少單個檔案大小的2倍以上，匯入完成后可以改回默認的8M，注意不是innodb_buffer_pool_size，

有自增列的，設定：innodb_autoinc_lock_mode的值為2，

洗掉索引

除主鍵外，洗掉其他索引，匯入完成后重建索引，

執行批量腳本

bash腳本內容：

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

說明：

關閉自動提交：autocommit=0，

關閉唯一索引檢查：unique_checks=0，

關閉外鍵檢查：foreign_key_checks=0，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/92739.html

標籤：MySQL

上一篇：資料庫中間件DBLE學習(一) 基礎介紹和快速搭建

下一篇：mysql中的表操作