「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 inset(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-0209: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 0209:11:08 zyl-server systemd[1]: Starting LSB: start and stop MySQL...
Jan 0209:11:09 zyl-server mysqld[1785]: Starting MySQL.[ OK ]
Jan 0209: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 isset 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.132024-01-02T01:36:56.467302Z 0[Note] InnoDB: Using Linux native AIO
2024-01-02T01:36:56.467551Z 0[Note] InnoDB: Number of pools:12024-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(137428992bytes) failed; errno 122024-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_memory2[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_memory2[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语句。
AlTERDATABASE 数据库名 CHARACTERSET utf8;
2、修改表的字符集: 可以通过ALTER TABLE语句来修改表的字符集。
AlTERTABLE 表名 CHARACTERSET utf8;
3、修改字段的字符集:可以通过ALTER TABLE语句来修改字段的字符集。
AlTERTABLE 表名 MODIFY 字段 VARCHAR(255)CHARACTERSET utf8;
VARCHAR(255)可以根据字段的实际类型进行调整。
5.2 编码问题解决–永久(修改my.cnf)
- 修改数据库配置文件(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
- 重启数据库服务
systemctl restart mysqld
- 查看设置结果
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💖点点关注,收藏不迷路💖
版权归原作者 醉颜凉 所有, 如有侵权,请联系我们删除。