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
    	   
	
  • 
			        	
    
			    	        
    
				    	        WPF 已知問題 監聽 WMI 事件導致觸摸失效
			            	
    
				        	
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/  ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        [STAThread]特性
			            	
    
				        	
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
			            	
    
				        	
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .NET 多版本 WinForm 開源控制項庫 SunnyUI
			            	
    
				        	
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .net core8 使用Swagger（附當前源碼）
			            	
    
				        	
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
			            	
    
				        	
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        C# 開源教程帶你輕鬆掌握數據結構與演算法
			            	
    
				        	
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第22篇 生成proto文件bat腳本
			            	
    
				        	
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        記一次 .NET某上位機視覺程式 卡死分析
			            	
    
				        	
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        醫學DICOM文件與PACS系統進行數據傳輸
			            	
    
				        	
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類