Skip to content

MySQL #
Find similar titles

Structured data

Category
Programming
Database

MySQL #

Image

현재 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 수를 확인 후에 다운로드 버튼을 눌러 설치하면 된다.

Image

<그림. MySQL Community Server Download Page From dev.mysql.com >

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

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

Image

다음은 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

Incoming Links #

Related Data Sciences #

0.0.1_20140628_0