MySQL備份和恢復具體實施(上)

kumu_linux發表於2012-11-30
MySql


MySQL備份類別，參考筆者上篇文章MySQL備份類別

冷備：

對於InnoDB儲存引擎的冷備非常簡單，只需要備份MySQL資料庫的frm檔案、共享表空間檔案、獨立表空間檔案（*.ibd）、重做日誌檔案。--建議定期備份MySQL資料庫的配置檔案my.cnf

 

冷備的優點：

a)        備份簡單，只要拷貝相關檔案

b)        備份檔案易於在不同作業系統，不同MySQL版本進行恢復

c)        恢復相當簡單，只需要把檔案恢復到指定位置即可

d)        恢復速度快，不需要執行任何SQL語句，也不需要重建索引

冷備的缺點：

a)        InnoDB儲存引擎冷備的檔案通常比邏輯檔案大很多，因為表空間中存放著資料，如Undo段，插入緩衝等資訊

b)        冷備不總是可以輕易地跨平臺。作業系統、MySQL的版本、檔案大小寫敏感和浮點數格式都會成為問題

 

邏輯備份：

mysqldump備份工具

mysqldump的語法如下：

shell>mysqldump [argument] > file_name

 

備份全部資料庫，使用--all-database選項：

shell>mysqldump --all-databases > all.sql


備份指定的資料庫，--database：

shell> mysqldump–database db1 db2 db3 > dump.sql

 

mysqldump一些重要引數選項：

--single-transaction：在備份開始前，先執行STARTTRANSACTION命令，以此來獲得備份的一致性

--lock-tables(-l):在備份中，以此鎖住每個架構下的所有表。一般用於MyISAM儲存引擎，備份時只能對資料庫進行讀取操作，不過備份依然可以保證一致性。對於InnoDB不需要使用該引數，用--single-transaction即可，並且--lock-tables和--single-transaction是互斥的，不能同時使用。如果你的MySQL資料庫中既有MyISAM儲存引擎表，又有InnoDB儲存引擎表，那麼這時你的選擇只有--lock-tables了。--lock-tables選項是依次對每個架構中的表上鎖的，因此只能保證每個架構下表備份的一致性，不能保證所有架構下表的一致性。

--lock-all-tables（-x）：在備份過程中，對所有架構中的所有表上鎖

--add-drop-database：在createdatabase之前先執行drop database。這個引數需要和--all-databases或者--database選項一起使用

 

備份指定的表：

# mysqldump--single-transaction --socket=/var/run/mysqld/mysql5.socket test1 test1 >test1.sql

# cattest1.sql

-- MySQL dump 10.13 Distrib 5.1.61, for redhat-linux-gnu (i386)

--

-- Host: localhost    Database: test1

-- ------------------------------------------------------

-- Server version       5.5.20-ndb-7.2.5-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 `test1`

--

 

DROP TABLE IF EXISTS `test1`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `test1` (

  `id`int(11) NOT NULL,

  `name`varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client*/;

 

--

-- Dumping data for table `test1`

--

 

LOCK TABLES `test1` WRITE;

/*!40000 ALTER TABLE `test1` DISABLE KEYS */;

INSERT INTO `test1` VALUES(1,'wu'),(2,'terry'),(3,'tang'),(4,'jack'),(4,'cat'),(3,NULL),(3,'dog');

/*!40000 ALTER TABLE `test1` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;

/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

-- Dump completed on 2012-11-10  1:39:53

 

select … into outfile

select..into語句也是一種邏輯備份的方法，或者更準確地說是匯出一張表中的資料

SELECT [column 1],[column2] … INTO OUTFILE'file_name' from table where… …

mysql> select * into outfile '/root/test1.txt' from test1;  //必須是mysql使用者可寫

ERROR 1 (HY000): Can't create/write to file'/root/test1.txt' (Errcode: 13)

mysql> select * into outfile '/home/mysql/test1.txt' from test1;

Query OK, 7 rows affected (0.00 sec)

mysql> quit

Bye

# cat/home/mysql/test1.txt

1       wu

2       terry

3       tang

4       jack

4       cat

3       \N

3       dog

 

# mysql -e"select * into outfile '/home/mysql/test_bak' from test1.test1";

# cat/home/mysql/test_bak

1       wu

2       terry

3       tang

4       jack

4       cat

3       \N

3       dog

 

邏輯備份的恢復:

         mysqldump的恢復操作比較簡單，因為備份的檔案就是匯出的SQL語句

在shell命令列匯入：

# mysqldump-uroot -p --socket=/var/run/mysqld/mysql5.socket >test1.sql test1

 

使用source恢復：

mysql> usetest1;

 

mysql>source /root/test1.sql;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

… …

 

注：mysqldump可以匯出儲存過程、觸發器、事件、資料，但是卻不能匯出檢視

 

Load data infile

若是通過mysqldump --tab或者select into outfile匯出的資料需要恢復時，則需要通過Load data infile命令來進行匯入

LOAD DATA [LOW_PRIORITY | CONCURRENT][LOCAL] INFILE 'file_name'

   [REPLACE | IGNORE]

   INTO TABLE tbl_name

   [CHARACTER SET charset_name]

   [{FIELDS | COLUMNS}

       [TERMINATED BY 'string']

       [[OPTIONALLY] ENCLOSED BY 'char']

       [ESCAPED BY 'char']

    ]

   [LINES

       [STARTING BY 'string']

       [TERMINATED BY 'string']

    ]

   [IGNORE number LINES]

   [(col_name_or_user_var,...)]

[SET col_name =expr,...]

mysql> select * fromtest1;
Empty set (0.00 sec)

mysql> load datainfile '/home/mysql/test1.txt' into table test1;
Query OK, 7 rows affected (0.05 sec)
Records: 7  Deleted:0  Skipped: 0  Warnings: 0

mysql> select * fromtest1;
+----+-------+
| id | name  |
+----+-------+
|  1 | wu    |
|  2 | terry |
|  3 | tang  |
……

7 rows in set (0.00 sec)

mysql>

為了更快的匯入，可以在匯入過程忽略對外來鍵的檢查，可以使用：

mysqlimport

         mysqlimport是MySQL資料庫提供的一個命令列程式，從本質上來說，是LOAD DATA INFILE的命令介面，而且大多數的選項都和LOAD DATA INFILE語法相同

shell> mysqlimport[options] db_name testfile1 [textfile2 …]

 

         與LOAD DATA INFILE不同的是，mysqlimport命令是可以匯入多張表的，並且通過--user-thread引數來併發匯入不同的檔案。這裡的併發是指併發匯入多個檔案，並不是指mysqlimport可以併發地匯入一個檔案，這是有區別的，並且併發地對同一張表進行匯入，效果一般不會比序列的方式好。

通過mysqlimport併發匯入兩張表：

# /usr/local/mysql/bin/mysqlimport--use-threads=2 -S /var/run/mysqld/mysql5.socket test1 /home/mysql/test1.txt/home/mysql/test3.txt

test1.test1: Records: 7  Deleted: 0 Skipped: 0  Warnings: 0

test1.test3: Records: 7  Deleted: 0 Skipped: 0  Warnings: 0

二進位制日誌備份和恢復

         二進位制日誌非常關鍵，我們可以通過它來完成point-in-time的恢復工作

開啟二進位制日誌:

[mysqld]

bin-log

sync_binlog=1

 

sync_binlog

如果為正，當每個sync_binlog’th寫入該二進位制日誌後，MySQL伺服器將它的二進位制日誌同步到硬碟上(fdatasync())。請注意如果在autocommit模式，每執行一個語句向二進位制日誌寫入一次，否則每個事務寫入一次。預設值是0，不與硬碟同步。值為1是最安全的選擇，因為崩潰時，你最多丟掉二進位制日誌中的一個語句/事務；但是，這是最慢的選擇(除非硬碟有電池備份快取，從而使同步工作較快)。

 

二進位制日誌預設存在databasedir目錄下,如下mysql5-bin.000001

使用mysqlbinlog檢視二進位制日誌的內容：

# mysqlbinlogmysql5-bin.000001

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#121110 15:40:06 server id 1 end_log_pos 112   Start: binlog v4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

1gSeUA8BAAAAbAAAAHAAAAABAAQANS41LjIwLW5kYi03LjIuNS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADWBJ5QEzgNAAgAEgAEBAQEEgAAWQAEGggAAAAICAgCAAAACgoK

'/*!*/;

… …

 

mysqlbinlog有一些選項可以使用,簡單說明常用選項:

Ø  -d,--database=name :指定資料庫名稱,只列出指定資料庫的操作.

Ø  -D, --disable-log-bin :執行恢復的時候,禁止二進位制日誌.可以防止同一臺MySQL加上-t時進入死迴圈

Ø  -o,--offset=n :忽略掉日誌前n行命令

Ø  -r,--result-file=name :將輸出日誌到指定檔案

Ø  -R,--read-from-remote-server :從一個MySQL伺服器上讀取二進位制

Ø  -s,--short-form :顯示簡單格式,省略一些資訊

Ø  -S, --socket=name  :socket檔案連線path.

Ø  -t, --to-last-log  :和-R一起使用,在二進位制日誌結束的時候並不會停止,而是在MySQL伺服器最後生成的binlog結束,如果輸出和輸入都在一臺MySQL上可能會導致死迴圈.

Ø  --set-charset=char-name :在輸出文字格式的時候,在第一行加上set names char-name.

Ø  --start-datetime=#--stop-datetime=# :指定輸出起始日期的日誌.

Ø  --start-position=#--stop-position=# :指定起始日誌的位置.

 

# mysqlbinlog -d test2mysql5-bin.000001 

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#121110 15:40:06 server id 1 end_log_pos 112          Start:binlog v 4, server v 5.5.20-ndb-7.2.5-log created 121110 15:40:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

1gSeUA8BAAAAbAAAAHAAAAABAAQANS41LjIwLW5kYi03LjIuNS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADWBJ5QEzgNAAgAEgAEBAQEEgAAWQAEGggAAAAICAgCAAAACgoK

'/*!*/;

# at 112

#121110 16:07:51 server id 1 end_log_pos 197          Query       thread_id=3     exec_time=0    error_code=0

SET TIMESTAMP=1352534871/*!*/;

SET @@session.pseudo_thread_id=3/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,@@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=0/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C latin1 *//*!*/;

SET@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

createdatabase test2

/*!*/;

… …

 

# mysqlbinlog -d test1--stop-position=870 mysql5-bin.000001 | mysql -uroot -p

 

--start-datetime=# --stop-datetime=# :指定輸出起始日期的日誌.

--start-position=# --stop-position=# :指定起始日誌的位置.

以上兩個選項類似,一個日期,一個日誌的位置

文章內容為筆者讀《MySQL技術內幕Innodb引擎》筆記

相關文章