一窥妇、安裝
1、安裝包
- 最新版本包源
>sudo vi /etc/yum.repos.d/MariaDB.repo
- 輸入內(nèi)容
[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
可能會出現(xiàn)問題：
...
...
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
- 歐拉系統(tǒng)鏡像源更改為阿里鏡像：
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
歐拉操作系統(tǒng)報錯
2娩践、設(shè)置開機啟動服務(wù)
> sudo systemctl enable mariadb
3、啟動/停止/重啟MariaDB服務(wù)
- 啟動
> sudo systemctl start mariadb
- 停止
> sudo systemctl stop mariadb
- 重啟
> sudo systemctl restart mariadb
4烹骨、查看運行狀態(tài)
> sudo systemctl status mariadb
5翻伺、安全配置
# 默認(rèn)安裝
sudo mysql_secure_installation
# 【推薦】或指定安裝目錄以及數(shù)據(jù)目錄
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'.(您的根帳戶已受保護(hù)，因此可以安全地回答“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?（是否設(shè)置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遠(yuǎn)程訪問） [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?（是否刪除測試數(shù)據(jù)庫并訪問它） [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?（是否立即重新加載權(quán)限表） [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遠(yuǎn)程訪問權(quán)限
注意：123456為密碼峦树，可以修改你需要設(shè)置的密碼
- 遠(yuǎn)程權(quán)限：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION; - 刷新權(quán)限：
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設(shè)置方式
修改配置文件
vi /etc/sysconfig/iptables
添加-A RH-Firewall-1-INPUT -p tcp -m conntrack --ctstate NEW -m tcp --dport 3306 -j ACCEPT內(nèi)容辣辫，注意添加的位置否則影響防火墻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
- 查看數(shù)據(jù)目錄
mysqld --help --verbose | grep 'datadir' | tail -1
二急灭、卸載
- 查看相關(guān)包
> 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
- 刪除所有相關(guān)包
> yum -y remove mariadb*
三、數(shù)據(jù)目錄更改為新位置
- 查看原目錄 - 方式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命令行中執(zhí)行）
> 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
注意：如果你將目錄設(shè)置為/home/xxx/mysql谷遂，可能引發(fā) [Warning] Can't create test file /var/lib/mysql/localhost.lower-test葬馋，你會查各種資料，最終涉及到SELinux權(quán)限問題肾扰，整個處理起來太過繁瑣畴嘶。教你排查問題：/home/xxx/mysql新位置mysql目錄的遞歸上級目錄不能包含非root權(quán)限組的權(quán)限，否則你就算是按照很多教程配置的數(shù)據(jù)目錄集晚，最后還是不能啟動服務(wù)窗悯。最后一句：注意整個路徑所有目錄權(quán)限！Ｍ蛋巍＝骸！
- 停止服務(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ù)庫目錄復(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，允許訪問/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ù)庫
1、備份數(shù)據(jù)庫
- 創(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ù)庫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
- 添加定時任務(wù)间驮，每兩小時執(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ù)，使定時服務(wù)生效
systemctl restart crond.service
- crontab基本操作
crontab -u //設(shè)定某個用戶的cron服務(wù)
crontab -l //列出某個用戶cron服務(wù)的詳細(xì)內(nèi)容
crontab -r //刪除某個用戶的cron服務(wù)
crontab -e //編輯某個用戶的cron服務(wù)
crontab -i //打印提示马昨，輸入yes等確認(rèn)信息
/var/spool/cron/root (以用戶命名的文件) 是所有默認(rèn)存放定時任務(wù)的文件
/etc/cron.deny 該文件中所列出用戶不允許使用crontab命令
/etc/cron.allow 該文件中所列出用戶允許使用crontab命令竞帽，且優(yōu)先級高于/etc/cron.deny
/var/log/cron 該文件存放cron服務(wù)的日志
2、還原數(shù)據(jù)庫
- 創(chuàng)建數(shù)據(jù)庫
drop databases daas;
create databases daas;
- 還原備份數(shù)據(jù)庫文件
>mysql -uroot -p -f --default-character-set=utf8 daas < E:\daas_back\daas_20200714_120001_219507331.sql
Enter password: ******
注意：在導(dǎo)入數(shù)據(jù)庫時鸿捧，有可能多個視圖有關(guān)聯(lián)屹篓，導(dǎo)致執(zhí)行SQL時關(guān)聯(lián)視圖還未創(chuàng)建，引發(fā)視圖不存在的錯誤匙奴。最簡單的解決方案就是忽略錯誤堆巧，多導(dǎo)入一次就可以了
導(dǎo)入錯誤示例：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;
忘記密碼
- 設(shè)置配置文件泼菌，在
[mysqld]下添加skip-grant-tables
> sudo vi /etc/my.cnf.d/server.cnf
[mysqld]
skip-grant-tables
- 重啟服務(wù)
> sudo systemctl restart mariadb
- 無密碼數(shù)據(jù)庫連接
> 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í)行谍肤，否則會報錯 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)
常見操作
- 賦予用戶某一個數(shù)據(jù)庫權(quán)限
-- database_name 數(shù)據(jù)庫名
-- user_name 數(shù)據(jù)庫用戶名
-- user_password 數(shù)據(jù)庫用戶名
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 個有用的 MySQL/MariaDB 性能調(diào)整和優(yōu)化技巧
MySQL/MariaDB 配置文件位于 /etc/my.cnf。 每次更改此文件后你需要重啟 MySQL 服務(wù)哗伯，以使更改生效荒揣。
# 數(shù)據(jù)庫表放在一個單獨的存儲設(shè)備
innodb_file_per_table=1
# 在一個專用的機器上，你可能會把 60-70％ 的內(nèi)存分配給 innodb_buffer_pool_size
innodb_buffer_pool_size=22G
- 百萬數(shù)據(jù)無條件查詢count(1)超慢解決方案
試過文章千百遍笋颤，靈機一動就好很多了乳附，憂傷得很。
表中200多萬數(shù)據(jù)伴澄，53個字段赋除，select count(1) from JW_CJ_XSCJB花了240多秒時間，恐怖啊非凌。
一看分析举农，索引用的主鍵，但試想應(yīng)該也不至于這么慢吧敞嗡，然后各種mariadb查詢參數(shù)緩存調(diào)優(yōu)颁糟，嘗試各種方案，都無用
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 路徑下，
執(zhí)行 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 路徑的權(quán)限
解決方法：
1. 關(guān)閉 selinux
2. 修改 存儲路徑權(quán)限婚脱，
chown -R mysql:mysql /home/mysql_data
chmod -R 764 /home/mysql_data
3. 如果你的存儲路徑是home 下面的路徑，那么接下來是重點:
sudo vi /etc/systemd/system/mysql.service
服務(wù)配置文件有可能不同版本有差異，可以在/etc/systemd/system/目錄下找到對應(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)
問題描述：安裝之后障贸，通過
mysql -uroot -p報錯
sudo mysql -uroot -p
- 設(shè)置root遠(yuǎn)程訪問
- 遠(yuǎn)程權(quán)限：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION; - 刷新權(quán)限：
flush privileges;
