十一、MySQL数据备份

目录

数据备份

物理备份

  • 物理备份

    物理拷贝数据
cp -r /var/lib/mysql /mysql.bak
tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*

恢复方式(恢复前删除原有数据)
cp -r 备份目录/mysql.bak /var/lib/mysql/
tar -xf /root/mysql.tar.gz -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

  • 实验示例

    这里实验就不做了
大概流程就是
 - 打包A服务器原有数据 拷贝至另B服务器
 - 停止B服务器MySQL服务，删掉/var/lib/mysql/下的目录
 - 解压数据包到/var/lib/mysql/，并赋予权限
 - 启动MySQL服务 进行验证

逻辑备份

  • 什么是逻辑备份

    使用软件自带备份程序(备份命令)对数据做备份，在执行备份命令时，会根据已有的数据生成对应的命令，吧命令存放到指定的备份文件里
恢复时，执行备份文件里的命令回复数据

数据备份策略

  • 完全备份

    备份所有数据(一张表，一个库，一台服务器)

  • 增量备份

    只备份上次备份后，所有新产生的数据

# 在生产环境中，可能实行的策略是 周一完全备份，周二到周日进行差异备份

  • 差异备份

    备份上次"完全备份"后，所有新产生的数据

# 在生产环境中，可能实行的策略是 周一完全备份，周二到周日进行差异备份
# 记重点 "完全备份" 完全备份的时间是周一

数据备份操作

数据的完全备份与恢复

  • 命令格式

    # 备份命令格式
mysqldump -uroot -p密码 库名 > 目录/xxx.sql

# 恢复命令格式
mysql -uroot -p密码 库名 < 目录/xxx.sql

备份时库名表示方式
	- "--all-databases" 或 "-A"		# 所有库
	- 数据库名						  # 单个库 (db2)
    - 数据库名 表名					 # 单张表 (db2 user)
    - "-B 数据库1 数据库2"			 # 多个库 (-B db1 db2 db3)

# 注意: 
 - 无论备份还是恢复，都要验证用户权限！！！
 - 恢复一个库下的表的时候，需要指定库名(提前创建库)，多个库操作时不需要创建(指定)
 - 恢复多个库或全局时，".sql"文件中有数据库创建切换语句，所以不需要指定

  • 演示示例

    [root@admin ~]# mysqldump -uroot -p'123456' --all-databases >/root/mybak/alldb.sql
[root@admin ~]# mysqldump -uroot -p'123456' db2 user >/root/mybak/db2_user.sql

[root@admin ~]# ls /root/mybak/			# 可以使用vim查看文件内容

将备份内容拷贝到node1主机上，进行数据恢复
[root@admin ~]# scp /root/mybak/db2_user.sql root@192.168.4.11:/root/

恢复单个库的时候，需要在node1主机上的mysql库中创建db2库
mysql> create database db2;

[root@node1 ~]# mysql -uroot -p'123456' db2 < /root/db2_user.sql	# 数据恢复

恢复全部数据示例(多个库也是这样操作)
[root@admin ~]# scp /root/mybak/alldb.sql root@192.168.4.11:/root/
[root@node1 ~]# mysql -uroot -p'123456' < /root/alldb.sql

Binlog日志(增量备份原理)

使用MySQL服务的binlog日志实现数据的增量备份与恢复

  • binlog日志的使用

    • binlog日志介绍

      也叫作 二进制日志
MySQL服务日志的文件一种
记录除查询之外的所有SQL命令(只记录让数据发生改变的命令)
可用户数据备份和恢复
配置MySQL主从同步的必要条件
# 默认关闭，需要手动开启

    • 启用binlog日志

      server_id=数字			# 指定di值(1-255)
log_bin[=目录/文件]			# 启动binlog日志
max_binlog_size=数值m		# 指定日志文件容量，默认1G

[root@admin ~]# vim /etc/my.cnf
[mysqld]
server_id=50
log_bin

[root@admin ~]# systemctl restart mysqld

mysql> show master status;		# 查看正在使用的binlog文件
# Position: 偏移量 只有修改数据内容偏移量才会发生变化，默认154

[root@admin ~]# ls /var/lib/mysql		# 查看日志文件
admin-bin.000001    admin-bin.index		# xx.index 是索引文件，记录日志文件位置，可以cat查看

# 修改binlog日志存放地址(注意不能放root目录下，mysql没有权限)
[root@admin ~]# vim /etc/my.cnf
[mysqld]
server_id=50
log_bin=/mylog/plj		# "plj" 指定生成文件的名称

[root@admin ~]# mkdir /mylog
[root@admin ~]# chown mysql /mylog
[root@admin ~]# ls -ld /mylog

[root@admin ~]# systemctl restart mysqld

[root@admin ~]# ls /mylog/
plj.000001  plj.index

[root@admin ~]# mysql -uroot -p'123456'
mysql> show master status;

    • 手动创建新的binlog日志文件

      每次重启mysql服务，就会生成一个新的binlog文件
[root@admin ~]# systemctl restart mysqld		(1)

# 在交互模式下手动穿创建新的日志文件
mysql> flush logs; 				(2)

# 在非交互模式下手动创建新的日志文件
[root@admin ~]# mysql -uroot -p'123456' -e "flush logs"			(3)
	
# 创建新的完全备份文件并创建新的日志文件
[root@admin ~]# mysqldump -uroot -p'123456' --flush-logs db2 > /root/mybak/db2.sql			(4)

# 每次创建新的日志文件可以查看mysql当前在使用哪个日志文件记录
[root@admin ~]# mysql -uroot -p'123456' -e "show master status"

    • 删除已有的日志文件

      # 删除指定编号之前的binlog日志
purge master logs to "binlog文件名";

# 删除所有binlog日志，重建新日志
reset master;

      mysql> purge master logs to "plj.000003";

mysql> reset master;

    • 查看binlog日志内容

      [root@admin ~]# mysqlbinlog /mylog/plj.000001

# 通过该方法查看binlog日志内容，发现看不懂，这时候我们需要修改binlog日志的格式

      修改日志格式

      • 查看日志当前记录格式

        mysql> show variables like "binlog_format";

# 三种记录模式
 1. statement 	报表模式
 2. row			行模式
 3. mixed		混合模式

      • 修改日志记录格式

        [root@admin ~]# vim /etc/my.cnf
[mysqld]
binlog_format="mixed"

[root@admin ~]# systemctl restart mysqld

        mysql> reset master; 

# 重新记录日志文件之后插入几条数据再对binlog日志进行检索，发现已经可以看懂了

[root@admin ~]# mysqlbinlog /mylog/plj.000001 | grep "insert"
insert into user(username) values('aier1')

# 可以测试 插入 修改 和删除

  • 使用binlog日志恢复数据

    mysqlbinlog 目录/日志文件 | mysql -uroot -p'密码'

    # 将binlog日志拷贝要恢复的服务器(node1) ps：node1先删掉之前恢复的库
[root@admin ~]# scp /mylog/plj.000001 root@192.168.4.11:/root/

[root@node1 ~]# mysqlbinlog /root/plj.000001 | mysql -uroot -p'123456'

  • 使用binlog日志恢复指定范围内的数据

    mysqlbinlog 选项 binlog日志文件 | mysql -uroot -p'密码'

# 选项
起始时间	--start-datetime="yyyy-mm-dd hh:mm:ss"
结束时间	--stop-datetime="yyyy-mm-dd hh:mm:ss"
起始偏移量		--start-position=数字
结束偏移量		--stop-position=数字

    • 日志文件如何区分记录的多条SQL命令

      偏移量			# Position
	--start-position=1054 --stop-position=2098
时间点
	--start-datetime="yyyy-mm-dd hh:mm:ss" --stop-datetime="yyyy-mm-dd hh:mm:ss"



    # 删除一张表记录
mysql> delete from db2.user where username="aier3";
[root@admin ~]# scp /mylog/plj.000001 root@192.168.4.11:/root/

# 关于日志文件偏移量的分析看视频吧，这里不好解释(这得靠眼睛分析 要命)
[root@node1 ~]# mysqlbinlog --start-position=647 --stop-position=1253 /root/plj.000001 | mysql -uroot -p'123456'
posted @ 2021-11-27 00:20  CatdeXin  阅读(61)  评论(0)    收藏  举报