MySQL数据备份操作步骤

常见的数据备份命令

备份命令	备份速度	恢复速度	介绍	功能	适用场景
lvm2快照	快	快	一般、支持几乎热备、速度快	一般	中小型数据量的备份
cp	快	快	物理备份、灵活性低	很弱	少量数据备份
xtrabackup	较快	较快	实现innodb热备、对存储引擎有要求	强大	较大规模的备份
mysqldump	慢	慢	逻辑备份、适用所有存储引擎	一般	中小型数据量备份

常用的备份工具mysqldump

mysqldump客户端使用程序执行 逻辑备份，生成一组 SQL 语句，可以执行这些语句以重现原始数据库对象定义和表数据 。它转储一个或多个 MySQL 数据库以进行备份或传输到另一台 SQL 服务器。mysqldump 命令还可以生成 CSV、其他分隔文本或 XML 格式的输出 。对整个数据库、数据库结构和数据的备份，保存的是完整时刻的数据库，是差异备份与增量备份的基础。但是数据存在大量的重复，占用大量的备份空间，而且备份时间和恢复时间长。

mysqldump基本操作

使用 mysqldump -help来查看基本语法

 mysqldump -help

选项含义

登录选项：

• -u user：指定用户
• -h host：指定主机
• -p：表示要使用密码
• -E, --events: 备份事件调度器
• -R, --routines: 备份存储过程和存储函数

备份选项：

• –all-databases：备份所有数据库
• –databases db1 db2：备份指定的数据库
• –single-transaction：对事务引擎执行热备
• –flush-logs：更新二进制日志文件
• –master-data等于1 => 每备份一个库就生成一个新的二进制文件(默认) 等于2 => 只生成一个新的二进制文件
• –quick：在备份大表时指定该选项

导出所有数据库

语法

mysqldump -u用户-p密码 --all-databases >输出文件地址

实战

mysqldump -uroot -proot --all-databases >/backup/all.sql

导出指定数据库的所有数据

语法

mysqldump -u用户 -p密码 --databases 指定数据库1 指定数据库2 >SQL文件输出地址

实战

mysqldump -uroot -proot --databases db1 db2 >/backup/user.sql

导出指定数据库中的表

指定表只能针对一个数据库进行导出，且导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据语句。

语法

mysqldump -u用户 -p密码 --databases 指定数据库1 --tables 指定表1 指定表2 >SQL文件输出路径地址

实战

mysqldump -uroot -proot --databases db1 --tables a1 a2  >/backup/db1.sql

条件导出

导出表中id=1的数据

语法

mysqldump -u用户 -p密码 --databases 指定数据库1 --tables 指定表1 --where='条件语句'  >SQL文件输出地址

实战

mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1'  >/backup/a1.sql

仅导出表结构不导出数据

语法

mysqldump -u用户 -p密码 --no-data --databases 指定数据看 >SQL文件输出地址

实战

mysqldump -uroot -proot --no-data --databases db1 >/backup/db1.sql

跨服务器导出导入数据

将服务器1中的数据库的所有数据导入到服务器2中的数据库中，服务器2所指的数据库必须存在。

语法

mysqldump --host=源主机IP -u用户 -p密码 --databases 指定数据库 |mysql --host=目标主机IP -u用户 -p密码 指定数据库

实战

mysqldump --host=127.x.x.x -uroot -proot --databases db1 |mysql --host=127.x.x.x -uroot -proot db2

mysqldump --host=127.x.x.x -uroot -proot -C --databases test |mysql --host=127.x.x.x -uroot -proot test //加上-C参数可以启用压缩传递。

恢复数据

create database school;
use school;
source /backup/all.sql;

mysql -uroot -p < /backup/all.sql;