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)

mysql> select * from test01;

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

| id   | name |

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

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

|   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: 

mysql> select * from test01;

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

| id   | name |

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

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

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

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

查看恢復后的表：

mysql> select * from test01;

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

| id   | name |

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

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

|   18 | dan  |

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

5 rows in set (0.00 sec)

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

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

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;                    #開啟二進制日志

Query OK, 0 rows affected (0.00 sec)

e 恢復完成后，重新進行一次完全備份

    表的導入和導出

1 導出

方式 1：

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

Enter password: 

[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 

1zhan

2liso

3wang

5kang

18dan

方式 3：

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

Enter password: 

[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;                #查看導入數據之前的內容

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

| id   | name |

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

|   10 | zwj  |

|   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;

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

| id   | name |

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

|   10 | zwj  |

|   10 | zwj  |

|    1 | zhan |

|    2 | liso |

|    3 | wang |

|    5 | kang |

|   18 | dan  |

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

備注：

鎖表：

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.03 sec)

釋放鎖表：

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)