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

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

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


MYSQLdump 实际上就是把mysql库里以逻辑的 sql 语句的形式输出或生成备份文件

常见的方式如以下三种:(备份后一定要检查********,mysqldump语法错误也会生成备份文件,但文件内容是空的)

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



分库分表备份脚本
 
通过cron定时任务,分库备份的意义是在所有库都备份成一个备份文件时,恢复其中一个库的数据是比较麻烦的,所以分库备份,利于恢复,分库备份
脚本如下:
# 分库备份
#!/bin/sh
mysql_mysql_user=root
mysql_password=111111
mysql_scoket=/data/3306/mysql.sock
my_login="mysql -u${mysql_mysql_user} -p${mysql_password} -S ${mysql_scoket}"
my_dump="mysqldump -u${mysql_mysql_user} -p${mysql_password} -S${mysql_scoket} -B -F -R --master-data=2 --single-transaction --flush-privileges --events"
database="$(${my_login} -e "show databases;"|egrep -vi "Data|_schema|mysql")"
for dbname in ${database}
do	
  backup_dir=/server/backup/$dbname
  [ ! -d ${backup_dir} ] && mkdir -p ${backup_dir}
  ${my_dump} ${dbname}|gzip >${backup_dir}/${dbname}_$(date +%F).sql.gz
done 

# 分表备份
#!/bin/sh
mysql_user=root
mysql_password=111111
mysql_scoket=/data/3306/mysql.sock
my_login="mysql -u$mysql_user -p$mysql_password -S$mysql_scoket"
my_dump="mysqldump -u$mysql_user -p$mysql_password -S$mysql_scoket"
database="$($my_login -e "show databases;"|egrep -vi "Data|_schema|mysql")"
for dbname in $database
do
  table="$($my_login -e "use $dbname;show tables;"|sed '1d')"
  for tname in $table
  do
    backup_dir=/server/backup/$dbname/${dbname}_$(date +%F)
    [ ! -d $backup_dir ] && mkdir -p $backup_dir
    $my_dump $dbname $tname |gzip >$backup_dir/${dbname}_${tname}_$(date +%F).sql.gz
  done
done

生产环境(推荐加的参数)
生产环境命令全备(不推荐,分开备份最佳) 
# -----------------------MyISAM-----------------------
# 备份会包含存储过程,视图等
mysqldump -uroot -p111111  -B -F -R -F --triggers --master-data=2  -x  --events  linyaohong|gzip >/backup/linyaohong.sql.gz

# -----------------------InnoDB-----------------------
# 备份会包含存储过程,视图等,备份库为:linyaohong
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -F -R --triggers --single-transaction --master-data=2 --events  linyaohong|gzip >/backup/linyaohong.sql.gz
# --hex-blob  如果数据库中有blob字段或者其他大字段,需要加上此参数,否则数据会丢失
# 顺便测试了一下排除test表,测试成功
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -F -R --single-transaction --master-data=2 --triggers --events --ignore-table=linyaohong.test linyaohong|gzip >/backup/linyaohong.sql.gz
  
恢复数据库
# 如果备份的时候加了-B 则可以直接恢复(无需提前创建数据库),并且恢复后数据库名和备份的一样子(推荐)
mysql  -uroot  -p111111 </home/bak_wordpress2018-12-23.sql

如果加-B参数,还想指定库恢复(自定义数据库名)
# 需要删除   CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wordpress` /*!40100 DEFAULT CHARACTER SET utf8 */;   
# 或者 备份的时候压根没加 -B 参数 
# 恢复的时候先创建数据库,然后指定库恢复
mysql  -uroot  -p111111 wordpress2018</home/bak_wordpress2018-12-23.sql
# 使用source  和字符集关联特别大,否则乱码,一定要看一下sql文件的编码
mysql> system ls /backup
all.sql  all.sql.gz
mysql> source /backup/all.sql
# 总结 恢复数据库的时候,如果不是自己备份的,最好看一下SQL文件中是否有 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wordpress` /*!40100 DEFAULT CHARACTER SET utf8 */;
# 直接恢复sql.gz文件--------------------------------------------------------
gunzip < /backup/all.sql.gz|mysql -uroot  -p111111 -S /data/3306/mysql.sock 

基本操作( 扯了这么多不如分库分表备份 )
# 导出dbname数据库(包括数据库中的数据)
mysqldump -u username -p dbname > dbname.sql 
# 导出dbname数据库结构(不含数据)
mysqldump -u username -p -d dbname > dbname.sql
# 导出数据库dbname中的tablename数据表(包含数据)
mysqldump -u username -p dbname tablename > tablename.sql
# 导出数据库dbname中的tablename数据表的表结构(不含数据)
mysqldump -u username -p -d dbname tablename > tablename.sql

# 备份linyaohong yaohong hong 三个数据库  -B
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B linyaohong yaohong  hong
# 备份linyaohong yaohong hong 三个数据库的表结构 -B -d
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -d linyaohong yaohong  hong
# 备份数据库linyaohong里的blog和test表
mysqldump -uroot -p111111 -S /data/3306/mysql.sock linyaohong blog test
# 仅备份 库linyaohong 的 test 表结构
mysqldump -uroot -p111111 -S /data/3306/mysql.sock  -d  linyaohong test 
# 仅 备份 库linyaohong 的 test表
mysqldump -uroot -p111111 -S /data/3306/mysql.sock  linyaohong test 
# 仅仅 导出某个数据库中的数据SQL(可跟表导出某个表的数据SQL)
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -t linyaohong
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -t linyaohong test 
  
参数
mysqldump --help  # 有特别详细的英文说明
-A --all-databases    # 导出全部数据库
-B --databases        # 导出几个数据库。参数后面所有名字参量都被看作数据库名,(并添加crate和use语句)
-E --events           # 导出事件。
-F --flush-logs       # 开始导出之前刷新日志,请注意:假如一次导出多个数据库(使用选项--databases或者--all-databases),
                      # 将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。
                      # 在这种情况下,日志将会被刷新一次,相应的所以表同时被锁定。
                      # 因此,如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs				  
--single-transaction  # 适合innodb事务数据库的备份。保证备份的一致性,原理是设定本次会话的隔离级别为Repeatable read,
                      # 来保证本次会话(也就是dump)时,不会看到其它会话已经提交了的数据。
-d                    # 只备份表结构, 不导出任何数据
--master-data=1       # 不注释 用与备份文件做丛库
	--
	CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;
	--
--master-data=2       # 注释 单纯的做增量恢复,用2就可以,不执行
	--
	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107;
	--
-x --lock-all-tables  # 提交请求锁定所有数据库中的所有表,以保证数据的一致性。
                      # 这是一个全局读锁,并且自动关闭--single-transaction 和--lock-tables 选项
-l --lock-tables:    # 开始导出前,锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。
                      # 对于支持事务的表例如InnoDB和BDB,--single-transaction是一个更好的选择,
                      # 因为它根本不需要锁定表。请注意当导出多个数据库时,--lock-tables分别为每个数据库锁定表。 
                      # 因此,该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
-R --routines         # 备份函数和过程
--triggers            # 备份触发器
--hex-blob            # 将导出的内容处理成为2进制流的形式,如果库中有blob等字段,没加这个参数,大字段数据就会丢失
--ignore-table        #不导出指定表。指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名。例如:--ignore-table=database.table1 --ignore-table=database.table2 ……
--flush-privileges   #在导出mysql数据库之后,发出一条FLUSH PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候
--force     # 在导出过程中忽略出现的SQL错误
--comppact  备份文件取消注释,备份文件大大变小 DBUG模式
			忽略锁,忽略drop table 不建议用在生产环境
-q quict              # 不做缓冲查询,直接导出到标准输出
--default-character-set # 字符集设置 --default-character-set=gb2312 --default-character-set=utf8


保持数据的一致性

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

1、对于支持事务的引擎如InnoDB,参数上是在备份的时候加上--single-transaction保证数据一致性

设定本次的会话隔离级别为repeatable read 以确保本次会话(dump)时,不会看到其他会话已经提交了的数据
 
--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=2 --flush-log --B 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小时之内删除!

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