🐧运维笔记
首页  /  数据库  /  正文

MySQL 常用管理工具与备份恢复

数据库 2026-10-02📖 12 分钟👁 —
🐬 数据库 · MySQL第 10 / 12 页123456789101112📖 完整导航 →

一、系统数据库介绍

有些客户端工具默认把 MySQL 的系统库隐藏了，想去翻它们，就得在数据源那一步把勾打上：

数据源属性里把 information_schema、mysql、performance_schema 这些系统库勾上
四个系统库分别存什么
数据库含义
mysql存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
information_schema提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
performance_schema为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys包含了一系列方便 DBA 和开发人员利用 performance_schema 性能数据库进行性能调优和诊断的视图

二、常用工具 1

1. mysql

主要用在脚本文件当中（shell）。

# 该mysql不是指mysql服务，而是指mysql的客户端工具。

语法：
    mysql [options] [database]

选项：
    -u, --user=name         # 指定用户名
    -p, --password=[name]   # 指定密码
    -h, --host=name         # 指定服务器IP或域名，连接远程 mysql
    -P, --port=port         # 指定连接端口
    -e, --execute=name      # 执行SQL语句并退出

-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，
对于一些批处理脚本，这种方式尤其方便

示例：
    mysql -uroot -p123456 db01 -e "select * from stu";

# 适用于 shell 脚本中批量查询或导出数据

# 使用场景
# 交互模式：日常开发调试，需要多条 SQL 逐步执行
# 非交互模式：cron 定时任务、数据导出脚本、自动化运维

2. mysqladmin

mysqladmin --help 的选项列表和两个示例

3. mysqlbinlog（运维篇会详写）

# 由于服务器生成的二进制日志文件以二进制格式保存
# 所以如果想要检查这些文本的文本格式
# 就会使用到mysqlbinlog 日志管理工具

语法：
    mysqlbinlog [options] log-files1 log-files2 ...

选项：
    -d, --database=name           # 指定数据库名称，只列出指定的数据库相关操作
    -o, --offset=#                # 忽略掉日志中的前n行命令
    -r, --result-file=name        # 将输出的文本格式日志输出到指定文件
    -s, --short-form              # 显示简单格式，省略掉一些信息
    --start-datetime=date1 --stop-datetime=date2    # 指定日期间隔内的所有日志
    --start-position=pos1 --stop-position=pos2      # 指定位置间隔内的所有日志

# 二进制日志在 /var/lib/mysql
-rw-r-----  1 lxd  docker  2996555 Aug 14 18:33  binlog.000001
-rw-r-----  1 lxd  docker    36540 Aug 22 20:41  binlog.000002
-rw-r-----  1 lxd  docker     1120 Aug 24 17:47  binlog.000003
# 二进制文件会自动定期清理
# 二进制文件是无法查看的，需要利用 mysqlbinlog 查看
root@VM-0-12-ubuntu:/opt/mysql/data# mysqlbinlog bnlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'bnlog.000001' not found (OS errno 2 - No such file or directory)
ERROR: Could not open log file
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

上面这段是我当时把 binlog 敲成了 bnlog，所以报了 not found，正确写法是 mysqlbinlog binlog.000001，输出里的报错我就原样留着了。

4. mysqlshow

# mysqlshow 客户端对象查找工具
# 用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法：
    mysqlshow [options] [db_name [table_name [col_name]]]

选项：
    --count     # 显示数据库及表的统计信息（数据库，表均可以不指定）
    -i          # 显示指定数据库或者指定表的状态信息

示例：
    # 查询每个数据库的表的数量及表中记录的数量
    mysqlshow -uroot -p2143 --count

    # 查询test库中每个表中的字段名，及行数
    mysqlshow -uroot -p2143 test --count

    # 查询test库中book表的详细情况
    mysqlshow -uroot -p2143 test book --count

# 示例
root@VM-4-16-ubuntu:/etc/mysql/conf.d# mysqlshow -uroot -p itcast score -i
Enter password: 
Database: itcast  Table: score
+---------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field   | Type        | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+---------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id      | int         |                    | NO   | PRI |         | auto_increment | select,insert,update,references |         |
| name    | varchar(20) | utf8mb4_0900_ai_ci | YES  |     |         |                | select,insert,update,references |         |
| math    | int         |                    | YES  |     |         |                | select,insert,update,references |         |
| english | int         |                    | YES  |     |         |                | select,insert,update,references |         |
| chinese | int         |                    | YES  |     |         |                | select,insert,update,references |         |
+---------+-------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+

三、常用工具 2

1. mysqldump 数据备份

这是之前配合全局锁备份数据库的命令，这里是另一种写法，加了一些扩展内容。

# mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移
# 备份内容包含创建表，及插入表的SQL语句

# 热备份
mysqldump -uroot -p --single-transaction --quick itcast > /var/lib/mysql-files/itcast.sql

语法：
    mysqldump [options] db_name [tables]
    mysqldump [options] --database/-B db1 [db2 db3...]
    mysqldump [options] --all-databases/-A

连接选项：
    -u, --user=name         # 指定用户名
    -p, --password=[name]   # 指定密码
    -h, --host=name         # 指定服务器ip或域名
    -P, --port=#            # 指定连接端口

输出选项：
    --add-drop-database     # 在每个数据库创建语句前加上 drop database 语句
    --add-drop-table        # 在每个表创建语句前加上 drop table 语句，默认开启；不开启(--skip-add-drop-table)
    -n, --no-create-db      # 不包含数据库的创建语句
    -t, --no-create-info    # 不包含数据表的创建语句
    -d, --no-data           # 不包含数据（只有建表语句没有insert）
    -T, --tab=name          # 自动生成两个文件：一个.sql文件，创建表结构的语句；一个.txt文件，数据文件
    # txt是按照指定格式记录的数据
# 加了 -T 就不用写备份文件名了
mysqldump -uroot -p123321 --single-transaction --quick  -T /var/lib/mysql-files/ itcast

如果是单纯的 sql 文件，导入时会一句句执行 sql，很慢；换成 txt 就非常快，但是导入起来复杂很多。

2. 数据备份 操作演示

先看一下变量（这是 MySQL 信任的备份目录，建议备份到这里）：

mysql> show variables like '%secure_file%'
    -> ;
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |# 需要创建
+------------------+-----------------------+
1 row in set (0.004 sec)

mkdir -p /var/lib/mysql-files/

root@VM-4-16-ubuntu:/etc/mysql/conf.d# mysqldump -uroot -p --single-transaction --quick itcast>/var/lib/mysql-files/itcast.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

这条警告的意思是：从带 GTID 的服务器导出的部分转储，默认会把所有事务的 GTID 都带上，包括那些改了被忽略部分的事务。如果不想恢复 GTID，就加上 --set-gtid-purged=OFF；要做一个完整的转储，就带上 --all-databases --triggers --routines --events。

3. mysqlimport 数据导入

把备份数据还原成正常的数据库文件，用的是 mysqlimport 或者 source：

# mysqlimport 是客户端数据导入工具
# 用来导入 mysqldump 加 -T 参数后导出的文本文件

# 导入 txt 语法
    mysqlimport [options] db_name textfile1 [textfile2...]

# 导入 sql 语法（在 mysql 客户端内执行）
    source /root/xxxxx.sql



# 演示（docker 容器内可能没有 mysqlimport 命令）
# 1. 使用 -T 参数导出表结构和数据（每个表生成 .sql 和 .txt 两个文件）
docker exec mysql01 mysqldump -uroot -p123321 --single-transaction --quick -T /var/lib/mysql-files/ itcast

# 2. 查看导出的文件
docker exec mysql01 ls -lh /var/lib/mysql-files/

# 3. 导入 txt 数据文件（需容器内有 mysqlimport 命令）
docker exec mysql01 mysqlimport -uroot -p123321 itcast /var/lib/mysql-files/user.txt

# 若容器内没有 mysqlimport，改用 LOAD DATA 语句导入
docker exec -i mysql01 mysql -uroot -p123321 itcast -e "LOAD DATA INFILE '/var/lib/mysql-files/user.txt' INTO TABLE user;"
# 表恢复了


# 备份为普通 .sql 文件（重定向到宿主机）
docker exec mysql01 mysqldump -uroot -p123321 --single-transaction --quick itcast > /tmp/itcast.sql

# 删除 itcast 库的所有表后恢复
# 方法一：复制到容器内，进入 mysql 客户端执行 source
docker cp /tmp/itcast.sql mysql01:/root/itcast.sql
docker exec -it mysql01 mysql -uroot -p123321
source /root/itcast.sql;

# 方法二：使用输入重定向直接恢复（推荐，无需进入容器）
docker exec -i mysql01 mysql -uroot -p123321 < /tmp/itcast.sql

# 恢复后刷新工具查看，表已恢复

四、小结

常用工具小结，mysql、mysqladmin、mysqlbinlog、mysqlshow、mysqldump、mysqlimport/source

到这儿 MySQL 进阶的内容就结束了。

收工，可喜可贺
#MySQL#mysqldump#数据备份#mysqlbinlog
← 上一页InnoDB 存储引擎与事务原理下一页 →MySQL 四种日志实战