DBA成長之路---mysql數據備份與恢復

數據備份與恢復

備份方式： 

    物理備份：直接拷貝備份庫和表對應的文件 

        cp -r /var/lib/mysql/mysql  /mysql.bak

        tar -zcvf /mysql.tar.gz  /varlib/mysql/mysql/*

    邏輯備份：執行備份時，根據已有的庫和表生成對應的sql命令,把生成的sql命令存儲到指定的備份文件里

備份策略：

    完全備份： 備份所有數據(表，庫，服務器)

    差異備份：備份自完全備份后所新產生（備份新產生的數據）

    增量備份：備份自上一次備份后，所有新產生（備份新產生的數據）

備份數據時要考慮的問題

備份頻率 備份時間 備份策略 存儲空間 備份文件的命名 xx.sql

備份策略使用方式

    完全 + 增量

    完全 + 差異

執行數據備份的方式：周期性計劃任務crond 執行備份腳本

完全備份數據mysqldump

    mysqldump -uroot -pabc123 庫名 > 目錄/名.sql

    庫名的表示方式

    --all-database         #備份數據庫服務器的所有數據

    庫名                         #備份一個庫里的所有數據

    庫    表                    #備份一個表里的所有數據( 庫和表用空格間隔 )

    -B 庫名1 庫名2      #一起備份多各庫里的所有數據

    [root@mysql4-1 admin]# mkdir /bakdir

    [root@mysql4-1 admin]# mysqldump -uroot -pabc123 mysql > /bakdir/mysql.sql

    mysqldump: [Warning] Using a password on the command line interface can be insecure.

    mysql> create database test;

    [root@mysql4-1 admin]# mysql -uroot -pabc123 test  < /bakdir/mysql.sql 

    mysql: [Warning] Using a password on the command line interface can be insecure.

增量備份與增量恢復

一，啟用binlog日志 實現實時增量備份

 binlog日志介紹：又被稱為二進制日志 是mysql數據庫服務日志文件的一種，記錄連接服務器后，執行的除查詢之外的sql命令

查看的：show desc select 

寫的：insert update delete

啟用binlog日志

vim /etc/my.cnf

[mysqld]

server_id=1#5.7之前的版本不需要使用 值1-255

log-bin

#systemctl restart mysqld

默認存儲路徑 /var/lib/mysql

默認文件名 主機名-bin.000001 > 500M 時會自動在生成

   主機名-bin.000002

日志索引文件 主機名-bin.index記錄當前已有的binlog日志文件名

[root@mysql4-1 ~]# cd /var/lib/mysql

[root@mysql4-1 mysql]# cat mysql4-1-bin.index 

./mysql4-1-bin.000001

自定義 binlog日志

#mkdir /logdir

#chown mysql /logdir

#vim /etc/my.cnf

server_id=1

log-bin=目錄名/日志文件名

max_binlog_size=數字m超過 數子m 大小時自動生成下一各日志文件

binlog_format="mixed"

[root@mysql4-1 ~]# vim /etc/my.cnf

[mysqld]

server_id=1

log-bin=/logdir/test

binlog_format="mixed"

#systemctl restart mysqld

查看日志當前記錄格式

mysql> show variables like 'binlog_format';

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

| Variable_name      | Value     |

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

| binlog_format         | ROW     |

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

有三種記錄格式：

statement: 每條修改數據的sql命令都會記錄在binlog日志中

row:不記錄sql語句上下文相關信息，僅保存那條記錄被修改

mixed:是以上兩種格式混合使用

 查看binlog日志內容

    mysqlbinlog binlog日志文件名

內容中/* */ 為注釋內容

5.7 之前直接sql寫直接記sql

進入數據庫

mysql> insert into studb.user(name,gid,uid) values('li',123,456);

mysql> insert into studb.user(name,gid,uid) values('bob',23,56);

mysql> insert into studb.user(name,gid,uid) values('ail',5023,5056);

[root@mysql4-1 mysql]# mysqlbinlog /logdir/test.000001 | grep -i insert

SET INSERT_ID=48/*!*/;

insert into studb.user(name,gid,uid) values('li',123,456)

SET INSERT_ID=49/*!*/;

insert into studb.user(name,gid,uid) values('bob',23,56)

SET INSERT_ID=50/*!*/;

insert into studb.user(name,gid,uid) values('ail',5023,5056)

 binlog日志文件記錄sql命令的方式：

時間點

pos點（偏移量）

# at 4從偏移量量4 到 123 時間 2017-12-25 20:59:39

#171225 20:59:39 server id 1  end_log_pos 123 

執行binlog里記錄的sql命令恢復數據

mysqlbinlog  選項 binlog日志文件名 |  mysql -uroot -pabc123 

選項 

時間點  --start-datetime="yyyy/mm/dd hh:mm:ss" 

--stop-datetime="yyyy/mm/dd hh:mm:ss"#不聲明結束時間 默認讀到日志結束

偏移量pos點

--start-positon=數字

--stop-positon=數字

什么情況下會生成新的binlog日志文件 （默認>500M后自動創建新的）

#systemctl restart mysqld

mysql>flush logs;

#mysql -uroot -pabc123 -e "flush logs"

#mysqldump -uroot -pabc123 --flush-logs 表名 > 備份文件.sql

[root@mysql4-1 ~]# mysqldump -uroot -pabc123 --flush-logs mysql > /root/mysql.sql

mysql> show master status;

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

| File                      | Position         | Binlog_Do_DB    | Binlog_Ignore_DB      | Executed_Gtid_Set        |

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

| test.000002        |      154            |                               |                                        |                                            |

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

1 row in set (0.00 sec)

mysql> create database db1;

mysql> create table db1.t1(id int);

mysql> insert into db1.t1 values(100);

mysql> insert into db1.t1 values(101);

mysql> insert into db1.t1 values(102);

mysql> flush logs;

mysql> show master status;

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

| File                      | Position          | Binlog_Do_DB   | Binlog_Ignore_DB      | Executed_Gtid_Set        |

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

| test.000003        |      154            |                               |                                        |                                            |

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

1 row in set (0.00 sec)

[root@mysql4-1 ~]# mysql -uroot -pabc123 -e "flush logs"

[root@mysql4-1 ~]# cat /logdir/test.index 

/logdir/test.000001

/logdir/test.000002

/logdir/test.000003

/logdir/test.000004

刪除binlog日志文件方法

#rm -rf 日志文件    #這樣刪除不會同步索引文件 不建議用

mysql>reset master;#刪除當前所有日志文件 重新生成一各日志文件 

purge master logs to "日志文件名"#刪除從最開始到當前日志文件名的文件

mysql> purge master logs to 'test.000003';

[root@mysql4-1 ~]# cat /logdir/test.index 

/logdir/test.000003

/logdir/test.000004

mysql> reset master;

[root@mysql4-1 ~]# cat /logdir/test.index 

/logdir/test.000001

練習增量備份并恢復

1) 完全備份一個數據庫

[root@mysql4-1 ~]# mysqldump -uroot -pabc123 student > /bakdir/stu.sql

2) 插入三條記錄

mysql> insert into student.users(name,UID,GID) values('test01',1,1);

mysql> insert into student.users(name,UID,GID) values('test02',2,2);

mysql> insert into student.users(name,UID,GID) values('test03',3,3);

mysql> select count(*) from student.users;

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

| count(*)         |

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

|       47            |

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

3) 刪除所以內容

mysql> delete from student.users;

Query OK, 47 rows affected (0.06 sec)

4)進行恢復

先使用完全備份恢復

[root@mysql4-1 ~]# mysql -uroot -pabc123 student < /bakdir/stu.sql 

在使用binlog 做增量恢復

[root@mysql4-1 ~]#  mysqlbinlog /logdir/test.000001 | grep -i -n delete

174:delete from student.users

查找其偏移量在1780-2765之間

[root@mysql4-1 ~]# mysqlbinlog --start-position=1780 --stop-position=2765 /logdir/test.000001 | mysql -uroot -pabc123

mysql> select count(*) from student.users;

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

| count(*)         |

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

|       47           |

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

1 row in set (0.00 sec)

mysql> select * from student.users where name like "test%";

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

| id       | name      | password      | UID     | GID      | describes       | home    | shell               |

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

| 45       | test01    | NULL             |    1       |    1        | NULL             | NULL   | /bin/bash         |

| 46      | test02     | NULL             |    2       |    2        | NULL             | NULL   | /bin/bash         |

| 47      | test03     | NULL             |    3       |    3        | NULL             | NULL   | /bin/bash         |

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

3 rows in set (0.00 sec)

二，使用第三方軟件提供的命令做增量備份

percona 

 安裝軟件包

     yum -y install perl-DBD-mysql

     yum -y install perl-Digest-MD5

     rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 

     rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 

 命令格式

#innobackupex <選項>

--use用戶名

--password密碼

--database備份數據庫名

--no-timestamp不用日期命名備份文件存儲的子目錄

--apply-log恢復日志  重做已提交的事務，回滾未提交的事務

--copy-back恢復備份至數據庫服務器的數據目錄

備份文件夾必須為空

要求 /var/lib/mysql/ 為空

xtrabackup只能備份innodb和xtradb兩種引擎的表，而不能備份myisam引擎的表；

innobackupex是一個封裝了xtrabackup的Perl腳本，支持同時備份innodb和myisam，但在對myisam備份時需要加一個全局的讀鎖。還有就是myisam不支持增量備份。

支持事務 和 事務回滾

ls /var/lib/mysql

事務日志文件

ibdata1

LSN 日志序列號

ib_logfile0 記錄sql命令

ib_logfile1

備份過程

start xtrabackup_log

copy .ibd;ibdata1

FLUSH TABLES WITH READ LOCK

copy .FRM;MYD;MYI;misc files

Get binary log position

UNLOCK TABLES

stop and copy xtrabackup_log

備份開始時首先會開啟一個后臺檢測進程，實時檢測mysql redo的變化，一旦發現redo中有新的日志寫入，立刻將日志記入后臺日志文件xtrabackup_log中。之后復制innodb的數據文件和系統表空間文件ibdata1，待復制結束后，執行flush tables with read lock操作，復制.frm，MYI，MYD，等文件（執行flush tableswith read lock的目的是為了防止數據表發生DDL操作，并且在這一時刻獲得binlog的位置）最后會發出unlock tables，把表設置為可讀可寫狀態，最終停止xtrabackup_log

完全備份

#innobackupex --user root --password abc123 --database="系統庫列表 存儲數據庫"  備份路徑名

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" /allbak

[root@mysql4-1 ~]# ls /allbak/2017-12-26_01-35-30/

[root@mysql4-1 ~]# rm -rf /allbak/

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" /allbak --no-timestamp

[root@mysql4-1 ~]# ls /allbak/

innobackupex還會在備份目錄中創建如下文件

[root@mysql4-1 allbak]# cat xtrabackup_checkpoints#備份配置文件 

backup_type = full-backuped#備份類型 （完全或增量）

from_lsn = 0

to_lsn = 5379257#LSN 日志序列號 范圍信息

last_lsn = 5379266

compact = 0

recover_binlog_info = 0

xtrabackup_logfile    #后臺日志文件

xtrabackup_binlog_info #mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。

ibdata1#系統表空間文件

xtrabackup_info#有關此次備份的各種詳細信息

backup-my.cnf  #執行備份占用的系統資源

恢復

先恢復日志 在恢復數據

首先要保證 /var/lib/mysql 為空

[root@mysql4-1 allbak]# rm -rf /var/lib/mysql/*

[root@mysql4-1 allbak]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --apply-log  /allbak 

[root@mysql4-1 allbak]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --copy-back  /allbak 

[root@mysql4-1 allbak]# chown -R mysql:mysql /var/lib/mysql

[root@mysql4-1 allbak]# systemctl restart mysqld

[root@mysql4-1 allbak]# cat xtrabackup_checkpoints 

backup_type = full-prepared#恢復過數據了

from_lsn = 0

to_lsn = 5379257

last_lsn = 5379266

compact = 0

recover_binlog_info = 0

增量備份

--incremental 目錄名  增量備份

--incremental-basedir=目錄名  增量備份是 指定上一次數據存儲的目錄名

#innobackupex --user root --password abc123 --databases="系統庫列表 存儲數據庫" --incremental 目錄名 --incremental-basedir=目錄名  --no-timestamp

先有一次完全備份

[root@mysql4-1 ~]# rm -rf /allbak/

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" /allbak --no-timestamp

在數據庫中添加一些數據

增量備份 /new1

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --incremental /new1  --incremental-basedir=/allbak  --no-timestamp

[root@mysql4-1 ~]# cat /new1/xtrabackup_checkpoints 

backup_type = incremental

from_lsn = 5379681

to_lsn = 5386256

last_lsn = 5386265

compact = 0

recover_binlog_info = 0

在數據庫中添加一些數據

增量備份 /new2

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --incremental /new2  --incremental-basedir=/new1  --no-timestamp

[root@mysql4-1 ~]# cat /new2/xtrabackup_checkpoints 

backup_type = incremental

from_lsn = 5386256

to_lsn = 5394126

last_lsn = 5394135

compact = 0

recover_binlog_info = 0

增量恢復

--incremental-dir=目錄名 

--redo-only 合并日志

1 恢復日志文件

先恢復完全備份

innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --apply-log --redo-only /allbak 

innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --apply-log --redo-only /allbak --incremental-dir=/new1 

innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --apply-log --redo-only /allbak --incremental-dir=/new2 

2 恢復數據

innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --copy-back  /allbak 

3 重啟服務

具體操作

[root@mysql4-1 ~]# rm -rf /var/lib/mysql/*

[root@mysql4-1 ~]# cat /allbak/xtrabackup_checkpoints 

backup_type = full-backuped

from_lsn = 0

to_lsn = 5379681

last_lsn = 5379690

compact = 0

recover_binlog_info = 0

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --apply-log  --redo-only /allbak 

[root@mysql4-1 ~]# cat /allbak/xtrabackup_checkpoints

backup_type = log-applied

from_lsn = 0

to_lsn = 5379681

last_lsn = 5379690

compact = 0

recover_binlog_info = 0

[root@mysql4-1 ~]# cat /new1/xtrabackup_checkpoints 

backup_type = incremental

from_lsn = 5379681

to_lsn = 5386256

last_lsn = 5386265

compact = 0

recover_binlog_info = 0

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --apply-log --redo-only /allbak --incremental-dir=/new1

[root@mysql4-1 ~]# cat /allbak/xtrabackup_checkpoints 

backup_type = log-applied

from_lsn = 0

to_lsn = 5386256

last_lsn = 5386265

compact = 0

recover_binlog_info = 0

[root@mysql4-1 ~]# cat /new2/xtrabackup_checkpoints 

backup_type = incremental

from_lsn = 5386256

to_lsn = 5394126

last_lsn = 5394135

compact = 0

recover_binlog_info = 0

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --apply-log --redo-only /allbak --incremental-dir=/new2 

[root@mysql4-1 ~]# cat /allbak/xtrabackup_checkpoints 

backup_type = log-applied

from_lsn = 0

to_lsn = 5394126

last_lsn = 5394135

compact = 0

recover_binlog_info = 0

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --copy-back  /allbak 

[root@mysql4-1 ~]# ls /var/lib/mysql

db1             ib_logfile0  mysql               xtrabackup_binlog_pos_innodb

ib_buffer_pool  ib_logfile1  performance_schema  xtrabackup_info

ibdata1         ibtmp1       sys

[root@mysql4-1 ~]# chown -R mysql:mysql /var/lib/mysql

[root@mysql4-1 ~]# systemctl restart mysqld

[root@mysql4-1 ~]# mysql -uroot -pabc123

new1 和 new2 下的信息已經記錄到allbak下了

[root@mysql4-1 ~]# rm -rf /new1

[root@mysql4-1 ~]# rm -rf /new2

在數據庫中添加一些數據

[root@mysql4-1 ~]# cat /allbak/xtrabackup_checkpoints 

backup_type = log-applied

from_lsn = 0

to_lsn = 5394126

last_lsn = 5394135

compact = 0

recover_binlog_info = 0

在執行增量備份 new1

[root@mysql4-1 ~]# innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --incremental /new1  --incremental-basedir=/allbak  --no-timestamp

[root@mysql4-1 ~]# cat /new1/xtrabackup_checkpoints 

backup_type = incremental

from_lsn = 5394126

to_lsn = 5386173

last_lsn = 5386182

compact = 0

recover_binlog_info = 0

使用完全備份文件恢復單個表----innobackupex

--apply-log    --export  導出表信息     .exp  .cfg

discard  tablespace  刪除表空間          .ibd

import  tablespace 導入表空間

完全備份

innobackupex --user root --password abc123 --databases="performance_schema mysql sys gamedb" /gamedbbak --no-timestamp

模擬數據丟失

drop table t1;

1.按照原表結構創建表

create table t1(id int);

2.從配置文件里導出表信息

innobackupex --user root --password abc123 --databases="performance_schema mysql sys gamedb" --apply-log --export  /gamedbbak

3.刪除表空間

alter table t1 discard tablespace; 

4.把導出的表信息 拷貝的對應的數據庫目錄下

ti.ibd t1.exp t1.cfg

cd /gamedbbak/gamedb

cp t1.{exp,ibd,cfg} /var/lib/mysql/gamedb/

ls /var/lib/mysql/gamedb/t1.*

5.導入表空間

chown mysql:mysql t1.*

alter table t1 import tablespace;