MariaDB 설치 및 MySQL 기본 명령어

MariaDB 설치 및 MySQL 기본 명령어

---

DB (DataBase) 

- 데이터의 집합

DBMS (DataBase Management System) 

- 데이터베이스 운영하고 관리하는 소프트웨어
- 리눅스에서 주로 쓰는건 "MySQL"과 "MariaDB"

 
관계형 DBMS

- 행(레코드,row)과 열(column)로 이루어진 테이블로 데이터 저장하는것
- 대부분 관계형 사용
 

1. mariadb 설치

mariadb(클라이언트), mariadb-server 설치
# yum install -y mariadb mariadb-server
# rpm -ql mariadb-server | grep system
>> /usr/lib/systemd/system/mariadb.service << systemctl로 실행할수 있는 것
 

2. mariadb 접속 및 기본 명령어

# pgrep -fl mysqld >> mysql 올라와 있는지 확인
# cat /etc/services | grep mysql 포트번호 확인 (mysql=3306)
# mysql -u root -p (-h 127.0.0.1(로컬호스트) -P 3306(mysql포트))  >> 초기 사용자 없음 root뿐
Enter password: [엔터] >> 초기 비밀번호 없음
MariaDB [(none)]> show databases;
MariaDB [(none)]> use mysql >> DataBase 교체 ";" 안써도 됨(써도됨)
MariaDB [mysql]> show tables;
** MariaDB [(none)]> help 에서 봤을때 나오는 명령어들은 ";" 써야함
MariaDB [mysql]> select host,user,password from user; >> user에서 host,user,password 순서의 탭으로 정보를 보여줘라
 

2-1. mariadb 비밀번호 설정

1)
# mysql_secure_installation
Enter current password for root (enter for none):

Set root password? [Y/n] y
New password:
Re-enter new password:

Remove anonymous users? [Y/n] y - 익명계정 삭제

Disallow root login remotely? [Y/n] n - putty 사용하려면 n

Remove test database and access to it? [Y/n] y - test 데이터베이스 삭제

Reload privilege tables now? [Y/n] y - 테이블을 리로드

 
2)
# mysqladmin -u root -p password mypass[바꿀 비밀번호]
> root 비밀번호 변경 (mariaDB 접속할때의 비밀번호)
>> 10.5 버전은 root 비밀번호 바꿀때 원래 비밀번호 몰라도 된다.
  (root계정에서 mariaDB root 계정으로 들어갈땐, 애초에 비밀번호 안넣어도 됨 #mysql -u root)
 

2-2. mariadb 한글 설정

/etc/my.cnf 설정
음영이 있는 것들 추가 하고 저장한뒤, mariadb 재부팅
** MariaDB 10.5 버전에서는 조금 다르게 보일 수 있다 하지만 [mysqld] 탭을 만들고 음영부분 두곳 다 넣어주면 된다.
  확인해보려면 "MariaDB [(none)]> status"

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

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

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[mysqldump]
default-character-set=utf8

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

 

3. SQL (Structured Query Language)

- DBMS가 알아들을 수 있는 언어

4. SQL 기본 문법

** MariaDB 데이터 형식
varchar(n) - 최대 n개의 크기를 갖는 가변길이 문자열
char(n) - n개의 문자가 저장되는 고정 길이 문자열 
* varchar(100) vs char(100) - 'ABCDE'를 저장한다 했을 때 varchar는 5바이트만 사용하지만, 
char는 100바이트를 확보 후 5바이트만 사용(95바이트 낭비)
int - 정수형 숫자
float - 실수형 숫자
date - 날짜 형식
time - 시간 형식

 

1) 데이터베이스 관련

show database; -- 데이터베이스 이름 조회
use 데이터베이스이름; -- 사용할 데이터베이스 지정
create database 데이터베이스이름; -- 데이터베이스 생성
drop database 데이터베이스이름; -- 데이터베이스 삭제

 
2) 테이블 운영 관련

show tables; -- 테이블이름 조회
explain 테이블이름; -- 테이블 구조 조회
create table 테이블이름; -- 테이블 생성
drop table 테이블이름; -- 테이블 삭제
alter table 테이블이름; -- 테이블 수정
ex1) alter table customer modify name char(20);
-- customer 테이블의 name 필드 데이터타입을 char(20)으로 변경한다.
ex2) alter table customer change name fullname char(10);
-- customer 테이블의 name 필드 이름을 fullname으로 바꾸고 자릿수는 char(10)으로 지정한다.
ex3) alter table customer add phone varchar(20) after name;
-- customer 테이블에서 name 필드 바로 다음에 전화번호를 의미하는 phone 필드를 추가한다.
ex4) alter table customer drop age;
-- customer 테이블에서 age 필드를 삭제한다.

 
2-2) SELECT문

select 필드이름1,필드이름2,... from 테이블이름 where 조건;
-- 테이블이름에서 필드이름1,2,...를 조회하되, 조건을 따라라
ex1) select * from customer;
-- customer 테이블에서 모든 행의 모든 필드를 조회한다.
ex2) select id,name from customer;
-- customer 테이블에서 모든행의 id필드와 name 필드 값을 조회한다.
ex3) select id name from customer where id = 'john';
-- customer 테이블에서 id 필드값이 'john'인 행의 id 필드와 name 필드값을 조회한다.
ex4) select id name from customer where age >= 25;
-- customer 테이블에서 age 필드 값이 25 이상인 행의 id 필드와 name 필드값을 조회한다.
** ex5) select id,name from customer where name like 'j%';
-- customer 테이블에서 이름 앞에 j가 들어가는 모든 행의 id필드와 name 필드값을 조회한다.
ex6) select * from customer order by name asc/desc;
-- customer 테이블에서 모든 필드 값을 name의 asc(오름차순:작은거부터)/desc(내림차순:큰거부터)으로 정렬해서 조회.
ex7) select * from customer order by name asc, id asc;
-- customer 테이블에서 모든 필드 값을 name의 asc(오름차순)으로, 같다면 id의 asc(오름차순)으로 조회
ex8) select distinct id from customer;
-- customer 테이블에서 id필드의 값을 중복 제거하고 조회한다.
ex9) select emp_no,hire_date from employees order by hire_date limit 1,10;
-- employees 테이블에서 hire_date 기준으로 정렬하고 1(생략할 개수-0은 생략가능),10(출력개수) 조회한다.
ex10) select userid,sum(amount) from buyTbl group by userid;
-- buyTbl 테이블에서 같은 userid를 그룹으로 묶고, userid와 amount의 합계(sum(amount))를 조회한다.
ex11) select userid as '사용자', sum(price * amount) as '총구매금액' from buyTbl 
group by userid having sum(price * amount) > 100;
-- group by로 묶은 다음에는 where 절이 아닌 having 절을 사용해야한다.
** where절 아래에 올수 있는 것은 and,or,not,between(사이값),in(여러값중 맞는거) 등이 더 있고
where id > (select id from customer where name='john'); 이런식으로 이중select문(SubQuery)도 사용가능
하지만 WHERE height >= (SELECT height FROM usertbl WHERE addr='경남');
-- 둘이상의 값이 리턴되면(경남이 여러명이 있을수 있으니) 오류가 발생한다.
WHERE height >= ANY (SELECT height FROM usertbl WHERE addr='경남'); -- 이러면 해결완료
* ANY 대신에 IN을 사용해도 결과는 같다.

실습 예제

1. userTbl 에서 키가 180이상이고 185 미만인 사람이 누구인지 출력

select * from userTbl where height between 180 and 185;

2. userTbl 에서 키순서대로(내림차순) 출력

(* 키가 같으면 나이순대로(오름차순) 출력)

select * from userTbl order by height desc, birthyear asc;

3. buyTbl 에서 물건구매를 많이한 순서대로 출력하는데 세번째 레코드 까지만 출력

select userid, count(amount) from buyTbl group by userid limit 3;

4. buyTbl 에서 각 상품 구매갯수를 출력하시오 (모니터, 메모리, 책만 표시)

select prodname, sum(amount) from buyTbl group by prodname having prodname in ('모니터','메모리','책');

내용이 조금 많아서 모르는 것은 매뉴얼을 찾아보는 것이 좋을 듯하다.
** MySQL SELECT Statement Manual
MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 SELECT Statement

MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 SELECT Statement

** MariaDB SELECT Statement Manual

SELECT - MariaDB Knowledge Base

SELECT

 
3) 행 입력/삭제/수정 관련

insert into 테이블이름 (변경하고자하는 행) vlaues (값1, 값2, ..., ...); -- 행 입력
ex) insert into customer (id,name,age,addr) values ('hong', '홍길동', 22, '경기');
-- customer 테이블에 홍길동 고객의 행을 삽입한다.
-- 행 순서를 알고 있다면 values 앞의 값을 생략가능 (대신 다 넣어야함)
delete from 테이블이름 where 조건; -- 행 삭제
ex) delete from customer where id='hong';
-- customer 테이블에서 id 필드값이 'hong'인 행을 삭제한다.
update 테이블이름 set 필드이름1=수정할값1, 필드이름2=수정할값2, ... where 조건; -- 행 수정
ex) update customer set age=25 where id='hong';
-- customer 테이블에서 id 필드 값이 'hong'인 행의 age 필드 값을 25로 수정한다.

 
 

5. mariadb / mysql 암호 재설정

1) mariadb 종료
root)# systemctl stop mariadb 
2) grant tables 참조하지않고 sql 실행
root)# mysqld_safe --skip-grant-tables &
3) mysql로 들어가 user password 바꾸기
MariaDB [(none)]> use mysql
MariaDB [mysql]> update user set password=password('변경할비밀번호') where user='변경하고자하는유저명'
ex) MariaDB [mysql]> update user set password=password('mypass') where user='root'
4) mariadb 종료 후 다시 켜기 *
root)# mysqladmin -u root shutdown
root)# systemctl start mariadb

 
6. 외부 .sql 파일 적용하기

1) mysql 안에서 적용하기
MariaDB [mysql]> source /tmp/dbsource/shop.sql
2) mysql 실행하면서 적용하기
# mysql -u root -p [데이터베이스명] < [sql파일 경로]
** .sql 파일에 어떤식으로 적혀있는 지에 따라서 적용 방식을 바꾸는 것이 좋다.

 
7. HeidiSQL

- 데이터베이스 GUI툴
리눅스를 HeidiSQL로 Database 운영하기 위해서는 방화벽 설정을 해주어야한다.
# firewall-cmd --add-service=mysql
그 뒤에는 권한을 줘야한다.
MariaDB [mysql]> create user 'dba'@'192.168.56.1'(윈도우 IP);
MariaDB [mysql]> grant all privileges on *(모든db).*(모든table) to 'dba'@'192.168.56.1' identified by 'mypass'(비밀번호);
** 모든 주소는 '192.168.56.1' 대신 '%'를 사용하면 된다.
 
*privileges (권한)
mysql의 user 테이블을 확인해보면, (use mysql >> explain user)
select, insert, update, delete, create, drop 등등 _priv 있는 것들이 있다.
이걸 "grant select,insert,create, .. on" 이런식으로 쓸 수 있다.
물론 grant_priv는 all privileges를 했을 때 들어가지 않는다.
> 강제로 주려면 "grant all privileges on *.* to 'dba'@'192.168.40.1' identified by 'centos' with grant option;