mysql dump恢复_mysql备份与还原之mysqldump

一.备份类型

逻辑备份：把数据导出到一个文档中(.sql/.txt)

适合场景：中小型数据库

物理备份：保存/usr/local/mysql/data数据库文件目录

适合场景：大型数据库

在线热备(冗余)：需要有多台机器，主备模式，集群模式

二.逻辑备份(mysqldump)

表级别备份

mysqldump [OPTIONS] database [tables]

库级别备份

mysqldump [OPTIONS] --databases DB1 [DB2 DB3...]

全库级别备份

mysqldump [OPTIONS] --all-databases [OPTIONS]

① 表级别备份

语法：mysqldump -p密码 数据库名称 表名称 > /tmp/mysqlbak/it_student.sql

案例：把db_itcast中的it_student表进行逻辑备份导出文件放入/tmp/mysqlbak目录下

第一步：创建目录

# mkdir /tmp/mysqlbak

第二步：备份数据表it_student

[root@localhost ~] # mysqldump -p123 db_itcast it_student > /tmp/mysqlbak/it_student.sql

② 表级别恢复

方法一：

第一步：登录MySQL

# mysql -uroot -p

第二步：选择数据库(因为这个表属于某一个数据库)

mysql > use db_itcast;

第三步：使用source 导出sql文件路径+名称

mysql > source /tmp/mysqlbak/it_student.sql

方法二：

# mysql -u root -p 数据库名称

③ 库级别备份

退出mysql，使用mysqldump进行库级别备份

语法：mysqldump -p123 --databases 数据库名称1 [数据库名称2] > /tmp/mysqlbak/db_itcast.sql

特别说明：

如果计算机中有多个MySQL实例，备份一定要指定备份那个MySQL

# mysqldump -S /tmp/mysql31.sock

案例：把db_itcast数据库进行备份操作

# mysqldump --databases db_itcast > /tmp/mysqlbak/db_itcast.sql -p

Enter password:

④ 库级别恢复

方法一：

第一步：登录MySQL

# mysql -uroot -p

第二步：删除原来的数据库

mysql > drop database 数据库名称;

第三步：使用source命令恢复文件

mysql > source /tmp/mysqlbak/db_itcast.sql

方法二：

# mysql -u root -p

⑤ 全库级别备份

基本语法：# mysqldump --all-databases [options] > /tmp/mysqlbak/all.sql

常用参数：

--flush-logs, -F 开始备份前刷新日志

--flush-privileges 备份包含mysql数据库时刷新授权表

--lock-all-tables, -x MyISAM一致性，服务可用性(针对所有库所有表)

--lock-tables, -l 备份前锁表(针对要备份的库)

--single-transaction 适用InnoDB引擎，保证一致性，服务可用性

--master-data=2  表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行；

--master-data=1  表示将二进制日志位置和文件名写入到备份文件,在dump文件中不注释这一行；

--master-data参数其他说明：

1)恢复时会执行，默认是1

2)需要RELOAD privilege并必须打开二进制文件

3)这个选项会自动打开--lock-all-tables，关闭--lock-tables

--single-transaction ：保持数据的一致性与完整性

--master-data ：开启使用二进制文件，默认关闭的

开启二进制文件：如果/usr/local/mysql目录下存在my.cnf文件，则直接修改。如果没有my.cnf文件，自己创建一个即可。

# vim my.cnf

# log-bin = /usr/local/mysql/data/mybinlog

# :wq

# service mysql restart

案例：对MySQL进行全库备份(开启二进制文件)

# mysqldump --all-databases --single-transaction --master-data > /tmp/mysqlbak/all.sql -p

⑥ 全库级别恢复

# 退出MySQL

# exit

# 使用mysql命令进行恢复

# mysql < /tmp/mysqlbak/all.sql -p

三.mysqldump+二进制日志实现增量备份

1、特殊说明

mysqldump默认情况下不支持增量备份的，其提供了表级、库级、全库级备份

如果想实现增量备份必须结合二进制日志文件

2、说说什么是增量备份

增量备份 = 全量备份 + 后期增加的数据(要备份的核心)=== 差异备份

3、二进制日志文件(类似日常生活中的日记)

① 错误日志 ：把mysql启动、运行、关闭过程中产生的异常写入到错误日志中$hostname.err

localhost.localdomain.err

在my.cnf配置文件中，可以通过如下形式进行定义：

# log-error = /usr/local/mysql/mysql.err

但是默认情况下，要特别注意：

第一种情况：如果定义了错误日志，但是没有这个文件，会报错，会导致数据库无法启动。

第二种情况：mysql.err没有数据写入，原因：权限问题 chown mysql.mysql mysql.err

② 一般查询日志(数据查询过程中产生的信息，了解)

当客户端连接或断开时，服务器会将信息写入该日志，并记录从客户端收到的每一条SQL语句。当您怀疑客户端的错误并想知道客户端发送给mysqld的确切信息时，一般查询日志可能非常有用。

默认情况下，一般查询日志是==被禁用==的。 如果要开启，可以使用以下参数：

general_log[={0|1}0表示禁用，1表示开启.

默认情况下，系统会在数据目录下创建以host_name.log命名的一般查询日志。如果要自己指定，可以使用以下参数：

指定一般查询日志的路径及文件名

general_log_file=/path/file.log

该参数用于定义general log和slow log的输出目标

log-output=[value,...]

value=[TABLE|FILE|NONE],默认值为FILE

TABLE表示将日志记录到表中，general_log表或者slow_log表中

FILE表示将日志记录的文本文件中

NONE表示不记录到表或者文件

注意：

如果log-output=NONE，则即使启用了日志，也不会写入条目。

如果log-output不等于NONE,但是没有启用日志也不会写入条目。

③ 二进制日志(binary log，非常非常重要)，我们对数据库的任何更改(增加、修改、删除)都会写入到二进制日志文件中。

在my.cnf配置文件中，可以通过如下形式进行定义：

# log-bin = /usr/local/mysql/data/mybinlog

一般情况下，二进制文件没有后缀

还有一点要特别注意：二进制日志没有办法直接通过cat命令查看，使用mysqlbinlog命令进行查看

4、具体增量备份步骤

① 首先要做全量备份

前提：先开启二进制文件(my.cnf)

# log-bin = /usr/local/mysql/data/mybinlog

# 全量备份

# mysqldump --all-databases --single-transaction --flush-logs --master-data=2 > /tmp/mysqlbak/all.sql -p

问题：如果以上语句报错，mysqldump: Got error: 1105: Unknown error when doing refresh

解决方案：代表mysql.err没有权限，chown mysql.mysql /usr/local/mysql/mysql.err

② 对数据库进行增删改操作

at 120 按时间

# delete from it_student where id=5;

# insert into it_student values (null,'廖星辰',25,'男','9999.00','yunwei');

# insert into it_student values (null,'上官婉儿',19,'女','6666.00','ui');

③ 对二进制文件进行备份

# show master status

# mysql> show master status;

+-----------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+----------+--------------+------------------+-------------------+

| mybinlog.000003 | 980 | | | |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

方法一：备份03这个二进制日志文件

# cp /usr/local/mysql/data/mybinlog.000003 /tmp/mysqlbak/

方法二：把03这个二进制日志文件导出成sql文件

# mysqlbinlog mybinlog.000003

从以上命令中我们可以看到备份点:# at 209

# mysqlbinlog --start-position=209 mybinlog.000003 > /tmp/mysqlbak/mybinlog.sql

④ 模拟故障

drop database myblog;

use db_itcast;

drop table it_student;

⑤ 数据恢复

第一步：先进行全量恢复

# mysql < /tmp/mysqlbak/all.sql -p

第二步：恢复增量备份的数据

方案一：直接source 增量的sql文件

# mysql > source /tmp/mysqlbak/mybinlog.sql

方案二：恢复二进制文件

# mysqlbinlog --start-position=209 /tmp/mysqlbak/mybinlog.000003 |mysql -p

Enter password:123

⑥ 测试验证

# select * from db_itcast.it_student;

二、逻辑导入导出(了解)

1、导入导出

只能针对数据表内容，最终结果：产生一个txt文本文档

2、案例：把it_student表中的数据全部导出

基本语法：select 字段列表 into outfile '存放路径+名称' from 数据表

# use db_itcast

# select * into outfile '/tmp/mysqlbak/it_student.txt' from it_student

默认报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

解决方案：出现以上错误，是因为我们没有设置导出的目录，在my.cnf中，设置以上目录即可

vim my.cnf

secure-file-priv = ''

chown -R mysql.mysql /tmp/mysqlbak

service mysql restart

恢复数据：

# 必须有一个表，因为它只保存了表中的内容

# load data local infile '/tmp/mysqlbak/it_student.txt' into table '数据表名称';

案例：把/etc/passwd文件，导入到以下表中:不要直接导入/etc/passwd，最好把/etc/passwd，最好把复制到/tmp/mysqlbak/password

CREATE TABLE`password` (

`uname`varchar(50) DEFAULT NULL,

`pass`char(2) DEFAULT NULL,

`uid`int(11) DEFAULT NULL,

`gid`int(11) DEFAULT NULL,

`comment`varchar(80) DEFAULT NULL,

`home`varchar(50) DEFAULT NULL,

`shell`varchar(50) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--fields-terminated-by=':' # 设置分隔符

--lines-terminated-by='\n' # \n代表遇到换行符就认为是一条记录的结束

/tmp/mysqlbak/password一共有7列

root:x:0:0:备注:/root:/bin/bash

导入：mysqlimport dbname /path/file -p

案例：把/tmp/mysqlbak/password文件导入到password表中

mysqlimport db_itcast --fields-terminated-by=':' --lines-terminated-by='\n' /tmp/mysqlbak/password -p