MySQL 备份

MySQL 备份

开启二进制日志
在 /etc/my.cnf 的 [mysqld] 配置域中添加如下配置内容

log-bin=/var/log/mysql/mysql-bin

/var/log/mysql/mysql-bin 是二进制日志文件的前缀。
例如完整的日志可能是
/var/log/mysql/mysql-bin-000001

备份用户需要的完整权限
select
reload # 允许使用该 FLUSH 语句，告诉服务器将授权表重新加载到内存中

lock tables
show view
event
trigger

process # 允许显示有关服务器内执行的线程的信息（即有关会话正在执行的语句的信息）

replication client # 读取二进制日志的位置信息，

普通文件方式
简单数据的导入和导出

编辑配置文件 my.cnf 添加如下配置项

[mysqld]
secure_file_priv = 保存到本地目录
例如

secure_file_priv = /tmp
配置完成后需要重启服务

本地目录需要给 mysql 用户授予读写权限

登录到服务器，可以验证配置项是否生效

SELECT @@GLOBAL.secure_file_priv;
或者
SHOW VARIABLES LIKE “secure_file_priv”;
假如没有使用此配置项，查询到的值为 NULL。

mysql> SELECT @@GLOBAL.secure_file_priv;
±--------------------------+
| @@GLOBAL.secure_file_priv |
±--------------------------+
| NULL |
±--------------------------+
1 row in set (0.00 sec)

执行下面的导入导出语句时就会报如下错误

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
导出
select * from school.class into outfile “/tmp/class2.db”;
导入
导入到数据文件中的列必须和表中的一一对应。包含 id 列的值。

实例文件

5,云计算1809
6,云计算1901
7,云计算1902
导入语句

载入外部“形式整齐”的数据(csv 格式的文件，没有域之家用逗号隔开)：
load data infile ‘文件完整名（含路径）’ into table 表名
fields terminated by ‘域分隔符’ optionally enclosed by ‘"’
ignore 1 lines;
optionally enclosed by ‘"’ 用于识别域值的界定符
ignore 1 lines; 忽略第一行

MySQL 逻辑备份 mysqldump
逻辑备份特点
备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
效率相对较低
在日常工作中，我们会使用 mysqldump 命令创建SQL格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主从复制等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句，也能生成其它分隔符的输出或XML格式的文件。

特点
自动记录position位置。
show master status\G;
可用性，一致性
锁表机制

用法
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

/查看帮助/
mysqldump --help
常见参数
–single-transaction 备份前启用一个事务，保证数据一致性。
仅对 InnoDB 存储引擎有效。还有需要保证没有其他的连接正在使用以下语句： ALTER TABLE， CREATE TABLE，DROP TABLE， RENAME TABLE， TRUNCATE TABLE

-l, --lock tables 对于不支持事务的存储引擎的表备份使用此选项，比如 MySAM, 可以保证备份期间的数据一致性。会依次对正在备份的每个数据库中的所有表进行锁表操作，此时只可以读。和 --single-transaction 互斥。

-x, --lock-all-tables 锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。

有几个选项控制mysqldump如何 处理存储的程序（存储过程和函数，触发器和事件）：

–events：备份事件计划程序事件

–routines：备份存储过程和函数

–triggers：备份表的触发器

–triggers默认情况下启用 该选项，以便在转储表时，它们伴随着它们具有的任何触发器。

默认情况下要禁用这些选项。需要明确设置这些选项：
–skip-events， --skip-routines, --skip-triggers。

日常用法
备份所有库

// 先配置用户名和密码
shell> vi ~/.mysql_user
[mysqldump]
user=root
password=123

shell> mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > date +%FT%H_%M_%Sdump_all.sql

不包含 INFORMATION_SCHEMA,performance_schema,sys

备份指定的多个库

// 为了考虑篇幅，请自行添加指定用户名密码参数和指定服务器的参数
// --defaults-file=~/.mysql_user -hip
shell> mysqldump --databases db1 db2 db3 > date +%FT%H_%M_%Sdump_all.sql

备份指定库的指定几个表

shell> mysqldump db1 t1 t3 t7 > dump.sql
其他参数

–master-data=0|1|2

服务器的二进制日志必须打开

0 不记录二进制日志文件及位置：

1 以CHANGE MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器：

2 以CHANGE MASTER TO 的方式记录位置，但默认被注释：

–dump-slave 用于在slave上dump数据，建立新的slave。因为我们在使用mysqldump时会锁表，所以大多数情况下，我们的导出操作一般会在只读备库上做，为了获取主库的Relay_Master_Log_File(二进制日志)和Exec_Master_Log_Pos(主服务器二进制日志中数据所处的位置)，需要用到这个参数，不过这个参数只有在5.7以后的才会有

–no-data, -d 不导出任何数据，只导出数据库表结构