• CentOS 7 Nginx安装步骤及常见问题汇总，亲测有效！
• CentOS 7 Redis安装、数据目录更改新位置，亲测有效！

一、安装

1、安装包

• 最新版本包源

>sudo vi /etc/yum.repos.d/MariaDB.repo 

• 输入内容

[mariadb]
name = MariaDB
baseurl = http://mirrors.aliyun.com/mariadb/yum/10.4/centos7-amd64/
gpgkey = http://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

• 清理yum并重新加载yum

sudo yum clean all
sudo yum makecache

• 安装MariaDB包

> sudo yum install MariaDB-server MariaDB-client -y

• 可能会出现问题：

...
...
You could try using --skip-broken to work around the problem
 You could try running: package-cleanup --problems
                        package-cleanup --dupes
                        rpm -Va --nofiles --nodigest

解决方案：更换Centos的镜像文件 https://developer.aliyun.com/mirror/centos?spm=a2c6h.13651102.0.0.3e221b11UsWMz9

• 欧拉系统镜像源更改为阿里镜像：

cp /etc/yum.repos.d/openEuler.repo /etc/yum.repos.d/openEuler.repo.backup
sed -i "s#repo.openeuler.org#mirrors.aliyun.com/openeuler#g" /etc/yum.repos.d/openEuler.repo
yum clean all
yum makecache

欧拉操作系统报错

2、设置开机启动服务

> sudo systemctl enable mariadb

3、启动/停止/重启MariaDB服务

• 启动

> sudo systemctl start mariadb

• 停止

> sudo systemctl stop mariadb

• 重启

> sudo systemctl restart mariadb

4、查看运行状态

> sudo systemctl status mariadb

5、安全配置

# 默认安装
sudo mysql_secure_installation
# 【推荐】或指定安装目录以及数据目录
sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/datas

• 交互操作

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.(您的根帐户已受保护，因此可以安全地回答“n”。)

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password?（是否设置root密码） [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? （是否删除匿名访问）[Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely?（是否禁止root远程访问） [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it?（是否删除测试数据库并访问它） [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now?（是否立即重新加载权限表） [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

6、测试访问

> mysql -u root -p Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.27-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

7、配置root远程访问权限

注意：123456为密码，可以修改你需要设置的密码

• 远程权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
• 刷新权限： flush privileges;

> mysql -u root -p Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.27-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye
[xnzf@localhost ~]$ 

• 开启防火墙端口 firewall安装教程
  添加3306端口

> sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent

重新加载防火墙

> sudo firewall-cmd --reload

查看防火墙开放成功的端口

> sudo firewall-cmd --list-ports

删除防火墙开放的端口

> sudo firewall-cmd --zone=public --remove-port=3306/tcp --permanent

• 或 iptables 设置方式

修改配置文件

vi  /etc/sysconfig/iptables

添加-A RH-Firewall-1-INPUT -p tcp -m conntrack --ctstate NEW -m tcp --dport 3306 -j ACCEPT内容，注意添加的位置否则影响防火墙3306端口失效

vi  /etc/sysconfig/iptables

# Generated by iptables-save v1.4.21 on Sun May 10 01:05:48 2020
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [32:5584]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp -m icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -i xenapi -p udp -m udp --dport 67 -j ACCEPT
-A RH-Firewall-1-INPUT -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m conntrack --ctstate NEW -m udp --dport 694 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m conntrack --ctstate NEW -m tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m conntrack --ctstate NEW -m tcp --dport 3306 -j ACCEPT # 注意添加位置
-A RH-Firewall-1-INPUT -p tcp -m conntrack --ctstate NEW -m tcp --dport 80 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m conntrack --ctstate NEW -m tcp --dport 443 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 21064 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m multiport --dports 5404,5405 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT
# Completed on Sun May 10 01:05:48 2020

重启防火墙

sudo systemctl restart iptables.service

查看防火墙是否生效，命令：iptables -L -n

> iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0           

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination         

Chain RH-Firewall-1-INPUT (2 references)
target     prot opt source               destination         
ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           
ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0            icmptype 255
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0            udp dpt:67
ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0            ctstate RELATED,ESTABLISHED
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0            ctstate NEW udp dpt:694
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            ctstate NEW tcp dpt:22
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            ctstate NEW tcp dpt:3306
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            ctstate NEW tcp dpt:80
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            ctstate NEW tcp dpt:443
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:21064
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0            multiport dports 5404,5405
REJECT     all  --  0.0.0.0/0            0.0.0.0/0            reject-with icmp-host-prohibited

8 、其他

• 查看错误日志

mysqld --help --verbose | grep 'log-error' | tail -1

• 查看数据目录

mysqld --help --verbose | grep 'datadir' | tail -1

二、卸载

• 查看相关包

> rpm -aq|grep mariadb  
mariadb-libs-5.5.50-1.e17_2.x86_64  
mariadb-5.5.50-1.e17_2.x86_64  
mariadb-server-5.5.50-1.e17_2.x86_64  

• 删除所有相关包

> yum -y remove mariadb*

三、数据目录更改为新位置

• 查看原目录 - 方式1

> mysqld --help --verbose | grep 'datadir' | tail -1
2019-11-05 10:48:03 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 4198)
2019-11-05 10:48:03 0 [Warning] Changed limits: max_open_files: 1024  max_connections: 151 (was 151)  table_cache: 421 (was 2000)
2019-11-05 10:48:03 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-11-05 10:48:03 0 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
datadir                                                    /var/lib/mysql/

• 查看原目录 - 方式2（需登录MySQL命令行中执行）

> sudo mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.27-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@datadir as dataPath from dual ;
+--------------+
| dataPath     |
+--------------+
| /var/lib/mysql/ |
+--------------+
1 row in set (0.00 sec)

• 更改新位置目录为：/home/mysql

注意：如果你将目录设置为/home/xxx/mysql，可能引发 [Warning] Can't create test file /var/lib/mysql/localhost.lower-test，你会查各种资料，最终涉及到SELinux权限问题，整个处理起来太过繁琐。教你排查问题：/home/xxx/mysql新位置mysql目录的递归上级目录不能包含非root权限组的权限，否则你就算是按照很多教程配置的数据目录，最后还是不能启动服务。最后一句：注意整个路径所有目录权限！！！！

• 停止服务

> sudo systemctl stop mariadb

• 查看服务状态是否停止

> sudo systemctl status mariadb
. . .
Dec 16 18:29:26 mysql systemd[1]: Stopped MariaDB database server.

• 将现有数据库目录复制到新位置

> sudo cp -a -R /var/lib/mysql /home

• 原目录备份

> sudo mv /var/lib/mysql /var/lib/mysql.bak

• 修改/etc/my.cnf.d/server.cnf，在[mysqld]下新增datadir、socket

> sudo vi /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]
datadir=/home/mysql
socket=/home/mysql/mysql.sock

#
# * 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.4 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.4]

• 修改/etc/my.cnf，新增socket=/home/mysql/mysql.sock。如果文件不存在直接新增

> sudo vi /etc/my.cnf

内容如下：

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
port=3306
socket=/home/mysql/mysql.sock

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

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

• 修改systemctl服务/usr/lib/systemd/system/mariadb.service，将ProtectHome设置为false，允许访问/home目录

> sudo vi /usr/lib/systemd/system/mariadb.service

内容如下：

#
# /etc/systemd/system/mariadb.service
#
# This file is free software; you can redistribute it and/or modify it
# under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation; either version 2.1 of the License, or
# (at your option) any later version.
#
# Thanks to:
# Daniel Black
# Erkan Yanar
# David Strauss
# and probably others

[Unit]
Description=MariaDB 10.4.8 database server
Documentation=man:mysqld(8)
Documentation=https://mariadb.com/kb/en/library/systemd/
After=network.target

[Install]
WantedBy=multi-user.target
Alias=mysql.service
Alias=mysqld.service


[Service]

##############################################################################
## Core requirements
##

Type=notify

# Setting this to true can break replication and the Type=notify settings
# See also bind-address mysqld option.
PrivateNetwork=false

##############################################################################
## Package maintainers
##

User=mysql
Group=mysql

# CAP_IPC_LOCK To allow memlock to be used as non-root user
# CAP_DAC_OVERRIDE To allow auth_pam_tool (which is SUID root) to read /etc/shadow when it's chmod 0
#   does nothing for non-root, not needed if /etc/shadow is u+r
# CAP_AUDIT_WRITE auth_pam_tool needs it on Debian for whatever reason
CapabilityBoundingSet=CAP_IPC_LOCK CAP_DAC_OVERRIDE CAP_AUDIT_WRITE

# PrivateDevices=true implies NoNewPrivileges=true and
# SUID auth_pam_tool suddenly doesn't do setuid anymore
PrivateDevices=false

# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full

# Doesn't yet work properly with SELinux enabled
# NoNewPrivileges=true

# Prevent accessing /home, /root and /run/user
ProtectHome=false

# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true



# Perform automatic wsrep recovery. When server is started without wsrep,
# galera_recovery simply returns an empty string. In any case, however,
# the script is not expected to return with a non-zero status.
# It is always safe to unset _WSREP_START_POSITION environment variable.
# Do not panic if galera_recovery script is not available. (MDEV-10538)
ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
ExecStartPre=/bin/sh -c "[ ! -e /usr/bin/galera_recovery ] && VAR= || \
 VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] \
 && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"

# Needed to create system tables etc.
# ExecStartPre=/usr/bin/mysql_install_db -u mysql

# Start main service
# MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
# Use the [Service] section and Environment="MYSQLD_OPTS=...".
# This isn't a replacement for my.cnf.
# _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION


# Unset _WSREP_START_POSITION environment variable.
ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"

KillSignal=SIGTERM

# Don't want to see an automated SIGKILL ever
SendSIGKILL=no

# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s

UMask=007

##############################################################################
## USERs can override
##
##
## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
## and adding/setting the following under [Service] will override this file's
## settings.

# Useful options not previously available in [mysqld_safe]

# Kernels like killing mysqld when out of memory because its big.
# Lets temper that preference a little.
# OOMScoreAdjust=-600

# Explicitly start with high IO priority
# BlockIOWeight=1000

# If you don't use the /tmp directory for SELECT ... OUTFILE and
# LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
PrivateTmp=false

##
## Options previously available to be set via [mysqld_safe]
## that now needs to be set by systemd config files as mysqld_safe
## isn't executed.
##

# Number of files limit. previously [mysqld_safe] open-file-limit
LimitNOFILE=16364

# Maximium core size. previously [mysqld_safe] core-file-size
# LimitCore=

# Nice priority. previously [mysqld_safe] nice
# Nice=-5

# Timezone. previously [mysqld_safe] timezone
# Environment="TZ=UTC"

# Library substitutions. previously [mysqld_safe] malloc-lib with explicit paths
# (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
# Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=

# Flush caches. previously [mysqld_safe] flush-caches=1
# ExecStartPre=sync
# ExecStartPre=sysctl -q -w vm.drop_caches=3

# numa-interleave=1 equalivant
# Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......

# crash-script equalivent
# FailureAction=

• 重启服务

sudo systemctl daemon-reload
sudo systemctl restart mariadb.service

• 查看目录是否修改成功

> mysqld --help --verbose | grep 'datadir' | tail -1
2019-11-05 14:16:08 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 4198)
2019-11-05 14:16:08 0 [Warning] Changed limits: max_open_files: 1024  max_connections: 151 (was 151)  table_cache: 421 (was 2000)
2019-11-05 14:16:08 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-11-05 14:16:08 0 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
datadir                                                    /home/mysql/

到此结束。

四、备份、还原数据库

1、备份数据库

• 创建备份脚本

vi daas_mysql_back.sh

• 赋予可执行权限

chmod +x daas_mysql_back.sh

• 添加脚本内容

backupdir='/home/uname/data_back/daas'
time=`date +%Y%m%d_%H%M%S_%N`
mysqldump --opt --lock-tables=false -uroot -p'密码' -R daas | gzip > $backupdir/daas_$time.sql.gz

• 创建清理备份脚本

vi clear_mysql_back.sh

• 添加脚本内容，备份数据库30天

find /home/xnzf/data_back/daas -mtime +30 -name "*.sql.gz" -exec rm -rf {} rm -rf {} \; > /dev/null 2>&1

• 赋予可执行权限

chmod +x clear_mysql_back.sh

• 添加定时任务，每两小时执行一次

crontab -e

0 */2 * * * /home/xnzf/data_back_sh/daas_mysql_back.sh
0 */2 * * * /home/xnzf/data_back_sh/clear_mysql_back.sh

• 重启服务，使定时服务生效

systemctl restart crond.service

• crontab基本操作

crontab -u //设定某个用户的cron服务
crontab -l //列出某个用户cron服务的详细内容
crontab -r //删除某个用户的cron服务
crontab -e //编辑某个用户的cron服务
crontab -i //打印提示，输入yes等确认信息

/var/spool/cron/root (以用户命名的文件) 是所有默认存放定时任务的文件
/etc/cron.deny 该文件中所列出用户不允许使用crontab命令
/etc/cron.allow 该文件中所列出用户允许使用crontab命令，且优先级高于/etc/cron.deny
/var/log/cron 该文件存放cron服务的日志

2、还原数据库

• 创建数据库

drop databases daas;
create databases daas;

• 还原备份数据库文件

>mysql -uroot -p -f --default-character-set=utf8 daas < E:\daas_back\daas_20200714_120001_219507331.sql
Enter password: ******

注意：在导入数据库时，有可能多个视图有关联，导致执行SQL时关联视图还未创建，引发视图不存在的错误。最简单的解决方案就是忽略错误，多导入一次就可以了
导入错误示例：ERROR 1146 (42S02) at line 3735: Table 'daas.v_publish_data_standard_field' doesn't exist

五、常见问题

• 修改“/etc/my.cnf”配置文件

[client]
default-character-set=utf8
socket=/storage/db/mysql/mysql.sock
[mysql]
default-character-set=utf8
socket=/storage/db/mysql/mysql.sock

• Error 1615: Prepared statement needs to be re-prepared

MySQL > SET GLOBAL table_open_cache=16384;
MySQL > SET GLOBAL table_definition_cache=16384;

忘记密码

• 设置配置文件，在[mysqld]下添加skip-grant-tables

> sudo vi /etc/my.cnf.d/server.cnf

[mysqld]
skip-grant-tables

• 重启服务

> sudo systemctl restart mariadb

• 无密码数据库连接

>  mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>  flush privileges; # 先执行，否则会报错 ERROR 1348 (HY000): Column 'Password' is not updatable
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> ALTER USER'root'@'localhost' IDENTIFIED BY '123456'; # 再修改密码
Query OK, 0 rows affected (0.009 sec)

常见操作

• 赋予用户某一个数据库权限

-- database_name 数据库名
-- user_name     数据库用户名
-- user_password 数据库用户名

grant all privileges on `database_name`.* to user_name@'%'identified by 'user_password';
flush privileges;

Error 1615: Prepared statement needs to be re-prepared

SET GLOBAL table_open_cache=16384;
SET GLOBAL table_definition_cache=16384;     

性能调优

15 个有用的 MySQL/MariaDB 性能调整和优化技巧

MySQL/MariaDB 配置文件位于 /etc/my.cnf。 每次更改此文件后你需要重启 MySQL 服务，以使更改生效。

# 数据库表放在一个单独的存储设备
innodb_file_per_table=1
# 在一个专用的机器上，你可能会把 60-70％ 的内存分配给 innodb_buffer_pool_size
innodb_buffer_pool_size=22G

• 百万数据无条件查询count(1)超慢解决方案

试过文章千百遍，灵机一动就好很多了，忧伤得很。
表中200多万数据，53个字段，select count(1) from JW_CJ_XSCJB 花了240多秒时间，恐怖啊。
一看分析，索引用的主键，但试想应该也不至于这么慢吧，然后各种mariadb查询参数缓存调优，尝试各种方案，都无用

id   select_type  table         type        possible_keys   key             key_len   ref   rows       Extra
1   SIMPLE        JW_CJ_XSCJB   index                       Primarykey      157             2685172    Using index

然后测试给表添加一项索引，查询时间提升了上百倍，耗时

id   select_type  table         type        possible_keys   key             key_len   ref   rows       Extra
1   SIMPLE        JW_CJ_XSCJB   index                       logkit_rowhash  157             2685172    Using index

image.png

MariaDB 修改存储路径后启动失败问题解决

• 修改 MariaDB 路径到 home 路径下，

执行 systemctl start mariadb 启动MariaDB 时，报错提示：

[root]$ systemctl start mariadb
[root]$ Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

查看报错详情

[root]$ systemctl status mariadb.service 
[root]$
...
...
 [Warning] Can't create test file /home/data/mariadbData/localhost.lower-test
mariadb.service: main process exited, code=exited, status=1/FAILURE
Failed to start MariaDB 10.3.9 database server.

问题的原因是因为 mariadb 没有 home 路径的权限

解决方法：

1. 关闭 selinux

2. 修改 存储路径权限，

chown -R mysql:mysql /home/mysql_data
chmod -R 764 /home/mysql_data

3. 如果你的存储路径是home 下面的路径，那么接下来是重点:

sudo vi /etc/systemd/system/mysql.service

服务配置文件有可能不同版本有差异，可以在/etc/systemd/system/目录下找到对应的service服务进行修改

• 找到ProtectHome改为false

[Service]
ProtectHome=false

• 然后执行

sudo systemctl daemon-reload

• 重启mariadb

systemctl start mariadb

ERROR 2002 (HY000): Can't connect to local server through socket '/home/mysql/mysql.sock' (13)

问题描述：安装之后，通过 mysql -uroot -p 报错

sudo mysql -uroot -p

• 设置root远程访问
• 远程权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
• 刷新权限： flush privileges;