MySQL備份與還原系列二：cp冷備份 和 mysqldump

標籤：mysqldump   恢複   mysql備份   

    如果要備份，請確保 mysql 開啟 log-bin，有了 binarylog，mysql 才可以在必要的時候做完整恢複，或基於時間點的恢複，或基於位置的恢複。

我的資料存放目錄為：

mysql> SHOW VARIABLES LIKE ‘%datadir%‘;+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| datadir       | /var/lib/mysql/ |+---------------+-----------------+

1、建立目錄用於存放二進位日誌

[[email protected] ~]# mkdir /mybinlog[[email protected] ~]# chown mysql:mysql /mybinlog

 2、編輯設定檔，啟用二進位日誌

[[email protected] ~]# vi /etc/my.cnf### 二進位日誌目錄及檔案名稱首碼log-bin = /mybinlog/mysql-bin## 然後重啟mysqld服務[[email protected] ~]# service mysqld restart

3、查看產生的binlog日誌

[[email protected] ~]# ls /mybinlog/mysql-bin.000001  mysql-bin.index

4、準備測試資料

mysql> CREATE DATABASE mydb;mysql> USE mydb;### 建立 myisam 引擎的表mysql> CREATE TABLE myisam_tbl(    ->     id INT NOT NULL AUTO_INCREMENT,    ->     name VARCHAR(50),    ->     PRIMARY KEY(id)    -> ) ENGINE=myisam DEFAULT charset=utf8;mysql> INSERT INTO myisam_tbl(name)    -> VALUES(‘one‘),(‘joy‘),(‘li‘),(‘tom‘),(‘jerry‘),(‘hello‘);### 造資料的好辦法    mysql> INSERT INTO myisam_tbl(name)    -> SELECT name FROM myisam_tbl;    ### 建立innodb引擎的表mysql> CREATE TABLE innodb_tbl(    ->     id INT NOT NULL AUTO_INCREMENT,    ->     name VARCHAR(50),    ->     PRIMARY KEY(id)    -> ) engine = innodb DEFAULT charset=utf8;# 把myisam_tbl的資料弄到innodb_tbl表mysql> INSERT INTO innodb_tbl(name)     -> SELECT name FROM myisam_tbl;

一、直接拷貝資料庫檔案（檔案系統備份工具 cp），物理備份（適合小型資料庫）

標準流程：鎖表->重新整理表到磁碟->停止服務->拷貝檔案->解鎖

冷備份步驟：

備份：

1.停掉 mysql 服務，在作業系統層級備份 mysql 的資料檔案。

2.重啟 mysql 服務，備份重啟以後產生的 binlog。

恢複：

1.停掉 mysql 服務，在作業系統層級恢複 mysql 的資料檔案。

2.重啟 mysql 服務，使用 mysqlbinlog 恢複自備份以來的 binlog。

1、在終端1

# 重新整理，開啟讀鎖mysql> FLUSH TABLES WITH READ LOCK;Query OK, 0 rows affected (0.00 sec)

2、在終端2

# 建立備份目錄[[email protected] ~]# mkdir /mnt/mysql-$(date +%F)# 以歸檔模式拷貝所有的資料檔案[[email protected] ~]# cp -a /var/lib/mysql/* /mnt/mysql-2014-07-21/

3、回到第一個終端

# 解鎖mysql> UNLOCK TABLES;Query OK, 0 rows affected (0.10 sec)

4、類比資料庫損壞， 刪除原來的所有資料檔案

[[email protected] ~]# rm -rf /var/lib/mysql/*# 然後關閉Mysql服務[[email protected] ~]# service mysqld stop### 我是通過yum安裝的mysql，是可以順利停掉服務的。# 如果不能停掉 ERROR! MySQL server PID file could not be found! ， 那麼[[email protected] ~]# ps -ef | grep mysql[[email protected] ~]# killall mysqld###進行資料庫初始化#1 編譯安裝的 ，請根據實際情況指定參數[[email protected] ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/mydata/data/ --user=mysql#2 yum安裝的[[email protected] ~]# /usr/bin/mysql_install_db# 初始化後，我們的資料已經丟失mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               |+--------------------+3 rows in set (0.00 sec)

5、恢複過程，複製完全備份的資料檔案到資料目錄中

# \cp  轉義別名，不然複製時老是提醒是否覆蓋[[email protected] ~]# \cp -a /mnt/mysql-2014-07-21/* /var/lib/mysql/# 啟動Mysql[[email protected] ~]# service mysqld startmysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mydb               || mysql              || test               |+--------------------+mysql> USE mydb;Database changedmysql> show tables;+-----------------+| Tables_in_mydb  |+-----------------+| innodb_tbl      || myisam_tbl      || test_myisam_tbl |+-----------------+3 rows in set (0.00 sec)

OK, 我們看到資料已經恢複。cp命令，對其進行的備份，速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統，但是跨平台能力很差，適合小型Database Backup！

二、mysqldump邏輯備份資料庫（完全備份+增加備份，速度相對較慢，適合中小型資料庫）（MyISAM是溫備份，InnoDB是熱備份）

邏輯備份步驟：

備份：

1.選擇在系統空閑時，比如在夜間，使用 mysqldump –F(flush-logs)備份資料

庫。

# mysqldump –u root –p*** pointcard –F > pointcard.sql

2.並備份 mysqldump 開始以後產生的 binlog。

恢複：

1. 停掉應用，執行 mysql 匯入備份檔案.

mysql –u root –p*** pointcard < pointcard.sql

2. 使用 mysqlbinlog 恢複自 mysqldump 備份以來的 binlog。

mysqlbinlog $HOME/data/mysql-bin.123456 | mysql -u root –p***

mysqldump -u -h -p--all-databases  --databases <dbname>   --events            #備份事件--flush-logs    #在資料庫匯出之前先執行FLUSH LOGS--lock-all-tables   #鎖定所有表--lock-tables    #鎖定某些表--master-data=n    #(指定備份點:log fiel name and position)--opt      #(PITR 精確恢複，時間點恢複)--routines    #(備份預存程序、儲存函數)--triggers    #(備份觸發器)--single-transaction#(為事務性資料庫提供備份)--flush-logs        # 滾動日誌--where     #(指定過濾條件，只備份符合條件資料)

由於mysqldump針對不同的引擎有所差異，所以務必清楚引擎類型

溫備:

    在使用MyISAM引擎中，只能使用溫備份，這時候要防止資料的寫入，所以先加上讀鎖。這時候可以進入資料庫手動加讀鎖。這樣比較麻煩，在mysqldump工具中直接有一個加鎖的選項

# mysqldump --databases mydb --lock-all-tables --flush-logs> /tmp/backup-`date +%F-%H-%M`.sql### 備份所有庫（完全備份）# mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/`date +%F-%H-%M`.full.sql

如果是針對某張表備份，只要在資料庫名稱後面加上表名稱就行了

# mysqldump -uroot  -p mydb course > /root/mydb.sql備份表course# mysqldump -uroot  -p --databases mydb dnsdata > /root/mydb.sql  同時備份兩個資料庫

    這裡注意，要實現時間點的恢複，加上--flush-logs選項，在使用備份檔案恢複後，然後再基於二進位日誌進行時間點的恢複

熱備：
如果使用的是InnoDB引擎，就不必進行對資料庫加鎖的操作，加一個選項既可以進行熱備份：--single-transaction

# mysqldump --databases mydb --single-transaction  --flush-logs --master-data=2 > /tmp/backup-`date +%F-%H-%M`.sql

實驗：

1、備份前的binlog日誌

mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000005 |      106 |              |                  |+------------------+----------+--------------+------------------+

2、備份所有庫（完全備份）

[[email protected] ~]# mysqldump -uroot -p --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/$(date +%F-%H-%M).full.sql[[email protected] ~]# ls mybackup/2014-07-21-16-53.full.sql

3、查看備份後的binlog， 主要是--flush-logs選項

mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000006 |      106 |              |                  |+------------------+----------+--------------+------------------+

4、我們向myisam_tbl表插入幾條新資料

mysql> INSERT INTO myisam_tbl(name)    -> VALUES(‘new-1‘), (‘new-2‘), (‘new-3‘);# 查看binlogmysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000006 |      263 |              |                  |+------------------+----------+--------------+------------------+

5、備份binlog（增量備份）

[[email protected] ~]# cp /mybinlog/mysql-bin.000006 /root/mybackup/$(date +%F-%H-%M).binlog.000006

6、類比誤操作（刪除myisam_tbl表）

mysql> drop table myisam_tbl;Query OK, 0 rows affected (0.01 sec)

7、恢複

恢複前先關閉對恢複過程的二進位日誌記錄，因為記錄恢複語句是毫無意義的

mysql> set sql_log_bin=0;        -- 關閉binlogQuery OK, 0 rows affected (0.00 sec)mysql> \. /root/mybackup/2014-07-21-16-53.full.sqlmysql> use mydb;mysql> show tables;+-----------------+| Tables_in_mydb  |+-----------------+| innodb_tbl      || myisam_tbl      || test_myisam_tbl |+-----------------+mysql> SELECT *          FROM myisam_tbl         ORDER BY id DESC         LIMIT 5;+----+-------+| id | name  |+----+-------+| 12 | hello || 11 | jerry || 10 | tom   ||  9 | li    ||  8 | joy   |+----+-------+

ok，現在已經恢複到完全備份時的狀態，但我們最後插入的三條資料沒有恢複。

[[email protected] ~]# mysqlbinlog /root/mybackup/2014-07-21-17-02.binlog.000006 | mysql -uroot -p mydbEnter password: mysqlbinlog: unknown variable ‘default-character-set=utf8‘

mysqlbinlog報錯： unknown variable ‘default-character-set=utf8‘

it‘s because inside the my.cnf  got 
default-character-set=utf8

the default-character-set is deprecated in 5.5. we should use instead:

character-set-server = utf8

or add --no-defaults

mysqlbinlog --no-defaults -v logbin-log.000003 > logbin003.sql

==============END=================

[[email protected] ~]# mysqlbinlog --no-defaults /root/mybackup/2014-07-21-17-02.binlog.000006 | mysql -uroot -p mydbmysql> set sql_log_bin=1;        -- 開啟binlogQuery OK, 0 rows affected (0.00 sec)mysql> SELECT *  FROM myisam_tbl ORDER BY id DESC LIMIT 5;+----+-------+| id | name  |+----+-------+| 15 | new-3 || 14 | new-2 || 13 | new-1 || 12 | hello || 11 | jerry |+----+-------+

OK, 恢複成功。

時間點的恢複：

1.如果今天上午 10 點發生了誤操作，可以用以下語句用備份和 binglog 將資料恢複到故

障前：

mysqlbinlog --no-defaults --stop-date="2014-07-24 9:59:59"/var/log/mysql/bin.123456 | mysql -u root –pmypwd

2.跳過故障時的時間點，繼續執行後面的 binlog，完成恢複

mysqlbinlog --no-defaults --start-date="2014-07-24 10:01:00"/var/log/mysql/bin.123456 | mysql -u root -pmypwd

位置恢複：

和時間點恢複類似，但是更精確，步驟如下：

mysqlbinlog  --no-defaults --start-date="2014-07-24 9:55:00" --stop-date="2014-07-2410:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/41/08/wKioL1PQg5ODXDqEAAHBOfJYF9M271.jpg" title="9.png" alt="wKioL1PQg5ODXDqEAAHBOfJYF9M271.jpg" />    

    該命令將在/tmp 目錄建立小的文字檔，編輯此檔案，找到出錯語句前後的位置號 ，

例如前後位置號分別是 368312 和 368315。恢複了以前的備份檔案後，你應從命令

行輸入下面內容：

mysqlbinlog --no-defaults --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -pmypwdmysqlbinlog --no-defaults --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -pmypwd

    上面的第 1 行將恢複到停止位置為止的所有事務。下一行將恢複從給定的起始位置

直到二進位日誌結束的所有事務。因為 mysqlbinlog 的輸出包括每個 SQL 陳述式記錄

之前的 SET TIMESTAMP 語句，恢複的資料和相關 MySQL 日誌將反應事務執行的原時

間。

注意點
恢複的時刻關閉二進位日誌

mysql>set sql_log_bin=0;

因為這是基於邏輯備份方式，在恢複日誌時會執行sql語句插入資料，而恢複時候插入資料的日誌沒有意義。

本文出自 “Share your knowledge” 部落格，請務必保留此出處http://skypegnu1.blog.51cto.com/8991766/1529586