MariaDB 수동 설치 하기

# MariaDB 10.2.15 Install

 

1.Mariadb/Mysql DB

기본 설정 아래 경로는 DB를 설치 하기전에 검토가 되어야 할 경로 이다.

왠만하면 다 분리 하여 경로를 생성한다. 로그 경로나 엔진 경로 데이타 경로는 분리 하여 관리 하는게 좋다.

data경로는 특히나 변경이 어려우니 용량이 제일 크게 확장이 가능한 mount경로로 설정한다.

Datadir /DBMS/MARIADB/DATA01
Logdir /DBMS/MARIADB/LOG
BinLogdir /DBMS/MARIADB/BINLOG
Basedir /DBMS/MARIADB/DB/mariadb
backupdir /DBMS/MARIADB/BACKUP

 

 

2. 설치 절차

2.1 OS 계정 생성 (root user로 수행)
[root@localhost ~]# groupadd dba
[root@localhost ~]# useradd -g dba maria -d /DBMS/MARIADB -m

2.2 디렉토리 생성 (root user로 수행)

예상 DB 사용 용량(운영시 업무양에 맞춰서 산정한다.)
용량 Mount 포인트
20GB

/DBMS/DBMS/MARIADB home경로
/DBMS/DBMS/MARIADB/LOG DB log 경로
/DBMS/DBMS/MARIADB/DB DB엔진 경로

50GB

/DBMS/DATA01 DB DATA 경로(DATA01,DATA02..N)
/DBMS/BINLOG 바이너리로그 경로(Archivelog)
150GB

/DBMS/BACKUP DB LOCAL백업경로

 

mkdir -p /DBMS/MARIADB/DB
mkdir -p /DBMS/DBMS/MARIADB/LOG

mkdir -p /DBMS/MARIADB/DATA01
mkdir -p /DBMS/MARIADB/LOG
mkdir -p /DBMS/MARIADB/BINLOG
mkdir -p /DBMS/MARIADB/BACKUP

 

2.3 FTP 로 서버에 넘긴후 tar 파일 압축 해제 (root user로 수행)
ftp tool를 이용해서 각 서버의 /DBMS/MARIADB에 업로드한다.

[root@localhost ~]# tar -zxf mariadb-10.2.16-linux-x86_64.tar.gz -C /DBMS/MARIADB/DB

 

2.4 심볼릭 링크 설정 (root user로 수행)
업그레이드 및 테스트에 링크정보만 변경하면 엔진변경이됨.
버전별로 선택하여 진행.

[root@localhost~]# cd /DBMS/MARIADB/DB
[root@localhost ]# mv mariadb-10.2.16-linux-x86_64 mariadb-10.2.16
[root@localhost ]# ln -s mariadb-10.2.16 mariadb

 

아래 내용은 엔진 업그레이드시만 적용한다.

[maria@node3 DB]$ ln -Tfs mariadb-10.2.16 mariadb

[maria@node3 DB]$ ll
total 8
lrwxrwxrwx. 1 maria dba 15 Jul 17 09:26 mariadb -> mariadb-10.2.15
drwxr-xr-x. 12 maria dba 4096 Jul 17 10:16 mariadb-10.2.15
drwxrwxr-x. 12 maria dba 4096 Jun 26 03:07 mariadb-10.2.16

 

 

2.4.1소유권 변경 (root user로 수행)
[root@localhost ~]# chown -R maria.dba /DBMS/MARIADB

/DBMS/MARIADB 는 계정의 홈디렉토리로 지정한다.
홈 디렉토리에 .my.cnf를 설정해야하니 참고 한다.
추가로 DB가 올라갈때 my.cnf를 읽어 오는데 /etc에 있는 파일들은 없어야한다.
my.cnf를 global하게 /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 순으로 읽고 추가 적으로 $MYSQL_HOME/my.cnf도 읽는다
바꿀수없다. 기본설정이다(개발하던가ㅋ)
여기서는 ~/.my.cnf를 사용예정

[참고자료]
https://DBMS/MARIADB.com/kb/en/library/configuring-mariadb-with-mycnf/

/etc/아래 my.cnf파일이 있으면 mv로 이름을 변경한다.
[root@node3 DB]# ls -l /etc/my.cnf_org /etc/mysql/my.cnf
ls: cannot access /etc/mysql/my.cnf: No such file or directory
-rw-r--r--. 1 root root 570 Jun 8 2017 /etc/my.cnf_org

 

[root@node3 DB]# mv /etc/my.cnf /etc/my.cnf_org

 

2.5 bash_profile 설정 (maria user로 수행)

[root@localhost ~]# su - maria
[maria@localhost ~]$ vi .bash_profile
export MYSQL_HOME=/DBMS/MARIADB/DB/DBMS/MARIADB
PATH=$PATH:$HOME/bin:$MYSQL_HOME/bin/
alias mhome='cd $MYSQL_HOME'
export PATH

[maria@localhost ~]$ . ./.bash_profile

 

2.7 my.cnf 설정 (maria user로 수행)
maria 계정으로 my.cnf파일 설정
사이즈에 맞는 my.cnf파일을 수정한다.(아래 용량별 파일은 어디서주워옴 필요에따라 수정바람)
$MYSQL_HOME/support-files/
시스템 메모리가 4G 이상일때 my-innodb-heavy-4G.cnf
시스템 메모리가 1G~2G일때 my-huge.cnf
시스템 메모리가 512MB일때 my-large.cnf

 

[maria@localhost ~] cp $MYSQL_HOME/support-files/my-huge.cnf ~/.my.cnf
[maria@node1 ~]$ vi .my.cnf

Example MariaDB config file for very large systems.

#

This is for a large system with memory of 1G-2G where the system runs mainly

MariaDB.

#

MariaDB programs look for option files in a set of

locations which depend on the deployment platform.

You can copy this option file to one of those

locations. For information about these locations, do:

'my_print_defaults --help' and see what is printed under

Default options are read from the following files in the given order:

More information at: http://dev.mysql.com/doc/mysql/en/option-files.html

#

In this file, you can use all long options that a program supports.

If you want to know which options a program supports, run the program

with the "--help" option.

The following options will be passed to all MySQL clients

[client]
#default-character-set=utf8
port = 5306
socket = /DBMS/MARIADB/mysql.sock

Here follows entries for some specific programs

The MySQL server

[mysqld]
local-infile = ON
enable-secure-auth

Instance Basic Config

user =maria
port = 5306
socket = /DBMS/MARIADB/mysql.sock
server-id = 1
autocommit=0

DeadLock logging

innodb_print_all_deadlocks = ON

Character setting

init_connect="SET collation_connection=utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

Instance Common config

skip-external-locking
transaction_isolation = READ-COMMITTED
log-output=file,table
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 2M
thread_cache_size = 8
key_buffer_size = 384M
max_connections=200
query_cache_size = 64M
table_open_cache = 2048
max_allowed_packet=16M
max_heap_table_size=64M
query_cache_limit=2M
tmp_table_size=64M
#binlog_cache_size=1M
#ft_min_word_len=4
#join_buffer_size=8M
#innodb_lock_wait_timeout=50 # default 50초 dml row lock이 걸렸을경우 대기하다가 세션이 종료되는 시간.

Instance Path Config

#tmpdir =
basedir = /DBMS/MARIADB/mariadb
datadir = /DBMS/MARIADB/DATA01
slow_query_log_file = /DBMS/MARIADB/LOG/MARIADB-slow.log
log-error = /DBMS/MARIADB/LOG/maria_db.log

slow Query

slow_query_log = 1
long_query_time = 5
log_slow_rate_limit = 1
log_slow_verbosity = query_plan
log_slow_admin_statements
log_output = FILE,TABLE

Inno DB config

You can set .._buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

###Cache Config
innodb_buffer_pool_size = 500M
innodb_log_file_size = 200M
innodb_log_files_in_group=3
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 50

###Data File of InnoDB tables Config
innodb_data_home_dir =
innodb_autoextend_increment= 20M
innodb_data_file_path = /DBMS/MARIADB/DATA01/ibdata1:500M;/DBMS/MARIADB/DATA01/ibdata2:500M;/DBMS/MARIADB/DATA01/ibdata3:500M;/DBMS/MARIADB/DATA02/ibdata4:10M:autoextend:max:500m

###BINLog FIle Config(Set .._log_file_size to 25 % of buffer pool size)
innodb_log_group_home_dir =/DBMS/MARIADB/DATA01
log-bin=/DBMS/MARIADB/BINLOG/mysql-bin
expire_logs_days = 7
max_binlog_size=200M
binlog_row_image=FULL

UNDO File Config

innodb_undo_directory=/DBMS/MARIADB/DATA01
innodb_undo_logs=2
innodb_undo_tablespaces=2
innodb-flush-log-at-trx-commit=1

#####################################################################################################

ETC setting

skip-character-set-client-handshake
skip-name-resolve

Remove the next comment character if you are not familiar with SQL

#safe-updates

[mysqldump]
quick
max_allowed_packet = 16M

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

 

2.8 Install DB (maria user로 수행)
설치를 하게되면 아래와 같이 OK메시지와 함께 추가설정내용을 출력해준다.
참고로 재설치시 생성된 파일 삭제하고 한다.
rm -f /DBMS/MARIADB/BINLOG/*
rm -rf /DBMS/MARIADB/DATA01/*

[maria@localhost mariadb]$ /DBMS/MARIADB/DB/mariadb/scripts/mysql_install_db --defaults-file=~/.my.cnf --basedir=/DBMS/MARIADB/DB/mariadb
Installing MariaDB/MySQL system tables in '/DB_DATA' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/DBMS/MARIADB/bin/mysqladmin' -u root password 'new-password'
'/DBMS/MARIADB/bin/mysqladmin' -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
'/DBMS/MARIADB/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://DBMS/MARIADB.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/DBMS/MARIADB' ; /DBMS/MARIADB/bin/mysqld_safe --datadir='/DB_DATA'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/DBMS/MARIADB/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://DBMS/MARIADB.org/jira

The latest information about MariaDB is available at http://DBMS/MARIADB.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDBs strong and vibrant community:
https://DBMS/MARIADB.org/get-involved/

CREATE TABLE IF NOT EXISTS gtid_slave_pos (
domain_id int(10) unsigned NOT NULL,
sub_id bigint(20) unsigned NOT NULL,
server_id int(10) unsigned NOT NULL,
seq_no bigint(20) unsigned NOT NULL,
PRIMARY KEY (domain_id,sub_id)
) COMMENT='Replication slave GTID position';

반응형

2.8.1 설치가 정상적으로 되었는지 DATAFILE들을 DATA경로에서 생성된것을 확인한다.(maria user로 수행)
[maria@localhost ~]$ ll DATA
total 274460
-rw-rw----. 1 maria dba 16384 Jun 19 15:18 aria_log.00000001
-rw-rw----. 1 maria dba 52 Jun 19 15:18 aria_log_control
-rw-rw----. 1 maria dba 938 Jun 19 15:18 ib_buffer_pool
-rw-rw----. 1 maria dba 12582912 Jun 19 15:18 ibdata1
-rw-rw----. 1 maria dba 134217728 Jun 19 15:18 ib_logfile0
-rw-rw----. 1 maria dba 134217728 Jun 19 15:18 ib_logfile1
drwx------. 2 maria dba 4096 Jun 19 15:18 mysql
drwx------. 2 maria dba 19 Jun 19 15:18 performance_schema
drwx------. 2 maria dba 6 Jun 19 15:18 test
[maria@localhost mariadb]$

 

 

2.9 Service 등록 (root user로 수행)
[root@localhost ~]# cp /DBMS/MARIADB/DB/mariadb/support-files/mysql.server /etc/init.d/maria

[root@localhost ~]# vi /etc/init.d/maria
line 46, 47 basedir, datadir 수정

####
basedir=/DBMS/MARIADB/DB/mariadb
datadir=/DBMS/MARIADB/DATA01
####

 

 

소유권 변경

[root@localhost ~]# chown maria.dba /etc/init.d/maria

 

2.10 MariaDB START (maria user로 수행)
[root@localhost ~]# su - maria
[maria@localhost ~]$ service maria start
[maria@node3 ~]$ service maria start
Reloading systemd: ==== AUTHENTICATING FOR org.freedesktop.systemd1.reload-daemon ===
Authentication is required to reload the systemd state.
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ===
[ OK ]
Starting maria (via systemctl): ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ===
Job for maria.service failed because the control process exited with error code. See "systemctl status maria.service" and "journalctl -xe" for details.
[FAILED]

 

centos 7에서는 service 명령이 안되므로 systemctl 세팅을 넘어 간다.

 

 

2.10설정은 버전별로 다른다. 설정이 안되면 3번으로 세팅한다.

 

 

3. systemctl 등록 (root user로 수행)

[root@localhost ~]# vi /etc/systemd/system/mariadb.service[Unit]Description = maria DB ServerAfter = network.target

[Service]
Type = forking
User = maria
ExecStart = /etc/init.d/maria start
ExecStop = /etc/init.d/maria stop

open file limit

LimitNOFILE = 8192

[Install]
WantedBy = multi-user.target

[root@localhost ~]# systemctl daemon-reload

3.2 OS 계정 sudo 권한 설정 (root user로 수행)
[root@localhost ~]# visudo
maria ALL=NOPASSWD: /usr/bin/systemctl start mariadb, /usr/bin/systemctl stop mariadb, /usr/bin/systemctl restart mariadb, /usr/bin/systemctl status mariadb

3.3 systemctl 명령어 (maria user로 수행)
[root@localhost ~]# su - maria
[maria@localhost ~]$ sudo systemctl start mariadb
[maria@localhost ~]$ sudo systemctl stop mariadb
[maria@localhost ~]$ sudo systemctl restart mariadb
[maria@localhost ~]$ sudo systemctl status mariadb

포트 오픈 및 프로세스 확인

[maria@node3 ~]$ netstat -ntlp | grep mysqld
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp6 0 0 :::3306 :::* LISTEN 3840/mysqld
[maria@node3 ~]$ ps -ef|grep mysqld
maria 3638 1 0 09:48 ? 00:00:00 /bin/sh /DBMS/MARIADB/DB/DBMS/MARIADB/bin/mysqld_safe --datadir=/DBMS/MARIADB/DATA --pid-file=/DBMS/MARIADB/DATA/node3.soon.com.pid
maria 3840 3638 1 09:48 ? 00:00:00 /DBMS/MARIADB/DB/DBMS/MARIADB/bin/mysqld --basedir=/DBMS/MARIADB/DB/DBMS/MARIADB --datadir=/DBMS/MARIADB/DATA --plugin-dir=/DBMS/MARIADB/DB/DBMS/MARIADB/lib/plugin --log-error=/DBMS/MARIADB/LOG/maria_db.log --pid-file=/DBMS/MARIADB/DATA/node3.soon.com.pid --socket=/DBMS/MARIADB/mysql.sock --port=3306
maria 3879 3578 0 09:49 pts/0 00:00:00 grep --color=auto mysqld
[maria@node3 ~]$

[maria@node1 ~]$ ps -fu maria
UID PID PPID C STIME TTY TIME CMD
maria 6109 6108 0 Jul02 pts/1 00:00:00 -bash
maria 6813 1 0 Jul02 ? 00:00:00 /bin/sh /DBMS/MARIADB/DB/DBMS/MARIADB/bin/mysqld_safe --datadir=/DBMS/MARIADB/DATA --pid-file=/DBMS/MARIADB/DATA/master.pid
maria 7021 6813 0 Jul02 ? 00:00:37 /DBMS/MARIADB/DB/DBMS/MARIADB/bin/mysqld --basedir=/DBMS/MARIADB/DB/DBMS/MARIADB --datadir=/DBMS/MARIADB/DATA --plugin-dir=/DBMS/MARIADB/DB/DBMS/MARIADB/lib/plugin --log-error=/DBMS/MARIADB/LOG/maria_db.log --pid-file=/M

프로세스 설명 :
mysqld_safe 메인 프로세스로 mysqld 프로세스가 죽으면 살려준다.

 

 

4. 기타 설정

4.1 mysql_secure_installation 설정 (DB는 올라와 있어야 설정가능)(maria user로 수행)
보안상 test db나 root의 외부사용을 막는설정.
[maria@localhost ~]$ mysql_secure_installation --basedir=/DBMS/MARIADB/DB/mariadb -S /DBMS/MARIADB/mysql.sock
print: /DBMS/MARIADB/DB/DBMS/MARIADB/bin/my_print_defaults

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, well need the current
password for the root user. If youve just installed MariaDB, and
you havent 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 ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [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] n
... skipping.

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? [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] n
... skipping.

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 youve completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

4.2 OS 계정 limit 설정 (root user로 수행)
[root@localhost ~]# vi /etc/security/limits.conf
maria soft nproc 65536
maria hard nproc 65536
maria soft nofile 65536
maria hard nofile 65536

[root@node3 DB]# su - maria
Last login: Tue Jul 17 09:48:43 KST 2018 on pts/0
[maria@node3 ~]$ ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 11254
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 65536
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 65536
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
[maria@node3 ~]$

4.3 방화벽 제거 또는 포트 오픈이 필요함.(root user로 수행)
아래 내용은 중지 후 비활성화 방법임.

[root@localhost DB]# systemctl stop firewalld
[root@localhost DB]# systemctl disable firewalld
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.
[root@node1 DB]#

 

 

5. 부팅시 자동실행 /미실행 설정방법.(root user로 수행)

부팅시 자동실행

[root@localhost DB]# systemctl enable mariadbCreated symlink from /etc/systemd/system/multi-user.target.wants/DBMS/MARIADB to /etc/systemd/system/DBMS/MARIADB.

 

부팅시 자동 미실행

[root@localhost DB]# systemctl disable mariadb
Removed symlink /etc/systemd/system/multi-user.target.wants/DBMS/MARIADB.

 

root 의 외부 사용을 허용 하기 위한 작업이 필요 하면 아래와 같이 진행한다.

 

[maria@node3 ~]$ mysql -uroot -proot123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.2.15-MariaDB-log 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)]>
MariaDB [(none)]>
MariaDB [(none)]> select host,user,password,ssl_cipher, x509_issuer, x509_subject from mysql.user;
+----------------+------+-------------------------------------------+------------+-------------+--------------+
| host | user | password | ssl_cipher | x509_issuer | x509_subject |
+----------------+------+-------------------------------------------+------------+-------------+--------------+
| localhost | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| node3.soon.com | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| 127.0.0.1 | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| ::1 | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| localhost | | | | | |
| node3.soon.com | | | | | |
+----------------+------+-------------------------------------------+------------+-------------+--------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use mysql
Database changed

 

-- User를 생성하고 권한을 부여합니다.
MariaDB [orademo]> create user 'root'@'%' identified by 'wndlswkd1!';
Query OK, 0 rows affected (0.00 sec)

 

-- 모든 데이터베이스에 대한 권한을 부여합니다.
MariaDB [orademo]> grant all privileges on . to root@'%' identified by 'wndlswkd1!' with grant option;
Query OK, 0 rows affected (0.00 sec)

grant all privileges on mysql.* to root@'%' identified by 'wndlswkd1!';
grant all privileges on . to root@'%' identified by 'root123' with grant option;

 

-- 확인합니다.
MariaDB [orademo]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select host,user,password,ssl_cipher, x509_issuer, x509_subject from mysql.user;
+----------------+------+-------------------------------------------+------------+-------------+--------------+
| host | user | password | ssl_cipher | x509_issuer | x509_subject |
+----------------+------+-------------------------------------------+------------+-------------+--------------+
| localhost | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| node3.soon.com | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| 127.0.0.1 | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| ::1 | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
| localhost | | | | | |
| node3.soon.com | | | | | |
| % | root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | | | |
+----------------+------+-------------------------------------------+------------+-------------+--------------+
7 rows in set (0.00 sec)

-- 삭제시 아래와 같이
MariaDB [(none)]> drop user root@'%';

MariaDB [orademo]> exit
Bye

git clone https://github.com/good-dba/DBMS/MARIADB-sys.git /tmp/maria
참고 자료 https://github.com/good-dba/DBMS/MARIADB-sys

업그레이드 테스트

1. maria db 를 중지 하고
2. 엔진을 ftp로 업로드
3. 심볼링크를 변경한다.
4. maria db 를 시작한다.
5. mysql_upgrade 를 수행한다.

[maria@node3 bin]$ ./mysql_upgrade -u root -proot123 --datadir=/DBMS/MARIADB/DATA
./mysql_upgrade: the '--datadir' option is always ignored
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Processing databases
information_schema
mysql
performance_schema
test
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Processing databases
information_schema
mysql
performance_schema
test
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
performance_schema
test
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

[maria@node3 bin]$
업그레이드 및 호환성 채크가 끝나면 반드시 재시작 필요(다운그레이드 안됨 백업하고 진행 필수)

[maria@node3 bin]$ sudo systemctl restart mariadb

 

 

root 비밀번호 잃어 버렸을경우 설정

인증 생략 옵션 / 안전모드로 데몬을 실행한다.
/usr/bin/mysqld_safe --skip-grant-tables &

콘솔로 들어간다.

/usr/bin/mysql -u root mysql

mariadb~
use mysql;
update user SET PASSWORD=PASSWORD('root123') WHERE USER='root';
flush privileges;

원격접속 command line

ip가 10.10.10.20인 원격서버에 root 사용자로 3306 포트의 wp데이터베이스에 접속

1

mysql -u root -p --port 3306 --host 192.168.56.7 wp

skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

Yum 도는 RPM 삭제 방법

service mysql stop
Remove the packages

yum list | grep Maria

yum remove Maria*
Remove the data and configuration files

rm -rf /etc/my.cnf
rm -rf /var/log/mysql
rm -rf /var/log/mysql.*
rm -rf /var/lib/mysql
rm -rf /etc/mysql

/etc/my.cnf.d/에서도 세팅되는 것도 있다.

mysql 설치 확인 : #rpm -qa | grep mysql
mysql 설치 위치 확인 : #find / -name mysql
mysql 시작 : #/etc/rc.d/init.d/mysqld start

 

[참고 자료 및 알면 좋은 자료]
https://www.yongbok.net/blog/how-to-install-mariadb-source-on-linux/ -- 설치방법
https://DBMS/MARIADB.com/kb/en/library/toad-for-mysql-80/ -- 토드접속 툴
http://gywn.net/2012/09/DBMS/MARIADB-galera-cluster/ -- 이중화 이슈