當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

mysql的数据备份及恢复

發(fā)布時(shí)間：2024/8/1 数据库 25 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql的数据备份及恢复小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

一、mysql數(shù)據(jù)庫(kù)備份的前因后果

1、備份的概念：

將數(shù)據(jù)收集并保存至另外的多個(gè)副本，其目的是將數(shù)據(jù)還原恢復(fù)至備份數(shù)據(jù)時(shí)那個(gè)狀態(tài)。

2、備份數(shù)據(jù)的原因

1）做災(zāi)難恢復(fù)使用，要將數(shù)據(jù)副本做到異地多份備份；

2）數(shù)據(jù)庫(kù)數(shù)據(jù)改動(dòng)時(shí)使用備份；

3）需要對(duì)當(dāng)前服務(wù)器做相關(guān)測(cè)試時(shí)使用備份，備份與測(cè)試都是最好在服務(wù)器訪問(wèn)量最少時(shí)進(jìn)行

3、備份的事先注意事項(xiàng)

1）可以容忍丟失多長(zhǎng)時(shí)間的數(shù)據(jù)；

2）恢復(fù)數(shù)據(jù)能在多長(zhǎng)時(shí)間內(nèi)完成；

3）是否需要持續(xù)提供服務(wù)；

4）需要恢復(fù)哪些內(nèi)容，整個(gè)服務(wù)器的數(shù)據(jù)庫(kù)，單個(gè)數(shù)據(jù)庫(kù)，一個(gè)或多個(gè)表。

二、數(shù)據(jù)的備份類型

1、根據(jù)是否需要數(shù)據(jù)庫(kù)離線可分為

1）冷備：cold backup ?備份需要關(guān)閉mysql服務(wù)或讀寫(xiě)請(qǐng)求均不允許；

2）溫備：warm backup ?備份的同時(shí)，mysql服務(wù)在線，只允許讀不允許寫(xiě)，在線交易要終止：

3）熱備：hot backup

備份的同時(shí)，mysql服務(wù)在線，支持讀寫(xiě)請(qǐng)求，業(yè)務(wù)不受影響，但服務(wù)器的性能會(huì)有所下降。

2、根據(jù)要備份的數(shù)據(jù)范圍可分為

1）完全備份：full backup

備份當(dāng)前狀態(tài)的整個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)；

2）增量備份：increment backup

備份基于上次的完全備份或增量備份以來(lái)所改變的數(shù)據(jù)；

3）差異備份：different backup

備份基于上次的完全備份改變了的數(shù)據(jù)。

3、根據(jù)備份數(shù)據(jù)是否為文件可分為

1）物理備份：直接備份數(shù)據(jù)庫(kù)文件

2）邏輯備份：備份表中的數(shù)據(jù)和庫(kù)代碼

三、備份的對(duì)象及備份工具

1、備份對(duì)象

1）數(shù)據(jù)

2）配置文件

3）代碼、存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)、觸發(fā)器等

4）OS相關(guān)的配置文件

5）復(fù)制相關(guān)的配置

6）二進(jìn)制日志

2、引擎所支持的備份

MyISAM引擎只支持溫備，而InnoDB還支持熱備。

3、備份工具

1）mysqldump，mysql客戶端的經(jīng)典備份工具 ? ，為邏輯備份工具，備份和恢復(fù)比較慢；

2）mylvmdumper，mysqldump升級(jí)版

多線程的邏輯備份工具，備份和恢復(fù)速度稍快于mysqldump；

3）lvm-snapshot,基于快照卷的備份工具 ?，接近于熱備的物理備份工具，備份和恢復(fù)的速度較快；

4）select ，mysql內(nèi)置的備份工具 ?，邏輯備份工具，速度快于mysqldump；

select into outfile；

load data infile；

5）xtrabackup，由percana提供的免費(fèi)開(kāi)源備份工具

為物理備份工具，速度快。

6）mysql hotcopy：幾乎冷備的工具，一般不采用，速度慢。

四、數(shù)據(jù)從備份到恢復(fù)的完整流程

1）停止mysql服務(wù)；

2）記錄服務(wù)和配置文件權(quán)限；

3）復(fù)制備份文件與數(shù)據(jù)目錄；

4）按需調(diào)整配置；

5）按需改變文件權(quán)限；

6）嘗試啟動(dòng)服務(wù)；

7）裝載邏輯備份；

8）檢查和重放二進(jìn)制日志；

9）確定數(shù)據(jù)還原正常完成；

10）以完全權(quán)限重啟服務(wù)器。

五、使用mysqldump進(jìn)行數(shù)據(jù)備份及恢復(fù)

1：為測(cè)試的數(shù)據(jù)庫(kù)及二進(jìn)制創(chuàng)建備份目錄；

[root@node1 ~]#?mkdir -pv {/mydata/data,/backup,/var/binlog}

mkdir: created directory `/mydata/data'

mkdir: created directory `/backup'

mkdir: created directory `/var/binlog'

2：啟動(dòng)mysqld服務(wù)，創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)，并創(chuàng)建測(cè)試數(shù)據(jù)

[root@node1 ~]# service mysqld restart

Shutting down MySQL.... ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?[ ?OK ?]

Starting MySQL.. ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? [ ?OK ?]

[root@node1 ~]#mysql

mysql> create database students;

Query OK, 1 row affected (0.00 sec)

mysql> use students

Database changed

mysql> create table TLtb (Id tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M',Courses char(30) not null);

Query OK, 0 rows affected (0.28 sec)

mysql> insert into TLtb (Name,Age,Gender,Courses) values ('Xu zu',20,'M','Xiao Wuxianggong'),('Qiao Feng',28,'M','Xianglong Shibazhang'),('Duan Fu',23,'M','Liumai Shenjian');

Query OK, 3 rows affected (0.01 sec)

Records: 3 ?Duplicates: 0 ?Warnings: 0

mysql> select * from TLtb;

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

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

| ?1 | Xu zu ? ? | ? 20 | M ? ? ?| Xiao Wuxianggong ? ? |

| ?2 | Qiao Feng | ? 28 | M ? ? ?| Xianglong Shibazhang |

| ?3 | Duan Fu ? | ? 23 | M ? ? ?| Liumai Shenjian ? ? ?|

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

3 rows in set (0.01 sec)

3：使用mysqldump對(duì)測(cè)試數(shù)據(jù)庫(kù)進(jìn)行備份，并且滾動(dòng)二進(jìn)制日志，記錄日志位置；

[root@node1 ~]#?mysqldump --lock-all-tables ?--flush-logs --master-data=2 --databases students > /backup/students_`date +%F`.sq

[root@node1 ~]# cp /var/binlog/mysql-bin.

mysql-bin.000001 ?mysql-bin.000002 ?mysql-bin.000003 ?mysql-bin.000004 ?mysql-bin.000005 ?mysql-bin.000006 ?mysql-bin.index

4：備份二進(jìn)制日志文件；

[root@node1 ~]# cp /var/binlog/mysql-bin.00000* /backup/

[root@node1 ~]# ls /backup/

mysql-bin.000001 ?mysql-bin.000003 ?mysql-bin.000005 ?students_2013-09-30.sql

mysql-bin.000002 ?mysql-bin.000004 ?mysql-bin.000006 ?students_.sql

5：新增數(shù)據(jù)庫(kù)數(shù)據(jù)進(jìn)行增量備份，查看當(dāng)前日志位置；

mysql> create table CDtb (Id tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M',Courses char(30) not null);

Query OK, 0 rows affected (0.14 sec)

mysql> insert into CDtb (Name,Age,Gender,Courses) values ('Yideng Dashi',80,'M','Yiyangzhi'),('Hong Qigong',66,'M','Dagou Bangfa'),('Huang Yaoshi',60,'M','Tanzhi Shengong');

Query OK, 3 rows affected (0.05 sec)

Records: 3 ?Duplicates: 0 ?Warnings: 0

mysql> select * from CDtb;

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

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

| ?1 | Yideng Dashi | ? 80 | M ? ? ?| Yiyangzhi ? ? ? |

| ?2 | Hong Qigong ?| ? 66 | M ? ? ?| Dagou Bangfa ? ?|

| ?3 | Huang Yaoshi | ? 60 | M ? ? ?| Tanzhi Shengong |

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

3 rows in set (0.01 sec)

mysql>?show master status;

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

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

| mysql-bin.000006 | ? ? ?716 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?|

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

1 row in set (0.00 sec)

[root@node1 ~]# less /backup/students_2013-09-30.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=107;

[root@node1 ~]#?mysqlbinlog --start-position=107 /var/binlog/mysql-bin.000006 > /backup/students_incremental.sql

6：模擬數(shù)據(jù)庫(kù)數(shù)據(jù)損壞；

mysql> insert into CDtb (Name,Age,Gender,Courses) values ('Ou Yangfeng',75,'M','Hamagong');

Query OK, 1 row affected (0.05 sec)

mysql> drop database students;

Query OK, 2 rows affected, 2 warnings (0.13 sec)

mysql>?show master status;

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

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

| mysql-bin.000006 | ? ? 1082 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?|

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

1 row in set (0.00 sec)

7：恢復(fù)備份數(shù)據(jù)：完全備份+增量備份+二進(jìn)制日志文件；

bac

[root@node1 ~]#?mysqlbinlog ?/var/binlog/mysql-bin.000006

......

# at 993

#130930 22:51:40 server id 1 ?end_log_pos 1082 ?Query ? thread_id=2 exec_time=0 error_code=1146

SET TIMESTAMP=1380552700/*!*/;

drop database students

[root@node1 ~]#?mysqlbinlog --start-position=716 --stop-position=993 /var/binlog/mysql-bin.000006 > /backup/students_993.sql

[root@node1 ~]#mysqlbinlog /var/binlog/mysql-bin.000006

......

# at 993

#130930 22:51:40 server id 1 ?end_log_pos 1082 ?Query ? thread_id=2 exec_time=0 error_code=1146

SET TIMESTAMP=1380552700/*!*/;

drop database students

[root@node1 ~]#?mysqlbinlog --start-position=716 --stop-position=993 /var/binlog/mysql-bin.000006 > /backup/students_993.sql

mysql> set global sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> source /backup/students_2013-09-30.sql

mysql> source /backup/students_incremental.sql

mysql>?source /backup/students_993.sql

8：檢測(cè)備份的數(shù)據(jù)是否已經(jīng)正常恢復(fù)。

mysql> set global sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

mysql> use students;

Database changed

mysql> show tables;

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

| Tables_in_students |

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

| CDtb ? ? ? ? ? ? ? |

| TLtb ? ? ? ? ? ? ? |

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

2 rows in set (0.00 sec)

mysql> select * from TLtb;

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

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

| ?1 | Xu zu ? ? | ? 20 | M ? ? ?| Xiao Wuxianggong ? ? |

| ?2 | Qiao Feng | ? 28 | M ? ? ?| Xianglong Shibazhang |

| ?3 | Duan Fu ? | ? 23 | M ? ? ?| Liumai Shenjian ? ? ?|

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

3 rows in set (0.00 sec)

mysql> select * from CDtb;

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

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

| ?1 | Yideng Dashi | ? 80 | M ? ? ?| Yiyangzhi ? ? ? |

| ?2 | Hong Qigong ?| ? 66 | M ? ? ?| Dagou Bangfa ? ?|

| ?3 | Huang Yaoshi | ? 60 | M ? ? ?| Tanzhi Shengong |

| ?4 | Ou Yangfeng ?| ? 75 | M ? ? ?| Hamagong ? ? ? ?|

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

4 rows in set (0.00 sec)

六、使用lvm-snapshot進(jìn)行數(shù)據(jù)備份及恢復(fù)

1、創(chuàng)建LVM邏輯卷并開(kāi)機(jī)自動(dòng)掛載，并創(chuàng)建mysql數(shù)據(jù)與二進(jìn)制日志存放目錄；

[root@localhost ~]#?pvcreate /dev/sdb{1,2}

? Physical volume "/dev/sdb1" successfully created

? Physical volume "/dev/sdb2" successfully created

[root@localhost ~]#?vgcreate vg1 /dev/sdb{1,2}

? Volume group "vg1" successfully created

[root@localhost ~]#?lvcreate -L +10G -n lv1 vg1

? Logical volume "lv1" created

[root@localhost ~]#?mke2fs -t ext4 /dev/vg1/lv1

[root@localhost ~]#?vim /etc/fstab

/dev/vg1/lv1 ? ? ? ? ? ?/Mydata ? ? ? ? ? ? ? ? ext4 ? ?defaults ? ? ? ?0 0

[root@localhost ~]# mount -a

[root@localhost ~]#?mkdir /Mydata/{data,,binlog} ? ?/backup1

[root@localhost ~]# chown -R mysql:mysql /Mydata/*

2、新建mysql數(shù)據(jù)庫(kù)并新增數(shù)據(jù)；

mysql> create database schooldb;

Query OK, 1 row affected (0.01 sec)

mysql> use schooldb

Database changed

mysql> create table studentstb (Id tinyint unsigned not null primary key auto_increment,Name char(20) not null unique key,Age tinyint unsigned,Gender char(1) default 'M',Courses char(30) not null);

Query OK, 0 rows affected (0.38 sec)

mysql> insert into studentstb (Name,Age,Gender,Courses) values ('Zhang San',19,'M','Shujujiegou'),('Li Ling',18,'F','Daxueyingyu'),('Wang Wu',20,'M','Dianluyuanli');

Query OK, 3 rows affected (0.09 sec)

Records: 3 ?Duplicates: 0 ?Warnings: 0

mysql> select * from studentstb;

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

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

| ?1 | Zhang San | ? 19 | M ? ? ?| Shujujiegou ?|

| ?2 | Li Ling ? | ? 18 | F ? ? ?| Daxueyingyu ?|

| ?3 | Wang Wu ? | ? 20 | M ? ? ?| Dianluyuanli |

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

3 rows in set (0.03 sec)

3、登錄mysql，對(duì)所有表加鎖，并滾動(dòng)日志，查看當(dāng)前日志所在位置；

mysql>?flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql>?flush logs;

Query OK, 0 rows affected (0.04 sec)

mysql>?show master status;

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

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

| mysql-bin.000015 | ? ? ?107 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?|

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

1 row in set (0.01 sec)

4、另外啟動(dòng)一個(gè)終端，為邏輯卷創(chuàng)建快照卷，做完快照釋放施加的鎖請(qǐng)求；

[root@localhost ~]#?lvcreate -L 1G -s -p r -n lv1-snap /dev/vg1/lv1

? Logical volume "lv1-snap" created

mysql>?unlock tables;

Query OK, 0 rows affected (0.00 sec)

5、掛載快照卷，并備份數(shù)據(jù)，備份完成卸載快照卷并刪除；

[root@localhost ~]# cp -rp /mnt/* /backup1

[root@localhost ~]# umount /mnt/

[root@localhost ~]#?lvremove /dev/vg1/lv1-snap

Do you really want to remove active logical volume lv1-snap? [y/n]: y

? Logical volume "lv1-snap" successfully removed

6、停止mysql服務(wù)，模擬數(shù)據(jù)庫(kù)數(shù)據(jù)損壞，進(jìn)行數(shù)據(jù)恢復(fù)；

[root@localhost ~]# service mysqld stop

Shutting down MySQL... ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? [ ?OK ?]

[root@localhost ~]# rm -rf /Mydata/*

[root@localhost ~]# cp -rp /backup1/* /Mydata/

[root@localhost ~]# service mysqld start

Starting MySQL.. ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? [ ?OK ?]

7、檢測(cè)數(shù)據(jù)恢復(fù)是否正常恢復(fù)完成。

mysql> show databases;

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

| Database ? ? ? ? ? |

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

| information_schema |

| hellodb ? ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| performance_schema |

| schooldb ? ? ? ? ? |

| test ? ? ? ? ? ? ? |

| xiaozheng ? ? ? ? ?|

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

7 rows in set (0.01 sec)

mysql> use schooldb

Database changed

mysql> show tables;

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

| Tables_in_schooldb |

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

| studentstb ? ? ? ? |

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

1 row in set (0.00 sec)

mysql> select * from studentstb;

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

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

| ?1 | Zhang San | ? 19 | M ? ? ?| Shujujiegou ?|

| ?2 | Li Ling ? | ? 18 | F ? ? ?| Daxueyingyu ?|

| ?3 | Wang Wu ? | ? 20 | M ? ? ?| Dianluyuanli |

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

3 rows in set (0.00 sec)

七、使用xtrabackup進(jìn)行數(shù)據(jù)備份恢復(fù)

1、下載并安裝xtrabackup；

[root@localhost ~]# ls

anaconda-ks.cfg ?install.log ? ? ? ? percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

hellodb.sql ? ? ?install.log.syslog

[root@localhost ~]#?yum -y install percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

2、創(chuàng)建有備份權(quán)限的數(shù)據(jù)庫(kù)用戶；

mysql> create user 'xtrabackup'@'localhost' identified by 'mypass';

Query OK, 0 rows affected (0.09 sec)

mysql> revoke all privileges,grant option from 'xtrabackup'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> grant reload,lock tables,replication client,event on *.* to 'xtrabackup'@'localhost';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3、對(duì)數(shù)據(jù)庫(kù)進(jìn)行完全備份；

[root@localhost ~]#?innobackupex --user=xtrabackup --password=mypass /backup2

........

innobackupex: Backup created in directory '/backup2/2013-09-08_21-06-19'

innobackupex: MySQL binlog position: filename 'mysql-bin.000017', position 598

130908 21:06:24 ?innobackupex: Connection to database server closed

130908 21:06:24 ?innobackupex: completed OK!

4、關(guān)閉mysql服務(wù)并模擬數(shù)據(jù)損壞,并準(zhǔn)備一個(gè)完全備份（prepare）；

[root@localhost ~]# service mysqld stop

Shutting down MySQL... ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? [ ?OK ?]

[root@localhost ~]# rm -rf /mydata/data/*

[root@localhost backup2]# innobackupex --apply-log /backup2/2013-09-08_21-06-19/

........

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

130908 21:13:44 ?InnoDB: Starting shutdown...

130908 21:13:48 ?InnoDB: Shutdown completed; log sequence number 1626636

130908 21:13:48 ?innobackupex: completed OK!

5、從一個(gè)完全備份中恢復(fù)數(shù)據(jù),并檢測(cè)數(shù)據(jù)庫(kù)數(shù)據(jù)是否正?；謴?fù)完成。

[root@localhost ~]#?innobackupex --copy-back /backup2/2013-09-08_21-06-19/

............

innobackupex: Copying '/backup2/2013-09-08_21-06-19/ib_logfile1' to '/mydata/data'

innobackupex: Finished copying back files.

130908 21:21:23 ?innobackupex: completed OK!

[root@localhost ~]#service mysqld start

[root@localhost ~]# innobackupex --copy-back /backup2/2013-09-08_21-06-19/

............

innobackupex: Copying '/backup2/2013-09-08_21-06-19/ib_logfile1' to '/mydata/data'

innobackupex: Finished copying back files.

130908 21:21:23 ?innobackupex: completed OK!

[root@localhost ~]# service mysqld start

Starting MySQL.. ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? [ ?OK ?]

mysql> use schooldb

Database changed

mysql> show tables;

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

| Tables_in_schooldb |

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

| studentstb ? ? ? ? |

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

1 row in set (0.00 sec)

mysql> select * from studentstb;

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

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

| ?1 | Zhang San | ? 19 | M ? ? ?| Shujujiegou ?|

| ?2 | Li Ling ? | ? 18 | F ? ? ?| Daxueyingyu ?|

| ?3 | Wang Wu ? | ? 20 | M ? ? ?| Dianluyuanli |

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

3 rows in set (0.00 sec)

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

6、在測(cè)試數(shù)據(jù)庫(kù)中新增數(shù)據(jù)，實(shí)現(xiàn)數(shù)據(jù)的增量備份；

7、進(jìn)行第一次增量備份；

8、繼續(xù)于測(cè)試數(shù)據(jù)庫(kù)中新增數(shù)據(jù)，進(jìn)行第二次增量備份；

9、關(guān)閉mysql服務(wù)并模擬數(shù)據(jù)庫(kù)數(shù)據(jù)損壞，進(jìn)行兩次增量備份的恢復(fù)；

10、檢測(cè)增量備份的數(shù)據(jù)恢復(fù)是否正常完成。

mysql 熱備

在運(yùn)維中需定期備份mysql，為崩潰后的恢復(fù)數(shù)據(jù)做準(zhǔn)備。一般分為冷備和熱備，

冷備就是停掉mysql服務(wù)，直接cp文件，但是在生產(chǎn)環(huán)境中，很少有機(jī)會(huì)這樣，一般都是在mysql提供服務(wù)的時(shí)候就進(jìn)行備份，因此這牽扯到數(shù)據(jù)一致性的問(wèn)題。

所以，在MyISAM存儲(chǔ)引擎下，我們的思路就是：鎖表 –>備份–>解鎖

# 進(jìn)入mysql環(huán)境鎖表

root@mysql : test >?flush tables with read lock;# 在系統(tǒng)環(huán)境下備份

cp -R test /tmp/backup/test_back

# 進(jìn)入mysql環(huán)境解鎖

root@mysql : test >?unlock tables;

mysqlhotcopy 熱備工具

當(dāng)然也可通過(guò)mysql自帶的工具 mysqlhotcopy 解決，事實(shí)上mysqlhotcopy是個(gè)perl程序，

提供MyISAM下的鎖表備份解鎖操做。因?yàn)槭莗erl腳本，所以需要機(jī)器上有PERL-DBD 模塊，

不然會(huì)報(bào)錯(cuò): Can’t locate DBI.pm in @INC ,安裝過(guò)程很簡(jiǎn)單，在這里: perl-DBI連接mysql

mysqlhotcopy也很簡(jiǎn)單，參數(shù)可用 –help 查看

# 備份test庫(kù)為新的test_tmp庫(kù)，與test同級(jí)目錄?

mysqlhotcopy ?--user=root ?--password=root ? test ? test_tmp

# 備份test庫(kù) 到 /tmp/ 目錄下?

mysqlhotcopy ?--user=root ?--password=root ? test ? /tmp/

# --checkpoint dbinfo.checkpoint 這個(gè)是指定存放操作記錄的數(shù)據(jù)庫(kù)/表# --addtodest 增量備份，新的備份自動(dòng)覆蓋掉原來(lái)的

mysqlhotcopy ?--user=root ?--password=root ?--checkpoint=dbinfo.checkpoint ?--addtodest ?test ?/tmp/

mysqlhotcopy的安裝方法如下：

yum -y install perl perl-DBI

wget?http://down1.chinaunix.net/distfiles/DBD-mysql-3.0002.tar.gz

tar zxvf ?DBD-mysql-3.0002.tar.gz

cd DBD-mysql-3.0002

perl Makefile.PL ?–mysql_config=/usr/local/mysql/bin/mysql_config

make

make test

make install

記錄到到數(shù)據(jù)庫(kù)的方法：

1、在數(shù)據(jù)庫(kù)段分配一個(gè)專門(mén)用于備份的用戶

mysql> grant select,reload,lock tables on *.* to 'hotcopyer'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

2、在/etc/my.cnf或者登陸用戶的個(gè)人主文件.my.cnf里面添加

[mysqlhotcopy]

interactive-timeout

user=hotcopyer

password=123456

port=3306

重新加載mysql

3、可以把記錄寫(xiě)到專門(mén)的表中。具體察看幫助。

mysql> create database hotcopy;

Query OK, 1 row affected (0.03 sec)

mysql> use hotcopy

Database changed

mysql> create table checkpoint(time_stamp timestamp not null,src varchar(32),dest varchar(60), msg varchar(255));

Query OK, 0 rows affected (0.01 sec)

同時(shí)記得給hotcopyer用戶權(quán)限。

mysql> grant insert on hotcopy.checkpoint to hotcopyer@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

4、備份數(shù)據(jù)（寫(xiě)入數(shù)據(jù)庫(kù){備份的結(jié)果}，增量備份）

/usr/local/mysql/bin/mysqlhotcopy --user=root --password=mysql --checkpoint=hotcopy.checkpoint --addtodest radius /bak

5、查看

mysql> show databases;

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

| Database ? ? ? ? ? |

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

| information_schema |

| 1234la ? ? ? ? ? ? |

| conntrack ? ? ? ? ?|

| hotcopy ? ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| pinphp ? ? ? ? ? ? |

| radius ? ? ? ? ? ? |

| test ? ? ? ? ? ? ? |

| vod ? ? ? ? ? ? ? ?|

| web ? ? ? ? ? ? ? ?|

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

10 rows in set (0.00 sec)

mysql> show tables;

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

| Tables_in_hotcopy |

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

| checkpoint ? ? ? ?|

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

1 row in set (0.00 sec)

mysql> select * from checkpoint;

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

| time_stamp ? ? ? ? ?| src ? ?| dest ? ? ? ? ?| msg ? ? ? |

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

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

1 row in set (0.00 sec)

mysql>

一、mysqldump備份結(jié)合binlog日志恢復(fù)

MySQL備份一般采取全庫(kù)備份加日志備份的方式，例如每天執(zhí)行一次全備份，每小時(shí)執(zhí)行一次二進(jìn)制日志備份。這樣在MySQL故障后可以使用全備份和日志備份將數(shù)據(jù)恢復(fù)到最后一個(gè)二進(jìn)制日志備份前的任意位置或時(shí)間

binlog日志恢復(fù)介紹

·首先要開(kāi)啟binary log功能

通過(guò)編輯my.cnf中的log-bin選項(xiàng)可以開(kāi)啟二進(jìn)制日志；形式如下：

log-bin [=DIR/[filename]] ?（配置文件中只寫(xiě)log_bin不寫(xiě)后面的文件名和路徑時(shí)，默認(rèn)存放在/usr/local/mysql/data目錄下，文件名為主機(jī)名-bin.000001…命名）

·下面用一個(gè)例子來(lái)說(shuō)明binlog日志恢復(fù)數(shù)據(jù)的方法

查看二進(jìn)制日志中的事件，默認(rèn)顯示可找到的第一個(gè)二進(jìn)制日志文件中的事件，包含了日志文件名、事件的開(kāi)始位置、事件類型、結(jié)束位置、信息等內(nèi)容。

語(yǔ)法格式：SHOW BINLOG EVENTS?[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

選項(xiàng)解析：

IN 'log_name' ? ? ?指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)

FROM pos ? ? ? ? ? ? ? 指定從哪個(gè)pos起始點(diǎn)開(kāi)始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開(kāi)始算)

offset ? ? ? ? ? 偏移量(不指定就是0),例如為3就會(huì)從第3行開(kāi)始

row_count ? ? ? ? ?查詢總條數(shù)(不指定就是所有行)

顯示信息中的參數(shù)介紹：

Format_desc ?此事件為格式描述事件

Query ?為查詢事件

BEGIN ?為事務(wù)開(kāi)始

Table_map ?為表映射事件

Write_rows ? ?為我們執(zhí)行的insert事件

XidXid ? ?時(shí)間是自動(dòng)提交事務(wù)的動(dòng)作

Rotate ? ?為日志輪換事件，是我們執(zhí)行flush logs開(kāi)啟新日志文件引起的。

COMMIT ? ?為事務(wù)的提交

為了便于查看記錄了行變化信息的事件在當(dāng)時(shí)具體執(zhí)行了什么樣的SQL語(yǔ)句可以使用mysqlbinlog工具的-v（--verbose）選項(xiàng)，該選項(xiàng)會(huì)將行事件重構(gòu)成被注釋掉的偽SQL語(yǔ)句，

如果想看到更詳細(xì)的信息可以將該選項(xiàng)給兩次如-vv，這樣可以包含一些數(shù)據(jù)類型和元信息的注釋內(nèi)容。

執(zhí)行以下命令，我們可以看到上圖中顯示的信息：

mysqlbinlog ?-v ?/usr/local/mysql/data/mysql-bin.000002

另外mysqlbinlog和可以通過(guò)--read-from-remote-server選項(xiàng)從遠(yuǎn)程服務(wù)器讀取二進(jìn)制日志文件，

這時(shí)需要一些而外的連接參數(shù)，如-h，-P，-p，-u等，這些參數(shù)僅在指定了--read-from-remote-server后有效。

從上圖中可以看出delete事件發(fā)生position是291，事件結(jié)束position是420

恢復(fù)流程：直接用bin-log日志將數(shù)據(jù)庫(kù)恢復(fù)到刪除位置291前,然后跳過(guò)故障點(diǎn),再進(jìn)行恢復(fù)。下面所有的操作,命令如下：

常見(jiàn)的選項(xiàng)有以下幾個(gè)：

--start-datetime

從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之后的日志事件。

--stop-datetime

從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之前的日志事件。

--start-position ? ? ? ?

從二進(jìn)制日志中讀取指定position 事件位置作為開(kāi)始。

--stop-position

從二進(jìn)制日志中讀取指定position 事件位置作為事件截至。

刪除test數(shù)據(jù)庫(kù)，利用binlog恢復(fù)數(shù)據(jù)，完成后發(fā)現(xiàn)數(shù)據(jù)都恢復(fù)過(guò)來(lái)了。

注意：

（1）在實(shí)際生產(chǎn)環(huán)境中，如果遇到需要恢復(fù)數(shù)據(jù)庫(kù)的情況，不要讓用戶能訪問(wèn)到數(shù)據(jù)庫(kù)，以避免新的數(shù)據(jù)插入進(jìn)來(lái)，以及在主從的環(huán)境下，關(guān)閉主從。

（2）以上方法中僅僅利用了binlog來(lái)恢復(fù)數(shù)據(jù)，并沒(méi)有使用到mysqldump全庫(kù)備份來(lái)恢復(fù)數(shù)據(jù)庫(kù)，所以在恢復(fù)數(shù)據(jù)前要?jiǎng)h除數(shù)據(jù)庫(kù)。

2.利用腳本實(shí)現(xiàn)mysql的備份與恢復(fù)

（1）mysqldump介紹

mysqldump是mysql用于備份和數(shù)據(jù)轉(zhuǎn)移的一個(gè)工具。它主要產(chǎn)生一系列的SQL語(yǔ)句，可以封裝到文件，該文件包含有所有重建你的數(shù)據(jù)庫(kù)所需要的?SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等?？梢杂脕?lái)實(shí)現(xiàn)輕量級(jí)的快速遷移或恢復(fù)數(shù)據(jù)庫(kù)。

mysqldump?是將數(shù)據(jù)表導(dǎo)成?SQL?腳本文件，在不同的?MySQL?版本之間升級(jí)時(shí)相對(duì)比較合適，這也是最常用的備份方法。

mysqldump一般在數(shù)據(jù)量很小的時(shí)候（幾個(gè)G）可以用于備份。當(dāng)數(shù)據(jù)量比較大的情況下，就不建議用mysqldump工具進(jìn)行備份了。

·數(shù)據(jù)庫(kù)的導(dǎo)出

導(dǎo)出對(duì)象說(shuō)明：

mysqldump可以針對(duì)單個(gè)表、多個(gè)表、單個(gè)數(shù)據(jù)庫(kù)、多個(gè)數(shù)據(jù)庫(kù)、所有數(shù)據(jù)庫(kù)進(jìn)行導(dǎo)出的操作

#?mysqldump [options] db_name [tbl_name ...]???//導(dǎo)出指定數(shù)據(jù)庫(kù)或單個(gè)表

#?mysqldump [options] --databases db_name ...???//導(dǎo)出多個(gè)數(shù)據(jù)庫(kù)

#mysqldump [options] --all-databases???????????//導(dǎo)出所有

·數(shù)據(jù)庫(kù)的導(dǎo)入

mysql ?-uroot ?-p ?[options] ?[db_name] ?[tbl_name] ?< ?/路徑/備份文件

生產(chǎn)環(huán)境中Mysql數(shù)據(jù)庫(kù)的備份是周期性重復(fù)的操作，所以通常是要編寫(xiě)腳本實(shí)現(xiàn)，通過(guò)crond計(jì)劃任務(wù)周期性執(zhí)行備份腳本。

本文轉(zhuǎn)自 chengxuyonghu 51CTO博客，原文鏈接：http://blog.51cto.com/6226001001/1911031，如需轉(zhuǎn)載請(qǐng)自行聯(lián)系原作者

總結(jié)

以上是生活随笔為你收集整理的mysql的数据备份及恢复的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： java 分布式序列号_分布式序列号生成
下一篇：单片机外文参考文献期刊_单片机-英文参考