[리눅스] mariadb 설치

mariadb 설치

현재 지원되는 버전은 10.3, 10.4, 10.5, 10.6(5년간 지원)

테스트 환경

$ cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

repository 설정(저장소 추가)

vim /etc/yum.repos.d/MariaDB.repo

# MariaDB 10.6 CentOS repository list - created 2022-12-09 11:34 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirror.rackspace.com/mariadb/yum/10.6/centos7-amd64
gpgkey=https://mirror.rackspace.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum 리포지토리 활성화

yum install -y yum-utils

yum-config-manager --enable mariadb

$ yum-config-manager --enable mariadb
Loaded plugins: fastestmirror, ovl
============================================ repo: mariadb ============================================
[mariadb]
async = True
bandwidth = 0
base_persistdir = /var/lib/yum/repos/x86_64/7
baseurl = https://mirror.rackspace.com/mariadb/yum/10.6/centos7-amd64
cache = 0
cachedir = /var/cache/yum/x86_64/7/mariadb
check_config_file_age = True
compare_providers_priority = 80
cost = 1000
deltarpm_metadata_percentage = 100
deltarpm_percentage =
enabled = True
enablegroups = True
exclude =
failovermethod = priority
ftp_disable_epsv = False
gpgcadir = /var/lib/yum/repos/x86_64/7/mariadb/gpgcadir
gpgcakey =
gpgcheck = True
gpgdir = /var/lib/yum/repos/x86_64/7/mariadb/gpgdir
gpgkey = https://mirror.rackspace.com/mariadb/yum/RPM-GPG-KEY-MariaDB
hdrdir = /var/cache/yum/x86_64/7/mariadb/headers
http_caching = all
includepkgs =
ip_resolve =
keepalive = True
keepcache = False
mddownloadpolicy = sqlite
mdpolicy = group:small
mediaid =
metadata_expire = 21600
metadata_expire_filter = read-only:present
metalink =
minrate = 0
mirrorlist =
mirrorlist_expire = 86400
name = MariaDB
old_base_cache_dir =
password =
persistdir = /var/lib/yum/repos/x86_64/7/mariadb
pkgdir = /var/cache/yum/x86_64/7/mariadb/packages
proxy = False
proxy_dict =
proxy_password =
proxy_username =
repo_gpgcheck = False
retries = 10
skip_if_unavailable = False
ssl_check_cert_permissions = True
sslcacert =
sslclientcert =
sslclientkey =
sslverify = True
throttle = 0
timeout = 30.0
ui_id = mariadb
ui_repoid_vars = releasever,
   basearch
username =

yum repolist mariadb

$ yum repolist mariadb
Loaded plugins: fastestmirror, ovl
Loading mirror speeds from cached hostfile
 * base: mirror.kakao.com
 * extras: mirror.kakao.com
 * updates: mirror.kakao.com
repo id                                         repo name                                        status
mariadb                                         MariaDB                                          96
repolist: 96

yum info mariadb-server

$ yum info mariadb-server
Loaded plugins: fastestmirror, ovl
Loading mirror speeds from cached hostfile
 * base: mirror.kakao.com
 * extras: mirror.kakao.com
 * updates: mirror.kakao.com
Available Packages
Name        : MariaDB-server
Arch        : x86_64
Version     : 10.6.11
Release     : 1.el7.centos
Size        : 25 M
Repo        : mariadb
Summary     : MariaDB database server binaries
URL         : http://mariadb.org
License     : GPLv2
Description : MariaDB: a very fast and robust SQL database server
            :
            : It is GPL v2 licensed, which means you can use the it free of charge under the
            : conditions of the GNU General Public License Version 2 (http://www.gnu.org/licenses/).
            :
            : MariaDB documentation can be found at https://mariadb.com/kb
            : MariaDB bug reports should be submitted through https://jira.mariadb.org

Name        : mariadb-server
Arch        : x86_64
Epoch       : 1
Version     : 5.5.68
Release     : 1.el7
Size        : 11 M
Repo        : base/7/x86_64
Summary     : The MariaDB server and related files
URL         : http://mariadb.org
License     : GPLv2 with exceptions and LGPLv2 and BSD
Description : MariaDB is a multi-user, multi-threaded SQL database server. It is a
            : client/server implementation consisting of a server daemon (mysqld)
            : and many different client programs and libraries. This package contains
            : the MariaDB server and some accompanying files and directories.
            : MariaDB is a community developed branch of MySQL.

mariadb server 패키지 설치

yum install -y MariaDB-server MariaDB-client

mariadbd 버전 확인

mariadbd --version

$ mariadbd --version
mariadbd  Ver 10.6.11-MariaDB for Linux on x86_64 (MariaDB Server)

$ mariadb --version
mariadb  Ver 15.1 Distrib 10.6.11-MariaDB, for Linux (x86_64) using readline 5.1

 

systemctl list-unit-files | egrep mariadb

$ systemctl list-unit-files | egrep mariadb
mariadb.service                        disabled
mariadb@.service                       disabled

mysql 시작

systemctl --now enable mariadb.service

systemctl --now enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

mariadb 접속 확인

mysql

$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.11-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)]>

status

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.6.11-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		4
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.6.11-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8mb3
Conn.  characterset:	utf8mb3
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			7 min 17 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 17  Open tables: 10  Queries per second avg: 0.013
--------------

show variables like 'lower%';

MariaDB [(none)]> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.001 sec)

mariadb 설정

db root 비밀번호 설정

mysqladmin -u root password 'P@ssw0rd'

mariadb 접속 확인

mysql -uroot -p'P@ssw0rd'

$ mysql -uroot -p'P@ssw0rd'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.6.11-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)]>

- character set

- collation

- lower_case_table_names

- autocommit

vim /etc/my.cnf.d/server.cnf

[server]

[mysqld]
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"

character-set-server = utf8

collation-server = utf8_general_ci

lower_case_table_names = 1

autocommit = 0

[galera]

[embedded]

[mariadb]

[mariadb-10.6]

vim /etc/my.cnf.d/mysql-clients.cnf

[mysql]
default-character-set = utf8

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]
default-character-set = utf8

[mysqlimport]

[mysqlshow]

[mysqlslap]

mariadb 재기동

systemctl restart mariadb.service

$ mysql -uroot -p'P@ssw0rd'

status

show variables like 'lower_case_table_names%';

show variables like "character_set%";

MariaDB [(none)]> show variables like "character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb3                    |
| character_set_connection | utf8mb3                    |
| character_set_database   | utf8mb3                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb3                    |
| character_set_server     | utf8mb3                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.000 sec)

사용자 확인 및 사용자 생성(추가)

select host, user from mysql.user;

MariaDB [(none)]> select host, user from mysql.user;
+-----------+-------------+
| Host      | User        |
+-----------+-------------+
| centos7   |             |
| localhost |             |
| localhost | mariadb.sys |
| localhost | mysql       |
| localhost | root        |
+-----------+-------------+
5 rows in set (0.001 sec)

create user sdb_user@localhost identified by 'P@ssw0rd';

MariaDB [(none)]> create user sdb_user@localhost identified by 'P@ssw0rd';
Query OK, 0 rows affected (0.004 sec)

flush privileges;

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

원격(외부)에서 접속 가능하도록 권한 설정

grant all privileges on *.* to sdb_user@'%' identified by 'P@ssw0rd';

MariaDB [(none)]> grant all privileges on *.* to sdb_user@'%' identified by 'P@ssw0rd';
Query OK, 0 rows affected (0.002 sec)

Host, User, Password 확인

select host, user, password from mysql.user;

MariaDB [(none)]> select host,user,password from mysql.user;
+-----------+-------------+-------------------------------------------+
| Host      | User        | Password                                  |
+-----------+-------------+-------------------------------------------+
| localhost | mariadb.sys |                                           |
| localhost | root        | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| localhost | mysql       | invalid                                   |
| localhost |             |                                           |
| centos7   |             |                                           |
| localhost | sdb_user    | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
| %         | sdb_user    | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
+-----------+-------------+-------------------------------------------+
7 rows in set (0.001 sec)