MySQL的備份與恢復理解與備份策略

来源:https://www.cnblogs.com/anay/archive/2018/03/09/8535606.html
-Advertisement-
Play Games

MySQL的備份主要分為邏輯備份和物理備份 邏輯備份 在MySQL中邏輯備份的最大優點是對各種存儲引擎都可以用同樣的方法來備份。而物理備份則不同，不同的存儲引擎有著不同的備份方法。Mysql中的邏輯備份是將資料庫中的數據備份為一個文本文件，備份的文件可以被查看和編輯，在mysql中常用mysqldu ...


 

MySQL的備份主要分為邏輯備份和物理備份

邏輯備份

在MySQL中邏輯備份的最大優點是對各種存儲引擎都可以用同樣的方法來備份。而物理備份則不同，不同的存儲引擎有著不同的備份方法。Mysql中的邏輯備份是將資料庫中的數據備份為一個文本文件，備份的文件可以被查看和編輯，在mysql中常用mysqldump工具來完成邏輯備份。

mysqldump的使用方法：

查看測試資料庫信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+--------------------+
6 rows in set (0.01 sec)

1.備份指定的資料庫： suzhou

[root@anan tmp]# mysqldump -u root -p suzhou  >  ./dbbak/suzhou.sql     --備份suzhou 資料庫
Enter password: 
[root@anan tmp]# 
[root@anan tmp]# cd dbbak/ && ls
suzhou.sql            --備份生成的文件
[root@anan dbbak]#

2.備份指定的某個庫中的某個表：tb1

[root@anan dbbak]# mysqldump -u root -p suzhou tb1 > ./tb1.sql
Enter password: 
[root@anan dbbak]# ls
suzhou.sql  tb1.sql

3.備份所有資料庫

mysqldump -uroot -p --all-database > ./alldb.sql
Enter password: 

[root@anan dbbak]# ls
alldb.sql  suzhou.sql  tb1.sql

　　需要強調的是，為了保證數據備份的一致性，MyISAM存儲引擎在備份時需要加上  -l （lock table）參數，表示將所有表加上讀鎖。在備份期間，所有表將引擎只能讀不能進行更新數據。但是對於事物存儲引擎（InnoDB和BDB）來說，可以使用--single-transaction，該選項將使InnoDB引擎得到一個快照，使得備份的數據能夠保證一致性。

完全恢復

mysqldump的恢復也比較簡單，將備份的數據文件作為輸入導入。但是註意，備份恢復後數據並不完整，因為備份後資料庫中又會產生新的數據記錄，需要將備份後的日誌進行重寫。使用方法：mysqlbinlog   binlog-file  |  mysql  -u  -p

下麵進行一次完整的備份與恢復

新建一個資料庫 data1，庫中有tb1，信息如下：

mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.00 sec)

備份data1資料庫

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000010 |     2935 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
備份前先查看一下當前使用的bin-log日誌文件編號

　　

[root@anan dbbak]# mysqldump -uroot -p -l -F data1 > ./data1.sql
Enter password: 
[root@anan dbbak]# ls
data1.sql
--備份指令中使用的-l :鎖定所有表為只讀狀態;  -F ： 刷新日誌文件
再看bin-log文件信息：

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000011 | 107 | | |
+-------------------+----------+--------------+------------------+
下麵再對data1庫中寫入數據，寫入後bin-log文件中的position的數值會產生變化。

mysql> insert into tb1 values (4,'D'),(5,'E'),(6,'F');
Query OK, 3 rows affected (0.03 sec)

再看bin-log文件的position值

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000011 | 313 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

　刪除資料庫data1，模擬故障

mysql> drop database data1;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

data1 庫已經被刪除了

　　恢複數據庫 data1

[root@anan dbbak]# mysql -uroot -p  data1 < ./data1.sql
Enter password: 
......
......

mysqldump: Got error: 1049: Unknown database 'data1' when selecting the database
【恢復的資料庫必須要在資料庫中存在，恢復前先新建一個同名的資料庫 data1】

　　【註意：mysqldump只用來備份數據，恢複數據使用 mysql指令，第一次恢復大意地使用mysqldump指令來恢復，怎麼也沒有成功，也沒有報錯，還是得細心才是】

[root@anan dbbak]# mysql -u root -p data1 < ./data1.sql        --恢復備份
Enter password:
[root@anan dbbak]#

查看恢復後的數據如下，後寫入的數據還沒有

mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
+------+------+
3 rows in set (0.00 sec)

 

　恢復備份後的數據，由於失誤操作，日誌文件中存在誤操作的語句，所以可以可以使用基於時間點和基於位置的恢復。下麵以位置恢復。

恢復前需要先看新寫入的數據在bin-log日誌文件中的position值： mysqlbinlog master-bin.000011 

[root@anan mysqldata]# mysqlbinlog master-bin.000011 | cat -n | grep -C 5  'insert'
     1	/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
     2	/*!40019 SET @@session.max_insert_delayed_threads=0*/;
     3	/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
     4	DELIMITER /*!*/;
     5	# at 4
     6	#180308  2:28:18 server id 2  end_log_pos 107 	Start: binlog v 4, server v 5.5.32-log created 180308  2:28:18
     7	# Warning: this binlog is either in use or was not closed properly.
--
    24	/*!*/;
    25	# at 176
    26	#180308  2:33:31 server id 2  end_log_pos 286 	Query	thread_id=13	exec_time=0 error_code=0
    27	use `data1`/*!*/;
    28	SET TIMESTAMP=1520447611/*!*/;
    29	insert into tb1 values (4,'D'),(5,'E'),(6,'F')
    30	/*!*/;
    31	# at 286
    32	#180308  2:33:31 server id 2  end_log_pos 313 	Xid = 611
    33	COMMIT/*!*/;
    34	# at 313
--
   213	BEGIN
   214	/*!*/;
   215	# at 3393
   216	#180308  2:33:31 server id 2  end_log_pos 3503 	Query	thread_id=13	exec_time=4490	error_code=0
   217	SET TIMESTAMP=1520447611/*!*/;
   218	insert into tb1 values (4,'D'),(5,'E'),(6,'F')
   219	/*!*/;
   220	# at 3503
   221	#180308  2:33:31 server id 2  end_log_pos 3530 	Xid = 916
   222	COMMIT/*!*/;
   223	DELIMITER ;

使用日誌文件恢復後寫入的數據 ；使用mysqlbinlog 指令

[root@anan mysqldata]# mysqlbinlog   --start-position='176'   --stop-position='286'   ./master-bin.000011  |  mysql  -u  root  -p
Enter password:
[root@anan mysqldata]# 

再看資料庫信息

mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
+------+------+
6 rows in set (0.01 sec)

 

物理備份和恢復

物理備份又分為冷備和熱備兩種，和邏輯備份相比，它最大的優點是備份和恢復的速度更快，因為物理備份的原來是基於文件的cp。

冷備份

冷備份其實就是停掉資料庫服務，cp數據文件的方法，這種方法對MyISAM和InnoDB引擎都適合，但是一般很少用，因為很多應用是不允許停機的。

熱備份

在MySQL中，對於不同的存儲引擎熱備的方法也有所不同，下麵主要介紹MyISAM和InnoDB兩種常用的存儲引擎的熱備方法。

          MyISAM存儲引擎

MyISAM存儲引擎的熱備方法本質其實就是將要備份的表加讀鎖，然後在cp數據文件到備份目錄，常用的方法有以下兩種：

方法1：使用mysqlhotcopy工具

方法2：使用手動鎖表copy

首先將資料庫中的所有表加讀鎖： flush  tables  for read ; 然後再cp數據文件到備份目錄即可。

         InnoDB存儲引擎

InnoDB存儲引擎資料庫可以使用Xtrabackup工具來備份

下麵仔細介紹xtarbackup熱備工具的使用：

         xtarbackup熱備工具的使用

xtrabackup是percona公司參與開發的一款基於InnoDB的線上熱備工具，具有開源，免費，支持線上熱備，備份恢復速度快，占用磁碟空間小等特點。

xtrabackup包含兩個主要的工具。即xtrabackup和innobackupex，兩者區別如下：

                   xtrabackup：只能備份InnoDB存儲引擎和XtraDB兩種數據表，而不能備份MyISAM數據表。

                   Innobackupex：是一個封裝了xtrabackup的perl腳本，支持同時備份InnoDB和MyISAM,但在對MyISAM備份時需要加一個全局的讀鎖

備份前查看資料庫的存儲引擎是什麼： 

mysql> show variables like '%engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)


innobackupex的參數比較多，詳細的參數可以參考學習下麵一位博友的文章，寫的很詳細

      https://www.cnblogs.com/zhoujinyi/p/5893333.html

    進行全備

[root@anan dbbak]# innobackupex  --defaults-file=/etc/my.cnf  --user=root  --password=fsz...  ./

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

180308 06:11:41  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
180308 06:11:41  innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.5.32-log

innobackupex: Created backup directory /tmp/dbbak/2018-03-08_06-11-41

180308 06:11:41  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/dbbak/2018-03-08_06-11-41 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=3736) to suspend
innobackupex: Suspend file '/tmp/dbbak/2018-03-08_06-11-41/xtrabackup_suspended_2'

xtrabackup_55 version 2.1.2 for Percona Server 5.5.16 Linux (x86_64) (revision id: 611)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysqldata
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (1645580)
[01] Copying ./ibdata1 to /tmp/dbbak/2018-03-08_06-11-41/ibdata1
[01]        ...done
>> log scanned up to (1645580)

180308 06:11:43  innobackupex: Continuing after ibbackup has suspended
180308 06:11:43  innobackupex: Starting to lock all tables...
180308 06:11:43  innobackupex: All tables locked and flushed to disk

180308 06:11:43  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/usr/local/mysqldata'
innobackupex: Backing up files '/usr/local/mysqldata/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up file '/usr/local/mysqldata/data1/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/data1/tb1.frm'
innobackupex: Backing up file '/usr/local/mysqldata/sysdb/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/sysdb/tb1.frm'
innobackupex: Backing up files '/usr/local/mysqldata/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
>> log scanned up to (1645580)
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/tb1.frm'
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/fengsuzhou.frm'
180308 06:11:44  innobackupex: Finished backing up non-InnoDB tables and files

180308 06:11:44  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1645580'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1645580)

xtrabackup: Transaction log of lsn (1645580) to (1645580) was copied.
180308 06:11:45  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tmp/dbbak/2018-03-08_06-11-41'
innobackupex: MySQL binlog position: filename 'master-bin.000011', position 3736
180308 06:11:45  innobackupex: Connection to database server closed
180308 06:11:45  innobackupex: completed OK!

完整的備份過程信息可以細細查看
備份後在備份目錄會生成一個時間戳目錄，備份的數據文件就放在該目錄中

 

　　熱備恢復

 恢復前可以測試一下把原來的數據目錄清空然後恢復看看效果

1.先關閉服務
service  mysqld  stop

2.重命名數據目錄並創建原同名目錄
mv mysqldata  mysqldatabak
mkdir  mysqldata

3.還原後再重新修改新的數據目錄的許可權
chown -R mysql:mysql  mysqldata/*

4.啟動服務
service mysqld  start

    還原數據

#提交或者回滾事務
 [root@anan dbbak]# innobackupex --apply-log ./2018-03-08_06-11-41/
#上面這指令是利用--apply-log的作用是通過回滾未提交的事務及同步已經提交的事務至數據文件使數據文件處於一致性狀態

#恢複數據
[root@anan dbbak]# innobackupex --defaults-file=/etc/my.cnf --copy-back ./2018-03-08_06-11-41/

還可以使用 【--database=“db1 db2 db3”】選項備份指定的資料庫，多個資料庫用空格隔開
還可以使用 【--databases="db1.tb1 db2.tb2 db3.tb3"】方式備份不同庫的不同表，同樣多庫用空格隔開

　　最後檢查數據和查看數據目錄

mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
+------+------+
9 rows in set (0.01 sec)




　　創建增量備


在第一次全備的基礎上進行增量備份，接著再繼續往資料庫里寫入一些數據



mysql> insert into tb1 values (7,'G'),(8,'H'),(9,'K');
Query OK, 3 rows affected (0.03 sec)




　　執行增量備份


[root@anan dbbak]# innobackupex --apply-log --redo-only ./2018-03-08_06-11-41/


 


---恢復內容結束---


MySQL的備份主要分為邏輯備份和物理備份


邏輯備份


在MySQL中邏輯備份的最大優點是對各種存儲引擎都可以用同樣的方法來備份。而物理備份則不同，不同的存儲引擎有著不同的備份方法。Mysql中的邏輯備份是將資料庫中的數據備份為一個文本文件，備份的文件可以被查看和編輯，在mysql中常用mysqldump工具來完成邏輯備份。


mysqldump的使用方法：



查看測試資料庫信息
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+--------------------+
6 rows in set (0.01 sec)




1.備份指定的資料庫： suzhou



[root@anan tmp]# mysqldump -u root -p suzhou  >  ./dbbak/suzhou.sql     --備份suzhou 資料庫
Enter password: 
[root@anan tmp]# 
[root@anan tmp]# cd dbbak/ && ls
suzhou.sql            --備份生成的文件
[root@anan dbbak]#




2.備份指定的某個庫中的某個表：tb1



[root@anan dbbak]# mysqldump -u root -p suzhou tb1 > ./tb1.sql
Enter password: 
[root@anan dbbak]# ls
suzhou.sql  tb1.sql




3.備份所有資料庫



mysqldump -uroot -p --all-database > ./alldb.sql
Enter password: 

[root@anan dbbak]# ls
alldb.sql  suzhou.sql  tb1.sql




　　需要強調的是，為了保證數據備份的一致性，MyISAM存儲引擎在備份時需要加上  -l （lock table）參數，表示將所有表加上讀鎖。在備份期間，所有表將引擎只能讀不能進行更新數據。但是對於事物存儲引擎（InnoDB和BDB）來說，可以使用--single-transaction，該選項將使InnoDB引擎得到一個快照，使得備份的數據能夠保證一致性。


完全恢復


mysqldump的恢復也比較簡單，將備份的數據文件作為輸入導入。但是註意，備份恢復後數據並不完整，因為備份後資料庫中又會產生新的數據記錄，需要將備份後的日誌進行重寫。使用方法：mysqlbinlog   binlog-file  |  mysql  -u  -p


下麵進行一次完整的備份與恢復


新建一個資料庫 data1，庫中有tb1，信息如下：



mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.00 sec)



備份data1資料庫



mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000010 |     2935 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
備份前先查看一下當前使用的bin-log日誌文件編號




　　



[root@anan dbbak]# mysqldump -uroot -p -l -F data1 > ./data1.sql
Enter password: 
[root@anan dbbak]# ls
data1.sql
--備份指令中使用的-l :鎖定所有表為只讀狀態;  -F ： 刷新日誌文件
再看bin-log文件信息：


mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000011 |      107 |              |                  |
+-------------------+----------+--------------+------------------+
下麵再對data1庫中寫入數據，寫入後bin-log文件中的position的數值會產生變化。


mysql> insert into tb1 values (4,'D'),(5,'E'),(6,'F');
Query OK, 3 rows affected (0.03 sec)


再看bin-log文件的position值


mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000011 |      313 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


 










　刪除資料庫data1，模擬故障



mysql> drop database data1;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| suzhou             |
| sysdb              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

data1 庫已經被刪除了



　　恢複數據庫 data1



[root@anan dbbak]# mysql -uroot -p  data1 < ./data1.sql
Enter password: 
......
......

mysqldump: Got error: 1049: Unknown database 'data1' when selecting the database
【恢復的資料庫必須要在資料庫中存在，恢復前先新建一個同名的資料庫 data1】




　　【註意：mysqldump只用來備份數據，恢複數據使用 mysql指令，第一次恢復大意地使用mysqldump指令來恢復，怎麼也沒有成功，也沒有報錯，還是得細心才是】



[root@anan dbbak]# mysql -u root -p data1 < ./data1.sql        --恢復備份
Enter password: 
[root@anan dbbak]#


查看恢復後的數據如下，後寫入的數據還沒有


mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.00 sec)


 










　恢復備份後的數據，由於失誤操作，日誌文件中存在誤操作的語句，所以可以可以使用基於時間點和基於位置的恢復。下麵以位置恢復。


恢復前需要先看新寫入的數據在bin-log日誌文件中的position值： mysqlbinlog master-bin.000011 



[root@anan mysqldata]# mysqlbinlog master-bin.000011 | cat -n | grep -C 5  'insert'
     1	/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
     2	/*!40019 SET @@session.max_insert_delayed_threads=0*/;
     3	/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
     4	DELIMITER /*!*/;
     5	# at 4
     6	#180308  2:28:18 server id 2  end_log_pos 107 	Start: binlog v 4, server v 5.5.32-log created 180308  2:28:18
     7	# Warning: this binlog is either in use or was not closed properly.
--
    24	/*!*/;
    25	# at 176
    26	#180308  2:33:31 server id 2  end_log_pos 286 	Query	thread_id=13	exec_time=0 error_code=0
    27	use `data1`/*!*/;
    28	SET TIMESTAMP=1520447611/*!*/;
    29	insert into tb1 values (4,'D'),(5,'E'),(6,'F')
    30	/*!*/;
    31	# at 286
    32	#180308  2:33:31 server id 2  end_log_pos 313 	Xid = 611
    33	COMMIT/*!*/;
    34	# at 313
--
   213	BEGIN
   214	/*!*/;
   215	# at 3393
   216	#180308  2:33:31 server id 2  end_log_pos 3503 	Query	thread_id=13	exec_time=4490	error_code=0
   217	SET TIMESTAMP=1520447611/*!*/;
   218	insert into tb1 values (4,'D'),(5,'E'),(6,'F')
   219	/*!*/;
   220	# at 3503
   221	#180308  2:33:31 server id 2  end_log_pos 3530 	Xid = 916
   222	COMMIT/*!*/;
   223	DELIMITER ;

使用日誌文件恢復後寫入的數據 ；使用mysqlbinlog 指令


[root@anan mysqldata]# mysqlbinlog   --start-position='176'   --stop-position='286'   ./master-bin.000011  |  mysql  -u  root  -p
Enter password: 
[root@anan mysqldata]# 


再看資料庫信息


mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
| 6 | F |
+------+------+
6 rows in set (0.01 sec)










 


物理備份和恢復


物理備份又分為冷備和熱備兩種，和邏輯備份相比，它最大的優點是備份和恢復的速度更快，因為物理備份的原來是基於文件的cp。


冷備份


冷備份其實就是停掉資料庫服務，cp數據文件的方法，這種方法對MyISAM和InnoDB引擎都適合，但是一般很少用，因為很多應用是不允許停機的。


熱備份


在MySQL中，對於不同的存儲引擎熱備的方法也有所不同，下麵主要介紹MyISAM和InnoDB兩種常用的存儲引擎的熱備方法。


          MyISAM存儲引擎


MyISAM存儲引擎的熱備方法本質其實就是將要備份的表加讀鎖，然後在cp數據文件到備份目錄，常用的方法有以下兩種：


方法1：使用mysqlhotcopy工具


方法2：使用手動鎖表copy


首先將資料庫中的所有表加讀鎖： flush  tables  for read ; 然後再cp數據文件到備份目錄即可。


         InnoDB存儲引擎


InnoDB存儲引擎資料庫可以使用Xtrabackup工具來備份


下麵仔細介紹xtarbackup熱備工具的使用：


         xtarbackup熱備工具的使用


xtrabackup是percona公司參與開發的一款基於InnoDB的線上熱備工具，具有開源，免費，支持線上熱備，備份恢復速度快，占用磁碟空間小等特點。


xtrabackup包含兩個主要的工具。即xtrabackup和innobackupex，兩者區別如下：


                   xtrabackup：只能備份InnoDB存儲引擎和XtraDB兩種數據表，而不能備份MyISAM數據表。


                   Innobackupex：是一個封裝了xtrabackup的perl腳本，支持同時備份InnoDB和MyISAM,但在對MyISAM備份時需要加一個全局的讀鎖


備份前查看資料庫的存儲引擎是什麼： 



mysql> show variables like '%engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)





innobackupex的參數比較多，詳細的參數可以參考學習下麵一位博友的文章，寫的很詳細


      https://www.cnblogs.com/zhoujinyi/p/5893333.html


    進行全備



[root@anan dbbak]# innobackupex  --defaults-file=/etc/my.cnf  --user=root  --password=fsz...  ./

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

180308 06:11:41  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
180308 06:11:41  innobackupex: Connected to MySQL server
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql server version 5.5.32-log

innobackupex: Created backup directory /tmp/dbbak/2018-03-08_06-11-41

180308 06:11:41  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/tmp/dbbak/2018-03-08_06-11-41 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=3736) to suspend
innobackupex: Suspend file '/tmp/dbbak/2018-03-08_06-11-41/xtrabackup_suspended_2'

xtrabackup_55 version 2.1.2 for Percona Server 5.5.16 Linux (x86_64) (revision id: 611)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysqldata
xtrabackup: Target instance is assumed as followings.
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
>> log scanned up to (1645580)
[01] Copying ./ibdata1 to /tmp/dbbak/2018-03-08_06-11-41/ibdata1
[01]        ...done
>> log scanned up to (1645580)

180308 06:11:43  innobackupex: Continuing after ibbackup has suspended
180308 06:11:43  innobackupex: Starting to lock all tables...
180308 06:11:43  innobackupex: All tables locked and flushed to disk

180308 06:11:43  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/usr/local/mysqldata'
innobackupex: Backing up files '/usr/local/mysqldata/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex: Backing up file '/usr/local/mysqldata/data1/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/data1/tb1.frm'
innobackupex: Backing up file '/usr/local/mysqldata/sysdb/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/sysdb/tb1.frm'
innobackupex: Backing up files '/usr/local/mysqldata/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
>> log scanned up to (1645580)
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/db.opt'
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/tb1.frm'
innobackupex: Backing up file '/usr/local/mysqldata/suzhou/fengsuzhou.frm'
180308 06:11:44  innobackupex: Finished backing up non-InnoDB tables and files

180308 06:11:44  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '1645580'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1645580)

xtrabackup: Transaction log of lsn (1645580) to (1645580) was copied.
180308 06:11:45  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/tmp/dbbak/2018-03-08_06-11-41'
innobackupex: MySQL binlog position: filename 'master-bin.000011', position 3736
180308 06:11:45  innobackupex: Connection to database server closed
180308 06:11:45  innobackupex: completed OK!

完整的備份過程信息可以細細查看
備份後在備份目錄會生成一個時間戳目錄，備份的數據文件就放在該目錄中




 



　　熱備恢復


 恢復前可以測試一下把原來的數據目錄清空然後恢復看看效果



1.先關閉服務
service  mysqld  stop

2.重命名數據目錄並創建原同名目錄
mv mysqldata  mysqldatabak
mkdir  mysqldata

3.還原後再重新修改新的數據目錄的許可權
chown -R mysql:mysql  mysqldata/*

4.啟動服務
service mysqld  start



    還原數據



#提交或者回滾事務
 [root@anan dbbak]# innobackupex --apply-log ./2018-03-08_06-11-41/
#上面這指令是利用--apply-log的作用是通過回滾未提交的事務及同步已經提交的事務至數據文件使數據文件處於一致性狀態

#恢複數據
[root@anan dbbak]# innobackupex --defaults-file=/etc/my.cnf --copy-back ./2018-03-08_06-11-41/

還可以使用 【--database=“db1 db2 db3”】選項備份指定的資料庫，多個資料庫用空格隔開
還可以使用 【--databases="db1.tb1 db2.tb2 db3.tb3"】方式備份不同庫的不同表，同樣多庫用空格隔開



　　最後檢查數據和查看數據目錄



mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
+------+------+
9 rows in set (0.01 sec)







　　創建增量備



在進行增量部分前先進行一次基礎備份
innobackupex --user=root --password=fsz... --defaults-file=/etc/my.cnf /tmp/dbbak/




　基礎備份完成後，再對資料庫進行一些數據操作，這裡添加一個新表



mysql> create table tb2 (
    -> id int(10),
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into tb2 values (1,'A');
Query OK, 1 row affected (0.04 sec)




　　接著進行第一次增量備份



#第一次增量備份

innobackupex --user=root --password=fsz... --defaults-file=/etc/my.cnf  --incremental /tmp/dbbak/increment_one/  
--incremental-basedir=/tmp/dbbak/2018-03-08_10-07-07/




　　第一次增量備份後，再接著寫兩條數據進去，然後進行第二次增量備份



mysql> insert into tb2 values (2,'B'),(3,'C');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM tb2;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.00 sec)
最終有三條數據




　　



#第二次增量備份
innobackupex --user=root --password=fsz... --defaults-file=/etc/my.cnf  --incremental /tmp/dbbak/increment_two/  --incremental-basedir=/tmp/dbbak/increment_one/2018-03-08_10-12-39/

兩次增量備份後備份目錄生成備份文件如下

[root@anan dbbak]# tree -d ./
.
├── 2018-03-08_10-07-07   --基礎備份
│   ├── data1
│   ├── mysql
│   ├── performance_schema
│   ├── suzhou
│   ├── sysdb
│   └── test
├── increment_one
│   └── 2018-03-08_10-12-39        --第一次增量備份
│       ├── data1
│       ├── mysql
│       ├── performance_schema
│       ├── suzhou
│       ├── sysdb
│       └── test
└── increment_two
    └── 2018-03-08_10-13-43        --第二次增量備份
        ├── data1
        ├── mysql
        ├── performance_schema
        ├── suzhou
        ├── sysdb
        └── test




　增量備份恢復過程大致如下：


1.恢復基礎備份（全備）


2.恢復增量備份到基礎備份（開始恢復的增量備份需要增加 --redo-only 參數，到恢復最後一次增量備份要去掉 --redo-only 參數）　


3.對整體的基礎備份進行恢復，回滾那些未提交的數據。



恢復基礎備份數據（註意：這裡一定要加 --redo-only 參數該參數的意思是只應用xtrabackup日誌中已經提交的事務數據，不回滾還未提交的數據）

 innobackupex --apply-log --redo-only /tmp/dbbak/2018-03-08_10-07-07/




　　



將第一次增量備份increment_one應用到基礎備份 2018-03-08_10-07-07 中

innobackupex --apply-log --redo-only /tmp/dbbak/2018-03-08_10-07-07/ --incremental-dir=/tmp/dbbak/increment_one/2018-03-08_10-12-39/




　　



將第二次增量備份 increment_two 應用到 基礎備份 2018-03-08_10-07-07，（註意：恢復最後一次增量備份需要去掉 --redo-only 參數，回滾xtrabackup日誌中那些未提交的數據）



   innobackupex  --apply-log  /tmp/dbbak/2018-03-08_10-07-07/  --incremental-dir=/tmp/dbbak/increment_two/2018-03-08_10-13-43/



 


把所有合在一起的基礎備份進行一次 apply 操作，回滾未提交的數據；



#利用--apply-log的作用是通過回滾未提交的事務及同步已經提交的事務至數據文件使數據文件處於一致性狀態

innobackupex --apply-log  /tmp/dbbak/2018-03-08_10-07-07




　把恢復完的備份文件複製到數據文件目錄中，賦權，然後重啟服務大致步驟如下：



1，關閉服務
 service mysqld stop

2，備份原數據目錄

mv mysqldata mysqldata.old

3，創建新的同名數據目錄

 mkdir mysqldata

4，複製備份數據文件

innobackupex --defaults-file=/etc/my.cnf  --copy-back /tmp/dbbak/2018-03-08_10-07-07/

5，對新的數據目錄賦權

chown -R mysql:mysql ./mysqldata

6，重啟服務

service mysqld start




　　最後查看恢復後的數據



Database changed
mysql> show tables;
+-----------------+
| Tables_in_data1 |
+-----------------+
| tb1             |
| tb2             |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from tb2;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.03 sec)




　　


 



              
	   




	

	
您的分享是我們最大的動力!




			 	

	
	

    


-Advertisement-
Play Games
     


	


    

    

    




	       	  

              
更多相關文章  

              

                   
    


				
  • 
				
    
			    	
    
				    	linux系統查看CPU、記憶體、磁碟、版本等信息
			    	
    
					
    【聲明】 本篇博客內容均來自網友的博客，本篇只是進行彙總整理，以方便自己查看。 參考資料：https://www.cnblogs.com/xubiao/p/6497533.html https://www.cnblogs.com/wzk-0000/p/7483262.html 本文鏈接：http:/ ...
    
				
    
		    
  • 
				
    
			    	
    
				    	ubuntu下Tomcat綁定80埠
			    	
    
					
    轉載自：http://www.cnblogs.com/mabaoqing/p/7808354.html 在部署自己的小項目時發生了一個問題，在修改tomcat的server.xml中將8080埠修改為80埠，重啟tomcat用功能變數名稱直接訪問時發現訪問不了。經過google後發現，發現tomcat使 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	Linux系統是32位還是64位查看方法總結
			    	
    
					
    【聲明】 本文版權歸原作者所有，歡迎轉載，轉載請註明出處。 原作者：瀟湘隱者 出處：http://www.cnblogs.com/kerrycode/ 原文鏈接：https://www.cnblogs.com/kerrycode/p/3785768.html Linux系統是32位還是64位查看方法 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	shell 和進程
			    	
    
					
    shell和進程的關係： 我們從login shell 說起，login shell用於表示登陸進程，是指用戶剛登錄系統時，由系統創建，用以運行shell 的進程。 這裡先運行幾個命令： 列印登陸進程（一直存在的，直到登陸退出）ID george.guo@ls:~$ echo $PPID3411ge ...
    
				
    
		    
  • 
				
    
			    	
    
				    	Linux(CentOS)之-性能監控
			    	
    
					
    這篇主要講一下Linux（CentOS）上性能性能監控的操作。 1.監控cpu使用情況--uptime 該命令將會列印出當前時間 系統運行了多久 當前登陸用戶數 系統平均負載 這裡的負載是單位時間內，cpu等待隊列中，評價有多少個進程在等待，等待的進程越多，cpu越忙。 2.監控記憶體及交換分區的使用 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	ORA-28000: the account is locked-的解決辦法
			    	
    
					
    轉載（遇到問題，找到一篇比較全面優秀的文章，解決了剛剛我遇到的問題。原文本人已經推薦。也希望自己留一份，方便以後查閱）！！！！！ 原文連接：https://www.cnblogs.com/jianqiang2010/archive/2011/09/01/2162574.html ORA-28000: ...
    
				
    
		    
  • 
				
    
			    	
    
				    	JDCP連接池連接資料庫報錯：java.lang.AbstractMethodError: com.mysql.jdbc.Connection.isValid(I)Z
			    	
    
					
    完整報錯是這樣的： 小編的情況： 使用mysql的jar包版本： 使用的jdcp的相關jar包版本： 報錯的原因： mysql的jar包版本過低。 更新到最新版mysql的jar包即可。 小編更新後的版本： 參考博客： http://blog.csdn.net/ouyida3/article/det ...
    
				
    
		    
  • 
				
    
			    	
    
				    	mysql基礎語句
			    	
    
					
    轉載自： http://blog.csdn.net/u011001084/article/details/51318434 一、關係型資料庫和SQL 實際上準確的講，SQL是一門語言，而不是一個資料庫。 什麼是SQL呢？簡而言之，SQL就是維護和使用關係型資料庫中的的數據的一種標準的電腦語言。 1 ...
    
				
    
		    
    • 
				 
		  
  
                  

              

          



		
        

        
        

            
 







    
一周排行

    

         
    

 
    -Advertisement-
    Play Games
    	   
	
  • 
			        	
    
			    	        
    
				    	        移動開發（一）：使用.NET MAUI開發第一個安卓APP
			            	
    
				        	
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        wpf ToggleButton選中效果和一個登錄界面
			            	
    
				        	
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        彈幕樹洞項目功能新增篇
			            	
    
				        	
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第27篇 sqlserver2022詳細安裝步驟
			            	
    
				        	
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .NET 開源高性能 MQTT 類庫
			            	
    
				        	
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
			            	
    
				        	
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
			            	
    
				        	
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
			            	
    
				        	
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
			            	
    
				        	
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第28篇 如何.net中實現高效可靠數據同步api
			            	
    
				        	
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類