亚洲欧美日韩综合系列在线_91精品人妻一区二区_欧美大肥婆一级特大AA片_九色91视频免费观看_亚洲综合国产精品_av中文字幕在线不卡_久久精品色综合网_看黄色视频的软件_无卡无码高清中文字幕码2024_亚洲欧美日韩天堂网

數(shù)據(jù)備份策略：

–完全備份：備份所有數(shù)據(jù)

–增量備份：備份上次備份后，所有新產(chǎn)生的數(shù)據(jù)

–差異備份：備份完全備份后，所有新產(chǎn)生的數(shù)據(jù)

數(shù)據(jù)備份方式：

–物理備份

–邏輯備份

當(dāng)訪問(wèn)量小的時(shí)候?qū)嵭袀浞?，完?增量 完全+差異

備份的文件要有標(biāo)識(shí)性，加上時(shí)間

1 物理備份：

（1） 將mysql50上的數(shù)據(jù)庫(kù)備份至mysql51數(shù)據(jù)，保持?jǐn)?shù)據(jù)庫(kù)一致

模擬刪除51虛擬機(jī)上的數(shù)據(jù)庫(kù)文件

[root@mysql51 ~]# systemctl stop mysqld;

[root@mysql51 ~]# rm -rf /var/lib/mysql

備份50虛擬機(jī)上的數(shù)據(jù)庫(kù)文件，并遠(yuǎn)程傳送給51主機(jī)

[root@mysql50 ~]# cp -r /var/lib/mysql/ /root/mysqlall.bak 

[root@mysql50 ~]# scp -r /root/mysqlall.bak/  root@192.168.4.51:/root

在51主機(jī)上部署數(shù)據(jù)庫(kù)文件，修改權(quán)限，再重啟服務(wù)完成數(shù)據(jù)恢復(fù)

[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)要一致，存儲(chǔ)引擎為innodb的數(shù)據(jù)庫(kù)備份單張數(shù)據(jù)庫(kù)表需要連帶備份數(shù)據(jù)庫(kù)日志

2 邏輯備份

備份：（由系統(tǒng)管理員來(lái)操作）

備份操作 ： mysqldump -uroot -p123456 庫(kù)名 > 路徑/文件 (完全備份）

恢復(fù)操作 ： mysql -uroot -p123456 庫(kù)名 < 路徑/文件 （完全恢復(fù)）

備份時(shí)庫(kù)名表示方式：

–all-databases 或 -A ---->所有庫(kù)

數(shù)據(jù)庫(kù)名 ---->單個(gè)庫(kù)

數(shù)據(jù)庫(kù)名 表名 ---->單張表

-B 數(shù)據(jù)庫(kù)1 數(shù)據(jù)庫(kù)2 ---->多個(gè)庫(kù)

注意事項(xiàng)： 無(wú)論是備份還是恢復(fù)，都要驗(yàn)證用戶權(quán)限

當(dāng)單個(gè)庫(kù)被刪除后，如果要用備份數(shù)據(jù)恢復(fù)這個(gè)庫(kù)，需要先創(chuàng)建這個(gè)庫(kù)，因?yàn)閭浞莸臅r(shí)候只會(huì)備份這個(gè)庫(kù)下的所有表的記錄

（1）備份恢復(fù)一個(gè)庫(kù)

[root@mysql50 ~]# mysqldump -uroot -p123456 db4 > /mydata/db4.sql    //備份

mysql> drop database db4;

mysql> create database db4;    //創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)名

[root@mysql50 ~]# mysql -uroot -p123456 db4 < /mydata/db4_ps1.sql    //恢復(fù)

1

2

3

4

（2）備份恢復(fù)一個(gè)庫(kù)下的表

[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）備份恢復(fù)幾個(gè)庫(kù)

[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

完全備份的兩個(gè)缺點(diǎn)：

–完全備份后，對(duì)于新增加的數(shù)據(jù)無(wú)法進(jìn)行恢復(fù)

–完全備份或恢復(fù)時(shí)，會(huì)對(duì)數(shù)據(jù)庫(kù)表加上寫(xiě)鎖，影響用戶的訪問(wèn)

3 binlog日志

啟用binlog日志實(shí)現(xiàn)數(shù)據(jù)實(shí)時(shí)增量備份和恢復(fù)

binlog日志又叫二進(jìn)制日志

記錄所有除查詢以外的所有操作

配置mysql主從同步的必備條件

（1）修改主配置文件開(kāi)啟binlog日志

[root@mysql50 ~]# vim /etc/my.cnf

[mysqld]

log_bin=/logdir/log （自定義日志文件路徑，如果不修改指定，默認(rèn)路徑為mysql根目錄下以當(dāng)前時(shí)間為名字的文件）

server_id=50   （注意指定id不能重復(fù)，值隨意 0-255之間）

binlog_format=mixed  (開(kāi)啟模式mixed 記錄每條修改的數(shù)據(jù)以及每條除select以外的寫(xiě)sql命令）

max_binlog_size=200m（當(dāng)日志文件達(dá)到200M后新建一個(gè)日志文件，默認(rèn)為1G）

1

2

3

4

5

6

7

（2 ） 日志文件的存儲(chǔ)格式：

三種日志記錄格式：

statement :記錄每條sql命令

row :記錄哪條記錄被修改

mixed：上兩種格式的總和

查看日志文件的默認(rèn)存儲(chǔ)格式

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）查看日志文件默認(rèn)最大存儲(chǔ)量

mysql> show variables like "max_binlog_size";

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

| Variable_name   | Value      |

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

| max_binlog_size | 1073741824 |

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

1

2

3

4

5

6

（4）查看主日志文件狀態(tài)（當(dāng)前偏移量）

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）查看二進(jìn)制日志文件

[root@mysql50 ~]# mysqlbinlog /logdir/log.000001

1

（6）刪除指定日志之前的日志文件

mysql> purge master logs to "log.000007";

1

（7）刪除所有日志文件，重置，生成log.000001

mysql> reset master;

1

（8）手動(dòng)生成日志文件的種方式

1 重啟Mysqld服務(wù)

2 執(zhí)行sql操作： mysql > flush logs

3 mysqldump   --flush-logs   在實(shí)行完全備份時(shí)生成新的日志文件

4 mysql -uroot -p123456 -e 'flush logs'

1

2

3

4

（9）分析binlog日志（日志以偏移量和時(shí)間來(lái)記錄對(duì)數(shù)據(jù)庫(kù)的操作）

[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語(yǔ)句

/*!*/;

# at 27120                   // 執(zhí)行sql語(yǔ)句提交后的偏移量

#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 [選項(xiàng)] binlog日志文件名

常用選項(xiàng) ：

--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庫(kù)tb1表，插入三條記錄

刪除t1表中剛插入的三條記錄

使用mysqlbinlog恢復(fù)刪除的三條記錄

1 修改配置文件，開(kāi)啟binlog，重啟mysqld服務(wù)

[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ù)庫(kù)表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 查看詳細(xì)日志記錄

[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ù)庫(kù)表的三條記錄

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日志恢復(fù)數(shù)據(jù)庫(kù)表

root@mysql50 ~]# mysqlbinlog --start-position=795 --stop-position=932 /logdir/log.000009  | mysql -uroot -p123456

7 再次查看數(shù)據(jù)庫(kù)表內(nèi)的記錄 （數(shù)據(jù)已恢復(fù)）

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工具實(shí)現(xiàn)增量熱備份

（1）常用的mysql備份工具

物理備份缺點(diǎn)

**跨平臺(tái)性差

**備份時(shí)間長(zhǎng)，冗余備份，浪費(fèi)存儲(chǔ)空間

mysqldump備份缺點(diǎn)

**效率低，備份和還原數(shù)度慢

** 備份過(guò)程中，數(shù)據(jù)插入和更新操作會(huì)被掛起

（2）XtraBackupex工具

一款強(qiáng)大的在線熱備份工具

**備份過(guò)程不鎖庫(kù)表，適合生產(chǎn)環(huán)境

**由專業(yè)組織percona提供

主要含有兩個(gè)組件

** xtrabackup：C程序，支持innodb和xtradb存儲(chǔ)引擎

** innobackupex: 以perl腳本封裝xtrabackup,還支持myisam存儲(chǔ)引擎

（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 主機(jī)名

–port 3306

–user 用戶名

–password 密碼

–databases=“庫(kù)名”

–databases=“庫(kù)1 庫(kù)2”

–databases=“庫(kù).表”

–no-timestamp 不用日期命名備份文件存儲(chǔ)的子目錄，使用備份的數(shù)據(jù)庫(kù)名做備份目錄名

–no-timestmap 不使用日期命名備份目錄名

5 做一個(gè)完整備份

默認(rèn)情況下，備份文件存儲(chǔ)的子目錄會(huì)用日期命名，

innobackupex作為客戶端工具，以mysql協(xié)議連入mysqld，將數(shù)據(jù)備份到/backup文件夾：

（1）完整備份數(shù)據(jù)庫(kù)

[root@mysql50 ~]#  innobackupex --user=root --password=123456 /b --no-timestamp

確認(rèn)備份文件

[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             //本次完全備份的結(jié)束日志序列號(hào)，也是下次增量備份開(kāi)始的日志序列號(hào)

last_lsn = 4966956

compact = 0

recover_binlog_info = 0

準(zhǔn)備恢復(fù)數(shù)據(jù)

[root@mysql50 ~]#  innobackupex  --apply-log /b

模擬數(shù)據(jù)庫(kù)丟失

[root@mysql50 ~]# systemctl stop mysqld

[root@mysql50 ~]# rm -rf /var/lib/mysql

[root@mysql50 ~]# mkdir /var/lib/mysql

恢復(fù)數(shù)據(jù)

[root@mysql50 ~]#  innobackupex  --copy-back /b

修改權(quán)限,啟動(dòng)服務(wù)，查看結(jié)果

[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ù)庫(kù)新增加數(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ù)庫(kù)丟失

[root@mysql50 ~]# systemctl stop mysqld

[root@mysql50 ~]# rm -rf /var/lib/mysql

1

2

（7） 準(zhǔn)備恢復(fù)數(shù)據(jù)

查看日志序列號(hào)

[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）修改權(quán)限，啟動(dòng)mysql服務(wù)，查看結(jié)果

 [root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql

 [root@mysql50 ~]# systemctl restart mysqld

 [root@mysql50 ~]# mysql -uroot -p123456

1

2

3

7 在完全備份中恢復(fù)單個(gè)表：

復(fù)制整個(gè)db4數(shù)據(jù)庫(kù)

[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庫(kù)中的pass表以及表里的數(shù)據(jù)記錄

mysql> drop table db1.pass;

導(dǎo)出表信息

[root@mysql50 ~]# innobackupex --apply-log --export /a

創(chuàng)建pass表（此時(shí)表中沒(méi)有數(shù)據(jù)記錄，注意創(chuàng)建時(shí)字段要和之前表的字段一致）

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

修改文件權(quán)限

[root@mysql50 ~]# chown -R mysql.mysql /var/lib/mysql/db1/pass.*

導(dǎo)入pass表空間 （數(shù)據(jù)恢復(fù)完成）

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

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