mysql中备份数据库之mysqldump

备份分类

根据能否停用数据库,可以将备份类型分为:

①、冷备:数据库服务停止后备份

②、温备:只能对数据库进行读操作,不能进行写操作

③、热备:在线备份,可以对数据库进行读写操作

④、暴力备份:直接停止mysql服务,把data目录备份到其它目录。。。。。。


根据是直接备份数据库文件还是数据库里面的数据,可以将备份类型分为:

①、物理备份:物理备份就是备份数据文件了,比较形象点就是copy下数据文件,但真正备份的时候自然不是copy这么简单。可以简单粗暴的理解为(直接复制数据库文件


②、逻辑备份:逻辑备份是备份sql语句,将sql语句导出到文件中。在恢复的时候执行备份的sql语句实现数据库数据的重现。

逻辑备份和物理备份的优缺点:逻辑备份和物理备份各有优劣,一般来说,物理备份恢复速度比较快,占用空间比较大。逻辑备份速度比较慢,占用空间比较小。逻辑备份的恢复成本高。


逻辑备份中的常用代表工具mysqldump、mydumper

物理备份中的常用代表工具:xtrabackup


根据备份是备份全部内容还是部分内容,可以将备份类型分为:

①、完全备份:full backup,备份全部数据

②、差异备份:fidderential backup,只备份上次完全备份以来变化的数据

③、增量备份:incrementl backup,只备份上次完全增量备份以后的数据

注:数据库备份策略一般是完全备份+增量备份,或者完全备份+差异备份

备份工具

mysqldump:mysqldump是逻辑备份工具。

当mysqldump进行温备份的时候,备份时需要加上一个全局锁,客户端只能进行读操作。


mysqlbackup:对于innodb引擎的表mysqlbackup可以进行热备份;但是对于非innodb表mysqlbackup就只能进行温备份了。

注:innodb引擎热备份,myisam引擎温备份。

mysqldump工具的简单使用说明

mysqldump工具的备份(导出)操作

1、备份整个数据库

#备份test数据库下的所有数据表(包括表结构和表对应的数据),并将备份文件输出到/home/test.sql
mysqldump -h 127.0.0.1 -u root -p test > /home/test.sql

#回车后,接着输入你的mysql root账号的密码就可以了


2、备份数据库中的某个表

#备份test数据库中的user表和student表
mysqldump -h 127.0.0.1 -u root -p test user,student > /home/back_db.sql


3、备份多个数据库

#备份test数据库和test2数据库
mysqldump -h 127.0.0.1 -u root -p --databases test test2 > /home/back_db.sql


4、备份系统中所有数据库

mysqldump -h 127.0.0.1 -u root -p --all-databases > /home/back_all_db.sql

mysqldump工具的恢复(导入)操作

#将/home/test.sql文件中的备份内容恢复到test数据库下,如果提示你Unknown database 'test',那你就创建一个test的数据库

mysql -f -h 127.0.0.1 -u root -p test < /home/test.sql

mysqldump工具命令行的更多选项说明

可以使用:mysqldump --help 命令来查看mysqldump工具的更多使用选项使用说明。


列举一些mysqldump工具常用的选项说明如下:

常用选项:
--add-drop-database:每个数据库创建之前添加drop数据库语句
-A,--all-databases: Dump all the databases
-B,--databases: --databases db1 db2... 备份指定的多个库
--x,--lock-all-tables:请求锁定所有表之后在备份
-l,--lock-tables:锁定指定的表
--single-transaction:能够对InnoDB存储引擎实现热备
--events:为备份的数据库备份事件
--routines:备份routines(存储过程和函数)
--triggers:为每个备份的表备份trigger
-q,--quick:快速导出,备份时逐行读取表而非一次全部行后缓冲在内存中。在备份大表时有用。
-F,--flush-logs:备份前,请求到锁之后滚动日志
--all-tablespaces,-Y: 备份所有表空间
--force, -f:即使发现sql错误,仍然继续备份
--no-data, -d:只导出表结构
--tables:覆盖 --databases or -B选项,后面所跟参数被视作表名
--add-drop-table:在每个创建数据库表语句前添加删除数据库表的语句
--add-locks:备份数据库表时锁定数据库表;
--comments:添加注释信息;
--compact:压缩模式,产生更少的输出
--complete-insert:输出完成的插入语句
--default-character-set:指定默认字符集
--host:指定要备份数据库的服务器;
--lock-tables:备份前,锁定所有数据库表
--no-create-db:禁止生成创建数据库语句
--no-create-info:禁止生成创建数据库库表语句
--password:连接MySQL服务器的密码
--port:MySQL服务器的端口号
--user:连接MySQL服务器的用户名
--socket,-S:指定连接mysql的socket文件位置

--master-data=[0|1|2]:记录二进制日志文件及位置
0表示不记录
1表示记录change master语句
2记录为注释的change master语句

--opt是quick,add-drop-table,add-locks,extended-insert,lock-tables几个参数的合称,一般都要使用

其它说明

为了备份数据库中数据的完整性,一般在使用备份工具备份的时候会在mysql中执行锁定整个数据库的操作,锁定后整个数据库都只能读不能写,如果在锁定整个数据库过程中有写的请求进入mysql,那这个写的操作就会被阻塞在哪里一直等待,知道释放整个数据库的锁,之前阻塞在哪里的写的操作才会执行。


锁定整个数据库,整个数据库只能进行读操作:

mysql> flush tables with read lock;

-- 如果在锁定整个数据库期间,有写数据的操作进来需要写入数据,那写数据的操作不会被执行,而是会被阻塞等待在哪里进行等待。


恢复整个数据库的写的操作:

mysql> unnlock tables;

-- 恢复数据库的写操作。如果之前在锁定整个数据库的操作过程中,有写的操作,那此时之前被阻塞在哪里进行等待写数据的操作,才会执行。自己可以试验一下就知道啦

尾声

总结:mysqldump备份方式是采用的逻辑备份,其最大的缺陷是备份和恢复速度较慢,如果数据库大于50G,mysqldump备份就不太适合。


使用:一般情况下都会配合编写shell脚本兼crontab定时任务进行数据的备份,当然也要看具体场景了。反正想备份就备份喽~ 



在光怪陆离的城市拼尽全力  最难的却是不忘初心    -->东野圭吾【解忧杂货店】



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与