mysql数据备份恢复

数据备份恢复

一.概述

MySQL数据备份即databases Binlog my.cnf

所有备份数据都应放在非数据库本地，而且建议有多份副本。测试环境中做日常恢复演练，恢复较备份更为重要。

备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。冗余： 数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

【多余的重复或啰嗦内容（包括信息、语言、代码、结构、服务、软件、硬件等等）均称为冗余】

备份过程中必须考虑因素：

1. 数据的一致性

2. 服务的可用性

逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，版本兼容性较好，但是效率相对较低。

mysqldump mydumper

物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同（版本跨度较大）的MySQL版本。

tar,cp xtrabackup（建议大家使用） inbackup

lvm快照

完全备份

增量备份（自上次备份发生数据变更的备份）

差异备份（始终针对完整备份不同的备份）

二.逻辑备份

1.binlog日志逻辑备份

2.mysqldump 逻辑备份

3.into outfile 逻辑备份

mysqldump 备份

mysqldump逻辑备份

===================================

使用mysqldump备份:主要针对小数据进行备份！

1. 备份单个数据库

# mysqldump -uroot -p1 库名 > /tmp/xxx.sql

2. 备份单个表

# mysqldump -u root -p1 库名 表名 > /tmp/xxx.sql

3. 备份多个表

# mysqldump -u root -p1 库名 表名 表名 > /tmp/xxx.sql

4. 多库备份 -B,–databases

# mysqldump -u root -p1 -B 库名 库名 > /tmp/xxx.sql

5. 全库备份 -A, --all-databases

# mysqldump -u root -p1 -A > /tmp/xxx.sql

恢复数据库

为保证数据一致性，应在恢复数据之前停止数据库对外的服务**,停止binlog日志因为binlog使用binlog日志恢复数据时也会产生binlog**日志

mysql> set sql_log_bin=0

1. 单库恢复

mysql> drop database db1;

Query OK, 2 rows affected (0.01 sec)

mysql> create database db1; //先建库Query OK, 1 row affected (0.00 sec)

第一种恢复方式：

[root@ULA mysql]# mysql -u root -p1 db1 < /tmp/db1.sql

第二种恢复方式：

[root@ULA mysql]# mysql -uroot -p1 mysql> use db1

mysql> source /tmp/db1.sql

2. 单表恢复

mysql -u root -p1 db1 < /tmp/db1.sql

3. 多表恢复(同单表恢复一样)

4. 多库恢复(恢复之前是不需要手动创建数据库的)

mysql -u root -p < /tmp/db.sql

6. 全库恢复

mysql -u root -p1 < /tmp/all.sql

mysqldum 常用选项（扩展）

常用备份选项：（结合mysqldump --help进行记忆）

-A, --all-databases

备份所有库

-B, --databases bbs test mysql

备份多个数据库

-F, --flush-logs

备份之前刷新binlog日志

–single-transaction

该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB

–master-data=1|2

该选项将会记录备份时binlog的日志位置与文件名并追加到文件中，内容如下： CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000016’, MASTER_LOG_POS=107;

表示备份的是日志文件16里位置为107之前的数据

这个参数在建立slave数据库的时候会用到（从库，后面将会学习），当这个参数的值为1的时候，mysqldump出

来

的文件就会包括CHANGE MASTER TO这个语句CHANGE MASTER TO后面紧接着就是 file和position的记录，file和position记录的位置就是slave从master端复制文件的起始位

置。默认情况下这个值是1，当这个值是2的时候，change master to也是会写到dump文件里面去的，但是不会有上面那个作用了

–default-character-set

指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

–quick，-q 没有上限 只与内存有关

该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

–no-data，-d

不导出任何数据，只导出数据库表结构。

-x, --lock-all-tables

在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭

–lock-tables

它和 --lock-all-tables 类似，不过只是锁定当前导出的数据表，而不是一下子锁定全部库中的表。本选项只适用于 MyISAM 表

-R, --routines 备份存储过程和存储函数

–triggers 备份触发器

mysqldump导出数据对表的控制： 主要有两种控制

一种是导出的全过程都加锁 lock-all-tables, 另一种则是不加。前者会在导出开始时执行 FLUSH TABLES WITH READ LOCK; 也就是加全局读锁，会阻塞其它写操作，以保证导出是一致性的；因此只有在导出测试数据时或导出时没有业务连接操作时可不加 lock-all-tables，正常工作中必须加该选项 .

至于说一致性导出的另一种方式 single-transaction, 则是有适用范围的，见下边。

single-transaction 选项和 lock-all-tables 选项是二选一的，前者是在导出开始时设置事务隔离状态并使用一致性快照开始事务,而后马上unlock tables，然后执行导出,导出过程不影响其它事务或业务连接，但只支持类似innodb多版本特性的引擎，因为必须保证即使导出期间其它操作(事务点t2)改变了数据，而导出时仍能取出导出开始的事务点t1 时的数据。而lock-all-tables则一开始就 FLUSH TABLES WITH READ LOCK; 加全局读锁，直到dump完毕。

master_data 选项开启时默认会打开lock-all-tables，因此同时实现了两个功能，一个是加锁，一个是取得log信息。

master_data取1和取2的区别，只是后者把 change master … 命令注释起来了，没多大实际区别；

当master_data和 single_transaction 同时使用时，先加全局读锁，然后设置事务一致性和使用一致性快照开始事务，然后马上就取消锁，然后执行导出

总结：

1. 如果需要binlog信息则使用 master_data;

2. 如果不想阻塞同时表是innodb引擎可使用 single_transaction 取得一致性快照(取出的数据是导出开始时刻事务点的状态)

3. 如果表不支持多版本特性，则只能使用 lock-all-tables 阻塞方式来保证一致性的导出数据。

4. 如果能保证导出期间没有任何写操作，可不加或关闭 lock-all-tables

binlog日志备份

逻辑备份

删除binlog日志的时候，需要先停止，my.cnf内的bin-log功能，否则会出现服务器不来！ 不产生bin-log日志：

mysql> set sql_log_bin=0

binlog日志方法备份恢复数据记录每一个操作

默认存储位置 ：

rpm ： /var/lib/mysql

编译: 安装目录自定义

binlog日志数据恢复流程

1. 开启binlog日志记录

#mysqld_safe --log-bin --user=mysql --server-id=1 & 临时开启

修改配置文件 (永久开启) #vim /etc/my.cnf [mysqld]

datadir=/data basedir=/usr/local/mysql server_id=1 指定结点号

log_bin=/data/binlog 指定日志路径

binlog_format=statement expire_logs_days=5

重启服务

2. 模拟数据(建库、建表、插入数据)

3. 查看binlog日志内容

#mysqlbinlog /data/binlog.000001

5. 摸拟停止记录binlog日志

mysql>set sql_log_bin=0 停止记录binlog日志或者修改配置文件停止binlog日志记录

5. 模拟数据丢失(删除mysql中的数据) 6.根据binlog日志进行恢复

根据binlog恢复数据

根据时间点恢复数据

#mysqlbinlog --start-datetime=‘2014-11-25 11:56:54’ --stop-datetime=‘2014-11-25 11:57:41’ binlog.000001 | mysql -u root -p1

根据位置点恢复数据

#mysqlbinlog --start-position 106 --stop-position 527 alan-bin.000001 | mysql -u root -p1

刷新bin-log日志

#mysqladmin flush-logs -u root -p’'

7.查看mysql数据恢复情况

三.物理备份

1.tar包备份2.lvm快照

tar包备份

备份tar包的时候，注意先看一下，tar包的大小，或者先解压到一个新的目录内（验证你的tar包不是坏包）

tar备份数据库+ binlog

注：备份期间，服务不可用

============================================================

备份的过程：【完全物理备份】

1. 停止数据库

2. tar备份数据

3. 启动数据库

[root@slave2 ~]# service mysqld stop [root@slave2 ~]# mkdir /backup

[root@slave2 ~]# tar -cf /backup/`date +%F`-mysql-all.tar /usr/local~~/mysql/data~~ [root@slave2 ~]# cd /usr/local/mysql

[root@slave2 ~]# tar cvf /backup/`date +%F`-mysql-all.tar data

启动服务

注：备份文件应该复制其它服务器或存储上

还原的过程：

1. 停止数据库

2. 清理环境

3. 导入备份数据

4. 启动数据库

5. binlog恢复

[root@slave2 ~]# service mysqld stop [root@slave2 ~]# rm -rf /usr/local/mysql/data/*

[root@slave2 ~]# tar -xf /backup/2016-12-07-mysql-all.tar -C /data/ [root@slave2 ~]# service mysqld start

lvm快照

四.Lvm快照实现物理备份 + binlog

只保存Inode 号

数据一致，服务可用

注：MySQL数据lv和将要创建的snapshot 必须在同一VG，因此VG必须要有一定的剩于空间

============================================================

优点：

几乎是热备 (创建快照前把表上锁，创建完后立即释放)

支持所有存储引擎备份速度快

无需使用昂贵的商业软件(它是操作系统级别的) 缺点：

可能需要跨部门协调(使用操作系统级别的命令，DBA一般没权限) 无法预计服务停止时间

数据如果分布在多个卷上比较麻烦(针对存储级别而言)

操作流程：

1、flush table with read locak; 锁表防止新数据产生

2、create snapshot 为数据创建快照

3、show master status; show slave status; [可选]

4、unlock tables; 解锁(1、2、4步同时执行才能完成) 5、Copy files from the snapshot 从快照原还数据

6、Unmount the snapshot. 卸载快照

7、Remove snapshot 删除快照

LVM备份MySQL的前提（2选1）：

1. 全新安装mysql在lvm卷

   1. 添加一块新硬盘

   2. 创建pv

# pvcreate /dev/sdb

3. 创建vg

# vgcreate -s 4M datavg /dev/sdb

4. 创建存储数据库的lv（留一部分空间以存储snapshot）

# lvcreate -L 10G -n datavg_mysql datavg

5. 格式化

# mkfs.xfs /dev/datavg/datavg_mysql

6. 挂载

# mkdir /usr/local/mysql

# mount /dev/datavg/datavg_mysql /usr/local/mysql/

7. 安装数据库

2. 将已运行的mysql迁移到lvm卷

   1. 创建lv及文件系统

# lvcreate -L 10G -n datavg_mysql datavg # mkfs.xfs /dev/datavg/datavg_mysql

2. 将数据库迁移到lvm

# service mysqld stop 建立临时挂载点拷贝数据# mkdir /mnt/mysql

# mount /dev/datavg/datavg_mysql /mnt/mysql

# cp -a /usr/local/mysql/ /mnt/mysql # umount /mnt/mysql

编辑永久挂载点文件

# vim /etc/fstab

/dev/datavg/datavg_mysql /usr/local/mysql xfs defaults 0 0

# mount -a

# chown -R mysql.mysql /usr/local/mysql # service mysqld start

LVM快照备份流程

==========================

1. 加全局读锁（只能读，不能写）

mysql> flush tables with read lock;

2. 创建快照

# lvcreate -L 500M -s -n datavg_mysql_snap /dev/datavg/datavg_mysql

查询二进制日志的位置，下次使用binlog作为还原数据（最后一次备份点到崩溃点之间的数据）的依据。

# mysql -p’1’ -e ‘show master status’ > /backup/`date +%F`_position.txt

3. 释放锁

mysql> unlock tables;

步骤1-3必须在同一会话中完成（因此可一步执行）

echo “FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n datavg_mysql_snap /dev/datavg/ datavg_mysql; UNLOCK TABLES;” | mysql -p’1’

4. 从快照中拷贝

# mount -o ro,nouuid /dev/datavg/datavg_mysql_snap /mnt/mysql/ //xfs文件系统，使用额外选项-o ro,nouuid # cd /mnt/mysql

# tar -cf /backup/`date +%F`-mysql-all.tar ./* # cd;umount /mnt/mysql/

5. 移除快照

# lvremove -f /dev/datavg/datavg_mysql_snap

LVM快照恢复流程

==========================

1. 停止数据库（也可以找一个新库）

# service mysqld stop

2. 清理环境

# rm -rf /usr/local/mysql/*

3. 导入数据

# tar -xf /backup/2019-04-21-mysql-all.tar -C /usr/local/mysql

4. 修改权限

# chown -R mysql.mysql /usr/local/mysql/

5. 启动数据库

# service mysqld start

6. binlog恢复（略）

7.脚本 + Cron

============================================

#!/bin/bash

# LVM backmysql… back_dir=/backup/`date +%F`

[ -d $back_dir ] || mkdir -p $back_dir

echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n datavg_mysql_snap /dev/datavg/ datavg_mysql; \

UNLOCK TABLES;" | mysql -p’1’

mount -o ro,nouuid /dev/datavg/datavg_mysql_snap /mnt/mysql/ rsync -a /mnt/mysql/ $back_dir

if [ $? -eq 0 ];then

umount /mnt/mysql

lvremove -f /dev/datavg/datavg_mysql_snap

fi

============================================

crontab -e

0 2 * * * /root/mysql.sh

============================================