mysql备份恢复详解_MySQL(四)：备份恢复详解

最新推荐文章于 2024-08-27 17:19:49 发布

fanvie

最新推荐文章于 2024-08-27 17:19:49 发布

阅读量181

点赞数

文章标签： mysql备份恢复详解

本文链接：https://blog.csdn.net/weixin_35924003/article/details/113284236

版权

一、备份的类型

随着互联网的迅速发展；数据的重要性不需要多说；相信大家都知道。所以数据备份就显得格外重要了；并且选择一个合适的备份方法能提高性能。下面就备份类型来分类：根据备份时；数据库服务器是否在线：

冷备：cold backup

温备：warm backup

热备：hot backup 基于事务的存储引擎(InnoDB)

根据备份时的数据集：

完全备份：full backup

部分备份：partial backup

根据备份时的接口(直接备份数据文件还是通过mysql服务器导出数据)：

物理备份(physical backup)：直接复制(归档)数据文件的备份方式；

1、不需要额外工具

2、跨平台性能差

3、表空间占据大

逻辑备份(logical backup)：把数据从库中提取出来保存为文本文件；

1、可以使用编辑器处理数据文件

2、可以使用批处理或网络恢复数据；可以更换数据引擎

3、有助于避免数据损坏等

缺陷：恢复慢；无法保证浮点数的精度；还原数据后需要重建索引,消耗时间和资源

根据备份时数据变化(整个数据|变化的数据)：

1、完全备份：full backup

2、增量备份：incremental backup

3、差异备份：differential backup

二、基本的备份工具

1、mysqldump：逻辑备份工具

对InnoDB热备、MyISAM温备、Aria温备

备份和恢复过程较慢

2、mysqldumper：多线程的mysqldump

很难实现差异或增量备份；

3、基于lvm-snapshot：

接近于热备的工具；因为要先请求全局锁；而后创建快照；并在创建完成后释放全局锁

cp、tar等工具进行物理备份：仅限于冷备

备份和恢复速度较快；

很难实现增量备份；并且请求全局需要等待一段时间；在繁忙的服务器上尤其如此。

4、部分备份工具；不会备份关系定义；仅备份表中的数据；逻辑备份工具

select clause into outfile '/path/to/somefile'

load data infile '/path/to/somefile'

5、Xtrabackup：由percona提供的开源备份工具

InnoDB热备；增量备份

MyISAM温备；不支持增量

物理备份；速度快

三、基于mydump进行备份#使用格式

[root@localhost ~]# mysqldump

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS] --all-databases [OPTIONS]

For more options, use mysqldump --help

#详细可以--help查看；man也可以查看详细说明备份单个库#备份单个库

mysql> show databases;

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

| Database |

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

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| test |

| ultrax |

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

6 rows in set (0.00 sec)

#假设备份上述中hellodb数据库

[root@localhost ~]# mysqldump -uroot -p --databases hellodb --lock-all-tables > /tmp/hdb.sql

Enter password:

[root@localhost ~]#

#删除、恢复

mysql> drop database hellodb;

Query OK, 7 rows affected (0.20 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| ultrax |

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

5 rows in set (0.00 sec)

#恢复

mysql> source /tmp/hdb.sql

Query OK, 0 rows affected (0.00 sec)

...

mysql> show databases;

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

| Database |

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

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| test |

| ultrax |

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

6 rows in set (0.00 sec)

#测试恢复成功完全备份以及利用二进制日志进行时间点恢复[root@localhost ~]# mysqldump -uroot -p --lock-all-tables --all-databases --flush-logs --master-data=2 --events >/tmp/all.sql

Enter password:

#--lock-all-tables 请求锁；不让其他用户此刻做修改

#--all-databases 备份所有数据库

#--flush-logs 滚动日志

#--master-data=2 记录二进制日志的位置

#--events 导出事件

[root@localhost ~]# vim /tmp/all.sql

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

#这行记录的就是二进制日志的位置；后续需要依赖进行时间点恢复

#进行测试删除和恢复

mysql> create database soul;

Query OK, 1 row affected (0.00 sec)

mysql> use soul;

Database changed

mysql> create table t1 (ID int);

Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values (1),(2),(3);

Query OK, 3 rows affected (0.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> drop database soul;

Query OK, 1 row affected (0.05 sec)

mysql> drop database hellodb;

Query OK, 7 rows affected (0.00 sec)

#利用备份恢复

mysql> set session sql_log_bin=0;

#关闭记录二进制日志；因为恢复过程不需要记录

mysql> source /tmp/all.sql

mysql> show databases;

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

| Database |

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

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| test |

| ultrax |

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

6 rows in set (0.00 sec)

#时间点之后的还没有

[root@localhost ~]# mysqlbinlog --start-position=107 /data/mydata/mysql-bin.000007

# at 547 #位置

#140324 1:04:00 server id 1 end_log_pos 628 Query thread_id=33 exec_time=0 error_code=0

SET TIMESTAMP=1395594240/*!*/;

drop database soul #删除的库

/*!*/;

# at 628 #位置

#140324 1:04:20 server id 1 end_log_pos 715 Query thread_id=33 exec_time=0 error_code=0

SET TIMESTAMP=1395594260/*!*/;

drop database hellodb #删除的库

/*!*/;

#能查看完全备份之后的操作；也能看到删除的操作；所以在此截取后来恢复

[root@localhost ~]# mysqlbinlog --start-position=107 --stop-position=547 /data/mydata/mysql-bin.000007 >/tmp/bin.sql

mysql> source /tmp/bin.sql

...

mysql> use soul;

Database changed

mysql> show tables

-> ;

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

| Tables_in_soul |

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

| t1 |

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

1 row in set (0.00 sec)

mysql> select * from t1;

+------+

| ID |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.15 sec)

mysql> set session sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

#在恢复的过程中；应该限制其他用户的任何操作；可以通过iptables来关闭端口；或其他策略

#备份除了数据文件还有二进制文件、配置文件等

四、基于LVM快照的备份#基于LVM备份前提数据目录需为逻辑卷

[root@localhost ~]# mount

/dev/mapper/vg_sql-lv_sql on /mydata/data type ext4 (rw)

mysql> flush tables with read lock; #请求全局锁；完成后不要退出；后续操作可以在另一个pts执行

Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.02 sec)

mysql> show master status;

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

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

| mysql-bin.000002 | 107 | | |

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

1 row in set (0.00 sec)

mysql>

#创建备份目录和快照卷

[root@localhost ~]# mkdir /backup/

[root@localhost ~]# mysql -e 'show master status' > /backup/binlog.pos

[root@localhost ~]# lvcreate -L 100M -s -n mydata-snap -p r /dev/vg_sql/lv_sql

Logical volume "mydata-snap" created

[root@localhost ~]#

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

#释放锁

#备份

[root@localhost ~]# mount /dev/vg_sql/mydata-snap /mnt/ -o ro

[root@localhost ~]# cp /mnt/ /backup/data-04-16 -a 备份完成

[root@localhost ~]# umount /mnt/

[root@localhost ~]# lvremove /dev/vg_sql/mydata-snap

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

Logical volume "mydata-snap" successfully removed

[root@localhost ~]# 移除快照卷

#演示数据库损坏恢复

#备份后下面又生成的数据

mysql> create database soul;

Query OK, 1 row affected (0.00 sec)

mysql> use soul;

Database changed

mysql> create table t1 (id int);

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1),(2),(3);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

[root@localhost ~]# service mysqld stop

Shutting down MySQL. [ OK ]

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

[root@localhost ~]# 数据库文件全部丢失

#恢复

[root@localhost ~]# cp /backup/data-04-16/* /mydata/data/ -a

[root@localhost ~]# ls /mydata/data/ -l

total 28696

drwx------ 2 mysql mysql 4096 Apr 16 17:15 hellodb

-rw-rw---- 1 mysql mysql 18874368 Apr 16 17:15 ibdata1

...

[root@localhost ~]# cat /backup/binlog.pos

File Position Binlog_Do_DB Binlog_Ignore_DB

master-bin.000002 107

[root@localhost ~]# service mysqld start

Starting MySQL [ OK ]

[root@localhost ~]# mysqlbinlog --start-position=107 /mydata/logs/master-bin.000002 | mysql

[root@localhost ~]#

mysql> use soul;

Database changed

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

mysql> #测试数据恢复成功

#恢复成功后建议在进行一次完全备份

五、基于Xtrabackup工具备份安装软件http://www.percona.com/[root@localhost ~]# ls

anaconda-ks.cfg mysql-5.5.33-linux2.6-x86_64.tar.gz xcache-3.0.1

percona-toolkit-2.2.4-1.noarch.rpm xcache-3.0.1.tar.bz2

install.log percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm

install.log.syslog set.sh

#安装两个percona工具；可以至percona官方网站下载

[root@localhost ~]# yum -y install *.rpm

#yum安装可以解决依赖关系备份选项[root@localhost ~]# innobackupex --help

Options:

--apply-log

Prepare a backup in BACKUP-DIR by applying the transaction log file

named "xtrabackup_logfile" located in the same directory. Also,

create new transaction logs. The InnoDB configuration is read from

the file "backup-my.cnf".

#可以使用该方法查看备份[root@localhost ~]# innobackupex -user=root /backup/

140416 17:53:26 innobackupex: Connection to database server closed

140416 17:53:26 innobackupex: completed OK!

#显示该信息说明成功；

[root@localhost ~]# ls /backup/2014-04-16_17-53-23/

backup-my.cnf mysql test xtrabackup_checkpoints

hellodb performance_schema xtrabackup_binary xtrabackup_logfile

ibdata1 soul xtrabackup_binlog_info

#backup-my.cnf mysqld上的一些配置信息

#xtrabackup_binary 与mysql版本相关的信息；可以使用cat查看

#xtrabackup_binlog_info 二进制日志时间点

#xtrabackup_checkpoints 备份类型状态等信息

#完全备份以完成

[root@localhost ~]# service mysqld stop

Shutting down MySQL. [ OK ]

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

#还原需要先准备

[root@localhost ~]# innobackupex --apply-log /backup/2014-04-16_17-53-23/

140416 18:05:59 InnoDB: Shutdown completed; log sequence number 1600012

140416 18:05:59 innobackupex: completed OK!

#恢复

[root@localhost ~]# innobackupex --copy-back /backup/2014-04-16_17-53-23/

140416 18:07:26 innobackupex: completed OK!

[root@localhost ~]# ls /mydata/data/ -l

total 28696

drwxr-xr-x 2 root root 4096 Apr 16 18:07 hellodb

-rw-r--r-- 1 root root 18874368 Apr 16 18:07 ibdata1

...

xtrabackup_binlog_pos_innodb

[root@localhost ~]# chown -R mysql.mysql /mydata/data/

[root@localhost ~]# service mysqld start

Starting MySQL... [ OK ]

mysql> show databases;

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

| Database |

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

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| soul |

| test |

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

#测试恢复成功；增量备份mysql> use soul;

Database changed

mysql> select * from t1

-> ;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.04 sec)

mysql> insert into t1 values (4),(5),(6);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

#增量备份上一次完全备份至现在的

[root@localhost ~]# innobackupex --incremental /backup/ --incremental-basedir=/backup/2014-04-16_18-09-51/

[root@localhost ~]# cat /backup/2014-04-16_18-15-52/xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1600012

to_lsn = 1600451

last_lsn = 1600451

compact = 0

[root@localhost ~]# cat /backup/2014-04-16_18-09-51/xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 1600012

last_lsn = 1600012

compact = 0

[root@localhost ~]# 从上述信息可以看出增量备份的信息

mysql> use soul;#增量备份后又修改了数据；就只能根据二进制日志做时间点还原

Database changed

mysql> insert into t1 values (7),(8),(9),(10);

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql>还原数据[root@localhost ~]# service mysqld stop

Shutting down MySQL. [ OK ]

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

#准备备份；先准备完全备份；在依次由第一次...往后准备

#完全准备

[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2014-04-16_18-09-51/

140416 18:23:17 innobackupex: completed OK!

#第一次增量准备

[root@localhost ~]# innobackupex --apply-log --redo-only /backup/2014-04-16_18-09-51/ --incremental-dir=/backup/2014-04-16_18-15-52/

140416 18:26:25 innobackupex: completed OK!

[root@localhost ~]# cat /backup/2014-04-16_18-09-51/xtrabackup_checkpoints

backup_type = full-prepared

from_lsn = 0

to_lsn = 1600451

last_lsn = 1600451

compact = 0

[root@localhost ~]#可以看出完全备份的信息已整理好

[root@localhost ~]# innobackupex --copy-back /backup/2014-04-16_18-09-51/

140416 18:30:07 innobackupex: completed OK!

#恢复

[root@localhost ~]# ls /mydata/data/ -l

total 18452

drwxr-xr-x 2 root root 4096 Apr 16 18:30 hellodb

-rw-r--r-- 1 root root 18874368 Apr 16 18:30 ibdata1

drwxr-xr-x 2 root root 4096 Apr 16 18:30 mysql

drwxr-xr-x 2 root root 4096 Apr 16 18:30 performance_schema

drwxr-xr-x 2 root root 4096 Apr 16 18:30 soul

drwxr-xr-x 2 root root 4096 Apr 16 18:30 test

[root@localhost ~]# chown -R mysql.mysql /mydata/data/

[root@localhost ~]# cat /backup/2014-04-16_18-09-51/xtrabackup_binlog_info

master-bin.000004 298

[root@localhost ~]# mysqlbinlog --start-position=298 /mydata/logs/master-bin.000004 > /tmp/log.sql

[root@localhost ~]# service mysqld start

Starting MySQL.. [ OK ]

mysql> set session sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/log.sql

mysql> use soul;

Database changed

mysql> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+------+

10 rows in set (0.00 sec)

#测试数据恢复成功。Xtrabackup的“流”及“备份压缩”功能

Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可：

# innobackupex --stream=tar /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz

甚至也可以使用类似如下命令将数据备份至其它服务器：

# innobackupex--stream=tar /backup | ssh user@www.magedu.com "cat - > /backups/`date +%F_%H-%M-%S`.tar"

此外，在执行本地备份时，还可以使用--parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：

# innobackupex --parallel /path/to/backup

同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：

# innobackupex --remote-host=root@www.magedu.com /path/IN/REMOTE/HOST/to/backup导入或导出单张表

默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table选项(严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项)，并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。

(1)“导出”表

导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：

# innobackupex --apply-log --export /path/to/backup

此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。

(2)“导入”表

要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：

mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;

然后将此表的表空间删除：

mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，注意复制后更改权限；然后使用如下命令将其“导入”：

mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE；

如有错误；恳请纠正。