Mysql資料庫的備份

1.mysqldump

mysqldump 是采用SQL級别的備份機制，它将資料表導成 SQL 腳本檔案，

在不同的 MySQL 版本之間更新時相對比較合适，也是最常用的備份方法。

備份：mysqldump -uroot -p 資料庫名 > /root/資料庫名.sql

恢複資料庫：首先得先建立:create database 資料庫名；

     mysql -uroot -p 資料庫名 < /root/資料庫名.sql

或者 mysql>Source /root/資料庫名.sql

例如：mysqldump -uroot -p mydb > /root/mydb.sql

  mysql>create database mydb;

mysql -uroot -p mydb < /root/mydb.sql

或者 source /root/mydb.sql

常見選項：

mysqldump -uroot -p --all-databases （備份所有資料庫）> /root/mysql.sql

--compatible=name 

它告訴 mysqldump，導出的資料将和哪種資料庫或哪個舊版本的 MySQL 伺服器相相容

--complete-insert，-c

導出的資料采用包含字段名的完整 INSERT 方式，也就是把所有的值都寫在一行

--default-character-set=charset

指定導出資料時采用何種字元集，如果資料表不是采用預設的 latin1 字元集的話，那麼

導出時必須指定該選項，否則再次導入資料後将産生亂碼問題。

--extended-insert = true|false

預設情況下，mysqldump 開啟 --complete-insert 模式，是以不想用它的的話，就使用本選項，

設定它的值為 false 即可。

--hex-blob

使用十六進制格式導出二進制字元串字段。如果有二進制資料就必須使用本選項。影響到的字

段類型有 BINARY、VARBINARY、BLOB。

--lock-all-tables，-x

在開始導出之前，送出請求鎖定所有資料庫中的所有表，以保證資料的一緻性。這是一個全局讀鎖，

并且自動關閉 --single-transaction 和 --lock-tables 選項。

--lock-tables

它和 --lock-all-tables 類似，不過是鎖定目前導出的資料表，而不是一下子鎖定全部庫下

的表。本選項隻适用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 選項。

--no-create-info，-t

隻導出資料，而不添加 CREATE TABLE 語句。

--no-data，-d

不導出任何資料，隻導出資料庫表結構。

--quick，-q

該選項在導出大表時很有用，它強制 mysqldump 從伺服器查詢取得記錄直接輸出而不是取得所有記錄後将它們緩存到記憶體中。

--routines，-R

導出存儲過程以及自定義函數。

--single-transaction

該選項在導出資料之前送出一個 BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證導出時資料庫的一緻性狀态。它隻适用于事務表，例如 InnoDB 和 BDB。

--triggers

同時導出觸發器。該選項預設啟用，用 --skip-triggers 禁用它

例如：使用以下 SQL 來備份 MyISAM 表：

mysqldump -uroot -p123 \

--default-character-set=utf8 --opt --extended-insert=false \

--triggers -R --hex-blob -x db_name > db_name.sql

使用以下 SQL 來備份 Innodb 表：

--triggers -R --hex-blob --single-transaction db_name > db_name.sql

實作線上備份，還可以使用 --master-data 參數來實作：

--default-character-set=utf8 --opt --master-data=1 \

--single-transaction --flush-logs db_name > db_name.sql

2.select inito outfile  隻針對某一個表或某一個表中的資料進行備份

備份：mysql> use mydb;

mysql>show tables;

mysql>select * inio outfile '/tmp/stu.sql' from stu;

恢複：mysql>TRUNCATE TABLE stu;(删除表）

mysql>LOAD DATA INFILE '/tmp/stu.sql' INTO TABLE stu;

注意：SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;

必須要有 FILE 權限才能執行本SQL，并且檔案 /tmp/db_name/tbl_name.txt 必須能被 mysqld 使用者可寫，導出的檔案不能覆寫已經存在的檔案，以避免安全問題

3.mk-parallel-dump 實作并行備份，mysqldump是單程序的，同時隻能備份1個表。

mk-parallel-dump它會同時并發4個mysqldump程序，同時備份4個表，預設是先備份大表，

在備份小表。和mysqldump出來生成一個檔案不同，它将每個表生成一個單獨的檔案。

mk-parallel-dump --basedir=/backups

mk-parallel-restore /backups

4.snapshot 使用快照備份

snapshot備份原理：

LVM snapshot的原理是當一個snapshot建立的時候，僅拷貝原始卷裡資料的中繼資料(meta-data)。

建立的時候，并不會有資料的實體拷貝，是以snapshot的建立幾乎是實時的，當原始卷上有寫操作執行時，

snapshot跟蹤原始卷塊的改變，這個時候原始卷上将要改變的資料在改變之前被拷貝到snapshot預留的空間裡，

是以這個原理的實作叫做寫時複制(copy-on-write)。

在寫操作寫入塊之前，将原始資料移動到 snapshot空間裡，這樣就保證了所有的資料在snapshot建立時保持一緻。

而對于snapshot的讀操作，如果是讀取資料塊是沒有修改過的，那麼會将讀操作直接重定向到原始卷上，

如果是要讀取已經修改過的塊，那麼就讀取拷貝到snapshot中的塊。

lvcreate -L 10G -n mydata vg0      建立邏輯卷

mkfs.ext3 /dev/vg0/mydata          格式化

mount /dev/vg0/mydata  /home/mysql  挂載到/home/mysql

lvcreate -L 1G -n mydatasnap -s vg0  做快照備份

5.mysqlhotcopy

mysqlhotcopy 是一個 PERL 程式它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 來快速備份資料庫。它是備份資料庫或單個表的最快的途徑，但它隻能運作在資料庫檔案（包括資料表定義檔案、資料檔案、索引檔案）所在的機器上。mysqlhotcopy 隻能用于備份 MyISAM，并且隻能運作在 類Unix 和 NetWare 系統上。 

備份之前最好将資料庫中的表LOCK

mysql> LOCK TABLE stu READ; (鎖定stu表）

mysql>UNLOCK TABLES stu; （解開表）

mysqlhotcopy mydb /tmp

mysqlhotcopy --method=scp mydb \[email protected]:/backup （遠端備份）

恢複備份：cp -rf mydb /usr/local/mysql/data

chown -R nobody:nobody /usr/local/mysql/data/ (将 db_name 目錄的屬主改成 mysqld 運作使用者)

時間點恢複：mysqlbinlog --start-position 15000(上次完全備份之後開始位置） --end-position 20000 （使用者做誤删除之前的位置）| mysql -uroot -p

mysqlbinlog --start-position 15000 --end-position 20000 > /tmp/bin.sql

mysql -uroot -p < /tmp/bin.sql

6.ibbackup

相比MYSQL自身的MYSQLDUMP 導入導出工具來說有以下優點：

實體備份；還原速度快。

而MYSQLDUMP在并發小的時候還可以用下。缺點如下：

邏輯備份；還原速度慢。

備份：/ibbackup --apply-log backup.cnf

整個的還原過程是：

1.先停掉MYSQLD。

2.COPY這些檔案到現網的MySQL資料以及日志目錄。

2.再次啟動MYSQLD。