linux mysql恢复数据_MySQL数据库备份与恢复-No.2

MySQL数据备份

databases　Binlog my.conf

所有备份数据都应放在非数据库本地，而且建议有多份副本。

测试环境中做日常恢复演练，恢复较备份更为重要。

备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群

---

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

1. 数据的一致性

2. 服务的可用性

逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL)，适用于中小型数据库，效率相对 较低。

mysqldump mydumper

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

tar,cp xtrabackup inbackup lvm snapshot

---

备份又有以下三种：

完全备份

增量备份

差异备份

tar数据备份(物理)

备份期间：服务不可用

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

1. 停止数据库

2. tar备份数据

3. 启动数据库

[root@cyy ~]# systemctl stop mysqld

[root@cyy ~]# mkdir /backup

[root@cyy ~]# tar -cf /backup/`date +%F`-mysql-all.tar /var/lib/mysql

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

还原的过程：

1. 停止数据库

2. 清理环境

3. 导入备份数据

4. 启动数据库

5. binlog恢复

[root@cyy ~]# systemctl stop mysqld

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

[root@cyy ~]# tar -xf /backup/2019-06-07-mysql-all.tar -C /

[root@cyy ~]# systemctl start mysqld

lvm快照(物理)

Lvm快照实现物理备份 + Binlog

数据一致，服务可用

注：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; 5、Copy files from the snapshot 6、Unmount the snapshot. 7、Remove snapshot

正常安装MySQL：

1. 安装系统 2. 准备LVM，例如 /dev/vg_tianyun/lv-mysql，mount /var/lib/mysql 3. 安装MySQL，默认datadir=/var/lib/mysql =====================================================MySQL运行一段时间，数据并没有存储LVM：将现在的数据迁移到LVM 1. 准备lvm及文件系统 [root@cyy ~]# lvcreate -n lv-mysql -L 2G datavg [root@cyy ~]# mkfs.xfs /dev/datavg/lv-mysql2. 将数据迁移到LVM [root@cyy ~]# systemctl stop mysqld [root@tianyun ~]# mount /dev/datavg/lv-mysql /mnt/ //临时挂载点 [root@tianyuncyy ~]# cp -a /var/lib/mysql/* /mnt //将MySQL原数据镜像到临时挂载点 [root@cyy ~]# umount /mnt/ [root@cyy ~]# vim /etc/fstab //加入fstab开机挂载 /dev/datavg/lv-mysql /var/lib/mysql xfs defaults 0 0 [root@cyy ~]# mount -a [root@cyy ~]# chown -R mysql.mysql /var/lib/mysql[root@cyy ~]# systemctl start mysqld =====================================================

LVM快照备份流程：

1. 加全局读锁 mysql> flush tables with read lock;2. 创建快照 [root@cyy ~]# lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql [root@cyy ~]# mysql -p'(cyyCyy..123)' -e 'show master status' > /backup/`date +% F`_position.txt3. 释放锁 mysql> unlock tables;1-3必须同一会话中完成 [root@cyy ~]# echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/ datavg/lv-mysql; UNLOCK TABLES;" | mysql -p'(cyyCyy..123)' [root@cyy ~]# echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/ datavg/lv-mysql; " | mysql -p'(cyyCyy..123)'4. 从快照中备份 [root@cyy ~]# mount -o ro /dev/datavg/lv-mysql-snap /mnt/ //xfs -o ro,nouuid [root@cyy ~]# cd /mnt/ [root@cyy mnt]# tar -cf /backup/`date +%F`-mysql-all.tar ./*5. 移除快照 [root@cyy ~]# cd; umount /mnt/ [root@cyy ~]# lvremove -f /dev/vg_cyy/lv-mysql-snap 

LVM快照恢复流程：

1. 停止数据库 2. 清理环境 3. 导入数据 4. 修改权限 5. 启动数据库 6. binlog恢复 [root@cyy ~]# tar -xf /backup/2019-06-07-mysql-all.tar -C /var/lib/mysql/ [root@cyy ~]# systemctl start mysqld

Tar解压的注意事项：

tar -tf 2019-06-07-mysql-all.tar |less

脚本 + Cron

#!/bin/bash/env 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 lv-mysql-snap /dev/datavg/lv-mysql;  UNLOCK TABLES;" | mysql -p'(Cyy..123)'mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/ rsync -a /mnt/ $back_dir if [ $? -eq 0 ];then  umount /mnt/ lvremove -f /dev/datavg/lv-mysql-snap fi