从小白到大神之路之学习运维第34天---第三阶段---mysql数据库之优化数据库、数据库备份、win10客户端连接

第三阶段基础

时  间：2023年6月6日

参加人：全班人员

内  容：

Mysql数据库

目录

前提环境配置

优化 MySQL

数据库备份

Win10客户端连接

---

前提环境配置

关闭防火墙

（systemtl stop firewalld、iptables -F、setenforce 0）

网络模式为桥接模式

ip地址为192.168.1.125

安装了mysql数据库

mysql内有远程登录客户hy

优化 MySQL

1. 调整缓冲池大小

MySQL 的缓冲池用于缓存表和索引数据以减少磁盘 I/O。缓冲池大小默认为 128MB，但你可以根据你的系统资源和数据库工作负载来增加或减少缓冲池大小。

在 `my.cnf` 文件中，将以下行添加到 `[mysqld]` 部分以设置缓冲池大小：

innodb_buffer_pool_size = 1G

这会将缓冲池大小设置为 1GB，可以根据需要进行更改。

2. 配置查询缓存

MySQL 的查询缓存可以缓存 SELECT 查询的结果，以避免重复查询。但是，查询缓存需要消耗大量内存，并且不适用于具有高并发查询的数据库。

如果你的数据库具有较高的并发查询，可以禁用查询缓存。在 `my.cnf` 文件中，将以下行添加到 `[mysqld]` 部分以禁用查询缓存：

query_cache_type = 0

query_cache_size = 0

3. 启用慢查询日志

慢查询日志用于记录执行时间超过阈值的查询，以便你可以识别和优化慢查询。你可以通过在 `my.cnf` 文件中添加以下行来启用慢查询日志：

slow_query_log = 1

slow_query_log_file = /var/log/mysql/mysql-slow.log

long_query_time = 2

上述配置将慢查询日志保存在

`/var/log/mysql/mysql-slow.log` 文件中，并将执行时间超过 2 秒的查询记录到日志中。

4. 调整并发连接数

如果你的 MySQL 服务器同时处理大量连接，则需要调整并发连接数。在 `my.cnf` 文件中，将以下行添加到 `[mysqld]` 部分：

max_connections = 200

上述配置将最大连接数设置为 200，可以根据需要进行更改。

5. 配置字符集

为防止字符编码问题并保持与应用程序的兼容性，建议在 `my.cnf` 文件中配置字符集。以下是一个示例：

[client]

default-character-set = utf8mb4

[mysqld]

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

[mysql]

default-character-set = utf8mb4

上述配置将客户端和服务器的字符集设置为 UTF-8，使用 `utf8mb4` 字符集和 `utf8mb4_unicode_ci` 排序规则。

6. 配置日志

为更好地排查故障和分析数据库行为，建议启用 MySQL 日志。以下是一个示例：

[mysqld]

log-error = /var/log/mysql/error.log

general_log_file = /var/log/mysql/mysql.log

general_log = 1

上述配置将错误日志记录到 `/var/log/mysql/error.log` 文件中，将通用日志记录到 `/var/log/mysql/mysql.log` 文件中。

这些是一些通用的优化建议，你可以根据你的系统和数据库工作负载进行某些微调。注意，对 MySQL 配置文件进行任何更改之前，务必备份原始文件。

例：配置文件/etc/my.cnf

vim /etc/my.cnf

重启服务，查看能否运行，能运行则表示配置文件没问题

systemctl restart mysqld

systemctl status mysqld

数据库备份

MySQL 5.7 数据库备份可以使用多种方法，以下是其中两种常用的方法：在此演示使用 mysqldump

1. 使用 mysqldump

`mysqldump` 是 MySQL 自带的备份工具，可以备份整个数据库或某些表，也可以备份单个查询的结果。

例：现有下列数据库，内有库如下：

现有数据库mydb，内有数据表customer；

如果你只想备份一个数据库，使用以下命令生成整个数据库的备份：

mysqldump -u 用户名 -p 数据库名 > 备份文件.sql

此命令将整个数据库备份为一个 SQL 文件，并保存到指定的备份文件（可以自定义）中。

例：

新建一个备份文件夹testmkdir test

备份数据库mysqldump -u root -p mydb > mydb.sql

如果你只想备份某些表，请使用以下命令：

mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份文件.sql你需要输入你的 MySQL 用户名和密码。

例：将mydb数据库里的customer表进行备份

mysqldump -u root -p mydb customer > customer.sql

如果你想备份单个查询的结果，请使用以下命令：

mysql -u 用户名 -p -e "查询语句" 数据库名 > 备份文件.sql

例：

备份mydb数据库的customer表里筛选含有a名字的文件

mysql -u root  -p -e " SELECT name, email FROM customer WHERE name LIKE '%a%'" mydb >  a.sql

2. 使用 MySQL Enterprise Backup在此不做演示

MySQL Enterprise Backup 是 MySQL 官方提供的备份工具，它可以在不停止数据库进程的情况下备份数据，同时支持增量备份和差异备份。以下是一个备份整个数据库的示例：

mysqlbackup --user=用户名 --password=密码 --port=端口号 --backup-dir=/路径 backup-and-apply-log

此命令将在指定目录中创建备份，并自动应用日志文件。你需要输入 MySQL 用户名、密码和端口号。

无论采用哪种备份方法，备份数据后，请务必将备份文件存放在安全的地方。同时，如果需要定期备份，你可以将备份脚本加入定时任务，以自动执行备份并定期删除旧备份。

Win10客户端连接

要在 Windows 客户端上连接 MySQL 服务器，需要先安装 MySQL 客户端工具。MySQL 客户端可使用命令行工具或可视化工具，下面列出了两种连接 MySQL 服务器的方法。

在此演示MySQL 客户端可视化工具（MySQL Workbench）

### 方法一：MySQL 命令行客户端（在此不做演示）

1. 下载 MySQL 命令行客户端（MySQL Community Edition），安装并配置好 MySQL。

2. 打开命令提示符（或 PowerShell）。

3. 输入以下命令并按 Enter 键：

   mysql -u 用户名 -h 主机名或IP地址 -p

   其中，`用户名` 是 MySQL 用户名，`主机名或IP地址` 是 MySQL 服务器的主机名或IP地址。如果 MySQL 服务器位于本地主机，可以使用 `localhost` 来代替主机名或 IP 地址。`-p` 表示将提示输入密码。

4. 如果密码正确，您将进入 MySQL 命令行模式，在那里您可以执行 SQL 语句或执行其他命令。

### 方法二：MySQL 客户端可视化工具（MySQL Workbench）

1. 下载 MySQL Workbench（MySQL 官方工具），安装并配置好 MySQL。

例：下载下列软件（官网获取），而后安装到本地

注意，安装目录若不为默认目录的话，选择安装的目录也要为英文目录！

2. 打开 MySQL Workbench 并单击“New Connection”按钮。

3. 在弹出的新连接对话框中，输入以下信息：

* Connection Name：连接名称   例：mysql-connect104

* Connection Method：TCP/IP  

* Hostname：MySQL 服务器的主机名或IP地址  例：192.168.1.104

* Port：MySQL 服务器的端口号，默认为3306

* Username：MySQL 用户名   例：hy

* Password：MySQL 密码     例：Hy-584520

   完成后，单击“Test Connection”按钮测试连接是否成功，然后单击“OK”。

4. 在 MySQL Workbench 窗口中，您将看到连接的 MySQL 服务器，并可以使用 MySQL Workbench 执行 SQL 查询和其他操作。

点击建立的连接，对此进行访问设置！

两种方法都可以让您在 Windows 客户端上连接到 MySQL 服务器，您可以使用其中的任何一种来管理 MySQL 数据库和执行查询。