Mysql&MariaDB

마리아_DB 시작하기

ksk04 2024. 9. 14. 21:35

 

  • mysql을 기반으로한 오픈소스 데이터베이스-
  • mysql 의 커뮤니티 버전을 대상으로 fork 해오기에 기본적인 아키텍처가는 같고 추가되거나
  • 스레드풀 같은 지원이 종료된 기능을 제공하기도 한다.

서평

특이하게도  입문서 치고는 실습 예제가 리눅스위주이다.
한 번 정도 또는 마리아db가 처음이라면 읽어볼만한 책이다.

설치하기 (centos)

  • vi /etc/yum.repo.d/MariaDB.repo
# MariaDB 10.5 CentOS repository list - created 2024-09-06 19:39 UTC
# <https://mariadb.org/download/>
[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See <https://mariadb.org/mirrorbits/> for details.
# baseurl = <https://rpm.mariadb.org/10.5/centos/$releasever/$basearch>
baseurl = <https://tw1.mirror.blendbyte.net/mariadb/yum/10.5/centos/$releasever/$basearch>
module_hotfixes = 1
# gpgkey = <https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB>
gpgkey = <https://tw1.mirror.blendbyte.net/mariadb/yum/RPM-GPG-KEY-MariaDB>
gpgcheck = 1
  • yum update
  • yum -y install MariaDB
  • systemctl start MariaDB
  • mysql -u root -p

GPG

  • gpg는 업계 데이터 암호화, 복호화 시스템을 확인하는 pgp의 오픈소스 버전이다.

리눅스 마리아DB 경로

  • 바이너리 : /usr/bin
  • 메뉴얼 페이지 /usr/share/mysql
  • 라이브러리:/usr/lib/mysql
  • 로그 /var/log/mysql
  • 플러그인 /var/lib/mysql/plugin
  • 설정파일 /etc/mycnf , /etc/mycnf.d/*

my.cnf

  • Mariadb 이 세부설정을 하는 파일이다.
  • 특이하게도 my.cnf.d 경로에도 세부설정이 가능하다.
  • Mairadb가 메모리에 올라올떄 my.cnf를 먼저 바라보고 my.cnf.d 폴더 아래의 경로를 바라보게 된다.
  • 교재에서 제공하는 예:
# An example MariaDB database server configuration file.
# 
# While the option names are the same on both Windows and Linux, the
# filesystem paths in this example file are Linux-centric and won't
# work on Windows. Refer instead to the my.ini file that comes with
# MariaDB on Windows. 

# The client group is for all MariaDB clients, It's configured here
# with the standard port to connect on and the standard location of
# the socket file on Ubuntu and Debian. On Windows the socket is a
# named pipe. You can change the port and socket location to custom
# locations, but if you do, be sure to change it in the [mysqld]
# group as well.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# On Fedora, Red Hat, and CentOS, the default socket location is:
# socket = /var/lib/mysql/mysql.sock

# The [mysqld] group is the main place for configuring the MariaDB
# server.
[mysqld]
user = mysql # The name of the user which owns the mysqld process
pid-file = /var/run/mysqld/mysqld.pid # location of the process id file

# The port and socket lines need to match the lines in the [client]
# group
port = 3306
socket = /var/run/mysqld/mysqld.sock

basedir = /usr # the dir under which the MariaDB files are installed
datadir = /var/lib/mysql # where the actual data is stored
tmpdir = /tmp # where to write temporary files

# MariaDB is a database server, but in order to allow other machines
# to connect to it, the bind-address variable needs to be set to the
# ip address of the server MariaDB is running on. When this variable
# is set to 127.0.0.1, MariaDB will not allow any external connections
# (127.0.0.1 is the loopback address, so the effect is that MariaDB
# only listens to itself).
bind-address = 127.0.0.1

# The variables below are some of the most popular variables to tweak
# when fine tuning a MariaDB installation.

max_connections = 100 # The number of simultaneous connections allowed

# connect_timeout is the time, in seconds, the server will wait during
# a connection attempt for a connect packet before sending a timeout
# error
connect_timeout = 5

# wait_timeout is the time in seconds that the server will wait for a
# connection to become active before closing it.
wait_timeout = 600

# When specifying sizes, as some of the next few settings do, we can
# use letters to specify what we are talking about. If we don't use a
# letter, the size is assumed to be in Bytes.
#   K = Kilobytes. M = Megabytes, G = Gigabytes
max_allowed_packet = 16M # max packet length to send or receive
thread_cache_size = 128 # how many threads to keep in the cache
sort_buffer_size = 4M # the size of the buffer a sort thread allocates

# bulk_insert_buffer_size is the per thread size limit of the buffer
# used when inserting a lot of data at once
bulk_insert_buffer_size = 16M 

# tmp_table_size sets the maximum in memory size of temporary tables.
# "in-memory" means that the table exists entirely in the server's
# RAM. If the size of a temporary table outgrows this size then the
# table will be converted to an "on disk" temporary table, which means
# the data put in it will be written to the hard disk. On disk tables
# are much slower than in memory tables.
tmp_table_size = 32M 

# The Query Cache keeps copies of the result sets of frequently run
# queries.  Cached queries are updated whenever the database tables
# the query uses are updated. Using the Query Cache is a great way to
# improve the performance of MariaDB, especially if you have lots of
# repeated queries. The query_cache_limit and query_cache_size
# variables control how big the result set of a cached query is
# allowed to be and how large the entire cache is allowed to be,
# respectively.
query_cache_limit = 128K
query_cache_size = 64M
#
# The query_cache_type variable controls the behavior of the cache.
# Possible values include:
#
#   0 or 'OFF' ; Off. Don't cache any results
#   1 or 'ON' ; On mode. Cache all results except for those marked
#               with the SQL_NO_CACHE option
#   2 or 'DEMAND' ; On-demand mode. Only cache results marked with the
#                   SQL_CACHE option.
query_cache_type = 1

# log_warnings lets you control how much logging of warnings and
# errors we do on the server. Possible values are 0, 1, 2, and 3.
#   0 = don't log warnings and errors
#   1 = log standard warnings and errors like usage errors, access
#       denied errors, read errors, option value errors, and so on
#   2 = log everything from level 1 and also log table handler errors
#   3 = log everything from levels 1 and 2 and also log all errors and
#       warnings during repair and recovery operations.
log_warnings = 2

# MariaDB can tell you if certain database operations are taking too
# long to complete. There is a performance penalty for doing this kind
# of logging, but it can greatly help with identifying performance
# bottlenecks. It is generally best to keep it disabled unless you
# need it, so I have the line below commented out, uncomment (remove
# the '#' from the beginning of) the following line to enable it:
# slow_query_log
slow_query_log_file = /var/log/mysql/mariadb-slow.log
#
# long_query_time is how long, in seconds, a query has to take to be
# logged in the slow query log
long_query_time = 10
# other specialized slow-query-log options are explained here:
# <https://kb.askmonty.org/en/slow-query-log-extended-statistics/>

# The last item in the MariaDB my.cnf config file on Linux-based
# machines should be the line that directs MariaDB to the conf.d or
# my.cnf.d directories. Remember that the files in those directories
# must end with '.cnf', otherwise they'll be ignored. Uncomment the
# appropriate line for your system. The first character on the line
# you want to use must be an exclamation point (!). Comment out or
# remove both lines on Windows. 
# 
# For Debian and Ubuntu: 
!includedir /etc/mysql/conf.d/ 
# For Fedora, Red Hat, and CentOS: 
#!includedir /etc/my.cnf.d

비밀번호 / 보안설정

mysql_secure_installtion

사용자 생성

MariaDB [(none)]> create user 'ksk'@'%' identified by 'ksk';
Query OK, 0 rows affected (0.002 sec)

권한삭제

reboke all,grant option from 'neil'@'example.com';

권한보기

 show grants for 'ksk'@'%';
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for ksk@%                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `ksk`@`%` IDENTIFIED BY PASSWORD '*EFC30697873C668B4B3844D5DC112DFDA31323AB' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

비밀번호 변경하기

set password for 'ksk'@'%' = password('ksk');

table생성

create table employees (
        id int not null auto_increment primary key,
        surname varchar(100),
        givenname varchar(100),
        pref_name  varchar(50),
        birthday date comment '생일';
);

칼럼추가

MariaDB [employees]> alter table employees add username varchar(10) after pref_name;
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

8장 마리아 db 유지보수

<aside> 💡

바이너리 로그

  • 이벤트 내용을 저장하며 2진수 포맷으로 되어있어 사람이
  • 읽을 수 없다. mysqlbinlog와 같은 툴을 이용.
  • 백업과 이중화 시 릴레이 로그를 전송하는 방식으로 사용된다.
  • 설정
log_bin = /var/log/mariadb-bin
[root@localhost ~]# ll /var/log/

-rw-rw----. 1 mysql  mysql     1127  9월  7 09:19 mariadb-bin.000001
-rw-rw----. 1 mysql  mysql       28  9월  7 09:18 mariadb-bin.index

에러로그

  • 마리아 db 인스턴스내에서의 심각한 에러만을 기록
  • 설정
log_error = /var/log/error.log

쿼리 로그

  • 마리아 db 가 실행하늕 모든로그를 기록한다.
  • 모든 쿼리를 기록하기에 시스템이 과부하 될 가능성이 높다.
  • general_log , general_log_file 변수를 사용해 파라미터를 지정 할 수 있다.
general_log=1 -- 활성화
log_error = /var/log/error.log -- 경로

슬로우 쿼리 로그

  • 느리게 실행되는 쿼리를 탐색
slow_query_log = 1
slow_query_log_file = /var/lib/mysql-slow.log
log_query_time = 5

백업

  • mysqldump
employees 스키마를 백업
mysqldump --tab /tmp/ -u root employees
  • sql 파일 .txt 파일을 /tmp/ 아래경로에 하나 씩 생성한다.
  • mysql import
 --local -u root /tmp/employee.txt
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
Copyright 2008-2011 Oracle and Monty Program Ab.
Copyright 2012-2019 MariaDB Corporation Ab.
mysqlimport  Ver 3.7 Distrib 10.5.26-MariaDB, for Linux (x86_64)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
  • mariabackup
  • xtrabackup 은 10.5 버전이상부터 지원을 안한다.
  • 대신 위에것을 fork한 mariabackup을 지원한다.
  • 디렉토리 파일 전체를 복사하는 풀 백업 방식이다.
[root@localhost ~]# mariabackup --backup --user=root --password=root --target-dir=/tmp

[root@localhost ~]# mariabackup --backup --user=root --password=root --target-dir=/tmp
[00] 2024-09-07 10:34:58 Connecting to server host: localhost, user: root, password: set, port: not set, socket: not set
[00] 2024-09-07 10:34:58 Using server version 10.5.26-MariaDB-log
mariabackup based on MariaDB server 10.5.26-MariaDB Linux (x86_64)
[00] 2024-09-07 10:34:58 uses posix_fadvise().
[00] 2024-09-07 10:34:58 cd to /var/lib/mysql/
[00] 2024-09-07 10:34:58 open files limit requested 0, set to 1024
[00] 2024-09-07 10:34:58 mariabackup: using the following InnoDB configuration:
[00] 2024-09-07 10:34:58 innodb_data_home_dir =
[00] 2024-09-07 10:34:58 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2024-09-07 10:34:58 innodb_log_group_home_dir = ./
[00] 2024-09-07 10:34:58 InnoDB: Using Linux native AIO
2024-09-07 10:34:58 0 [Note] InnoDB: Number of pools: 1
  • 백업
  • rm -rf /var/lib/mysql/*
[root@localhost ~]# mariabackup --copy-back --target-dir /tmp