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物理备份与逻辑备份的方法了,如有问题可与博主一起交流讨论!