1. 数据建模功能
– 创建ER图（实体关系图）
– 正向工程：从模型生成数据库
– 逆向工程：从数据库生成模型
– 同步模型与数据库
– 支持多种数据库对象（表、视图、存储过程等）

2. SQL开发功能
– SQL编辑器（语法高亮、自动补全）
– 查询结果可视化
– SQL脚本执行
– SQL格式化
– SQL历史记录
– 多查询执行

3. 服务器管理功能
– 服务器状态监控
– 用户与权限管理
– 配置管理
– 日志查看
– 变量管理

4. 数据管理功能
– 数据导入导出
– 数据备份恢复
– 数据迁移
– 数据同步

5. 性能监控功能
– 性能仪表板
– 慢查询分析
– 索引分析
– 表分析

6. 安全管理功能
– 用户管理
– 权限管理
– SSL配置
– 审计日志

7. 数据库迁移功能
– 从其他数据库迁移
– 版本升级迁移
– 迁移向导

8. 报告功能
– 数据库文档生成
– 模型报告
– 性能报告

1. MySQL Workbench Community Edition（社区版）
特点：
– 免费使用
– 开源
– 基本功能完整

包含功能：
– 数据建模
– SQL开发
– 基本服务器管理
– 数据导入导出
– 数据库迁移

2. MySQL Workbench Enterprise Edition（企业版）
特点：
– 商业许可
– 企业级功能
– 官方支持

额外功能：
– 企业级安全审计
– 高级性能仪表板
– 可视化解释计划
– 表空间管理
– 企业级备份
– 高级迁移功能
– 技术支持

3. 版本选择建议
– 学习和开发：社区版
– 中小型企业：社区版
– 大型企业：企业版
– 需要官方支持：企业版

# 1. Windows安装

# 方式1：使用安装包
# 下载MySQL Workbench安装包
# 访问 https://dev.mysql.com/downloads/workbench/
# 下载Windows MSI安装包

# 运行安装程序
mysql-workbench-community-8.0.33-winx64.msi

# 输出示例：
# Welcome to the MySQL Workbench 8.0.33 Setup Wizard
# Select Installation Type: Complete
# Destination Folder: C:\Program Files\MySQL\MySQL Workbench 8.0
# Installing…
# Installation Complete

# 方式2：使用Chocolatey
choco install mysql-workbench

# 输出示例：
# Chocolatey v1.3.0
# Installing the following packages:
# mysql-workbench
# By installing you accept licenses for the packages.
# mysql-workbench v8.0.33 [Approved]
# Downloading mysql-workbench
# from ‘https://cdn.mysql.com/Downloads/MySQLGUITools/mysql-workbench-community-8.0.33-winx64.msi’
# Progress: 100% – Completed
# The install of mysql-workbench was successful.

# 2. macOS安装

# 方式1：使用DMG安装包
# 下载MySQL Workbench DMG文件
# 访问 https://dev.mysql.com/downloads/workbench/
# 下载macOS DMG安装包

# 挂载DMG并安装
open mysql-workbench-community-8.0.33-macos-x86_64.dmg

# 输出示例：
# (图形界面安装过程)

# 方式2：使用Homebrew
brew install –cask mysql-workbench

# 输出示例：
# ==> Downloading https://cdn.mysql.com/Downloads/MySQLGUITools/mysql-workbench-community-8.0.33-macos-x86_64.dmg
# Already downloaded: /Users/xxx/Library/Caches/Homebrew/downloads/mysql-workbench-community-8.0.33-macos-x86_64.dmg
# ==> Installing Cask mysql-workbench
# ==> Moving App ‘MySQLWorkbench.app’ to ‘/Applications/MySQLWorkbench.app’
# 🍺 mysql-workbench was successfully installed!

# 3. Linux安装

# Ubuntu/Debian
# 添加MySQL APT仓库
wget https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.24-1_all.deb

# 输出示例：
# Selecting previously unselected package mysql-apt-config.
# (Reading database … 123456 files and directories currently installed.)
# Preparing to unpack mysql-apt-config_0.8.24-1_all.deb …
# Unpacking mysql-apt-config (0.8.24-1) …
# Setting up mysql-apt-config (0.8.24-1) …

# 更新包列表
sudo apt-get update

# 安装MySQL Workbench
sudo apt-get install mysql-workbench-community

# 输出示例：
# Reading package lists… Done
# Building dependency tree
# Reading state information… Done
# The following NEW packages will be installed:
# mysql-workbench-community
# 0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
# Need to get 25.3 MB of archives.
# After this operation, 123 MB of additional disk space will be used.
# Get:1 http://repo.mysql.com/apt/ubuntu focal/mysql-tools amd64 mysql-workbench-community amd64 8.0.33-1ubuntu20.04 [25.3 MB]
# Fetched 25.3 MB in 5s (5060 kB/s)
# Selecting previously unselected package mysql-workbench-community…
# Preparing to unpack …/mysql-workbench-community_8.0.33-1ubuntu20.04_amd64.deb …
# Unpacking mysql-workbench-community (8.0.33-1ubuntu20.04) …
# Setting up mysql-workbench-community (8.0.33-1ubuntu20.04) …
# Processing triggers for desktop-file-utils (0.24-1ubuntu3) …
# Processing triggers for mime-support (3.64ubuntu1) …

# CentOS/RHEL
# 添加MySQL Yum仓库
sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# 输出示例：
# Loaded plugins: fastestmirror
# Examining mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch
# Marking mysql80-community-release-el7-3.noarch.rpm to be installed
# Resolving Dependencies
# –> Running transaction check
# —> Package mysql80-community-release.noarch 0:el7-3 will be installed
# –> Finished Dependency Resolution
#
# Dependencies Resolved
#
# =============================================================================
# Package Arch Version Repository Size
# =============================================================================
# Installing:
# mysql80-community-release noarch el7-3 /mysql80-community-release-el7-3.noarch 31 k
#
# Transaction Summary
# =============================================================================
# Install 1 Package
#
# Total size: 31 k
# Installed size: 31 k
# Is this ok [y/d/N]: y
# Downloading packages:
# Running transaction check
# Running transaction test
# Transaction test succeeded
# Running transaction
# Installing : mysql80-community-release-el7-3.noarch 1/1
# Verifying : mysql80-community-release-el7-3.noarch 1/1
#
# Installed:
# mysql80-community-release.noarch 0:el7-3
#
# Complete!

# 安装MySQL Workbench
sudo yum install mysql-workbench-community

# 输出示例：
# Loaded plugins: fastestmirror
# Loading mirror speeds from cached hostfile
# Resolving Dependencies
# –> Running transaction check
# —> Package mysql-workbench-community.x86_64 0:8.0.33-1.el7 will be installed
# –> Processing Dependency: libzip for package: mysql-workbench-community-8.0.33-1.el7.x86_64
# –> Processing Dependency: tinyxml for package: mysql-workbench-community-8.0.33-1.el7.x86_64
# –> Running transaction check
# —> Package libzip.x86_64 0:1.5.1-1.el7 will be installed
# —> Package tinyxml.x86_64 0:2.6.2-4.el7 will be installed
# –> Finished Dependency Resolution
#
# Dependencies Resolved
#
# =============================================================================
# Package Arch Version Repository
# =============================================================================
# Installing:
# mysql-workbench-community x86_64 8.0.33-1.el7 mysql-tools-community
# Installing for dependencies:
# libzip x86_64 1.5.1-1.el7 epel
# tinyxml x86_64 2.6.2-4.el7 epel
#
# Transaction Summary
# =============================================================================
# Install 1 Package (+2 Dependent packages)
#
# Total download size: 26 M
# Installed size: 130 M
# Is this ok [y/d/N]: y
# Downloading packages:
# (1/3): tinyxml-2.6.2-4.el7.x86_64.rpm | 26 kB 00:00
# (2/3): libzip-1.5.1-1.el7.x86_64.rpm | 61 kB 00:00
# (3/3): mysql-workbench-community-8.0.33-1.el7.x86_64.rpm | 25 MB 00:02
# —————————————————————————–
# Total 9.8 MB/s | 26 MB 00:02
# Running transaction check
# Running transaction test
# Transaction test succeeded
# Running transaction
# Installing : tinyxml-2.6.2-4.el7.x86_64 1/3
# Installing : libzip-1.5.1-1.el7.x86_64 2/3
# Installing : mysql-workbench-community-8.0.33-1.el7.x86_64 3/3
# Verifying : mysql-workbench-community-8.0.33-1.el7.x86_64 1/3
# Verifying : libzip-1.5.1-1.el7.x86_64 2/3
# Verifying : tinyxml-2.6.2-4.el7.x86_64 3/3
#
# Installed:
# mysql-workbench-community.x86_64 0:8.0.33-1.el7
#
# Dependency Installed:
# libzip.x86_64 0:1.5.1-1.el7 tinyxml.x86_64 0:2.6.2-4.el7
#
# Complete!

# 4. 验证安装
mysql-workbench –version

# 输出示例：
# MySQL Workbench CE (GPL) 8.0.33 CE build 12345678

# 启动MySQL Workbench
mysql-workbench &

# 输出示例：
# (MySQL Workbench图形界面启动)

# 1. 全局设置
# 打开MySQL Workbench
# 菜单：Edit -> Preferences

# 常规设置
General:
– Default Target MySQL Version: 8.0
– Default Storage Engine: InnoDB
– Default Character Set: utf8mb4
– Default Collation: utf8mb4_unicode_ci

# 外观设置
Appearance:
– Theme: Light/Dark
– Font Size: Normal/Large
– Syntax Highlighting: Custom colors

# 2. 连接设置
# 菜单：Database -> Manage Connections

# 创建新连接
Connection Name: MySQL_Local
Connection Method: Standard (TCP/IP)
Hostname: localhost
Port: 3306
Username: root
Password: (存储在密钥环中)
Default Schema: testdb

# SSL设置
SSL:
– Use SSL: If Available
– SSL CA File: /path/to/ca.pem
– SSL CERT File: /path/to/client-cert.pem
– SSL Key File: /path/to/client-key.pem

# 高级设置
Advanced:
– Enable Cleartext Plugin: No
– Use Compression: Yes
– Connection Timeout: 30 seconds

# 3. SQL编辑器设置
# 菜单：Edit -> Preferences -> SQL Editor

# 查询设置
Query Editor:
– Auto-commit: Enabled
– Safe Updates: Enabled
– Limit Rows: 1000
– Max Query Length: 1000000

# 代码编辑器设置
Code Editor:
– Font: Courier New
– Font Size: 12
– Tab Size: 4
– Word Wrap: Enabled
– Line Numbers: Enabled
– Auto-indent: Enabled

# 语法高亮设置
Syntax Highlighting:
– Keywords: Blue
– Strings: Red
– Comments: Green
– Numbers: Purple

# 4. 模型设置
# 菜单：Edit -> Preferences -> Modeling

# 模型设置
Modeling:
– Default Table Engine: InnoDB
– Default Character Set: utf8mb4
– Default Collation: utf8mb4_unicode_ci
– Notation: Workbench (Crow’s Foot)
– Show Relationship Labels: Yes

# 图表设置
Diagram:
– Page Size: A4
– Orientation: Landscape
– Grid: Visible
– Snap to Grid: Enabled

# 5. 备份设置
# 菜单：Edit -> Preferences -> Administration

# 备份设置
Backup:
– mysqldump Path: /usr/bin/mysqldump
– mysql Path: /usr/bin/mysql
– Default Backup Path: /backup/mysql
– Compression: gzip

# 6. 配置文件位置

# Windows
%APPDATA%\MySQL\Workbench\

# macOS
~/.mysql/workbench/

# Linux
~/.mysql/workbench/

# 主要配置文件
workbench_preferences.json # 首选项配置
connections.xml # 连接配置
server_instances.xml # 服务器实例配置
wb_options.xml # 选项配置

# 7. 命令行配置示例
# 查看当前配置
cat ~/.mysql/workbench/workbench_preferences.json

# 输出示例：
# {
# “workbench.preferences:General:DefaultTargetMySQLVersion”: “8.0”,
# “workbench.preferences:General:DefaultStorageEngine”: “InnoDB”,
# “workbench.preferences:General:DefaultCharacterSet”: “utf8mb4”,
# “workbench.preferences:SQL_Editor:AutoCommit”: “1”,
# “workbench.preferences:SQL_Editor:SafeUpdates”: “1”,
# “workbench.preferences:SQL_Editor:LimitRows”: “1000”
# }

# 修改配置（示例）
# 编辑配置文件
vim ~/.mysql/workbench/workbench_preferences.json

# 修改后重启MySQL Workbench生效

# 1. 创建新连接

# 步骤1：打开连接管理
# 菜单：Database -> Manage Connections
# 或点击首页的 “+” 按钮

# 步骤2：配置连接参数
Connection Name: MySQL_Production
Connection Method: Standard (TCP/IP)
Parameters:
Hostname: 192.168.1.100
Port: 3306
Username: admin
Password: ********
Default Schema: production_db

# 步骤3：测试连接
# 点击 “Test Connection” 按钮

# 输出示例：
# Successfully made the MySQL connection
#
# Information related to this connection:
# Hostname: 192.168.1.100
# Port: 3306
# Username: admin
# SSL: Enabled
# Server Version: 8.0.33
# Protocol Version: 10
# Connection Character Set: utf8mb4

# 2. 连接类型

# Standard (TCP/IP) – 标准TCP/IP连接
Connection Method: Standard (TCP/IP)
Hostname: localhost
Port: 3306

# Local Socket/Pipe – 本地套接字连接
Connection Method: Local Socket/Pipe
Socket: /var/lib/mysql/mysql.sock

# Standard TCP/IP over SSH – SSH隧道连接
Connection Method: Standard TCP/IP over SSH
SSH Hostname: 192.168.1.100:22
SSH Username: sshuser
SSH Password: ********
MySQL Hostname: 127.0.0.1
MySQL Port: 3306
Username: root
Password: ********

# 3. 连接分组管理

# 创建连接分组
# 右键点击连接 -> Add to Group -> New Group

# 分组示例：
Development
– MySQL_Dev_Local
– MySQL_Dev_Server

Testing
– MySQL_Test_Server

Production
– MySQL_Prod_Master
– MySQL_Prod_Slave

# 4. 连接导入导出

# 导出连接配置
# 菜单：Database -> Manage Connections -> Export

# 导出文件示例：



MySQL_Local

localhost
3306
root

# 导入连接配置
# 菜单：Database -> Manage Connections -> Import

# 5. 连接脚本

# 创建连接脚本
# 菜单：Database -> Manage Connections -> Scripts

# 连接后执行脚本示例：
USE production_db;
SET NAMES utf8mb4;
SET time_zone = ‘+08:00’;

# 6. 连接快捷键

# 打开连接
Ctrl+U (Windows/Linux)
Cmd+U (macOS)

# 关闭连接
Ctrl+W (Windows/Linux)
Cmd+W (macOS)

# 刷新对象
F5

# 7. 连接状态监控

# 查看连接状态
# 菜单：Server -> Client Connections

# 输出示例：
# ID User Host DB Command Time State Info
# 1 root localhost:12345 production_db Query 0 executing SELECT * FROM users
# 2 admin 192.168.1.50:54321 testdb Sleep 10 NULL
# 3 app 192.168.1.51:54322 production_db Query 5 Sending data SELECT * FROM orders

# 8. 连接日志

# 查看连接日志
# 菜单：View -> Output -> Show Output

# 输出示例：
# 12:00:00 Connecting to MySQL Server localhost:3306…
# 12:00:01 Connection opened
# 12:00:01 Query: SELECT VERSION()
# 12:00:01 Query OK, 1 row affected
# 12:00:01 Query: SHOW DATABASES
# 12:00:01 Query OK, 5 rows affected

# 1. 打开SQL编辑器

# 方式1：从连接打开
# 双击连接 -> 选择数据库 -> 点击 “Create new SQL tab”

# 方式2：快捷键
Ctrl+T (Windows/Linux)
Cmd+T (macOS)

# 2. SQL编辑器界面

# 主要区域：
# – SQL编辑区：编写SQL语句
# – 结果区：显示查询结果
# – 侧边栏：数据库对象浏览器
# – 输出区：执行日志和消息

# 3. 执行SQL语句

# 执行当前语句
# 点击闪电图标或按 Ctrl+Enter

# 执行所有语句
# 点击闪电图标（全部）或按 Ctrl+Shift+Enter

# 执行示例：
SELECT * FROM users LIMIT 10;

# 输出示例：
# Query OK, 10 rows affected (0.00 sec)
#
# +—-+——–+——+———————+
# | id | name | age | email |
# +—-+——–+——+———————+
# | 1 | 张三 | 25 | zhangsan@test.com |
# | 2 | 李四 | 30 | lisi@test.com |
# | 3 | 王五 | 28 | wangwu@test.com |
# +—-+——–+——+———————+

# 4. SQL自动补全

# 启用自动补全
# 菜单：Edit -> Preferences -> SQL Editor -> Enable Code Completion

# 使用自动补全
# 输入部分关键字后按 Tab 或 Ctrl+Space

# 示例：
SEL -> SELECT
FROM -> FROM
WHE -> WHERE

# 5. SQL格式化

# 格式化SQL
# 菜单：Query -> Reformat SQL
# 快捷键：Ctrl+B (Windows/Linux), Cmd+B (macOS)

# 格式化前：
SELECT id,name,age FROM users WHERE age>20 ORDER BY id;

# 格式化后：
SELECT
id,
name,
age
FROM
users
WHERE
age > 20
ORDER BY
id;

# 6. 查询结果操作

# 导出结果
# 右键点击结果 -> Export -> 选择格式（CSV, JSON, HTML等）

# 编辑结果
# 点击结果单元格 -> 直接编辑 -> 点击 Apply

# 复制结果
# 选择行 -> Ctrl+C -> 粘贴到Excel等

# 7. SQL历史记录

# 查看历史
# 菜单：Query -> Query History
# 快捷键：Ctrl+H (Windows/Linux), Cmd+H (macOS)

# 历史记录示例：
# 2026-04-01 12:00:00 SELECT * FROM users LIMIT 10
# 2026-04-01 12:01:00 SELECT COUNT(*) FROM orders
# 2026-04-01 12:02:00 UPDATE users SET age = 26 WHERE id = 1

# 8. SQL模板

# 使用模板
# 菜单：Query -> Use Template

# 模板示例：
# – CREATE TABLE
# – CREATE INDEX
# – CREATE PROCEDURE
# – INSERT INTO
# – UPDATE

# 9. 多查询执行

# 执行多个SQL语句（用分号分隔）
SELECT * FROM users LIMIT 5;
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;

# 输出示例：
# Result 1:
# +—-+——–+——+
# | id | name | age |
# +—-+——–+——+
# | 1 | 张三 | 25 |
# | 2 | 李四 | 30 |
# …
#
# Result 2:
# +———-+
# | COUNT(*) |
# +———-+
# | 100 |
# +———-+
#
# Result 3:
# +———-+
# | AVG(age) |
# +———-+
# | 32.5000 |
# +———-+

# 10. SQL脚本执行

# 打开SQL脚本文件
# 菜单：File -> Open SQL Script
# 快捷键：Ctrl+O (Windows/Linux), Cmd+O (macOS)

# 执行脚本
# 菜单：Query -> Execute SQL Script

# 保存脚本
# 菜单：File -> Save SQL Script
# 快捷键：Ctrl+S (Windows/Linux), Cmd+S (macOS)

# 1. 创建数据模型

# 方式1：创建新模型
# 菜单：File -> New Model
# 快捷键：Ctrl+N (Windows/Linux), Cmd+N (macOS)

# 方式2：从数据库逆向工程
# 菜单：Database -> Reverse Engineer

# 逆向工程步骤：
# 1. 选择连接
# 2. 选择数据库
# 3. 选择要导入的对象（表、视图、存储过程等）
# 4. 执行逆向工程
# 5. 生成ER图

# 输出示例：
# Reverse engineering…
# Retrieving table structures…
# Retrieving view definitions…
# Retrieving stored procedures…
# Reverse engineering completed successfully
# Generated 25 tables, 5 views, 10 stored procedures

# 2. 创建ER图

# 添加表
# 双击 “Add Table” 或右键点击图表 -> Create Table

# 配置表属性
Table Name: users
Columns:
– id: INT, PK, AI, NOT NULL
– name: VARCHAR(255), NOT NULL
– age: INT
– email: VARCHAR(255)
– created_at: TIMESTAMP, DEFAULT CURRENT_TIMESTAMP

Indexes:
– PRIMARY: id
– idx_name: name
– idx_email: email

# 3. 创建关系

# 一对多关系
# 点击 “1:n” 图标 -> 点击主表 -> 点击从表

# 示例：用户和订单
users (1) —-< orders (n) # 多对多关系 # 创建中间表 # 示例：用户和角色 users >—- user_roles —-< roles # 4. 正向工程 # 从模型生成数据库 # 菜单：Database -> Forward Engineer

# 步骤：
# 1. 选择目标连接
# 2. 选择要生成的对象
# 3. 配置生成选项
# 4. 预览SQL
# 5. 执行生成

# 生成的SQL示例：
CREATE TABLE IF NOT EXISTS `users` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`age` INT NULL,
`email` VARCHAR(255) NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name`),
INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 输出示例：
# Forward engineering…
# Creating tables…
# Creating indexes…
# Creating foreign keys…
# Forward engineering completed successfully
# Generated 25 tables, 30 indexes, 20 foreign keys

# 5. 同步模型与数据库

# 同步模型到数据库
# 菜单：Database -> Synchronize Model

# 同步步骤：
# 1. 选择目标连接
# 2. 比较模型和数据库差异
# 3. 选择要应用的更改
# 4. 预览SQL
# 5. 执行同步

# 差异示例：
# Model -> Database:
# ALTER TABLE users ADD COLUMN phone VARCHAR(20);
# CREATE INDEX idx_phone ON users(phone);
#
# Database -> Model:
# (无差异)

# 输出示例：
# Synchronizing…
# Comparing model and database…
# Found 2 differences
# Applying changes…
# Synchronization completed successfully

# 6. 模型验证

# 验证模型
# 菜单：Model -> Validate

# 验证项目：
# – 表结构完整性
# – 外键关系
# – 索引有效性
# – 数据类型兼容性

# 输出示例：
# Validating model…
#
# Warnings:
# – Table ‘orders’ has no primary key defined
# – Foreign key ‘fk_user_id’ references non-existent column
#
# Errors:
# – Table ‘users’ has duplicate column name ’email’
#
# Validation completed with 2 warnings and 1 error

# 7. 模型文档生成

# 生成模型报告
# 菜单：Database -> Model Reporting

# 报告内容：
# – 数据库概览
# – 表列表
# – 表结构详情
# – 关系图
# – 索引信息

# 输出格式：
# – HTML
# – PDF
# – CSV

# 8. 模型导入导出

# 导出模型
# 菜单：File -> Export -> Forward Engineer SQL CREATE Script

# 导入模型
# 菜单：File -> Import -> Reverse Engineer MySQL Create Script

# 导出文件示例：
— MySQL Workbench Forward Engineering
— Version 8.0.33
— Generated on 2026-04-01 12:00:00

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES’;

— —————————————————–
— Schema testdb
— —————————————————–
CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8mb4;
USE `testdb`;

— —————————————————–
— Table `users`
— —————————————————–
CREATE TABLE IF NOT EXISTS `users` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;

# 1. 打开服务器管理

# 方式1：从连接打开
# 右键点击连接 -> Server Status

# 方式2：从菜单打开
# 菜单：Server -> Server Status

# 2. 服务器状态监控

# 状态仪表板
# 显示内容：
# – 服务器版本
# – 运行时间
# – 连接数
# – 查询数
# – 缓冲池状态
# – 网络流量

# 输出示例：
# Server Status
# =============
# MySQL Version: 8.0.33
# Uptime: 10 days, 5 hours, 30 minutes
# Connections: 45 (Max: 151)
# Queries per second: 123.45
# Threads: 12
# Open Tables: 256
# Buffer Pool Hit Rate: 99.87%

# 3. 用户与权限管理

# 打开用户管理
# 菜单：Server -> Users and Privileges

# 创建用户
# 点击 “Add Account” 按钮

# 用户配置：
Login Name: app_user
Authentication Type: Standard
Limit to Hosts Matching: %
Password: ********
Confirm Password: ********

# 权限配置：
Schema Privileges:
– testdb: SELECT, INSERT, UPDATE, DELETE

# 输出示例：
# User ‘app_user’@’%’ created successfully
# Granted privileges on schema ‘testdb’

# 4. 配置管理

# 打开配置管理
# 菜单：Server -> Options File

# 配置分类：
# – General
# – MyISAM
# – InnoDB
# – Logging
# – Replication
# – Security

# 修改配置示例：
# 找到参数：max_connections
# 修改值：200
# 点击 Apply

# 输出示例：
# Configuration change applied
# Note: Some changes require server restart

# 5. 日志管理

# 查看日志
# 菜单：Server -> Server Logs

# 日志类型：
# – Error Log
# – General Log
# – Slow Query Log

# 慢查询日志示例：
# Time: 2026-04-01T12:00:00.123456Z
# User@Host: app[app] @ [192.168.1.50]
# Query_time: 5.123456
# Lock_time: 0.000123
# Rows_sent: 1000
# Rows_examined: 50000
# SET timestamp=1712345678;
# SELECT * FROM orders WHERE status = ‘pending’;

# 6. 变量管理

# 查看变量
# 菜单：Server -> Status and System Variables

# 系统变量示例：
# Variable_name | Value
# ————————|——————
# innodb_buffer_pool_size | 134217728
# max_connections | 151
# query_cache_size | 0
# slow_query_log | ON
# long_query_time | 2.000000

# 状态变量示例：
# Variable_name | Value
# ————————|——————
# Connections | 1234
# Queries | 56789
# Slow_queries | 45
# Uptime | 864000

# 7. 数据导入导出

# 数据导出
# 菜单：Server -> Data Export

# 导出选项：
Databases to Export:
– testdb (selected)

Export Options:
– Export to Self-Contained File
– Include Create Schema
– Include Create Table
– Include Data

Advanced Options:
– Max Query Size: 1M
– Use Column Names in INSERT

# 输出示例：
# Exporting data…
# Dumping database: testdb
# Dumping table: users (1000 rows)
# Dumping table: orders (5000 rows)
# Export completed successfully
# File saved to: /backup/testdb_export_20260401.sql

# 数据导入
# 菜单：Server -> Data Import

# 导入选项：
Import from Self-Contained File: /backup/testdb_export.sql

Import Options:
– Create database if not exists
– Drop tables before import

# 输出示例：
# Importing data…
# Creating database: testdb
# Creating table: users
# Importing data: 1000 rows
# Creating table: orders
# Importing data: 5000 rows
# Import completed successfully

# 8. 性能仪表板

# 打开性能仪表板
# 菜单：Server -> Performance Dashboard

# 显示内容：
# – InnoDB状态
# – 连接统计
# – 查询统计
# – 缓冲池统计
# – 锁等待
# – 慢查询

# 输出示例：
# Performance Dashboard
# =====================
#
# InnoDB Buffer Pool:
# Total Size: 128 MB
# Pages: 8192
# Hit Rate: 99.87%
#
# Connections:
# Current: 45
# Max Used: 120
# Aborted: 2
#
# Queries:
# Total: 1,234,567
# Slow: 45
# Avg Time: 0.012s

# 1. 完整备份

# 步骤1：打开数据导出
# 菜单：Server -> Data Export

# 步骤2：选择数据库和表
Databases:
[x] production_db
[x] testdb

Tables:
production_db:
[x] users
[x] orders
[x] products

# 步骤3：配置导出选项
Export Options:
– Export to Self-Contained File
– File Path: /backup/mysql_full_backup_20260401.sql
– Include Create Schema: Yes
– Include Create Table: Yes
– Include Data: Yes

Advanced Options:
– Use gzip compression: Yes
– Add LOCK TABLES: Yes
– Disable keys: Yes
– Use extended INSERT: Yes

# 步骤4：执行导出
# 点击 “Start Export” 按钮

# 输出示例：
# Exporting data…
#
# Dumping database: production_db
# Dumping table: users (10000 rows)
# Dumping table: orders (50000 rows)
# Dumping table: products (1000 rows)
#
# Dumping database: testdb
# Dumping table: test_users (100 rows)
#
# Writing to file: /backup/mysql_full_backup_20260401.sql.gz
#
# Export completed successfully
# Total size: 15.6 MB (compressed)

# 2. 表级备份

# 步骤1：选择特定表
Databases:
[x] production_db

Tables:
production_db:
[x] orders (仅备份orders表)

# 步骤2：配置导出选项
Export Options:
– Export to Self-Contained File
– File Path: /backup/orders_backup_20260401.sql

# 步骤3：执行导出
# 输出示例：
# Exporting data…
# Dumping table: orders (50000 rows)
# Export completed successfully
# File size: 5.2 MB

# 3. 数据恢复

# 步骤1：打开数据导入
# 菜单：Server -> Data Import

# 步骤2：选择备份文件
Import from Self-Contained File: /backup/mysql_full_backup_20260401.sql.gz

# 步骤3：配置导入选项
Import Options:
– Create database if not exists: Yes
– Drop tables before import: Yes

# 步骤4：执行导入
# 点击 “Start Import” 按钮

# 输出示例：
# Importing data…
#
# Creating database: production_db
# Creating table: users
# Importing data: 10000 rows
# Creating table: orders
# Importing data: 50000 rows
# Creating table: products
# Importing data: 1000 rows
#
# Creating database: testdb
# Creating table: test_users
# Importing data: 100 rows
#
# Import completed successfully
# Total time: 45.6 seconds

# 4. 定时备份脚本

# 创建备份脚本
vim /backup/mysql_backup.sh

# 添加以下内容：
#!/bin/bash

# MySQL备份脚本
# 整理发布：风哥
# 日期：2026-04-01

# 配置变量
BACKUP_DIR=”/backup/mysql”
MYSQL_USER=”backup_user”
MYSQL_PASSWORD=”backup_password”
MYSQL_HOST=”localhost”
MYSQL_PORT=”3306″
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
mkdir -p $BACKUP_DIR

# 获取所有数据库列表
DATABASES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT -e “SHOW DATABASES;” | grep -Ev “(Database|information_schema|performance_schema|mysql|sys)”)

# 备份每个数据库
for DB in $DATABASES; do
echo “Backing up database: $DB”
mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -P$MYSQL_PORT \
–single-transaction \
–routines \
–triggers \
–events \
$DB | gzip > $BACKUP_DIR/${DB}_${DATE}.sql.gz

if [ $? -eq 0 ]; then
echo “Backup of $DB completed successfully”
else
echo “Backup of $DB failed”
fi
done

# 删除30天前的备份
find $BACKUP_DIR -name “*.sql.gz” -type f -mtime +30 -delete

echo “Backup completed at $(date)”

# 设置执行权限
chmod +x /backup/mysql_backup.sh

# 配置定时任务
crontab -e

# 添加以下内容（每天凌晨2点执行）：
0 2 * * * /backup/mysql_backup.sh >> /backup/mysql_backup.log 2>&1

# 输出示例：
# Backing up database: production_db
# Backup of production_db completed successfully
# Backing up database: testdb
# Backup of testdb completed successfully
# Backup completed at 2026-04-01 02:00:00

# 5. 恢复验证

# 验证备份文件
gunzip -c /backup/production_db_20260401_020000.sql.gz | head -50

# 输出示例：
# — MySQL dump 10.13 Distrib 8.0.33, for Linux (x86_64)
# —
# — Host: localhost Database: production_db
# — ——————————————————
# — Server version 8.0.33
#
# /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
# /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
# …
#
# —
# — Table structure for table `users`
# —
#
# DROP TABLE IF EXISTS `users`;
# /*!40101 SET @saved_cs_client = @@character_set_client */;
# …

# 恢复到测试环境
mysql -u root -p test_restore < /backup/production_db_20260401_020000.sql # 验证数据 mysql -u root -p test_restore -e "SELECT COUNT(*) FROM users;" # 输出示例： # +----------+ # | COUNT(*) | # +----------+ # | 10000 | # +----------+

# 1. 迁移向导

# 步骤1：打开迁移向导
# 菜单：Database -> Migration Wizard

# 步骤2：选择源数据库
Source Database:
Database System: MySQL
Connection Method: Standard TCP/IP
Hostname: source-server.example.com
Port: 3306
Username: root
Password: ********

# 步骤3：选择目标数据库
Target Database:
Database System: MySQL
Connection Method: Standard TCP/IP
Hostname: target-server.example.com
Port: 3306
Username: root
Password: ********

# 步骤4：选择迁移对象
Objects to Migrate:
[x] Schemas
[x] production_db
[x] Tables
[x] users
[x] orders
[x] products
[x] Views
[x] Stored Procedures
[x] Functions
[x] Triggers

# 步骤5：配置迁移选项
Migration Options:
– Create schema on target: Yes
– Copy table data: Yes
– Copy indexes: Yes
– Copy foreign keys: Yes
– Bulk insert buffer size: 1000

# 步骤6：执行迁移
# 点击 “Run Migration” 按钮

# 输出示例：
# Migration started at 2026-04-01 12:00:00
#
# Connecting to source database…
# Connection successful
#
# Connecting to target database…
# Connection successful
#
# Retrieving schema information…
# Found 25 tables, 5 views, 10 stored procedures
#
# Creating schema on target…
# Schema ‘production_db’ created
#
# Migrating tables…
# Table ‘users’ migrated (10000 rows)
# Table ‘orders’ migrated (50000 rows)
# Table ‘products’ migrated (1000 rows)
# …
#
# Migrating views…
# View ‘user_orders_view’ migrated
# …
#
# Migrating stored procedures…
# Procedure ‘calculate_total’ migrated
# …
#
# Migration completed successfully
# Total time: 5 minutes 30 seconds
# Total rows migrated: 61000

# 2. 版本升级迁移

# 从MySQL 5.7升级到MySQL 8.0

# 步骤1：检查兼容性
# 菜单：Server -> Upgrade Checker

# 输出示例：
# Running upgrade checker…
#
# Warnings:
# – Some reserved keywords have changed
# – Default authentication plugin changed
#
# Manual checks required:
# – Check for deprecated SQL modes
# – Check for removed system variables
# – Check for changed default values

# 步骤2：执行迁移
# 使用迁移向导从5.7迁移到8.0

# 输出示例：
# Upgrading from MySQL 5.7 to MySQL 8.0…
#
# Pre-upgrade checks passed
#
# Migrating schema…
# Migrating data…
# Running mysql_upgrade…
#
# Upgrade completed successfully
# MySQL version: 8.0.33

# 3. 跨数据库迁移

# 从PostgreSQL迁移到MySQL

# 步骤1：配置源数据库
Source Database:
Database System: PostgreSQL
Hostname: pg-server.example.com
Port: 5432
Database: production_db
Username: postgres
Password: ********

# 步骤2：配置目标数据库
Target Database:
Database System: MySQL
Hostname: mysql-server.example.com
Port: 3306
Username: root
Password: ********

# 步骤3：数据类型映射
Type Mapping:
PostgreSQL -> MySQL
——————- ——————-
SERIAL -> INT AUTO_INCREMENT
BIGSERIAL -> BIGINT AUTO_INCREMENT
TEXT -> TEXT
VARCHAR(n) -> VARCHAR(n)
TIMESTAMP -> DATETIME
BOOLEAN -> TINYINT(1)
JSONB -> JSON

# 步骤4：执行迁移
# 输出示例：
# Migrating from PostgreSQL to MySQL…
#
# Connecting to PostgreSQL…
# Connection successful
#
# Connecting to MySQL…
# Connection successful
#
# Schema conversion…
# Converting 25 tables
# Converting data types…
#
# Data migration…
# Table ‘users’ migrated (10000 rows)
# Table ‘orders’ migrated (50000 rows)
# …
#
# Migration completed successfully
# Total time: 8 minutes 15 seconds

# 4. 迁移验证

# 数据一致性验证
# 步骤1：验证表数量
# 源数据库
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ‘production_db’;

# 输出示例：
# +———-+
# | COUNT(*) |
# +———-+
# | 25 |
# +———-+

# 目标数据库
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ‘production_db’;

# 输出示例：
# +———-+
# | COUNT(*) |
# +———-+
# | 25 |
# +———-+

# 步骤2：验证数据行数
# 源数据库
SELECT
table_name,
table_rows
FROM information_schema.tables
WHERE table_schema = ‘production_db’
ORDER BY table_name;

# 输出示例：
# +————+————+
# | table_name | table_rows |
# +————+————+
# | orders | 50000 |
# | products | 1000 |
# | users | 10000 |
# +————+————+

# 目标数据库（应该一致）
SELECT
table_name,
table_rows
FROM information_schema.tables
WHERE table_schema = ‘production_db’
ORDER BY table_name;

# 输出示例：
# +————+————+
# | table_name | table_rows |
# +————+————+
# | orders | 50000 |
# | products | 1000 |
# | users | 10000 |
# +————+————+

# 步骤3：验证数据内容
# 随机抽样验证
SELECT * FROM users WHERE id IN (1, 100, 1000, 5000, 10000);

# 输出示例：
# +——-+———-+——+———————+
# | id | name | age | email |
# +——-+———-+——+———————+
# | 1 | 张三 | 25 | zhangsan@test.com |
# | 100 | 李四 | 30 | lisi@test.com |
# | 1000 | 王五 | 28 | wangwu@test.com |
# | 5000 | 赵六 | 35 | zhaoliu@test.com |
# | 10000 | 孙七 | 40 | sunqi@test.com |
# +——-+———-+——+———————+

# 1. 性能仪表板

# 打开性能仪表板
# 菜单：Server -> Performance Dashboard

# 查看关键指标
Performance Dashboard:

InnoDB Buffer Pool:
Size: 128 MB
Pages: 8192
Hit Rate: 99.87%
Reads: 1,234,567
Writes: 123,456

Connections:
Current: 45
Max Used: 120
Aborted: 2
Errors: 0

Queries:
Total: 1,234,567
Slow: 45
Avg Time: 0.012s
Max Time: 5.234s

Network:
In: 123.45 MB
Out: 456.78 MB

# 2. 慢查询分析

# 打开慢查询日志
# 菜单：Server -> Server Logs -> Slow Query Log

# 分析慢查询
# 输出示例：
# Time: 2026-04-01T12:00:00.123456Z
# User@Host: app[app] @ [192.168.1.50]
# Query_time: 5.123456
# Lock_time: 0.000123
# Rows_sent: 1000
# Rows_examined: 50000
# SET timestamp=1712345678;
# SELECT * FROM orders WHERE status = ‘pending’ ORDER BY created_at DESC;

# 优化建议
# 1. 添加索引
CREATE INDEX idx_status_created ON orders(status, created_at);

# 2. 优化查询
SELECT id, customer_id, total_amount
FROM orders
WHERE status = ‘pending’
ORDER BY created_at DESC
LIMIT 100;

# 3. 索引分析

# 打开表索引信息
# 右键点击表 -> Table Inspector -> Indexes

# 输出示例：
# Table: orders
#
# Indexes:
# +——————+————+————-+————-+
# | Index Name | Columns | Cardinality | Index Type |
# +——————+————+————-+————-+
# | PRIMARY | id | 50000 | BTREE |
# | idx_customer | customer_id| 5000 | BTREE |
# | idx_status | status | 10 | BTREE |
# | idx_created | created_at | 50000 | BTREE |
# +——————+————+————-+————-+

# 索引使用建议
# 1. 删除未使用的索引
# 2. 添加复合索引
# 3. 优化索引顺序

# 4. 执行计划分析

# 打开执行计划
# 在SQL编辑器中点击 “Execution Plan” 按钮

# 执行计划示例：
# +—-+————-+———+————+——+—————+——+———+——+——-+———-+————-+
# | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
# +—-+————-+———+————+——+—————+——+———+——+——-+———-+————-+
# | 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status | 2 | const | 5000 | 100.00 | Using where |
# +—-+————-+———+————+——+—————+——+———+——+——-+———-+————-+

# 分析结果：
# – type: ref (使用索引查找)
# – key: idx_status (使用了idx_status索引)
# – rows: 5000 (预计扫描5000行)
# – Extra: Using where (使用了WHERE条件)

# 5. 性能优化建议

# 查看优化建议
# 菜单：Query -> Explain

# 输出示例：
# Query Optimization Suggestions:
#
# 1. Consider adding an index on column ‘status’
# Current: Full table scan
# Suggested: CREATE INDEX idx_status ON orders(status)
#
# 2. Consider using LIMIT clause
# Current: Returning all rows
# Suggested: Add LIMIT clause to reduce result set
#
# 3. Consider using covering index
# Current: Index lookup + table lookup
# Suggested: CREATE INDEX idx_covering ON orders(status, created_at, id)

# 6. 实时监控

# 打开实时监控
# 菜单：Server -> Performance -> Live Monitor

# 监控项目：
# – 每秒查询数
# – 每秒连接数
# – 缓冲池命中率
# – 锁等待
# – 磁盘IO

# 输出示例：
# Live Monitor (refresh every 1 second)
#
# Queries per second: 123.45
# Connections per second: 1.23
# Buffer Pool Hit Rate: 99.87%
# Lock Waits: 0
# Disk Reads: 12
# Disk Writes: 45

# 7. 性能报告生成

# 生成性能报告
# 菜单：Server -> Performance -> Generate Report

# 报告内容：
# – 服务器配置摘要
# – 性能指标统计
# – 慢查询分析
# – 索引使用统计
# – 优化建议

# 输出格式：
# – HTML
# – PDF
# – CSV

# 输出示例：
# Performance Report
# ==================
#
# Server: MySQL 8.0.33
# Generated: 2026-04-01 12:00:00
#
# Summary:
# – Total Queries: 1,234,567
# – Slow Queries: 45 (0.004%)
# – Average Query Time: 0.012s
#
# Top 10 Slow Queries:
# 1. SELECT * FROM orders WHERE status = ‘pending’ (5.234s)
# 2. SELECT * FROM users WHERE email LIKE ‘%@test.com’ (3.456s)
# …
#
# Recommendations:
# 1. Add index on orders.status
# 2. Optimize LIKE queries
# 3. Increase buffer pool size