安装成功后配置MariaDB的下一步

Question

除了正式的文档(如https://mariadb.com/resources/blog/installing-mariadb-10-centos-7-rhel-7等)之外，还有无数关于安装MariaDB的教程，但是，没有一个教程说可能需要为特定的安装需求执行额外的步骤。下一个未写入和假定的步骤是根据需要修改/etc/my.cnf吗？当我开始这样做时，我惊讶地发现，我的配置文件实际上是空的，而不像典型的长卷配置文件，其中包含各种配置设置。这个/etc/my.cnf文件是我应该编辑的文件，还是某个常见的MySQL/MariaDB文件，还有其他一些特定于MariaDB的文件呢？或者是否可能没有像https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/提示那样的配置文件？如果我想做各种改变，例如时区等，下一步是什么？

cat /etc/os-release:

NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

PHP version: 7.1.14
cat /etc/yum.repos.d/MariaDB.repo (Gives information on how package was installed.):

# MariaDB 10.2 CentOS repository list - created 2017-07-17 12:45 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
ls -l /etc | grep sql (Nothing):

ls -l /etc | grep maria (Nothing):

ls -l /etc | grep my:

-rw-r--r--   1 root root      202 Feb 12 15:01 my.cnf
drwxr-xr-x   2 root root       78 Feb 19 12:05 my.cnf.d
cat /etc/my.cnf:

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

ls -l /etc/my.cnf.d:

total 12
-rw-r--r-- 1 root root  763 Feb 12 15:01 enable_encryption.preset
-rw-r--r-- 1 root root  232 Feb 12 15:01 mysql-clients.cnf
-rw-r--r-- 1 root root 1080 Feb 12 15:01 server.cnf
tail -n +1 -- /etc/my.cnf.d/*:

==> /etc/my.cnf.d/enable_encryption.preset <==
#
# !include this file into your my.cnf (or any of *.cnf files in /etc/my.cnf.d)
# and it will enable data at rest encryption. This is a simple way to
# ensure that everything that can be encrypted will be and your
# data will not leak unencrypted.
#
# DO NOT EDIT THIS FILE! On MariaDB upgrades it might be replaced with a
# newer version and your edits will be lost. Instead, add your edits
# to the .cnf file after the !include directive.
#
# NOTE that you also need to install an encryption plugin for the encryption
# to work. See https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/#encryption-key-management
#
[mariadb]
aria-encrypt-tables
encrypt-binlog
encrypt-tmp-disk-tables
encrypt-tmp-files
loose-innodb-encrypt-log
loose-innodb-encrypt-tables

==> /etc/my.cnf.d/mysql-clients.cnf <==
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]


==> /etc/my.cnf.d/server.cnf <==
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.1]

ls -l /usr/share/mysql | grep cnf (Should I be swapping out /etc/my.cnf with one of these?):

-rw-r--r-- 1 root root   4920 Feb 12 16:58 my-huge.cnf
-rw-r--r-- 1 root root  20441 Feb 12 16:58 my-innodb-heavy-4G.cnf
-rw-r--r-- 1 root root   4907 Feb 12 16:58 my-large.cnf
-rw-r--r-- 1 root root   4920 Feb 12 16:58 my-medium.cnf
-rw-r--r-- 1 root root   2846 Feb 12 16:58 my-small.cnf
-rw-r--r-- 1 root root   3452 Feb 12 16:58 wsrep.cnf

编辑。啊，mysqld --help --verbose和mysqladmin variables在一定程度上就是我要找的。但问题依然存在。这些默认设置在哪里定义，为什么我找不到任何建议修改它们的文档/教程/博客？

mysqld  Ver 10.2.13-MariaDB for Linux on x86_64 (MariaDB Server)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Starts the MariaDB database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-10.2 mariadb mariadb-10.2 client-server galera
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.

....

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}                           Value (after reading options)
---------------------------------------------------------- ---------------
allow-suspicious-udfs                                      FALSE
aria                                                       ON
aria-block-size                                            8192
aria-checkpoint-interval                           

PS。以下内容用于生成上述内容，仅供参考。

'.$cmd.($extra?'   ('.$extra.')':'').':'.shell_exec($cmd).'';
}

echo execute('cat /etc/os-release');
echo 'PHP version: ' . phpversion().'
';
echo execute('cat /etc/yum.repos.d/MariaDB.repo','Gives information on how package was installed.');
echo execute('ls -l /etc | grep sql','Nothing');
echo execute('ls -l /etc | grep maria','Nothing');
echo execute('ls -l /etc | grep my');
echo execute('cat /etc/my.cnf');
echo execute('ls -l /etc/my.cnf.d');
echo execute('tail -n +1 -- /etc/my.cnf.d/*');
echo execute('ls -l /usr/share/mysql | grep cnf','Should I be swapping out /etc/my.cnf with one of these?');

Answer 1

my.cnf设置(包括my.cnf.d/*) (如果有的话)在内置默认值之前。对于通用安装来说，所有这些都是“合理的”。

只有在您发现应用程序不是“一般”之后，添加(或更改它们)才是重要的。

最重要的设置(性能)是innodb_buffer_pool_size；它应该设置为大约70%的可用内存。几乎所有其他事情(有数百个“变量”)都不需要在没有充分理由的情况下进行更改。

的确，这个论坛包含了与my.cnf中的设置相关的问题。我发现它们属于以下几类：

• “更大就是更好”--用户通过增加各种设置而陷入麻烦，而不了解负面影响(比如内存不足)。
• “我的网站很慢，帮我调一下”--答案几乎总是涉及INDEXes和/或查询格式，而不是调优。
• “我认为.”

要获得完整的列表(例如，命令行工具"mysql")：

SHOW VARIABLES;

我给了你一件要改变的东西，在你得到具体的建议之前，不要担心改变任何其他的东西。