MySQL数据库备份与恢复基础
前言
1.备份数据的意义
运维工作的核心简单概括起来就是两件事:第一个是保护公司的数据,第二个是让网站能够7*24小时提供服务。
虽然这两件事情都很重要,但是相比较而言,丢失一部分数据和让网站7*24小时提供服务,哪个更重要呢?
对于绝大多数企业来讲,失去数据就相当于失去商机,失去产品,失去客户,甚至会造成公司倒闭,那么,在所有的数据中,最核心的数据又是哪些呢?这恐怕要属数据库中的数据了。既然数据库中的数据地位这么高,那么数据库备份与恢复的重要性就不言而喻了。
2.使用mysqldump进行数据库备份
mysql数据库自带了一个很优秀的备份命令,即mysqldump。
2.1.不带参数备份单个数据库
mysqldump oldboy >/opt/mysql_bak.sql
2.2.加-b参数备份
mysqldump -b oldboy >/opt/mysql_bak_b.sql #b参数的作用是增加创建数据库和连接数据库的语句,可同时备份多个库。
2.3.使用gzip压缩备份数据库
mysqldump -b oldboy|gzip>/opt/mysql_bak_b.gz
2.4.mysqldump命令工作原理
利用mysqldump命令备份数据的过程,实际上就是把数据(包括库表)从mysql库里以sql语句的形式直接输出或者生成备份文件的过程,这种备份成sql语句的方式称为逻辑备份。
使用mysqldump命令可以把数据库中的数据导出来,并通过sql语句的形式存储。这种备份方式称为逻辑备份,效率不是很高。在当下的生产场景中,多用于数据量不是很大的备份情况,例如30gb以内的数据。若在数据库数据很大的时候采用此备份方法,则所用的时候就会很长,恢复的时间也会很长,因此,当数据大于30gb后,建议选择其他的诸如xtrabackup的物理方式进行备份和恢复。
2.5.备份多个库
2.5.1.备份多个库
mysqldump -b oldboy oldboy_utf8 mysql|gzip>/opt/all.sql.gz
2.5.2.如何做分库备份
分库备份实际上就是每次只执行一个mysqldump备份命令语句备份一个库,如果数据库里有多个库,就执行多条相同的语句来备份各个库。
mysqldump -uroot -p'oldboy123' -b oldboy |gzip >/tmp/oldboy.sql.gz mysql -e "show databases;" |egrep -v "_schema|atabase" |sed -r 's#^(.*)#mysqldump -b \1 |gzip >/tmp/\1.sql.gz#g' |bash #bash执行这些备份命令,就是一次分开备份多个库了
2.6.备份单个表
当不加-b参数备份数据库时,例如“mysqldump oldboy test”,mysqldump命令默认就会把oldboy当作库,把test当作表,如果后面还有多个字符串,例如“mysqldump oldboy test test1“,那么除了oldboy为库之外,其他的test、test1都是oldboy库的表。
mysqldump oldboy test>/tmp/oldboy_test,sql
2.7.备份多个表
分表备份的缺点:数据文件多,很碎,一旦需要全部恢复又很麻烦。
1、做一个完整备份,再做一个分库分表备份。
2、虽然文件多、碎,但可以利用脚本批量操作多个sql文件。
mysqldump mysql user db>/tmp/mysql.sql egrep -v "#|\*|--|^$" /tmp/mysql.sql mysqldump oldboy test > /tmp/oldboy_test.sql
2.8.备份数据库表结构(不包含数据)
利用mysqldump的-d参数可以只备份表的结构,即建表的语句。
mysqldump -d oldboy >/opt/oldboy.sql
2.9.只备份数据库表的数据(不包含表结构)
利用-t参数备份数据库表的数据(sql语句形式)。
mysqldump -t oldboy >/opt/oldboy1.sql
2.10.同时将数据和表结构分离导出
利用-t参数可以实现将数据和表结构同时分离备份。
vi /etc/my.cnf secure_file_priv='' #在[mysqld]模块下增加 /etc/init.d/mysqld restart mysqldump oldboy test --compact -t /tmp/
1、-d参数的作用是只备份库表结构(sql语句形式)。
2、-t参数的作用是只备份表内的数据(sql语句形式)。
3、-t将库表和数据分离成不同的文件,数据是纯文本,表结构是sql语句。
2.11.刷新binlog文件参数(-f)
2.11.1.binlog是什么
binlog是一个二进制格式的文件,用于记录用户对数据库更新的sql语句信息,例如更改数据库库表和更改表内容的sql语句都会记录到binlog里,但是对库表等内容的查询则不会记录到日志中。
2.11.2.binlog对于备份的作用
当有数据写入到数据库时,还会同时把更新的sql语句写入到对应的binlog文件里。
使用mysqldump备份时,一般是对某一时刻的数据进行全备,例如,0点进行数据库备份。
假设是每天0点对数据库进行备份,那么在两次备份之间就有24小时的数据没有备份,在这期间如果数据库发生故障,使用mysqldump全量恢复也只能恢复到当日0点,但是有了binlog文件,就可以将两次完整备份间隔之间的数据还原,因为binlog文件里的数据就是写入数据库的数据,使用binlog文件恢复数据,我们称之为二进制增量数据恢复。
2.11.3.为什么要刷新binlog
刷新(切割)binlog日志的目的就是确定全备和增量备(binlog文件)的临界点,当全备完成后,全备时刻以前的binlog文件就无用了(全备里已有这部分数据了),但是全备以后到下一次全备之前的数据就是十分重要的,这部分数据就存在于binlog文件里,因此在进行全备时需要找到全备之后和binlog增量之间的临界点,使得恢复时,需要的binlog文件数据一条不多(不能和全备的内容重合),一条不少(全备后的所有数据都要有)。