从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件
  • 首页
  • MYSQL
  • 05 - MYSQLdump备份和导入数据库 保持数据一致性

05 - MYSQLdump备份和导入数据库 保持数据一致性

发布:蔺要红08-04分类: MYSQL


常见的方式如以下三种:

1、直接拷贝整个数据目录下的所有文件到新的机器。优点是简单、快速,只需要拷贝;缺点也很明显,在整个备份过程中新机器处于完全不可用的状态,且目的无法释放源数据文件中因为碎片导致的空间浪费和无法回收已发生扩展的innodb表空间
2、用xtrabackup进行热备。优点是备份过程中可继续提供服务;缺点和第一种方法差不多,目的分区无法释放源数据文件中因为碎片导致的空间浪费和无法回收已发生扩展的innodb表空间
3、使用官方自带的MySQLdump逻辑重做。优点是在整个备份过程中可以向外提供服务,最重要的一点是可以解决碎片浪费



 分库备份实
 
有多个库时,就执行多条相同的备份语句,只是备份的库名和备份文件名不同而已。
可能通过shell脚本自动生成并执行相应的操作,
也可以把所有单个备份语句写在一个shell脚本中,通过cron定时任务来备份。
分库备份的意义是在所有库都备份成一个备份文件时,
恢复其中一个库的数据是比较麻烦的,所以分库备份,利于恢复,分库备份脚本如下:
 
for dbname in ` mysql -uroot -p'123456' -e "show databases;" | grep -Evi "database|infor|perfor"`
do
    mysqldump -uroot -p"123456" --events -B $dbname | gzip > /mnt/${dbname}_bak.sql.gz
done
  
基本操作
# 导出整个数据库(包括数据库中的数据)
mysqldump -u username -p dbname > dbname.sql 
# 导出数据库结构(不含数据)
mysqldump -u username -p -d dbname > dbname.sql
# 导出数据库中的某张数据表(包含数据)
mysqldump -u username -p dbname tablename > tablename.sql
# 导出数据库中的某张数据表的表结构(不含数据)
mysqldump -u username -p -d dbname tablename > tablename.sql

# 通过source来恢复数据库或者执行SQL语句(前提是需要进入数据库) 
use dbname;
source /backup/dbname.sql
  
进阶操作
# 备份所有的库 
mysqldump  -uroot  -p111111 -B -A --events -x|gzip >bak_$(date +%F).sql.gz

# 备份单个数据库
mysqldump  -uroot  -p123456 -B -x wordpress|gzip >bak_wordpress$(date +%F).sql.gz

# 恢复数据库

# 指定要恢复的库名恢复,前提是备份的时候,不加-B参数,如果加了以后
# 需要删除   CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wordpress` /*!40100 DEFAULT CHARACTER SET utf8 */;
mysql  -uroot  -p111111 linyaohong</home/bak_wordpress2018-12-23.sql 

# 如果备份的时候加了-B 则可以直接恢复,并且恢复后数据库名和备份的一样子(推荐)
mysql  -uroot  -p111111 </home/bak_wordpress2018-12-23.sql



 
(3)生产环境DBA给出的命令
a、for MyISAM
mysqldump --user=root --all-databases --flush-privileges --lock-all-tables \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

b、for InnoDB
mysqldump --user=root --all-databases --flush-privileges --single-transaction \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

参数
# 参数

-A --all-databases    #导出全部数据库
-B--databases         #导出几个数据库。参数后面所有名字参量都被看作数据库名。
-E--events            #导出事件。
-F                    #刷新binlog,如果binlog打开了,-F参数会在备份时自动刷新binlog进行切换。
-x --lock-all-tables  #提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭--single-transaction 和--lock-tables 选项

--single-transaction    #适合innodb事务数据库的备份。保证备份的一致性,原理是设定本次会话的隔离级别为Repeatable read,来保证本次会话(也就是dump)时,不会看到其它会话已经提交了的数据。

--triggers     #为每个备份的表备份trigger
--routines     #备份routines(存储过程和函数)

-d --no-data       不导出任何数据,只导出数据库表结构。
-p --password      连接数据库密码
-P --port          连接数据库端口号
-u --user          指定连接的用户名
-h --host          需要导出的主机信息

--hex-blob    #这个命令是用于将导出的内容处理成为2进制流的形式。因为有些时候你会发现,导出的数据库文件大小与数据库本身真实内容的大小存在冲突,往往是小于真实的大小。加入这个命令你就可以解决这个问题了。这个命令加在备份数据库的时候

--add-locks    在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用--skip-add-locks取消选项)

--master-data    在备份文件中写入备份时的binlog文件,在恢复进,增量数据从这个文件之后的日志开始恢复。值为1时,binlog文件名和位置没有注释,为2时,则在备份文件中将binlog的文件名和位置进行注释
--flush-logs       开始导出之前刷新日志。请注意:假如一次导出多个数据库(使用选项--databases或者--all-databases),将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下,日志将会被刷新一次,相应的所以表同时被锁定。因此,如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs

--flush-privileges   #在导出mysql数据库之后,发出一条FLUSH PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候

--force在导出过程中忽略出现的SQL错误


--ignore-table   不导出指定表。指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名。例如:--ignore-table=database.table1 --ignore-table=database.table2 ……

-l --lock-tables:开始导出前,锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB,--single-transaction是一个更好的选择,因为它根本不需要锁定表。请注意当导出多个数据库时,--lock-tables分别为每个数据库锁定表。因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。



保持数据的一致性

MySQLdump对不同类型的存储引擎,内部实现也不一样。主要是针对两种类型的存储引擎:支持事务的存储引擎(如InnoDB)和不支持事务的存储引擎(如MyISAM)

1、对于支持事务的引擎如InnoDB,参数上是在备份的时候加上--single-transaction保证数据一致性
 
--single-transaction实际上通过做了下面两个操作:
1、在开始的时候把该session的事务隔离级别设置成repeatable read;
2、然后启动一个事务(执行begin),备份结束的时候结束该事务(执行commit)
 
在备份过程中,该session读到的数据都是启动备份时的数据(同一个点)。可以理解为对于innodb引擎来说加了该参数,备份开始时就已经把要备份的数据定下来了,备份过程中的提交的事务时是看不到的,也不会备份进去
 
2、对于不支持事务的引擎如MyISAM,只能通过锁表来保证数据一致性,这里分三种情况
 
1、导出全库:加--lock-all-tables参数,在备份开始的时候启动一个全局读锁(执行flush tables with read lock),其他session可以读取    但不能更新数据,备份过程中数据没有变化,所以最终得到的数据肯定是完全一致的;
2、导出单个库:加--lock-tables参数,在备份开始的时候锁该库的所有表,其他session可以读但不能更新该库的所有表,该库的数据一致;
3、导出单个表:加--lock-tables参数,在备份开始的时候锁该表,其他表不受影响,该表数据一致

 

上面只是展示了对不同引擎来讲加的参数只是为了让数据保持一致性,但在备份中业务并没有停止,时刻可能有新的数据进行写入,为了让我们知道备份时是备份了哪些数据,或者截止到那个指针(二进制日志),我们可以再加入 --master-data参数,备份好的sql文件就会记录从备份截至到哪个指针,指针之后的数据更新我们可以通过二进制日志进行恢复。

# --flush-log 表示备份开始之后的更行都切到下一个二进制日志
MySQLdump -u root -p --single-transaction --master-data --flush-log --database test > test.sql             

可以在备份的test.sql文件中前几行看到记录着备份当时的二进制日志信息

# vim test.sql 
--CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=436263492; 
---- Current Database: `test` 
..... 
#在全备恢复之后,可以通过之后的二进制日志进行恢复
mysqlbinlog --start-position=436263492 mysql-bin.000004 > 00004.sql
 
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站