• 停止服務(wù)

> sudo systemctl stop mariadb

• 查看服務(wù)狀態(tài)是否停止

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

• 將現(xiàn)有數(shù)據(jù)庫(kù)目錄復(fù)制到新位置

> 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

內(nèi)容如下：

# 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

內(nèi)容如下：

#
# 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服務(wù)/usr/lib/systemd/system/mariadb.service，將ProtectHome設(shè)置為false，允許訪問(wèn)/home目錄

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

內(nèi)容如下：

#
# /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=

• 重啟服務(wù)

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/

到此結(jié)束。

四、備份、還原數(shù)據(jù)庫(kù)

1、備份數(shù)據(jù)庫(kù)

• 創(chuàng)建備份腳本

vi daas_mysql_back.sh

• 賦予可執(zhí)行權(quán)限

chmod +x daas_mysql_back.sh

• 添加腳本內(nèi)容

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

• 創(chuàng)建清理備份腳本

vi clear_mysql_back.sh

• 添加腳本內(nèi)容，備份數(shù)據(jù)庫(kù)30天

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

• 賦予可執(zhí)行權(quán)限

chmod +x clear_mysql_back.sh

• 添加定時(shí)任務(wù)，每?jī)尚r(shí)執(zhí)行一次

crontab -e

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

• 重啟服務(wù)，使定時(shí)服務(wù)生效

systemctl restart crond.service

• crontab基本操作

crontab -u //設(shè)定某個(gè)用戶的cron服務(wù)
crontab -l //列出某個(gè)用戶cron服務(wù)的詳細(xì)內(nèi)容
crontab -r //刪除某個(gè)用戶的cron服務(wù)
crontab -e //編輯某個(gè)用戶的cron服務(wù)
crontab -i //打印提示，輸入yes等確認(rèn)信息

/var/spool/cron/root (以用戶命名的文件) 是所有默認(rèn)存放定時(shí)任務(wù)的文件
/etc/cron.deny 該文件中所列出用戶不允許使用crontab命令
/etc/cron.allow 該文件中所列出用戶允許使用crontab命令，且優(yōu)先級(jí)高于/etc/cron.deny
/var/log/cron 該文件存放cron服務(wù)的日志

2、還原數(shù)據(jù)庫(kù)

• 創(chuàng)建數(shù)據(jù)庫(kù)

drop databases daas;
create databases daas;

• 還原備份數(shù)據(jù)庫(kù)文件

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

注意：在導(dǎo)入數(shù)據(jù)庫(kù)時(shí)，有可能多個(gè)視圖有關(guān)聯(lián)，導(dǎo)致執(zhí)行SQL時(shí)關(guān)聯(lián)視圖還未創(chuàng)建，引發(fā)視圖不存在的錯(cuò)誤。最簡(jiǎn)單的解決方案就是忽略錯(cuò)誤，多導(dǎo)入一次就可以了
導(dǎo)入錯(cuò)誤示例：ERROR 1146 (42S02) at line 3735: Table 'daas.v_publish_data_standard_field' doesn't exist

五、常見(jiàn)問(wèn)題

• 修改“/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;

忘記密碼

• 設(shè)置配置文件，在[mysqld]下添加skip-grant-tables

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

[mysqld]
skip-grant-tables

• 重啟服務(wù)

> sudo systemctl restart mariadb

• 無(wú)密碼數(shù)據(jù)庫(kù)連接

>  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; # 先執(zhí)行，否則會(huì)報(bào)錯(cuò) 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)

常見(jiàn)操作

• 賦予用戶某一個(gè)數(shù)據(jù)庫(kù)權(quán)限

-- database_name 數(shù)據(jù)庫(kù)名
-- user_name     數(shù)據(jù)庫(kù)用戶名
-- user_password 數(shù)據(jù)庫(kù)用戶名

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;     

性能調(diào)優(yōu)

15 個(gè)有用的 MySQL/MariaDB 性能調(diào)整和優(yōu)化技巧

MySQL/MariaDB 配置文件位于 /etc/my.cnf。 每次更改此文件后你需要重啟 MySQL 服務(wù)，以使更改生效。

# 數(shù)據(jù)庫(kù)表放在一個(gè)單獨(dú)的存儲(chǔ)設(shè)備
innodb_file_per_table=1
# 在一個(gè)專用的機(jī)器上，你可能會(huì)把 60-70％ 的內(nèi)存分配給 innodb_buffer_pool_size
innodb_buffer_pool_size=22G

• 百萬(wàn)數(shù)據(jù)無(wú)條件查詢count(1)超慢解決方案

試過(guò)文章千百遍，靈機(jī)一動(dòng)就好很多了，憂傷得很。
表中200多萬(wàn)數(shù)據(jù)，53個(gè)字段，select count(1) from JW_CJ_XSCJB 花了240多秒時(shí)間，恐怖啊。
一看分析，索引用的主鍵，但試想應(yīng)該也不至于這么慢吧，然后各種mariadb查詢參數(shù)緩存調(diào)優(yōu)，嘗試各種方案，都無(wú)用

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

然后測(cè)試給表添加一項(xiàng)索引，查詢時(shí)間提升了上百倍，耗時(shí)

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 修改存儲(chǔ)路徑后啟動(dòng)失敗問(wèn)題解決

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

執(zhí)行 systemctl start mariadb 啟動(dòng)MariaDB 時(shí)，報(bào)錯(cuò)提示：

[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.

查看報(bào)錯(cuò)詳情

[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.

問(wèn)題的原因是因?yàn)?mariadb 沒(méi)有 home 路徑的權(quán)限

解決方法：

1. 關(guān)閉 selinux

2. 修改 存儲(chǔ)路徑權(quán)限，

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

3. 如果你的存儲(chǔ)路徑是home 下面的路徑，那么接下來(lái)是重點(diǎn):

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

服務(wù)配置文件有可能不同版本有差異，可以在/etc/systemd/system/目錄下找到對(duì)應(yīng)的service服務(wù)進(jìn)行修改

• 找到ProtectHome改為false

[Service]
ProtectHome=false

• 然后執(zhí)行

sudo systemctl daemon-reload

• 重啟mariadb

systemctl start mariadb

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

問(wèn)題描述：安裝之后，通過(guò) mysql -uroot -p 報(bào)錯(cuò)

sudo mysql -uroot -p

• 設(shè)置root遠(yuǎn)程訪問(wèn)
• 遠(yuǎn)程權(quán)限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
• 刷新權(quán)限： flush privileges;