數(shù)據(jù)備份與恢復(fù)
創(chuàng)新互聯(lián)建站是一家專業(yè)提供蘭溪企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站制作、做網(wǎng)站、html5、小程序制作等業(yè)務(wù)。10年已為蘭溪眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站制作公司優(yōu)惠進(jìn)行中。
備份方式：
物理備份：直接拷貝備份庫和表對(duì)應(yīng)的文件
cp -r /var/lib/MySQL/mysql /mysql.bak
tar -zcvf /mysql.tar.gz /varlib/mysql/mysql/*
邏輯備份：執(zhí)行備份時(shí)，根據(jù)已有的庫和表生成對(duì)應(yīng)的sql命令,把生成的sql命令存儲(chǔ)到指定的備份文件里
備份策略：
完全備份： 備份所有數(shù)據(jù)(表，庫，服務(wù)器)
差異備份：備份自完全備份后所新產(chǎn)生（備份新產(chǎn)生的數(shù)據(jù)）
增量備份：備份自上一次備份后，所有新產(chǎn)生（備份新產(chǎn)生的數(shù)據(jù)）
備份數(shù)據(jù)時(shí)要考慮的問題
備份頻率 備份時(shí)間 備份策略 存儲(chǔ)空間 備份文件的命名 xx.sql
備份策略使用方式
完全 + 增量
完全 + 差異
執(zhí)行數(shù)據(jù)備份的方式：周期性計(jì)劃任務(wù)crond 執(zhí)行備份腳本
完全備份數(shù)據(jù)mysqldump
mysqldump -uroot -pabc123 庫名 > 目錄/名.sql
庫名的表示方式
--all-database #備份數(shù)據(jù)庫服務(wù)器的所有數(shù)據(jù)
庫名 #備份一個(gè)庫里的所有數(shù)據(jù)
庫 表 #備份一個(gè)表里的所有數(shù)據(jù)( 庫和表用空格間隔 )
-B 庫名1 庫名2 #一起備份多各庫里的所有數(shù)據(jù)
[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.
增量備份與增量恢復(fù)
一，啟用binlog日志 實(shí)現(xiàn)實(shí)時(shí)增量備份
binlog日志介紹：又被稱為二進(jìn)制日志 是mysql數(shù)據(jù)庫服務(wù)日志文件的一種，記錄連接服務(wù)器后，執(zhí)行的除查詢之外的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
默認(rèn)存儲(chǔ)路徑 /var/lib/mysql
默認(rèn)文件名 主機(jī)名-bin.000001 > 500M 時(shí)會(huì)自動(dòng)在生成
主機(jī)名-bin.000002
日志索引文件 主機(jī)名-bin.index記錄當(dāng)前已有的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=數(shù)字m超過 數(shù)子m 大小時(shí)自動(dòng)生成下一各日志文件
binlog_format="mixed"
[root@mysql4-1 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/logdir/test
binlog_format="mixed"
#systemctl restart mysqld
查看日志當(dāng)前記錄格式
mysql> show variables like 'binlog_format';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+-----------+
| binlog_format | ROW |
+--------------------------+------------+
有三種記錄格式：
statement: 每條修改數(shù)據(jù)的sql命令都會(huì)記錄在binlog日志中
row:不記錄sql語句上下文相關(guān)信息，僅保存那條記錄被修改
mixed:是以上兩種格式混合使用
查看binlog日志內(nèi)容
mysqlbinlog binlog日志文件名
內(nèi)容中/* */ 為注釋內(nèi)容
5.7 之前直接sql寫直接記sql
進(jìn)入數(shù)據(jù)庫
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命令的方式：
時(shí)間點(diǎn)
pos點(diǎn)（偏移量）
# at 4從偏移量量4 到 123 時(shí)間 2017-12-25 20:59:39
#171225 20:59:39 server id 1 end_log_pos 123
執(zhí)行binlog里記錄的sql命令恢復(fù)數(shù)據(jù)
mysqlbinlog 選項(xiàng) binlog日志文件名 | mysql -uroot -pabc123
選項(xiàng)
時(shí)間點(diǎn) --start-datetime="yyyy/mm/dd hh:mm:ss"
--stop-datetime="yyyy/mm/dd hh:mm:ss"#不聲明結(jié)束時(shí)間 默認(rèn)讀到日志結(jié)束
偏移量pos點(diǎn)
--start-positon=數(shù)字
--stop-positon=數(shù)字
什么情況下會(huì)生成新的binlog日志文件 （默認(rèn)>500M后自動(dòng)創(chuàng)建新的）
#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 日志文件 #這樣刪除不會(huì)同步索引文件 不建議用
mysql>reset master;#刪除當(dāng)前所有日志文件 重新生成一各日志文件
purge master logs to "日志文件名"#刪除從最開始到當(dāng)前日志文件名的文件
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
練習(xí)增量備份并恢復(fù)
1) 完全備份一個(gè)數(shù)據(jù)庫
[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) 刪除所以內(nèi)容
mysql> delete from student.users;
Query OK, 47 rows affected (0.06 sec)
4)進(jìn)行恢復(fù)
先使用完全備份恢復(fù)
[root@mysql4-1 ~]# mysql -uroot -pabc123 student < /bakdir/stu.sql
在使用binlog 做增量恢復(fù)
[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 <選項(xiàng)>
--use用戶名
--password密碼
--database備份數(shù)據(jù)庫名
--no-timestamp不用日期命名備份文件存儲(chǔ)的子目錄
--apply-log恢復(fù)日志 重做已提交的事務(wù)，回滾未提交的事務(wù)
--copy-back恢復(fù)備份至數(shù)據(jù)庫服務(wù)器的數(shù)據(jù)目錄
備份文件夾必須為空
要求 /var/lib/mysql/ 為空
xtrabackup只能備份innodb和xtradb兩種引擎的表，而不能備份myisam引擎的表；
innobackupex是一個(gè)封裝了xtrabackup的Perl腳本，支持同時(shí)備份innodb和myisam，但在對(duì)myisam備份時(shí)需要加一個(gè)全局的讀鎖。還有就是myisam不支持增量備份。
支持事務(wù) 和 事務(wù)回滾
ls /var/lib/mysql
事務(wù)日志文件
ibdata1
LSN 日志序列號(hào)
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
備份開始時(shí)首先會(huì)開啟一個(gè)后臺(tái)檢測進(jìn)程，實(shí)時(shí)檢測mysql redo的變化，一旦發(fā)現(xiàn)redo中有新的日志寫入，立刻將日志記入后臺(tái)日志文件xtrabackup_log中。之后復(fù)制innodb的數(shù)據(jù)文件和系統(tǒng)表空間文件ibdata1，待復(fù)制結(jié)束后，執(zhí)行flush tables with read lock操作，復(fù)制.frm，MYI，MYD，等文件（執(zhí)行flush tableswith read lock的目的是為了防止數(shù)據(jù)表發(fā)生DDL操作，并且在這一時(shí)刻獲得binlog的位置）最后會(huì)發(fā)出unlock tables，把表設(shè)置為可讀可寫狀態(tài)，最終停止xtrabackup_log
完全備份
#innobackupex --user root --password abc123 --database="系統(tǒng)庫列表 存儲(chǔ)數(shù)據(jù)庫" 備份路徑名
[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還會(huì)在備份目錄中創(chuàng)建如下文件
[root@mysql4-1 allbak]# cat xtrabackup_checkpoints#備份配置文件
backup_type = full-backuped#備份類型 （完全或增量）
from_lsn = 0
to_lsn = 5379257#LSN 日志序列號(hào) 范圍信息
last_lsn = 5379266
compact = 0
recover_binlog_info = 0
xtrabackup_logfile #后臺(tái)日志文件
xtrabackup_binlog_info #mysql服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二進(jìn)制日志事件的位置。
ibdata1#系統(tǒng)表空間文件
xtrabackup_info#有關(guān)此次備份的各種詳細(xì)信息
backup-my.cnf #執(zhí)行備份占用的系統(tǒng)資源
恢復(fù)
先恢復(fù)日志 在恢復(fù)數(shù)據(jù)
首先要保證 /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#恢復(fù)過數(shù)據(jù)了
from_lsn = 0
to_lsn = 5379257
last_lsn = 5379266
compact = 0
recover_binlog_info = 0
增量備份
--incremental 目錄名 增量備份
--incremental-basedir=目錄名 增量備份是 指定上一次數(shù)據(jù)存儲(chǔ)的目錄名
#innobackupex --user root --password abc123 --databases="系統(tǒng)庫列表 存儲(chǔ)數(shù)據(jù)庫" --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
在數(shù)據(jù)庫中添加一些數(shù)據(jù)
增量備份 /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
在數(shù)據(jù)庫中添加一些數(shù)據(jù)
增量備份 /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
增量恢復(fù)
--incremental-dir=目錄名
--redo-only 合并日志
1 恢復(fù)日志文件
先恢復(fù)完全備份
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 恢復(fù)數(shù)據(jù)
innobackupex --user root --password abc123 --databases="performance_schema mysql sys db1" --copy-back /allbak
3 重啟服務(wù)
具體操作
[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 下的信息已經(jīng)記錄到allbak下了
[root@mysql4-1 ~]# rm -rf /new1
[root@mysql4-1 ~]# rm -rf /new2
在數(shù)據(jù)庫中添加一些數(shù)據(jù)
[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
在執(zhí)行增量備份 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
使用完全備份文件恢復(fù)單個(gè)表----innobackupex
--apply-log --export 導(dǎo)出表信息 .exp .cfg
discard tablespace 刪除表空間 .ibd
import tablespace 導(dǎo)入表空間
完全備份
innobackupex --user root --password abc123 --databases="performance_schema mysql sys gamedb" /gamedbbak --no-timestamp
模擬數(shù)據(jù)丟失
drop table t1;
1.按照原表結(jié)構(gòu)創(chuàng)建表
create table t1(id int);
2.從配置文件里導(dǎo)出表信息
innobackupex --user root --password abc123 --databases="performance_schema mysql sys gamedb" --apply-log --export /gamedbbak
3.刪除表空間
alter table t1 discard tablespace;
4.把導(dǎo)出的表信息 拷貝的對(duì)應(yīng)的數(shù)據(jù)庫目錄下
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.導(dǎo)入表空間
chown mysql:mysql t1.*
alter table t1 import tablespace;
重慶分公司
重慶分公司
