MYSQL資料的備份與恢複

 MYSQL資料的備份與恢複

1 SQL資料導入導出

實驗内容：

1．使用SQL語句将/etc/passwd檔案導入userdb庫userlist表，并給每條記錄添加自動編号。

2．将userdb庫userlist表中UID小于100的前10條記錄導出，存為/dbak/ulist.txt檔案。

實驗實作：

1．将/etc/passwd檔案導入MySQL資料庫

 導入後的表結構取決于/etc/passwd配置檔案。若一時記不住各字段的含義，也可以檢視passwd配置檔案的man手冊頁，找到格式描述相關的說明，比如：

# man 5 passwd

.. ..

There is one entry per line, and each line has the format:  account:password:UID:GID:GECOS:directory:shell                                                 //各字段的順序、大緻用途

1）建立userdb庫、userlist表

以資料庫使用者root登入MySQL服務：

# mysql -u root -p 123456

建立userdb庫，切換到userdb庫：

mysql> CREATE DATABASE userdb; Query OK, 1 row affected (0.04 sec)  mysql> USE userdb; Database changed

mysql> CREATE DATABASE userdb;

Query OK, 1 row affected (0.04 sec)

mysql> USE userdb;

Database changed

建立userlist表，字段設定及相關操作參考如下：

mysql> CREATE TABLE userlist( -> username varchar(24) NOT NULL,

-> password varchar(48) DEFAULT 'x',

-> uid int(5) NOT NULL,

-> gid int(5) NOT NULL,

-> fullname varchar(48),

-> homedir varchar(64) NOT NULL,

-> shell varchar(24) NOT NULL

-> );

Query OK, 0 rows affected (0.17 sec)

mysql> DESC userlist; //确認userlist表的結構：

2）執行導入操作

讀取/etc/passwd檔案内容，以“:”為分隔，導入到userlist表中：

mysql>load data infile "/etc/passwd" //執行導入表中

→into table userlist

→fields terminated by ":"

→lines  terminated by "\n";

query ok

3）确認導入結果

分别統計userlist、userlist2表内的記錄個數：

mysql> SELECT COUNT(*) FROM userlist;

mysql> SELECT COUNT(*) FROM userlist2;

2．為userlist表中的每條記錄添加自動編号

這個隻要修改userlist表結構，添加一個自增字段即可。

比如，添加一個名為sn的序号列，作為userlist表的第一個字段：

1）添加自增主鍵字段sn

mysql> ALTER TABLE userlist -> ADD sn int(4) AUTO_INCREMENT PRIMARY KEY FIRST;  

2）驗證自動編号結果

檢視userlist表的前10條記錄，列出序号、使用者名、UID、GID、宿主目錄：

mysql> SELECT sn,username,uid,gid,homedir -> FROM userlist LIMIT 10;

3．從MySQL資料庫中導出查詢結果

以将userdb庫userlist表中UID小于100的前10條記錄導出為/dbak/ulist.txt檔案為例，首先要確定目标檔案夾存在，且msyql使用者有權限寫入（否則導出會失敗）。

1）确認存放導出資料的檔案夾

# mkdir /dbbak                     //若沒有此檔案夾，可建立

# chown mysql /dbbak             //確定mysql有權限寫入

# ls -ld /dbbak/                 //确認權限 drwxr-xr-x.

mysql root 4096 1月 10 17:46 /dbbak/

）導出userlsit表中UID小于100的前10條記錄

如果以預設的'\n' 為行分隔，導出操作同樣可不指定LINES TERMINATED BY：

mysql> SELECT * FROM userdb.userlist WHERE uid<100   //執行導出操作

   -> INTO OUTFILE '/dbbak/ulist.txt'

    -> FIELDS TERMINATED BY ':';

Query OK, 26 rows affected (0.08 sec)

3）确認導出結果

傳回到Shell指令行，檢視/dbbak/ulist.txt檔案的行數：

# wc -l /dbbak/ulist.txt

4）驗證兩種會導出失敗的情況

目标檔案夾不存在時：

mysql> SELECT * FROM userdb.userlist

-> INTO OUTFILE '/databackup/ulist.txt'

-> FIELDS TERMINATED BY ':';

ERROR 1 (HY000): Can't create/write to file '/databackup/ulist.txt' (Errcode: 2 - No such file or directory)

目标檔案夾存在，但是mysql沒有寫入權限時：

-> INTO OUTFILE '/opt/ulist.txt'

ERROR 1 (HY000): Can't create/write to file '/opt/ulist.txt' (Errcode: 13 - Permission denied)

解決辦法：1修改目錄的權限 ，加入mysql組或者other+w權限

2 關閉selinux ，改為disabled

2、mysql備份與恢複

1．使用mysqldump進行邏輯備份（完全備份）

1）備份MySQL伺服器上的所有庫，将所有的庫備份為mysql-alldb.sql檔案：

#mysqldump -u root -p  --all-databases>/root/mysql-alldb.sql

//備份所有庫

Enter password:                                 //驗證密碼

# file /root/alldb.sql         //确認備份檔案類型 /root/alldb.sql: UTF-8 Unicode English text, with very long lines

***特别提示：若資料庫都使用MyISAM存儲引擎，可以采用冷備份的方式，直接複制對應數   據庫目錄即可；恢複時重新複制回來就行。

2）隻備份指定的某一個庫

将userdb庫備份為userdb.sql檔案：

#mysqldump -uroot -p userdb>userdb.sql//備份指定庫

3）同時備份指定的多個庫

同時備份mysql、test、userdb庫，儲存為mysql+test+userdb.sql檔案：

#mysqldump -uroot -p -B mysql test userdb>mysql+test+userdb.sql

//備份多個庫

4)備份指定庫下的指定表

#mysqldump -uroot -p mysql test>mysql_test.sql //備份mysql庫下的test表

2．使用mysql指令從備份中恢複資料庫、表

 以恢複userdb庫為例，可參考下列操作。通常不建議直接覆寫舊庫，而是采用建立新庫并導入邏輯備份的方式執行恢複，待新庫正常後即可廢棄或删除舊庫。

1）建立名為userdb2的新庫：

mysql> CREATE DATABASE userdb2;//建立新表

Query OK, 1 row affected (0.00 sec)

2）導入備份檔案，在新庫中重建表及資料：

#mysqldump -uroot -p userdb2</root/userdb.sql

3）确認新庫正常，啟用新庫：

mysql> USE userdb2;                             //切換到新庫

mysql> SELECT sn,username,uid,gid,homedir    //查詢資料，确認可用

4）廢棄或删除舊庫：

mysql> DROP DATABASE userdb;//确認新庫可用後删除舊庫

Query OK, 2 rows affected (0.09 sec)

3 使用binlog日志

1、啟用binlog日志

1）調整/etc/my.cnf配置，并重新開機服務

# vim /etc/my.cnf //修改my.cnf配置檔案

[mysqld]

log-bin=mysql-bin             //啟用二進制日志，并指定字首

（這裡可以指定存放的路徑，但是要確定目錄有被mysql使用者寫入的權限，可以更改目錄的所有者）

# service mysql restart

Shutting down MySQL..                         [确定]

Starting MySQL..                                    [确定]

2）确認binlog日志檔案

新啟用binlog後，每次啟動MySQl服務都會新生成一份日志檔案：

# ls /var/lib/mysql/mysql-bin.* //确認binlog啟用

/var/lib/mysql/mysql-bin.000001   /var/lib/mysql/mysql-bin.index

重新開機MySQL服務程式，或者執行SQL操作“FLUSH LOGS;”，會生成一份新的日志：

# ls /var/lib/mysql/mysql-bin.*

/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index /var/lib/mysql/mysql-bin.000002

     //最後一個是剛剛生成的檔案

心得總結：使用mysql指令從備份中恢複資料庫、表時通常不建議直接覆寫舊庫，而是采用

建立新庫并導入邏輯備份的方式執行恢複，待新庫正常後即可廢棄或删除舊庫。

2．利用binlog日志重做資料庫操作

1）執行資料庫表添加操作

建立db1·庫tb1表，表結構自定義：

mysql> CREATE DATABASE db1;//建立新庫

Query OK, 1 row affected (0.05 sec)

mysql> USE db1;

mysql> CREATE TABLE tb1(//建立新表

-> id int(4) NOT NULL,name varchar(24)

Query OK, 0 rows affected (0.19 sec)

mysql> IN   SERT INTO tb1 VALUES

-> (1,'Jack'),//插入3條表記錄

-> (2,'Kenthy'),

-> (3,'Bob');

Query OK, 3 rows affected (0.13 sec)

Records: 3 Duplicates: 0 Warnings: 0

 确認插入的表記錄資料：

mysql> SELECT * FROM tb1;

+----+--------+

| id | name |

| 1 | Jack |

| 2 | Kenthy |

| 3 | Bob |

3 rows in set (0.04 sec)

2）删除前一步添加的3條表記錄

執行删除所有表記錄操作：

mysql> DELETE FROM tb1; Query OK, 3 rows affected (0.00 sec)

mysql> DELETE FROM tb1;

Query OK, 3 rows affected (0.00 sec)

确認删除結果：

Empty set (0.00 sec)

3）通過binlog日志恢複表記錄

 binlog會記錄所有的資料庫、表更改操作，是以可在必要的時候重新執行以前做過的一

部分資料操作，但對于啟用binlog之前已經存在的庫、表資料将不适用。

根據上述“恢複被删除的3條表記錄”的需求，應通過mysqlbinlog工具檢視相關日志檔案

，找到删除這些表記錄的時間點，隻要恢複此前的SQL操作（主要是插入那3條記錄的操作）即可。

# mysqlbinlog /var/lib/mysql/mysql-bin.000002

... ...

# at 415

#140112 20:12:14 server id 1 end_log_pos 545 CRC32 0x98781640 Query thread_id=1 exec_time=0 error_code=0        //插入表記錄的起始時間點

SET TIMESTAMP=1389528734/*!*/;

INSERT INTO tb1 VALUES

(1,'Jack'),

(2,'Kenthy'),

(3,'Bob')

/*!*/;

#140112 20:12:14 server id 1 end_log_pos 576 CRC32 0x672e96e5 Xid = 9                                                 //确認事務的時間點

#140112 20:13:51 server id 1 end_log_pos 740 CRC32 0x253837bb Query thread_id=1 exec_time=0 error_code=0         //删除表記錄的時間點

 根據上述日志分析，隻要恢複從2014.01.12 20:12:14到2014.01.12 20:13:50之間的操作即可。可通過mysqlbinlog指定時間範圍輸出，結合管道交給msyql指令執行導入重做：

# mysqlbinlog --start-datetime="2014-01-12 20:12:14" \

--stop-datetime="2014-01-12 20:12:50" \ //按時間點恢複資料

/var/lib/mysql/mysql-bin.000002 | mysql -u root

-p Enter password:                                 //驗證密碼

4）确認恢複結果

mysql> SELECT * FROM db1.tb1;

3 rows in set (0.00 sec)

      本文轉自Jx戰壕  51CTO部落格，原文連結：http://blog.51cto.com/xujpxm/1386296，如需轉載請自行聯系原作者