centos7中MySQL备份还原策略

目录

一、直接拷贝数据库文件

1.1在shangke主机上停止服务并且打包压缩数据库文件

1.2 在shangke主机上把数据库文件传输到localhost主机上(ip为192.168.33.157)

1.3在localhost主机上停止服务，解压数据库文件

1.4 在localhost主机上开启服务

1.5 测试

二、mysqldump 进行备份

2.1 备份恢复单个数据库

2.2  备份恢复单个表

2.3 备份数据结构

2.4 mysqldump+binlog 增量备份

2.4.1 修改配置文件

2.4.2   模拟凌晨2:00 备份

2.4.3  表里继续插入数据

2.4.4  模拟10点，误删表ceshi表

2.4.5 恢复

三、mydumper备份

3.1 mydumper下载安装

3.2 备份

3.3 模拟数据丢失和恢复

四、lvm快照实现

4.1 添加硬盘

4.2 创建一个逻辑卷

4.3  停止服务备份数据库

4.4 挂载逻辑卷到当前mysql的数据目录里

4.5 将刚刚备份的数据解压到数据目录里

4.6 修改数据目录的权限

4.7 开启服务

4.8 加读锁，创建快照，解锁

4.9 将快照挂载到临时目录里

4.10  备份数据

4.11 卸载快照并删除

4.12 查看数据

 4.13 测试

五、xtrabackup 备份数据库

5.1 下载安装xtrabackup。

5.2 创建用户和授权

5.3 完全备份

5.4 增量备份

5.5 恢复数据

5.6 删除数据恢复数据

补充：

---

 环境：

 

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

一、直接拷贝数据库文件

把主机为shangke的数据库文件备份，然后在去localhost主机上恢复。

1.1在shangke主机上停止服务并且打包压缩数据库文件

systemctl stop mysqld
cd /var/lib/mysql
tar czf /opt/all_db.tar.gz *

---

1.2 在shangke主机上把数据库文件传输到localhost主机上(ip为192.168.33.157)

scp /opt/all_db.tar.gz 192.168.33.157:/opt

---

1.3在localhost主机上停止服务，解压数据库文件

---

1.4 在localhost主机上开启服务

 

---

1.5 测试

在shangke主机上的数据库中数据如下：

在localhost 主机上的数据库中数据如下：

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

二、mysqldump 进行备份

2.1 备份恢复单个数据库

[root@shangke ~]# mysqldump -uroot -p123456 --default-character-set=utf8 -B lianxi1 > /tmp/lianxi.$(date +%F).sql

mysql> source /tmp/lianxi1.2023-07-22.sql

备份多个库，-B 数据库1 数据库2 ... 

---

2.2  备份恢复单个表

#备份，lianxi1是数据库名字，ceshi是lianxi1里面的数据表
mysqldump -uroot -p123456 lianxi1 ceshi >/tmp/lianxi1_ceshi.sql

#恢复
mysql -uroot -p123456 lianxi1 </tmp/lianxi1_ceshi.sql

---

2.3 备份数据结构

mysqldump -uroot -p123456 -d lianxi1 >/tmp/lianxi1.sql

mysql -uroot -p123456 lianxi1</tmp/lianxi1.sql

---

2.4 mysqldump+binlog 增量备份

2.4.1 修改配置文件

前提：开启bin-log
 vim /etc/my.cnf
            
[mysqld]
log_bin=mysql-bin
server_id=158     ----》server_id一般是IP的最后一段
            
重启mysqld：systemctl restart mysqld

---

2.4.2   模拟凌晨2:00 备份

mysqldump -uroot -p123456 lianxi1 ceshi >/tmp/lianxi1_ceshi.sql

---

2.4.3  表里继续插入数据

mysql -uroot -p123456 -e "insert into lianxi1.ceshi values('xiaohong'),('xiaohua')"

---

2.4.4  模拟10点，误删表ceshi表

 mysql -uroot -p123456 -e "drop table lianxi1.ceshi"

---

2.4.5 恢复

(1)

检查全备后的所有binlog
# ls -lrt /var/lib/mysql/mysql-bin.*

立即刷新并备份出binlog
mysqladmin -uroot -p flush-logs

找到新插入数据的binlog日志

 (2)用mysqlbinlog  命令查看，进行确认。

#注意5.7版本，insert语句已经加密，默认看不到，查看时加上选项 --base64-output=DECODE-ROWS -vv

[root@shangke ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --base64-output=DECODE-ROWS -vv

(3)将日志复制到新目录

(4)寻找时间

从binlog日志中找到插入数据的开始时间开始节点，结束时间结束节点

(5)提取记录(基于时间)

[root@shangke back]# mysqlbinlog mysql-bin.000002 --start-datetime="2023-07-22 12:02:17" --stop-datetime="2023-07-22 12:02:49" -r time.sql

 

 注：mysqlbinlog mysql-bin.000002 --start-position=340 --stop-position=490 -r pos1.sql 是基于节点
 

(6)恢复凌晨备份

 (7)恢复增量备份

注意：备份完了一定要检查备份的数据内容，看看是否有数据，如果备份的数据是无效的就很麻烦。

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

三、mydumper备份

3.1 mydumper下载安装

下载资源：mydumper-0.14.5-3.el7.x86-64资源-CSDN文库

安装：

---

3.2 备份

---

3.3 模拟数据丢失和恢复

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

四、lvm快照实现

4.1 添加硬盘

---

4.2 创建一个逻辑卷

---

4.3  停止服务备份数据库

---

4.4 挂载逻辑卷到当前mysql的数据目录里

---

4.5 将刚刚备份的数据解压到数据目录里

 这时候就会把解压的所有数据保存到/dev/mysql/mysql_data设备上。

---

4.6 修改数据目录的权限

---

4.7 开启服务

---

4.8 加读锁，创建快照，解锁

给数据库加读锁、给mysql的数据库所在的逻辑卷创建快照、解锁数据库这些操作需要在一个会话完成。

 echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 500M -s /dev/mysql/mysql_data;unlock tables;" |mysql -uroot -p123456

---

4.9 将快照挂载到临时目录里

mkdir /mnt/mysql && mount /dev/mysql/mysql_data /mnt/mysql

如果没有rsync命令需要下载

---

4.10  备份数据

mkdir /backup && rsync -av /mnt/mysql/ /back

---

4.11 卸载快照并删除

umount /mnt/mysql && lvremove /dev/mysql/mysql_data 

---

4.12 查看数据

 4.13 测试

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

五、xtrabackup 备份数据库

5.1 下载安装xtrabackup。

网址：Software Downloads - Percona

安装

yum localinstall percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm -y

---

5.2 创建用户和授权

---

5.3 完全备份

innobackupex --user=bkpuser --password=123456 --no-timestamp /db_bak/full_`date +%F`

 

  

--no-timestamp 参数可以不会生成时间戳 

--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

---

5.4 增量备份

在以上完全备份的基础上，开始新增加数据。

 备份新增加的数据

 

5.5 恢复数据

5.6 删除数据恢复数据

 

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

补充：

1、完全备份、差异备份、增量备份

完全备份、增量备份、差异备份 （从数据收集来分）
完全备份：备份全部数据；
增量备份：仅备份上次完全备份或增量备份以后变化的数据；
差异备份：仅备份上次完全备份以来变化的数据；

如图，白色是完全备份，黄色部分是增量备份， 蓝色部分也是增量备份，黄色+蓝色是差异备份。

---

2、my.cnf文件

在my.cnf文件中，mysqld部分常用的字段有：

datadir：指定MySQL数据文件的存储路径。
port：指定MySQL服务器监听的端口号。
bind-address：指定MySQL服务器绑定的IP地址。
socket：指定MySQL服务器的套接字文件路径。
character-set-server：指定MySQL服务器的默认字符集。
collation-server：指定MySQL服务器的默认排序规则。
max_connections：指定MySQL服务器允许的最大连接数。
key_buffer_size：指定MySQL服务器使用的索引缓冲区大小。
innodb_buffer_pool_size：指定InnoDB存储引擎使用的缓冲池大小。
query_cache_size：指定查询缓存的大小。
log_error：指定错误日志文件的路径。
slow_query_log：指定是否启用慢查询日志。
log_slow_queries：指定慢查询日志文件的路径。
log_bin：指定是否启用二进制日志。
binlog_format：指定二进制日志的格式。

basedir : mysql的安装目录和解压目录是两个概念。
这些是常用的字段，根据实际需求可以在my.cnf文件中进行配置

---

3、如果在my.cnf 中删除log_bin 和server_id  字段之后再添加重启服务发现失败.

可能是因为日志文件冲突：如果在删除log_bin配置项后，MySQL已经生成了一些binlog日志文件，再重新添加log_bin配置项时，可能会导致日志文件冲突。在重新添加log_bin配置项之前，可以尝试删除已生成的binlog日志文件，然后再启动MySQL服务。