「MySQL运维常见问题及解决方法」

💖The Begin💖点点关注，收藏不迷路💖

一、查看MySQL数据库安装路径

在某些情况下，我们可能需要查看MySQL数据库的安装路径，以便进行一些特定的操作或配置。

1.1、方式一 --SHOW VARIABLES LIKE ‘basedir’;

步骤1：登录MySQL数据库

首先，我们需要登录MySQL数据库。可以使用命令行工具或者图形化界面进行登录。在命令行中，可以使用以下命令登录：

mysql -u 用户名 -p

其中，用户名是你的MySQL用户名。然后，系统会提示你输入密码，输入正确的密码后就可以登录到MySQL数据库。

步骤2：执行系统变量查询命令

登录成功后，可以执行以下命令来查询MySQL数据库的安装路径：

SHOW VARIABLES LIKE 'basedir';

该命令会返回一个结果集，其中包含了MySQL数据库的安装路径。在结果集中，Variable_name列显示的是系统变量名，Value列显示的是对应的值。我们需要找到basedir这个系统变量，它就是MySQL数据库的安装路径。

mysql> SHOW VARIABLES LIKE 'basedir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| basedir       | /usr/local/mysql/ |
+---------------+-------------------+
1 row in set (0.00 sec)

mysql>

步骤3：查看MySQL数据库的安装路径

在结果集中，找到basedir这个系统变量，它对应的值就是MySQL数据库的安装路径。

需要注意的是，MySQL数据库的安装路径可能因为操作系统的不同而有所差异。在Windows系统上，默认的安装路径是C:\Program Files\MySQL\MySQL Server X.X，其中X.X是MySQL的版本号。而在Linux系统上，默认的安装路径可能是/usr/local/mysql或者/usr/mysql。

1.2、方式二 --ps -ef | grep mysql

ps -ef | grep mysql 是一个常用的命令组合，用于查找正在运行的 MySQL 进程。

ps -ef | grep mysql 

其中包括 MySQL 进程的 PID（进程ID）、PPID（父进程ID）、USER（运行进程的用户）、COMMAND（进程的命令）等详细信息。

二、MySQL设置连接数与最大并发数

在高并发的应用场景中，MySQL数据库的连接数和最大并发数的设置是非常重要的。如何设置MySQL的连接数和最大并发数，以及如何合理地配置这些参数来提高数据库的性能和稳定性。

MySQL的max_connections参数用来设置最大连接（用户）数。每个连接MySQL的用户均算作一个连接。

MySQL无论如何都会保留一个用于管理员（SUPER）登录的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1；

这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准；

增加max_connections参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；

该参数设置过小的最明显特征是出现”Too many connections”错误；

2.1、永久生效–修改my.cnf文件

MySQL连接数是指同时连接到MySQL数据库的客户端数量。如果连接数超过MySQL的默认设置，可能会导致数据库性能下降，甚至崩溃。

步骤1：查看当前连接数的设置：

在MySQL命令行界面输入以下命令来查看当前连接数的设置：

SHOW VARIABLES LIKE 'max_connections';

默认情况下，MySQL的连接数为150。

步骤2：修改连接数的设置：

要修改MySQL的连接数，需要编辑MySQL的配置文件my.cnf。在该文件中找到[mysqld]节点下的max_connections参数，并将其修改为所需的值。例如，将连接数修改为500：

[mysqld]
max_connections = 500

步骤3：修改完成后，重启MySQL服务使配置生效。

[root@zyl-server ~]# systemctl restart mysqld
[root@zyl-server ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since Tue 2024-01-02 09:11:09 CST; 7s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 1753 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
  Process: 1785 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 28
   Memory: 172.6M
   CGroup: /system.slice/mysqld.service
           ├─1796 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/mysql --pid-file=/usr/local/mysql/mysql/zyl-server.pid
           └─2023 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.l...

Jan 02 09:11:08 zyl-server systemd[1]: Starting LSB: start and stop MySQL...
Jan 02 09:11:09 zyl-server mysqld[1785]: Starting MySQL.[  OK  ]
Jan 02 09:11:09 zyl-server systemd[1]: Started LSB: start and stop MySQL.
[root@zyl-server ~]# 

步骤4：再次查看当前连接数的设置：

在MySQL命令行界面输入以下命令来查看当前连接数的设置：

SHOW VARIABLES LIKE 'max_connections';

2.2、临时生效–通过命令设置的全局变量

SET GLOBAL  max_connections=1024;
SHOW VARIABLES LIKE '%max_connections%';

当MySQL重启时，会重新加载配置文件，因此这种通过命令设置的全局变量会失效。要使设置持久化，需要在MySQL的配置文件中进行相应的修改。

三、其他相关参数设置

除了连接数和最大并发数，还有一些相关的参数也需要注意设置，以提高数据库的性能和稳定性。

1、wait_timeout参数：

wait_timeout参数指定了一个连接在没有活动的情况下保持多长时间后自动关闭。默认情况下，wait_timeout为28800秒（8小时）。可以根据实际需求将其适当调小，以释放空闲连接资源。

2、max_allowed_packet参数：

max_allowed_packet: 指定了MySQL服务器接收和发送的最大数据包大小。默认情况下，其值为4MB。如果应用程序需要处理大的数据包，可以适当增大该值。

3、innodb_buffer_pool_size参数：

innodb_buffer_pool_size：设置InnoDB存储引擎的缓冲池大小。该参数决定了InnoDB存储引擎可以使用的内存大小。一般来说，将该参数设置为物理内存的70-80%是比较合适的。

4、key_buffer_size参数：

key_buffer_size：设置MyISAM存储引擎的键缓冲区大小。该参数决定了MyISAM存储引擎可以使用的内存大小。一般来说，将该参数设置为物理内存的10%是比较合适的。

5、query_cache_size参数：

query_cache_size：设置查询缓存的大小。查询缓存可以缓存查询结果，提高查询性能。但是在高并发的情况下，查询缓存可能会成为性能瓶颈。一般来说，将该参数设置为0或者较小的值是比较合适的。

6、innodb_flush_log_at_trx_commit参数：

innodb_flush_log_at_trx_commit：设置InnoDB存储引擎的日志刷新策略。该参数决定了事务提交时日志的刷新方式。一般来说，将该参数设置为1是比较合适的，可以保证事务的持久性。

7、innodb_log_file_size参数：

innodb_log_file_size：设置InnoDB存储引擎的日志文件大小。该参数决定了InnoDB存储引擎的日志文件可以使用的磁盘空间大小。一般来说，将该参数设置为1-2GB之间的值是比较合适的。

8、innodb_file_per_table参数：

innodb_file_per_table：设置是否为每个InnoDB表创建单独的表空间文件。该参数决定了InnoDB表的数据和索引是否存储在独立的表空间文件中。一般来说，将该参数设置为ON是比较合适的，可以提高表的维护和管理效率。

这些参数的具体设置需要根据服务器的硬件资源、应用程序的需求以及数据库的负载情况进行调整。可以通过修改MySQL配置文件（my.cnf）来设置这些参数。

四、MySQL启动时出现”Cannot allocate memory for the buffer pool”错误

修改mysql的innodb_buffer_pool_size为60G后，启动报错，截图如下：

当MySQL启动时出现”Cannot allocate memory for the buffer pool”错误提示，意味着MySQL无法为缓冲池分配足够的内存。

mysql错误日志：

2024-01-02T01:36:56.462874Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-01-02T01:36:56.462955Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2024-01-02T01:36:56.462980Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.42) starting as process 2656 ...
2024-01-02T01:36:56.467231Z 0 [Note] InnoDB: PUNCH HOLE support available
2024-01-02T01:36:56.467275Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2024-01-02T01:36:56.467282Z 0 [Note] InnoDB: Uses event mutexes
2024-01-02T01:36:56.467289Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2024-01-02T01:36:56.467296Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.13
2024-01-02T01:36:56.467302Z 0 [Note] InnoDB: Using Linux native AIO
2024-01-02T01:36:56.467551Z 0 [Note] InnoDB: Number of pools: 1
2024-01-02T01:36:56.467600Z 0 [Note] InnoDB: Using CPU crc32 instructions
2024-01-02T01:36:56.468311Z 0 [Note] InnoDB: Initializing buffer pool, total size = 60G, instances = 8, chunk size = 128M
2024-01-02T01:36:56.609202Z 0 [ERROR] InnoDB: mmap(137428992 bytes) failed; errno 12
2024-01-02T01:36:56.671297Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2024-01-02T01:36:56.671337Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-01-02T01:36:56.671344Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2024-01-02T01:36:56.671347Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-01-02T01:36:56.671351Z 0 [ERROR] Failed to initialize builtin plugins.
2024-01-02T01:36:56.671353Z 0 [ERROR] Aborting

2024-01-02T01:36:56.671360Z 0 [Note] Binlog end
2024-01-02T01:36:56.671403Z 0 [Note] Shutting down plugin 'MyISAM'
2024-01-02T01:36:56.675346Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

解决：

增加系统内存： 如果可行的话，可以尝试增加系统的物理内存。这样MySQL就能够分配更多的内存给缓冲池。

1、查看操作系统的/proc/sys/vm/overcommit_memory，默认值应该是0。

[root@zyl-server ~]# cat /proc/sys/vm/overcommit_memory
2
[root@zyl-server ~]#

参数含义：

overcommit_memory=0，  表示内核将检查是否有足够的可用内存供应用进程使用；如果有足够的可用内存，内存申请允许；否则，内存申请失败，并把错误返回给应用进程。

overcommit_memory=1， 表示内核允许分配所有的物理内存，而不管当前的内存状态如何。

overcommit_memory=2， 表示内核允许分配超过所有物理内存和交换空间总和的内存。

当overcommit_memory=2的时候，它一般是代表的是系统中总的内存的百分比，具体的虚拟内存总和为：

内存总和=SwapTotal + MemTotal * overcommit_ratio，这里的 overcommit_ratio默认为50%。

2、修改 /proc/sys/vm/overcommit_memory为0。

[root@zyl-server ~]# cat /proc/sys/vm/overcommit_memory
2
[root@zyl-server ~]# echo  0  > /proc/sys/vm/overcommit_memory
[root@zyl-server ~]# systemctl restart mysqld
[root@zyl-server ~]#

修改完成后，再次启动mysql，此时能够正常启动。

调整MySQL配置： 可以通过修改MySQL配置文件中的innodb_buffer_pool_size参数来调整缓冲池的大小。将其设置为一个较小的值，以适应当前可用的系统内存。

五、MySQL数据库的编码问题

在使用MySQL数据库时，出现了字符集不匹配、乱码等问题。

5.1 编码问题解决–临时（设置当前会话的字符集）

mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)

mysql>

它相当于分别设置客户端字符集、结果字符集和连接字符集为utf8。所有通过该会话发送给数据库的字符数据都会以utf8的编码方式进行处理。

SET character_set_client = utf8; 设置客户端字符集为utf8，即客户端发送给数据库的数据使用utf8编码。

SET character_set_results = utf8; 设置结果字符集为utf8，即数据库返回给客户端的结果使用utf8编码。

SET character_set_connection = utf8; 设置连接字符集为utf8，即客户端和数据库之间的连接使用utf8编码。

需要注意的是，SET NAMES ‘utf8’语句只是设置了当前会话的字符集，并不会修改数据库的默认字符集。如果需要修改数据库的默认字符集，需要通过其他方式来实现，比如在创建数据库时指定字符集。

如下：

1、修改MySQL数据库的默认字符集： 可以通过修改MySQL配置文件（my.cnf）中的default-character-set参数来设置MySQL数据库的默认字符集。或者通过AlTER DATABASE语句。

AlTER DATABASE 数据库名 CHARACTER SET utf8;

2、修改表的字符集： 可以通过ALTER TABLE语句来修改表的字符集。

AlTER TABLE 表名 CHARACTER SET utf8;

**3、修改字段的字符集：**可以通过ALTER TABLE语句来修改字段的字符集。

AlTER TABLE 表名 MODIFY 字段 VARCHAR(255) CHARACTER SET utf8;

VARCHAR(255)可以根据字段的实际类型进行调整。

5.2 编码问题解决–永久（修改my.cnf）

1. 修改数据库配置文件（vi /etc/my.cnf）

--在 [mysqld] 标签下加上三行

default-character-set = utf8

character_set_server = utf8

lower_case_table_names = 1 //表名不区分大小写（此与编码无关）

--在 [mysql] 标签下加上一行

default-character-set = utf8

--在 [mysql.server]标签下加上一行

default-character-set = utf8

--在 [mysqld_safe]标签下加上一行

default-character-set = utf8

--在 [client]标签下加上一行

default-character-set = utf8

2. 重启数据库服务

systemctl restart mysqld

3. 查看设置结果

show variables like "%char%";

show variables like "%collation%";

5.3 utf8 与 utf8mb4区别

MySQL数据库编码utf8与utf8mb4都是用于存储Unicode字符集的编码格式，但是它们之间有一些区别。

1、存储范围： utf8编码最多可以存储3个字节的Unicode字符，而utf8mb4编码可以存储4个字节的Unicode字符。utf8mb4编码可以支持更多的字符，包括一些特殊字符和emoji表情符号。

2、存储空间： 由于utf8mb4编码存储的字符范围更广，所以在存储相同的字符时，utf8mb4编码需要更多的存储空间。例如，存储一个emoji表情符号，utf8mb4编码需要4个字节，而utf8编码则无法存储。

3、索引长度限制： MySQL的索引长度限制是以字节为单位的，utf8编码下，一个字符最多占用3个字节，所以一个索引字段最多可以包含3333个字符；而utf8mb4编码下，一个字符最多占用4个字节，所以一个索引字段最多可以包含250个字符。

4、兼容性： utf8编码在早期版本的MySQL中存在一些问题，例如，无法正确存储某些特殊字符。utf8mb4编码是utf8编码的改进版，可以解决这些问题。

总而言之，如果你的应用需要存储特殊字符或者emoji表情符号，建议使用utf8mb4编码。如果只需要存储普通的字符，utf8编码已经足够。

💖The End💖点点关注，收藏不迷路💖