MySQL #

현재 IT 기술이 계속해서 빠르게 발전해올 수 있었던 주요 원인이 데이터베이스라고들 말한다. 데이터베이스는 접근성 용이(실시간 접근성), 지속적인 변화, 동시에 공유, Transaction과 같은 특징을 가지고 있으며 종류에는 관계형 데이터베이스, NoSQL, 객체지향형 데이터베이스, 그래프 데이터베이스 이렇게 네 가지가 있다. 그중에서 대부분 (80%~90%)이 관계형 데이터베이스를 사용한다. 관계형 데이터베이스에는 SQLite, MySQL, Oracle, PostgreSQL 등 많은 관계형 데이터베이스(RDBMS)가 있으며, 그중에 가장 많이 사용하는 오픈소스의 데이터베이스가 MySQL이다. 데이터베이스에 명령하여 조절하고자 할 때 사용하는 언어가 Structured Query Language인 SQL이다.

DBMS(Database Management System)? #

많은 사용자가 데이터베이스 내의 데이터에 접근할 수 있도록 해주는 데이터베이스 도구의 집합이다. DBMS 중에서 2차원의 표로 데이터를 표현하는 시스템을 RDBMS(관계형 데이터베이스)라고 하며 이는 RDBMS에 Relational이라는 관계형의 접두사를 붙여준 것이다. RDBMS의 특징은 매우 빠르게 처리하고 가시적이라서 사용하기가 쉽다.

MySQL이란? #

MySQL은 세상에서 가장 인기 있고 많이 사용되는 오픈소스 데이터베이스이다. 조금 어렵게 표현한다면 표준 데이터베이스 언어인 SQL(Structured Query Language)을 사용하는 오픈소스의 관계형 데이터베이스 관리시스템(RDBMS) 중 하나이다. MySQL은 크게는 두 가지로 나뉜다. 하나는 Community Edition인 오픈소스 무료 버전이고, 또 하나는 기업이나 기술적 필요에 의한 판매용 상용 데이터베이스이다.

MySQL 설치 #

먼저 GUI로 설치하는 방법이다. 다음 링크 페이지에 가서 http://dev.mysql.com/downloads/mysql/ 해당 OS Platform을 선택 후 맞는 bit 수를 확인 후에 다운로드 버튼을 눌러 설치하면 된다.

설치하고 나서 시작 메뉴에 가서 MySQL version Command Line Client 콘솔을 클릭하면 설치 시 지정해준 비밀번호를 입력하게 나오고 그 이후에는 다음과 같은 페이지가 나온다.

이것은 정상적으로 실행된 화면이다.

다음은 Linux의 terminal에서 설치하는 방법이다.

    $ sudo apt-get install mysql-server

이때 설치과정에서 root 암호도 입력해야 한다.

설치하고 나면 Mysql 접속해보자.

    $ mysql -uroot -p 암호

    welcome to the MySQL~~~하면서
    mysql>

다음과 같이 나타난다면 정상적으로 설치된 것이다.

쉽게 배우는 MySQL Tutorial #

출처 : https://www.youtube.com/watch?v=yPu6qV5byu4

MySQL 사용 Tips #

사용자 추가하기 #

사용자 추가는 MySQL 쉘에서 진행할 수 있다.

[root@localhost ~]# mysql -uroot -p
mysql> # myname 이라는 사용자 생성
mysql> create user myname; 
mysql> # myname 이라는 사용자 생성하면서 비밀번호도 같이 설정
mysql> create user myname identified by 'myname1234';
mysql> # 위와 같이 생성시 해당 서버(localhost)에서만 접속 가능, 외부에서도 접속을 허용하려면 아래와 같이 계정을 한번 더 생성
mysql> # '%' 는 모든 외부 접속을 허용하며 특정 서버에서만의 접속을 허용하려면 해당 서버의 hostname 또는 ip를 입력
mysql> create user 'myname'@'%' identified by 'myname1234';
mysql> # 사용자 추가 후 외부에서는 접속되는데 로컬에서 접속 안되는 경우는 아래처럼 계정을 추가
mysql> create user 'myname'@'localhost' identified by 'myname1234';

사용자 비밀번호 변경하기 #

비밀번호를 변경하는 방법은 여러가지가 있다. 아래 방법중 선택하여 사용한다. 참고로 쿼리는 대소문자 상관없이 작동한다. 아래 쿼리는 강조하기 위해 대문자로 적었다. 소문자로 사용해도 된다.

SET PASSWORD 사용 #

mysql> SET PASSWORD FOR myname@'%' = PASSWORD('myname1234');

GRANT 사용 #

mysql> GRANT USAGE ON *.* TO myname@'%' IDENTIFIED BY 'myname123';

UPDATE 사용 #

mysql> UPDATE mysql.user SET Password=PASSWORD('myname123456') WHERE User='myname' AND Host='%';
mysql> FLUSH PRIVILEGES;

mysqladmin 명령어 사용(일반 콘솔에서) #

mysqladmin 명령어를 사용하려면 해당 계정에 super권한이 있어야 한다. 만약 없다면 DB관리자에게 비밀번호 변경 요청을 해야 한다.

[root@localhost ~]# mysqladmin -umyname -pmyname password myname1234

데이터베이스 추가 및 권한 주기 #

데이터베이스 추가하기 #

mysql> create database [DB name];

사용자에게 데이터베이스에 대해 권한 주기 #

mysql> GRANT ALL privileges ON [DB name].* TO [사용자명]@'%';
mysql> FLUSH PRIVILEGES;

root 계정 비번 분실 시 #

root 비밀번호를 변경하려면 mysql 데몬을 내렸다 올려야 한다. 서비스 중인 서버라면 조심해서 사용해야 한다. 아래와 같이 4단계로 진행하여 변경할 수 있다.

mysqld stop #

[root@localhost ~]# service mysqld stop
mysqld 를 정지 중:                                         [  OK  ]

mysqld 비번없이 접속되도록 실행 #

[root@localhost ~]# /usr/bin/mysqld_safe --skip-grant &
[1] 28951
[root@localhost ~]# 170607 13:51:27 mysqld_safe Logging to '/var/log/mysqld.log'.
170607 13:51:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@localhost ~]#

root 비번 변경 #

[root@localhost ~]# mysql -uroot
mysql> UPDATE mysql.user SET password=PASSWORD('new-password') WHERE user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)
mysql> quit;

mysqld start #

[root@localhost ~]# service mysqld restart

데이터베이스 백업 및 복구 #

데이터베이스를 백업하는 방법은 크게 물리적 백업과 논리적 백업이 있다.

물리적 백업은 데이터베이스가 저장되는 실제 물리적인 파일을 백업하는 방법이다. 데이터가 저장되는 폴더를 통째로 백업했다가 같은 데이터베이스 버전에서 데이터 디렉토리만 바꿔치기 함으로써 백업 및 복구가 가능하다. 작업이 단순하다는 장점은 있지만 에러 발생 시 원인 파악이 어렵다.

논리적 백업은 mysqldump 같은 별도의 툴을 통해 백업하는 방법이다. 백업파일을 별도로 생성하기 때문에 작업은 복잡할 수 있어도 백업된 데이터를 검토하거나 데이터 복구시 에러율이 적다.

이 포스트에서는 mysqldump를 이용한 논리적 백업에 대해 설명한다.

백업 #

전체 DB 백업

$ mysqldump -u [사용자명] -p --all-databases -> all.sql

특정 DB 백업

$ mysqldump -u [사용자명] -p [DB name] -> dump.sql

복구 #

$ mysql -u [사용자명] -p [DB name] < dump.sql

OR

mysql> source dump.sql

max_allowed_packet 에러 관련 #

MySQL을 사용중 큰 데이터를 다루다 보면 아래와 비슷한 에러메시지를 보는 경우가 생긴다.

ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes

max_allowed_packet이 들어가는 에러가 생긴다면 MySQL 설정파일인 /etc/my.cnf (서버 환경에 따라 위치가 다를 수 있다.) 에서 아래 항목을 수정한다. default 값이 16M인데 상황에 맞춰 크기를 늘리면 된다. 설정 후 mysql 데몬을 재 시작 하면 적용이 된다.

[mysqld]
max_allowed_packet=128M

데이터베이스 백업을 위해 mysqldump를 사용할때 위와 같이 설정해도 아래처럼 max_allowed_packet 에러가 발생할 수 있다.

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `tb_bioseq_contig` at row: 82048

이때는 실행 커맨드에 --max_allowed_packet 옵션을 주거나 설정파일에 [mysqldump] 항목을 추가하여 해결 가능하다.

# command
mysqldump -u mysql_user -pmysql_password --max_allowed_packet=128M dbname > dbname.sql


# 설정 파일 수정
[mysqldump]
max_allowed_packet=128M

쿼리문 4가지(SELECT, INSERT, UPDATE, DELETE) #

MySQL의 쿼리문이다. 이 네 가지 쿼리문은 데이터 조작 언어 또는 DML이라고 한다. SELECT '조회 쿼리', INSERT '등록 쿼리', UPDATE '수정 쿼리', DELETE '삭제 쿼리' 에 대해 소개한다.

SELECT #

# SELECT 기본 형태
SELECT 'column names' or *(all column) FROM 테이블 명

column names는 조회하고 싶은 컬럼을 한 개 또는 여러 개를 적어 조회가 가능하고, '*' 을 사용해 전체 조회를 할 수 있다. FROM 절 뒤에는 조회하고자 하는 테이블 명을 적어 준다. 여기서 컬럼은 해당 테이블 안에 속해 있어야 조회할 수 있다.

# SELECT 조건 추가
SELECT USER_ID, USER_NM, USER_AGE FROM sampleTable WHERE USER_NM = '홍길동'

INSERT #

또 만약 열에 조건을 걸어 원하는 결과를 조회하기 위해 WHERE 옵션을 사용하여 조건을 추가 할 수 있다. USER_ID가 홍길동인 결과를 찾기 위한 예시 쿼리문이다.

#INSERT 기본 형태
INSERT INTO 테이블 명(column1, column2, column3) VALUES(value1, value2, value3);

테이블 명 다음 가로 안에 들어가는 컬럼에는 데이터 삽입을 원하는 컬럼을 적어 준다. 그리고 VALUES 다음에 오는 value 값은 삽입할 값들을 넣게 되는데 컬럼의 같은 위치에 데이터를 삽입하게 된다.

또한 컬럼을 지정하지 않고 삽입하는 방법도 있는데, 그럴 경우에는 전체의 값을 삽입하게 되므로 순서와 테이블에 속해있는 컬럼의 개수를 고려하여 데이터를 삽입해줘야 한다.

UPDATE #

#UPDATE 기본 형태
UPDATE 테이블 명 SET(column1 = value1 , column2 = value2 ...) WHERE 조건식

UPDATE의 쿼리는 기존 테이블에 있는 데이터를 새로 등록하지 않고 수정하는 쿼리문이다. 조건식에 맞는 컬럼들을 찾아 SET에 지정한 대입을 실행한다.

#UPDATE WHERE 조건식 예시

UPDATE sampleTable SET(USER_NM = '홍길순', USER_AGE = 30) WHERE USER_ID = 'HONG'

위 조건식 예시에서는 아이디가 HONG인 사람의 이름과 나이를 수정하는 쿼리문이다. 조건식에 수정하고자 하는 데이터를 이용해 수정할 수 있다.

DELETE #

#DELETE 기본 형태
DELETE FROM 테이블 명 WHERE 조건식

조건식에 해당하는 행들을 테이블에서 삭제한다.

#DELETE 사용 예제
DELETE FROM sampleTable WHERE USER_ID = 'HONG'

아이디가 'HONG'이 있는 행을 삭제하게 된다. 조건식에는 고유한 데이터를 조건으로 넣어줘야 같은 행이 지워지는 실수를 하지 않게 된다. 같은 데이터를 가지고 있는 행을 조건식에 넣게 되면 원하지 않는 데이터를 삭제할 수도 있으므로 고유한 키를 가지고 있는 데이터를 조건식에 넣어주어야 한다.

Incoming Links #

Related Data Sciences (DataScience 0) #

• DB - 데이터 타입/MYSQL
• PostgreSQL
• SeqHBase

Suggested Pages #

• 0.730 Hot topics
• 0.105 PostgreSQL/JSON Path Language
• 0.057 PostgreSQL/dblink
• 0.046 PostgreSQL/pg dump
• 0.039 PostgreSQL/JSON Functions and Operators
• 0.018 PostgreSQL/JSON Types
• 0.005 데이터 백업
• 0.000 pcr
• 0.000 Hyungyong Kim
• 0.000 삭제요청
• More suggestions...