來源：狂奔的螞蟻（達克） 發(fā)布時間：2018-11-24 10:09:54 閱讀量：1345
數(shù)據(jù)備份策略：
–完全備份：備份所有數(shù)據(jù)
–增量備份：備份上次備份后，所有新產(chǎn)生的數(shù)據(jù)
–差異備份：備份完全備份后，所有新產(chǎn)生的數(shù)據(jù)
數(shù)據(jù)備份方式：
–物理備份
–邏輯備份
當訪問量小的時候實行備份，完全+增量 完全+差異
備份的文件要有標識性，加上時間
1 物理備份：
（1） 將mysql50上的數(shù)據(jù)庫備份至mysql51數(shù)據(jù)，保持數(shù)據(jù)庫一致
模擬刪除51虛擬機上的數(shù)據(jù)庫文件
[root@mysql51 ~]# systemctl stop mysqld;
[root@mysql51 ~]# rm -rf /var/lib/mysql
備份50虛擬機上的數(shù)據(jù)庫文件，并遠程傳送給51主機
[root@mysql50 ~]# cp -r /var/lib/mysql/ /root/mysqlall.bak
[root@mysql50 ~]# scp -r /root/mysqlall.bak/ root@192.168.4.51:/root
在51主機上部署數(shù)據(jù)庫文件，修改權限，再重啟服務完成數(shù)據(jù)恢復
[root@mysql51 ~]# cp -r mysqlall.bak/ /var/lib/mysql
[root@mysql51 ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql51 ~]# systemctl restart mysqld;
1
2
3
4
5
6
7
8
9
10
物理備份局限性：
mysql版本要一致，操作系統(tǒng)要一致，存儲引擎為innodb的數(shù)據(jù)庫備份單張數(shù)據(jù)庫表需要連帶備份數(shù)據(jù)庫日志
2 邏輯備份
備份：（由系統(tǒng)管理員來操作）
備份操作 ： mysqldump -uroot -p123456 庫名 > 路徑/文件 (完全備份）
恢復操作 ： mysql -uroot -p123456 庫名 < 路徑/文件 （完全恢復）
備份時庫名表示方式：
–all-databases 或 -A ---->所有庫
數(shù)據(jù)庫名 ---->單個庫
數(shù)據(jù)庫名 表名 ---->單張表
-B 數(shù)據(jù)庫1 數(shù)據(jù)庫2 ---->多個庫
注意事項： 無論是備份還是恢復，都要驗證用戶權限
當單個庫被刪除后，如果要用備份數(shù)據(jù)恢復這個庫，需要先創(chuàng)建這個庫，因為備份的時候只會備份這個庫下的所有表的記錄
（1）備份恢復一個庫
[root@mysql50 ~]# mysqldump -uroot -p123456 db4 > /mydata/db4.sql //備份
mysql> drop database db4;
mysql> create database db4; //創(chuàng)建一個數(shù)據(jù)庫名
[root@mysql50 ~]# mysql -uroot -p123456 db4 < /mydata/db4_ps1.sql //恢復
1
2
3
4
（2）備份恢復一個庫下的表
[root@mysql50 ~]# mysqldump -uroot -p123456 db4 passwd1 > /mydata/db4_ps1.sql
mysql> drop table passwd1;
[root@mysql50 ~]# mysql -uroot -p123456 db4 < /mydata/db4_ps1.sql
1
2
3
（3）備份恢復幾個庫
[root@mysql50 ~]# mysqldump -uroot -p123456 -B db4 db3 > /mydata/db3_4.sql
mysql> drop database db4;
mysql> drop database db3;
[root@mysql50 ~]# mysql -uroot -p123456 < /mydata/db3_4.sql
1
2
3
4
完全備份的兩個缺點：
–完全備份后，對于新增加的數(shù)據(jù)無法進行恢復
–完全備份或恢復時，會對數(shù)據(jù)庫表加上寫鎖，影響用戶的訪問
3 binlog日志
啟用binlog日志實現(xiàn)數(shù)據(jù)實時增量備份和恢復
binlog日志又叫二進制日志
記錄所有除查詢以外的所有操作
配置mysql主從同步的必備條件
（1）修改主配置文件開啟binlog日志
[root@mysql50 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/logdir/log （自定義日志文件路徑，如果不修改指定，默認路徑為mysql根目錄下以當前時間為名字的文件）
server_id=50 （注意指定id不能重復，值隨意 0-255之間）
binlog_format=mixed (開啟模式mixed 記錄每條修改的數(shù)據(jù)以及每條除select以外的寫sql命令）
max_binlog_size=200m（當日志文件達到200M后新建一個日志文件，默認為1G）
1
2
3
4
5
6
7
（2 ） 日志文件的存儲格式：
三種日志記錄格式：
statement :記錄每條sql命令
row :記錄哪條記錄被修改
mixed：上兩種格式的總和
查看日志文件的默認存儲格式
mysql> show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
（3）查看日志文件默認最大存儲量
mysql> show variables like "max_binlog_size";
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1
2
3
4
5
6
（4）查看主日志文件狀態(tài)（當前偏移量）
mysql> show master status;
mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| log.000008 | 27328 | | | |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
（5）查看二進制日志文件
[root@mysql50 ~]# mysqlbinlog /logdir/log.000001
1
（6）刪除指定日志之前的日志文件
mysql> purge master logs to "log.000007";
1
（7）刪除所有日志文件，重置，生成log.000001
mysql> reset master;
1
（8）手動生成日志文件的種方式
1 重啟Mysqld服務
2 執(zhí)行sql操作： mysql > flush logs
3 mysqldump --flush-logs 在實行完全備份時生成新的日志文件
4 mysql -uroot -p123456 -e 'flush logs'
1
2
3
4
（9）分析binlog日志（日志以偏移量和時間來記錄對數(shù)據(jù)庫的操作）
[root@mysql50 ~]# mysqlbinlog /logdir/log.000008
…………
# at 26989 //起始偏移量
#181121 19:15:11 server id 50 end_log_pos 27120 CRC32 0xfafa5a5c Query thread_id=15 exec_time=0 error_code=0
SET TIMESTAMP=1542798911/*!*/;
INSERT INTO `yg` VALUES (3,'kenji'),(4,'natasha'),(888,'kenji') //記錄的SQL語句
/*!*/;
# at 27120 // 執(zhí)行sql語句提交后的偏移量
#181121 19:15:11 server id 50 end_log_pos 27151 CRC32 0x6593fcc8 Xid = 610
COMMIT/*!*/;
…………
1
2
3
4
5
6
7
8
9
10
11
（10）使用mysqlbinlog 工具
格式 ： mysqlbinlog [選項] binlog日志文件名
常用選項 ：
--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
--start-position=數(shù)字
--stop-position=數(shù)字
1
2
3
4
（11）案例：
啟用binlog日志
創(chuàng)建db1庫tb1表，插入三條記錄
刪除t1表中剛插入的三條記錄
使用mysqlbinlog恢復刪除的三條記錄
1 修改配置文件，開啟binlog，重啟mysqld服務
[root@mysql50 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/logdir/log
server_id=50
max_binlog_size=200m
binlog_format=mixed
[root@mysql50 ~]# systemctl restart mysqld
2 創(chuàng)建數(shù)據(jù)庫表tb1，插入3條數(shù)據(jù)
mysql> create database db1;
mysql> use db1;
mysql> create table tb1(
-> id int,
-> name varchar(12),
-> age int);
mysql> insert into tb1 values (1,"tom",22),(2,"kenji",24),(3,"harry",26);
mysql> select * from tb1;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 22 |
| 2 | kenji | 24 |
| 3 | harry | 26 |
+------+-------+------+
3 rows in set (0.00 sec)
3 查看日志記錄
[root@mysql50 ~]# mysqlbinlog /logdir/log.000009 | grep insert
insert into tb1 values (1,"tom",22),(2,"kenji",24),(3,"harry",26)
4 查看詳細日志記錄
[root@mysql50 ~]# mysqlbinlog /logdir/log.000009
# at 795
#181121 21:03:49 server id 50 end_log_pos 932 CRC32 0xc9dd7cec Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1542805429/*!*/;
insert into tb1 values (1,"tom",22),(2,"kenji",24),(3,"harry",26)
/*!*/;
# at 932
#181121 21:03:49 server id 50 end_log_pos 963 CRC32 0x05b7ce5e Xid = 16
COMMIT/*!*/;
5 刪除數(shù)據(jù)庫表的三條記錄
mysql> delete from tb1 where id=1;
mysql> delete from tb1 where id=2;
mysql> delete from tb1 where id=3;
ysql> select * from tb1;
Empty set (0.00 sec)
6 利用binlog日志恢復數(shù)據(jù)庫表
root@mysql50 ~]# mysqlbinlog --start-position=795 --stop-position=932 /logdir/log.000009 | mysql -uroot -p123456
7 再次查看數(shù)據(jù)庫表內的記錄 （數(shù)據(jù)已恢復）
mysql> select * from tb1;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 22 |
| 2 | kenji | 24 |
| 3 | harry | 26 |
+------+-------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
4 innobackupex工具實現(xiàn)增量熱備份
（1）常用的mysql備份工具
物理備份缺點
**跨平臺性差
**備份時間長，冗余備份，浪費存儲空間
mysqldump備份缺點
**效率低，備份和還原數(shù)度慢
** 備份過程中，數(shù)據(jù)插入和更新操作會被掛起
（2）XtraBackupex工具
一款強大的在線熱備份工具
**備份過程不鎖庫表，適合生產(chǎn)環(huán)境
**由專業(yè)組織percona提供
主要含有兩個組件
** xtrabackup：C程序，支持innodb和xtradb存儲引擎
** innobackupex: 以perl腳本封裝xtrabackup,還支持myisam存儲引擎
（3）安裝XtraBackup軟件包
[root@mysql50 ~]# yum -y install perl-DBD-MySQL perl-Digest-MD5 //安裝依賴包
[root@mysql50 ~]#rpm –ivh libev-4.15-1.el6.rf.x86_64.rpm //安裝依賴包
[root@mysql50 ~]# rpm -qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm
1
2
3
（4）innobackupex完整備份、增量備份操作
–host 主機名
–port 3306
–user 用戶名
–password 密碼
–databases=“庫名”
–databases=“庫1 庫2”
–databases=“庫.表”
–no-timestamp 不用日期命名備份文件存儲的子目錄，使用備份的數(shù)據(jù)庫名做備份目錄名
–no-timestmap 不使用日期命名備份目錄名
5 做一個完整備份
默認情況下，備份文件存儲的子目錄會用日期命名，
innobackupex作為客戶端工具，以mysql協(xié)議連入mysqld，將數(shù)據(jù)備份到/backup文件夾：
（1）完整備份數(shù)據(jù)庫
[root@mysql50 ~]# innobackupex --user=root --password=123456 /b --no-timestamp
確認備份文件
[root@mysql50 ~]# ls /b
backup-my.cnf db44 mysql xtrabackup_binlog_info
db1 db5 performance_schema xtrabackup_checkpoints
db2 ib_buffer_pool personinfo xtrabackup_info
db3 ibdata1 sys xtrabackup_logfile
查看本次完整本次備份信息
[root@mysql50 ~]# cat /b/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 4966947 //本次完全備份的結束日志序列號，也是下次增量備份開始的日志序列號
last_lsn = 4966956
compact = 0
recover_binlog_info = 0
準備恢復數(shù)據(jù)
[root@mysql50 ~]# innobackupex --apply-log /b
模擬數(shù)據(jù)庫丟失
[root@mysql50 ~]# systemctl stop mysqld
[root@mysql50 ~]# rm -rf /var/lib/mysql
[root@mysql50 ~]# mkdir /var/lib/mysql
恢復數(shù)據(jù)
[root@mysql50 ~]# innobackupex --copy-back /b
修改權限,啟動服務，查看結果
[root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql50 ~]# systemctl restart mysqld
[root@mysql50 ~]# mysql -uroot -p123456
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
（2）在數(shù)據(jù)庫新增加數(shù)據(jù)
mysql> create database db;
mysql> create table nsd(id int);
mysql> insert into nsd values(1);
mysql> insert into nsd values(2);
mysql> insert into nsd values(3);
mysql> select * from nsd;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
（3）第一次增量備份：
[root@mysql50 ~]# innobackupex --user=root --password=123456 --incremental /node --incremental-basedir=/b --no-timestamp
1
（4）第二次增加數(shù)據(jù)
mysql> insert into nsd values(4);
mysql> insert into nsd values(4);
mysql> insert into nsd values(4);
mysql> insert into nsd values(4);
mysql> select * from nsd;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 4 |
| 4 |
| 4 |
+------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
（5）第二次增量備份
[root@mysql50 ~]# innobackupex --user=root --password=123456 --incremental /node1 --incremental-basedir=/node --no-timestamp
1
2
（6） 模擬數(shù)據(jù)庫丟失
[root@mysql50 ~]# systemctl stop mysqld
[root@mysql50 ~]# rm -rf /var/lib/mysql
1
2
（7） 準備恢復數(shù)據(jù)
查看日志序列號
[root@mysql50 ~]# cat /b/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 4966947
last_lsn = 4966956
compact = 0
recover_binlog_info = 0
[root@mysql50 ~]# cat /node/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 4966947
to_lsn = 4973748
last_lsn = 4973757
compact = 0
recover_binlog_info = 0
[root@mysql50 ~]# cat /node1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 4973748
to_lsn = 4978822
last_lsn = 4978831
compact = 0
recover_binlog_info = 0
[root@mysql50 ~]# innobackupex --apply-log --redo-only /b
[root@mysql50 ~]# innobackupex --apply-log --redo-only /b --incremental-dir=/node
[root@mysql50 ~]# innobackupex --apply-log --redo-only /b --incremental-dir=/node1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
（8）拷貝文件
[root@mysql50 ~]# innobackupex --copy-back /b
1
（9）修改權限，啟動mysql服務，查看結果
[root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql50 ~]# systemctl restart mysqld
[root@mysql50 ~]# mysql -uroot -p123456
1
2
3
7 在完全備份中恢復單個表：
復制整個db4數(shù)據(jù)庫
[root@mysql50 ~]# innobackupex --user=root --password=123456 --databases="bd1" /a --no-timestamp
[root@mysql50 ~]# ls /a
backup-my.cnf ib_buffer_pool xtrabackup_binlog_info xtrabackup_info
db1 ibdata1 xtrabackup_checkpoints xtrabackup_logfile
刪除db4庫中的pass表以及表里的數(shù)據(jù)記錄
mysql> drop table db1.pass;
導出表信息
[root@mysql50 ~]# innobackupex --apply-log --export /a
創(chuàng)建pass表（此時表中沒有數(shù)據(jù)記錄，注意創(chuàng)建時字段要和之前表的字段一致）
mysql> create table pass( id int);
Query OK, 0 rows affected (0.25 sec)
[root@mysql50 ~]# ls /var/lib/mysql/db1/
db.opt pass.frm pass.ibd stu.frm stu.ibd
刪除pass表空間 .ibd文件
mysql> alter table pass discard tablespace;
Query OK, 0 rows affected (0.13 sec)
[root@mysql50 ~]# ls /var/lib/mysql/db1/
db.opt pass.frm stu.frm stu.ibd //少了文件pass.ibd
拷貝表信息文件
[root@mysql50 ~]# cp /a/db1/pass.{exp,cfg,ibd} /var/lib/mysql/db1
修改文件權限
[root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql/db1/pass.*
導入pass表空間 （數(shù)據(jù)恢復完成）
mysql> alter table pass import tablespace;
mysql> select * from pass;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
---------------------
13450931319
微信登錄
QQ登錄
微博登錄
售前咨詢
