MySQL 使用 mysqldump 命令备份数据

2024-05-07 21:45:40

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

一、语法

mysqldump -help

Usage: mysqldump [OPTIONS] database_name [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

mysqldump [选项] 数据库名 [表名] > 脚本名
或
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
或
mysqldump [选项] --all-databases [选项] > 脚本名

选项说明

--host,-h 服务器IP地址
--port,-P 服务器端口号
--user,-u MySQL 用户名
--pasword,-p MySQL 密码
--databases 指定要备份的数据库
--all-databases 备份mysql服务器上的所有数据库
--compact 压缩模式，产生更少的输出
--comments 添加注释信息
--complete-insert 输出完成的插入语句
--lock-tables 备份前，锁定所有数据库表
--no-create-db/--no-create-info 禁止生成创建数据库语句
--force 当出现错误时仍然继续备份操作
--default-character-set 指定默认字符集
--add-locks 备份数据库表时锁定数据库表

二、常见用法

导出 test 数据库里面的users表的表数据和表结构
mysqldump -u[用户名] -h[ip] -p[密码] -P[端口号] 数据库名表名 >导出的文件名.sql

# mysqldump -uroot -h127.0.0.1 -proot -P3306 test users>/data/user.sql

导出包括系统数据库在内的所有数据库（all.sql默认保存在bin文件夹下面）

# mysqldump -uroot -proot --all-databases>all.sql

导出多张表：

# mysqldump -uroot -proot --databases test --tables t1 t2>two.sql

只导出表结构不导表数据，添加 -d 命令参数

# mysqldump -uroot -h127.0.0.1 -proot -P3306 -d test users>d:/user.sql

只导出表数据不导表结构，添加 -t 命令参数

# mysqldump -uroot -h127.0.0.1 -proot -P3306 -t test users>d:/user.sql

只导出test数据库的表结构

导出：mysqldump -uroot -proot --no-data --databases test>s1.sql
导入：mysql -uroot -proot -h127.0.0.1 -P3306 test<s1.sql

三、备份脚本

#!/bin/bash
#NAME:数据库备份
#DATE:*/*/*
#USER:***

#设置本机数据库登录信息
mysql_user="user"
mysql_password="passwd"
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8mb4"
date_time=`date +%Y-%m-%d-%H-%M`

#保存目录中的文件个数
count=10
#备份路径
path=/***/

#备份数据库sql文件并指定目录
mysqldump --all-databases --single-transaction --flush-logs --master-data=2 -h$mysql_host -u$mysql_user -p$mysql_password > $path_$(date +%Y%m%d_%H:%M).sql
[ $? -eq 0 ] && echo "-----------------数据备份成功_$date_time-----------------" || echo "-----------------数据备份失败-----------------"

#找出需要删除的备份
delfile=`ls -l -crt $path/*.sql | awk '{print $9 }' | head -1`
#判断现在的备份数量是否大于阈值
number=`ls -l -crt $path/*.sql | awk '{print $9 }' | wc -l`
if [ $number -gt $count ]
then
          rm $delfile  #删除最早生成的备份，只保留count数量的备份
                                           #更新删除文件日志
            echo "-----------------已删除过去备份sql $delfile-----------------"
fi