mysql物理备份与逻辑备份详解

11 篇文章 0 订阅

mysql物理备份与逻辑备份详解

条件:
本文针对mysql版本为5.7,存储引擎为InnoDB,其他版本未做实验

一、逻辑备份恢复

备份:

$ mysqldump -h127.0.0.1 --add-drop-table --force -u$DB_USER  -p$DB_PASS $DB_NAME > $BACK_DIR/$NAME_sql
$DB_USER  ##表示用户名
$DB_PASS  ##表示密码
$DB_NAME  ##表示要备份的数据库
$NAME_sql  ##表示备份的文件名称

恢复:
1、如果mysql数据库中已经配置了账号密码,可通过指定my.cnf配置文件恢复

$ mysql --defaults-extra-file=/etc/my.cnf $NAME_dest <  $NAME_dest.sql
$NAME_dest  ##需要恢复的数据库
$NAME_dest.sql  ##备份好的数据库文件

2、如果mysql数据库中没有配置账号密码

$ mysql -u$DB_USER -p$DB_PASS $NAME_dest < $NAME_dest.sql
二、物理备份迁移

首先我们需要知道数据存储结构是怎么样的,创建一个数据库后数据存储目录都做了哪些改变?
实际上mysql在创建数据库后,会在数据存储目录生成一个同名的文件夹,然后初始化生成一个db.opt文件,数据库中的每个数据表又都是一个单独的文件,以.frm和.ibd格式成对存储,比如test1数据表,则会创建test1.frm和test1.ibd,同时还会修改ibdata1文件中的元信息tablespace id
在这里插入图片描述
那么直接拷贝.frm和.idb这两个文件到新的MySQL数据目录下可以吗,一般来说是不行的,只有在你的ibd文件的tablespace id和ibdata1文件中的元信息的tablespace id一致才可以。

##环境
机器1:192.168.1.11
机器2:192.168.1.12
两台机器的数据库存储目录均为:/data/mysqldb/data
##需求
将机器1上的数据全量迁移到机器2上去

#1、停止mysql服务(两台机器均执行) 这一步非常重要

# /etc/init.d/mysqld stop

#2、打包机器1的数据存储目录

# ps -ef |grep mysql
root       831     1  0 10:52 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysqldb/data --pid-file=/data/mysqldb/data/mysql1-210.pid
mysql     1453   831  0 10:52 pts/0    00:01:40 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysqldb/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysqldb/logs/mysql-error.log --pid-file=/data/mysqldb/data/mysql1-210.pid --socket=/tmp/mysql.sock
--datadir就表示数据库存储目录
# cd /data/mysqldb/
# tar -zcvf data.tar.gz data

#3、将数据拷贝到机器2

# scp data.tar.gz root@192.168.1.12:/data/mysqldb/

#4、在机器2解压data.tar.gz包

# cd /data/mysqldb/
# tar -zxvf data.tar.gz           --这里就会自动覆盖data目录下的数据文件
# chown -R mysql:mysql /data/mysqldb/data              

#5、启动mysql服务

# /etc/init.d/mysqld start
附加:

这里可能有人会问,如果机器2上本来就存在数据库,通过上面1-5步全量覆盖后就会导致原有的数据库消失,这里实际上是可以先将机器2上的数据逻辑备份,等全量覆盖之后再进行手动恢复

问题1、全量备份后机器2上原有的数据表不存在了,实际通过show tables可以查到
在这里插入图片描述
解决办法是进入数据存储目录去删除对应数据库文件下的.frm和.idb文件
问题2、全量备份后机器2上原有的数据库无法删除,实际通过show databases可以查到
在这里插入图片描述
解决办法是进入数据存储目录去删除对应的数据库文件目录

# rm -rf /data/mysqldb/data/maizi

问题1和问题2实际也印证了物理备份迁移只拷贝.frm和.idb这两个文件是行不通的,因为ibdata1文件被覆盖的原因导致出现了问题1和问题2,所以关于mysql数据库的存储原理非常重要!!!

好了,这就是mysql物理备份与逻辑备份的方法了,如有问题可与博主一起交流讨论!

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值