看完这篇MySQL备份的文章,再也不用担心删库跑路了

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 看完这篇MySQL备份的文章,再也不用担心删库跑路了

1 数据库备份和恢复概念

可以根据不同的类型来划分备份的方法。根据备份的方法可以分为:

  • 热备:在数据库运行中直接备份
  • 冷备:在数据库停止的时候进行备份
  • 温备:在数据库运行时进行备份,但是会对当前数据库的操作有所影响

如果按照备份后文件的内容,可以分为:

  • 逻辑备份:备份出的文件内容是可读的,一般是文本文件,比如是一条条的SQL语句
  • 裸文件备份:复制数据库的物理文件

按照备份数据库的内容来分,可以分为:

  • 完全备份:对一个数据库进行一个完整的备份
  • 增量备份:在上一次完全备份的基础上,对更改的数据进行备份
  • 日志备份:MySQL数据库二进制文件的备份

2 逻辑备份

2.1 mysqldump

mysqldump备份工具是mysql提供的,在安装mysql之后,在bin目录下就存在这个备份工具,通常用于完成转存数据库的备份以及不同数据库之间的移植。

语法

当我们直接在cmd命令行下进行备份时,可能是远程的数据库,所以我们这个时候是需要知道数据库所在服务器的ip地址,以及用户名和密码的,这样就可以完成备份的基本操作,下面是基本的格式。

mysqldump -h地址ip -P(大写)端口 -u用户名 -p密码 数据库名 [arguments] > d:XX.sql(路径)

实例

mysqldump -h127.0.0.1 -P3306 -uroot -p123 user > user.sql;

上面这个例子就是将本机的user数据库进行备份,如果需要更加详细的备份,就需要添加其他的参数进行设置了。

  • 备份所有数据库
mysqldump -h127.0.0.1 -P3306 -uroot -p123 --all-databases > user.sql;
  • 备份指定数据库
mysqldump -h127.0.0.1 -P3306 -uroot -p123 user > user.sql;
参数详情

请参考这篇文章:mysqldump工具命令参数大全

2.2 SELECT … INTO OUTFILE 备份

这种方法也是逻辑备份的一种方法。

语法

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

字段解释

(1)TERMINATED BY用来指定字段值之间的符号,例如,“TERMINATED BY ‘,’”指定了逗号作为两个字段值之间的标志。

(2)ENCLOSED BY子句用来指定包裹文件中字符值的符号,例如,“ENCLOSED BY ' " '”表示文件中字符值放在双引号之间,若加上关键字OPTIONALLY表示所有的值都放在双引号之间。

(3)ESCAPED BY子句用来指定转义字符,例如,“ESCAPED BY '#'”“#”指定为转义字符,取代“\”
(4)STARTING BY 'string'一行以什么字符开头。
(5)TERMINATED BY 'string'一行以什么字符结尾。

举例

SELECT
    * INTO OUTFILE '/data/test.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM
    t_fulltext;

执行之后发现一个问题:

The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

查看了一下,mysql文件的导入和导出路径有默认的设置,即 secure-file-priv,当传入的csv文件路径与默认的路径冲突时就会报错。

secure-file-priv的值有三种情况:

  • secure_file_prive=null ––限制mysqld 不允许导入导出
  • secure_file_priv=/path/ – –限制mysqld的导入导出只能发生在默认的/path/目录下
  • secure_file_priv=’’ – –不对mysqld 的导入 导出做限制

接着使用show variables like '%secure%';,查看一下。

发现是NULL,所以不允许。

通过打开my.ini配置文件进行查看及修改secure-file-priv,设置你需要的目录即可。

3 备份恢复

3.1 mysqldump如何恢复

对于这种恢复实际上是非常简单的,这是因为导出的就是sql语句,所以,只是需要执行一下sql语句就没有问题了。

mysql -uroot -p < test.sql

或者下面的方法:

source test.sql

3.2 SELECT INTO OUTFILE数据如何恢复

这种恢复跟上面的方式的恢复就有点不一样了,因为导出之后的就是单纯的数据,没有表结构,所以我们需要另外的方式:使用命令LOAD DATA INFILE来进行导入。

我们看一下这种方式的语法:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]

举例

load data infile 'test.txt' into table test;

注意:如果用SELECT INTO OUTFILE导出数据的时候,有特殊处理的,比如,以换行符结尾、字段被”包裹了,这时候,就需要相应的参数了,否则就会报错。

3.3 mysqlimport

mysqlimport就是MySQL数据库提供的一个命令行程序,从本质上来说,是LOAD DATA INFILE的命令接口。

LOAD DATA INFILEmysqlimport命令可以从文件中把格式化的数据导入数据库,如果有一个包含大数据量的文件,可以实现快速的数据导入。

基本用法

mysqlimport -u root -pPassword dbname filename1.txt [filename2.txt...] [options]

options选项

选项 说明
-d 或者 –delete 新数据导入数据表中之前删除数据数据表中的所有信息
-f 或者 –force 不管是否遇到错误,mysqlimport将强制继续插入数据
-i 或者 –ignore mysqlimport跳过或者忽略那些有相同唯一关键字的行, 导入文件中的数据将被忽略。
-l 或者 -lock-tables 数据被插入之前锁住表,这样就防止了,你在更新数据库时,用户的查询和更新受到影响。
-r 或者 -replace 这个选项与-i选项的作用相反;此选项将替代表中有相同唯一关键字的记录。
–fields-enclosed-by= char 指定文本文件中数据的记录时以什么括起的, 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
–fields-terminated-by=char 指定各个数据的值之间的分隔符,在句号分隔的文件中, 分隔符是句号。您可以用此选项指定数据之间的分隔符。默认的分隔符是跳格符(Tab)
–lines-terminated-by=str 此选项指定文本文件中行与行之间数据的分隔字符串或者字符。 默认的情况下mysqlimport以newline为行分隔符。您可以选择用一个字符串来替代一个单个的字符:一个新行或者一个回车。
–use-threads 并行的读取多个文件

更多参数使用mysqlimport -help查看。

举例

这里就使用并发的读取多个文件的数据。

mysqlimport --use-thread=2 test t1.txt t2.txt

3.4 二进制日志备份与恢复

首先想要开启二进制日志需要配置,配置如下:

[mysqld]
log-bin = mysql-bin
sync_binlog = 1
innodb_support_xa = 1

恢复二进制日志我们可以使用下面的命令恢复:

mysqlbinlog [options] log_file...

options参数请使用help查看。

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 看完这篇MySQL备份的文章,再也不用担心删库跑路了


 上一篇
深入理解Java虚拟机-常用vm参数分析 深入理解Java虚拟机-常用vm参数分析
Java虚拟机深入理解系列全部文章更新中… 深入理解Java虚拟机-Java内存区域透彻分析 深入理解Java虚拟机-常用vm参数分析 深入理解Java虚拟机-JVM内存分配与回收策略原理,从此告别JVM内存分配文盲 深入理解Java
下一篇 
MySQL事务,这篇文章就够了 MySQL事务,这篇文章就够了
在看这篇文章之前,我们回顾一下前面的几篇关于MySQL的系列文章,应该对你读下面的文章有所帮助。 InnoDB与MyISAM等存储引擎对比 面试官问你B树和B+树,就把这篇文章丢给他 MySQL的B+树索引的概念、使用、优化及使用场