Mariadb 数据库修改打开文件限制open_files_limit——筑梦之路

mariadb 遇到一个文件，使用mysqldump备份数据库的时候会出现 mysqldump: Got error: 1016: "Can't open file: './testxxx/table1.frm' (errno: 24)" when using LOCK TABLES 类似这样错误，一般的解决办法是在/etc/my.cnf 配置文件中添加参数open_files_limit=10240，然后重启数据库服务即可。但是mariadb这样做了之后，登陆数据库使用命令show variables like '%open%'; 验证的时候，仍然还是默认的1024，因此是不满足要求的，这里主要介绍两种方式来改变打开文件限制。

设置说明：

#cat /usr/lib/systemd/system/mariadb.service

# It's not recommended to modify this file in-place, because it will be

# overwritten during package upgrades.  If you want to customize, the

# best way is to create a file "/etc/systemd/system/mariadb.service",

# containing

#       .include /lib/systemd/system/mariadb.service

#       ...make your changes here...

# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",

# which doesn't need to include ".include" call and which will be parsed

# after the file mariadb.service itself is parsed.

#

# For more info about custom unit files, see systemd.unit(5) or

# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

 

# For example, if you want to increase mariadb's open-files-limit to 10000,

# you need to increase systemd's LimitNOFILE setting, so create a file named

# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:

#       [Service]

#       LimitNOFILE=10000

 

# Note: /usr/lib/... is recommended in the .include line though /lib/...

# still works.

# Don't forget to reload systemd daemon after you change unit configuration:

# root> systemctl --system daemon-reload

方式一：

#创建目录，新建配置文件
mkdir -p /etc/systemd/system/mariadb.service.d/

vim /etc/systemd/system/mariadb.service.d/limits.conf

[Service]
LimitNOFILE=10240

systemctl daemon-reload

systemctl restart mariadb


验证：show global variables like 'open_files_limit';


方式二：

直接编辑/usr/lib/systemd/system/mariadb.service

[Service]

Type=simple

User=mysql

Group=mysql

LimitNOFILE=10240

systemctl daemon-reload

systemctl restart mariadb

验证：show global variables like 'open_files_limit';

方式三：

LimitNOFILE=infinity  #推荐改成这个值

题外话：

my.cnf 配置文件中添加客户端编码配置

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4