MySQL之備份與恢複

    MySQL備份類型：

1 根據備份時，伺服器是否線上，分為：

1）熱備——伺服器線上，并且讀寫不受影響。

2）溫備——伺服器線上，但需要鎖表，并且可讀不可寫。

3）冷備——伺服器下線，讀寫中止。

2 按備份方式，分為：

實體備份——複制資料檔案，特點是備份和恢複速度比較快。占用空間較大，适合于大資料備份。

邏輯備份——将資料導出到檔案中，速度較慢，可能失去浮點數精度，适合于資料量較小的場景。

邏輯備份最大的優點是對于各種存儲引擎都可以采用同樣的方式來備份。而實體備份則不同，不同的存儲引擎有不同的備份方法，是以，對于不同存儲引擎混合的資料庫，用邏輯備份會理簡單一些。

    MySQL的備份工具:

1 mysqldump——邏輯備份工具，溫備，對innoDB可以實作熱備。

選項：

--master-data=0|1|2

0：不記錄二進制檔案及其位置

1：以change master to 的方式記錄位置，可用于恢複後直接啟動從伺服器

2：以change master to 的方式記錄位置，但預設為被注釋

-x, --lock-all-tables：加上此參數，即備份之前鎖定所有表

-F，--flush-logs：重新整理二進制日志

--databases：備份指定庫和表

-A, --all-databases：備份所有庫和表

1）備份和恢複

導出指定庫中的部分表：導出test庫中的test01、test02兩張表：

[root@localhost ~]# mysqldump -uroot -p test test01 test02 > /root/test_$(date +%F).sql

恢複：恢複表時，由于備份檔案中沒有建庫的語句，必須指明庫，如果庫不存在，需要先建立資料庫

[root@localhost ~]# mysql -uroot -p test < /root/test_2017-05-01.sql 

導出一個或多個資料庫：導出test yewu xiaoshou資料庫

[root@localhost ~]# mysqldump -uroot -p --databases test yewu xiaoshou > /root/test_yewu_xiaoshou_$(date +%F).sql

恢複：由于備份檔案中已包含完整的庫資訊，是以還原時不需要指定庫名

[root@localhost ~]# mysql -uroot -p < test_yewu_xiaoshou_2017-05-01.sql 

備份MySQL資料庫中所有的庫：

[root@localhost ~]# mysqldump -uroot -p --all-databases > alldatabase_$(date +%F).sql

恢複：

[root@localhost ~]# mysql -uroot -p < alldatabase_2017-05-01.sql 

2）生産環境執行個體

a 上午10：00備份資料庫

[root@localhost ~]# mysqldump -uroot -p --lock-all-tables --flush-logs --master-data=2 --databases test > test_$(date +%F).sql

其中表test01的内容如下：

mysql> select * from test01;

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

| id   | name |

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

b 向表中添加新的資料

mysql> insert into test01 values('18','dan');

Query OK, 1 row affected (2.56 sec)

|   18 | dan  |

5 rows in set (0.00 sec)

c 下午2：00，資料庫中的表被誤删，開始恢複備份，首先恢複全備

[root@localhost ~]# mysql -uroot -p test < test_2017-05-01.sql 

Enter password: 

4 rows in set (0.00 sec)

由以上結果可知，缺少id為18的行

d 使用mysqlbinlog指令恢複自mysqldump備份以來的binlog

檢視mysqldump備份時binlog日志的位置

[root@localhost ~]# less test_2017-05-01.sql 

-- MySQL dump 10.13  Distrib 5.6.30, for Linux (i686)

--

-- Host: localhost    Database: test

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

-- Server version       5.6.30-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 */;

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000003', MASTER_LOG_POS=120;

-- Current Database: `test`

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

-- Table structure for table `test01`

備份二進制日志

[root@localhost ~]#mysqlbinlog localhost-bin.000003 > /root/binlog.sql

删除二進制日志中的錯誤SQL語句

vim binlog.sql

...

DROP TABLE `test01`

DROP TABLE IF EXISTS `test01`

mysql -uroot -p  test < backbinlog.sql

檢視恢複後的表：

2 xtrabackup——是Percona公司參與開發的一款對InnoDB做資料備份的工具，備份方式為實體備份，而且支援熱備。

Xtrabackup包含兩個工具：即xtrabackup和innobackupex

xtrabackup隻能備份InnoDB和XtraDB兩種資料引擎，而不能備份MyISAM引擎的資料表

innobackupex是一個封裝了xtrabackup的Perl腳本，支援同時備份InnoDB和MyISAM。

1）安裝，需要安裝好epel的yum源

[root@localhost ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.8/binary/redhat/6/i386/percona-xtrabackup-2.3.8-1.el6.i686.rpm

[root@localhost ~]# yum -y localinstall percona-xtrabackup-2.3.8-1.el6.i686.rpm 

2）備份和恢複

備份：

a 建立備份賬戶：

mysql> grant reload,lock tables,replication client on *.* to 'dbbak'@'localhost' identified by 'bk2016' ;

b 完全備份，socket可以不指定，除非和預設不一緻

[root@localhost ~]# innobackupex --socket=/tmp/mysqld.sock --user=dbbak --password=bk2016 /root/

170501 23:20:35 Backup created in directory '/root/2017-05-01_23-20-25/'

MySQL binlog position: filename 'localhost-bin.000003', position '15651'

170501 23:20:35 [00] Writing backup-my.cnf

170501 23:20:35 [00]        ...done

170501 23:20:35 [00] Writing xtrabackup_info

xtrabackup: Transaction log of lsn (2031876) to (2031876) was copied.

170501 23:20:35 completed OK!

[root@localhost ~]# cd 2017-05-01_23-20-25/            #檢視備份後的目錄

[root@localhost 2017-05-01_23-20-25]# ls

backup-my.cnf  ibdata1  mysql  performance_schema  test  xiaoshou  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile  yewu

備份目錄說明：

xtrabackup_checkpoints——備份類型（如完全和增量）、備份狀态、LSN（日志序列号）範圍資訊

xtrabackup_my.cnf——備份指令用到的配置選項資訊

xtrabackup_binlog_info——MySQL伺服器目前使用的二進制日志檔案及到備份這一刻為止二進制日志事件的位置

c 預處理備份檔案，準備一個完全備份，可增加記憶體選項，--use-memory=1G

[root@localhost ~]# innobackupex --apply-log /root/2017-05-01_23-20-25 

d 資料更改之後，通過二進制進行增量備份

[root@localhost 2017-05-01_23-20-25]# cat xtrabackup_binlog_info 

localhost-bin.00000315651

[root@localhost ~]# mysqlbinlog /usr/data/mysql/localhost-bin.000003 > /root/bin$(date +%F).sql

a 停止服務

[root@localhost ~]# service mysqld stop

b 恢複全備，MySQL資料目錄必須為空，否則會報錯

[root@localhost ~]# innobackupex --copy-back --user=dbbak --password=bk2016 /root/2017-05-01_23-20-25/

[root@localhost mysql]# cd /usr/local/mysql/            #修改資料目錄的屬主屬組

[root@localhost mysql]# chown -R mysql.mysql *

c 啟動服務

[root@localhost mysql]# service mysqld start

d 通過二進制檔案恢複增量備份

mysql> set sql_log_bin=0;                    #暫時關閉二進制日志

Query OK, 0 rows affected (0.03 sec)

mysql> source /root/bin2017-05-01.sql;

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> set sql_log_bin=1;                    #開啟二進制日志

e 恢複完成後，重新進行一次完全備份

    表的導入和導出

1 導出

方式 1：

[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 -e "select * from test.test01" > /root/mysql.txt

[root@localhost ~]# ls

2017-05-01_23-20-25  anaconda-ks.cfg  bin2017-05-01.sql  cmake-3.7.1  cmake-3.7.1.tar.gz  install.log  install.log.syslog  mysql-5.6.30  mysql-5.6.30.tar.gz  mysql.txt  percona-xtrabackup-2.3.8-1.el6.i686.rpm

[root@localhost ~]# cat mysql.txt 

idname

1zhan

2liso

3wang

5kang

18dan

方式 2：

mysql> select * from test01 into outfile '/tmp/c.txt';

Query OK, 5 rows affected (0.00 sec)

[root@localhost tmp]# cat /tmp/c.txt 

方式 3：

[root@localhost tmp]# mysqldump -uroot -p -h 127.0.0.1 -T /tmp test test02

[root@localhost tmp]# ls

a.txt  c.txt  mysqld.sock  percona-version-check  test02.sql  test02.txt  vmware-root  vmware-root-1821705925

[root@localhost tmp]# cat test02.txt

a

b

2 導入

mysql> select * from test03;                #檢視導入資料之前的内容

|   10 | zwj  |

2 rows in set (0.00 sec)

mysql> load data infile '/tmp/a.txt' into table test.test03;        #導入資料

Query OK, 5 rows affected (0.13 sec)

Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test.test03;

備注：

鎖表：

mysql> flush tables with read lock;

釋放鎖表：

mysql> unlock tables;

本文轉自 zengwj1949 51CTO部落格，原文連結:http://blog.51cto.com/zengwj1949/1921030