湖南手机网站制作公司,电商网站对比,行牛建站,网站开发人员培训「MySQL运维常见问题及解决方法」 一、查看MySQL数据库安装路径1.1、方式一 --SHOW VARIABLES LIKE basedir;1.2、方式二 --ps -ef | grep mysql 二、MySQL设置连接数与最大并发数2.1、永久生效--修改my.cnf文件2.2、临时生效--通过命令设置的全局变量 三、其他相关参数设置四、… 「MySQL运维常见问题及解决方法」 一、查看MySQL数据库安装路径1.1、方式一 --SHOW VARIABLES LIKE basedir;1.2、方式二 --ps -ef | grep mysql 二、MySQL设置连接数与最大并发数2.1、永久生效--修改my.cnf文件2.2、临时生效--通过命令设置的全局变量 三、其他相关参数设置四、MySQL启动时出现”Cannot allocate memory for the buffer pool”错误五、MySQL数据库的编码问题5.1 编码问题解决--临时设置当前会话的字符集5.2 编码问题解决--永久修改my.cnf5.3 utf8 与 utf8mb4区别 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_connections1
这个参数实际起作用的最大值实际最大可连接数为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服务使配置生效。
[rootzyl-server ~]# systemctl restart mysqld
[rootzyl-server ~]# systemctl status mysqld
● mysqld.service - LSB: start and stop MySQLLoaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)Active: active (running) since Tue 2024-01-02 09:11:09 CST; 7s agoDocs: man:systemd-sysv-generator(8)Process: 1753 ExecStop/etc/rc.d/init.d/mysqld stop (codeexited, status0/SUCCESS)Process: 1785 ExecStart/etc/rc.d/init.d/mysqld start (codeexited, status0/SUCCESS)Tasks: 28Memory: 172.6MCGroup: /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 --usermysql --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.
[rootzyl-server ~]#
步骤4再次查看当前连接数的设置
在MySQL命令行界面输入以下命令来查看当前连接数的设置
SHOW VARIABLES LIKE max_connections;2.2、临时生效–通过命令设置的全局变量
SET GLOBAL max_connections1024;
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] Aborting2024-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。
[rootzyl-server ~]# cat /proc/sys/vm/overcommit_memory
2
[rootzyl-server ~]#参数含义
overcommit_memory0 表示内核将检查是否有足够的可用内存供应用进程使用如果有足够的可用内存内存申请允许否则内存申请失败并把错误返回给应用进程。overcommit_memory1 表示内核允许分配所有的物理内存而不管当前的内存状态如何。overcommit_memory2 表示内核允许分配超过所有物理内存和交换空间总和的内存。当overcommit_memory2的时候它一般是代表的是系统中总的内存的百分比具体的虚拟内存总和为内存总和SwapTotal MemTotal * overcommit_ratio这里的 overcommit_ratio默认为50%。2、修改 /proc/sys/vm/overcommit_memory为0。
[rootzyl-server ~]# cat /proc/sys/vm/overcommit_memory
2
[rootzyl-server ~]# echo 0 /proc/sys/vm/overcommit_memory
[rootzyl-server ~]# systemctl restart mysqld
[rootzyl-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
修改数据库配置文件vi /etc/my.cnf
--在 [mysqld] 标签下加上三行default-character-set utf8character_set_server utf8lower_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点点关注收藏不迷路
