MySQL 기본 사용법

28 Dec 2018

mysql

1. MySQL 설치

[root@sirna1 ~]# yum -y install mysql mysql-devel mysql-server

2. MySQL 데몬

MySQL 데몬의 이름은 mysqld 이다. MySQL 을 사용하기 위해서는 데몬이 실행되고 있는 상태이어야 한다. 이러한 데몬을 시작하고 종료하는 방법을 알아본다.

[root@sirna1 ~]# service mysqld start

Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
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 MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h sirna1.snu.ac.kr password 'new-password'

Alternatively you can run:
/usr/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 manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

서버에서 mysqld 데몬이 실행 중인지 다음과 같이 확인해볼 수 있다.

[root@sirna1 ~]# service mysqld status
mysqld (pid  30076) is running...

또한 데몬이 실행되고 나면 socket 파일이 생성되는데, /var/lib/mysql/mysql.sock 와 같은 위치에 sock 파일이 생성되었는지 확인해 봐도 된다.

[root@sirna1 ~]# ls -al /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Oct  8 16:54 /var/lib/mysql/mysql.sock

종료는 아래와 같이 한다.

[root@sirna1 ~]# service mysqld stop
mysqld (pid  30076) is running...

하나 더, mysqld (MySQL 데몬) 실행 시 오류가 나는 부분에 대해서는 log 파일을 참조하면 된다. 위치는 /var/log/mysqld.log 이다.

[root@sirna1 ~]$ mysql -u root
mysql> 

3. 접속 및 종료

초기 접속

설치 후 비밀번호를 설정해주기 전까지는 비밀번호 없이 접속 가능하다. 접속 방법은 아래와 같다.

[root@sirna1 ~]$ mysql -u root
mysql> 

데이터베이스 선택

형식: use {db_name};

mysql> use test;
Database changed
mysql> 

테이블 생성

create table member_table (
seq      int(20)   NOT NULL AUTO_INCREMENT,
user_id  char(20)  NOT NULL,
password char(41)  NOT NULL,
name     char(20)  NOT NULL,
level    int(2)    NOT NULL DEFAULT '6',
email    char(255) DEFAULT NULL,
phone    char(20)  DEFAULT NULL,
belonging char(40) DEFAULT NULL,
PRIMARY KEY (seq),
INDEX idx_level (level),
constraint fk_level foreign key (level) references level_table (seq) on delete cascade on update cascade
) type=InnoDB;

create table level_table (
    seq int(2) not null,
    dsc char(20) default null,
    primary key (seq)
) type=InnoDB;

테이블 확인

테이블이 제대로 만들어졌는지 현재 데이터베이스 내의 테이블 목록을 조회해 보자.

mysql> show tables;

그리고 해당 테이블의 구조도 한번 확인해 보자.

mysql> describe members;

테이블 삭제

mysql> drop table member_table;
mysql> drop table level_table;

레코드 삽입

세 가지의 방법이 존재한다.

insert into member (id, name) values (1, 'Sukjun Kim');
insert into member values (1, 'Sukjun Kim');
insert into member (name) values ('Sukjun Kim');

insert into level_table (seq, dsc) values (1,'관리자');
insert into level_table (seq, dsc) values (2,'정회원');
insert into level_table (seq, dsc) values (9,'인증대기');

insert into member_table (user_id, password, name, level, jumin, email, phone, belonging)
    values ('admin',md5('1234'),'관리자',1,'111111-1111111','webmaster@vbrc.snu.ac.kr','','');

레코드 조회

mysql> select * from member;

select count(*) ALIAS from member where sex=f;  // 조건에 맞는 데이터의 수 출력

레코드 변경

update (테이블명) set (컬럼명1) = 'value1' where (컬럼명2) = 'value2';
update level_table set seq = 9 where seq = 7;

레코드 삭제

delete from (테이블명) where (조건)
delete from member_table where seq=5;

데이터베이스 백업

mysqldump 를 이용해 데이터베이스를 백업하는 방법을 알아본다.

(mysqld 작동중)

mysqldump -u root -p {database_name} > {output_file}

mysqldump -u root -p www > db_www.sql

(mysqld_safe 작동중)

mysqldump -u root www > db_www.sql

mysqld_safe

mysql root 계정의 비밀번호 변경

계정정보가 있는 데이터베이스는 mysql, 테이블은 user 이다. 비밀번호를 암호화 시켜주는 함수는 password 이다.

update user set password=password('새로운비밀번호') where User='root';

mysqld_safe 권한없이 실행

먼저 mysql 데몬이 실행되고 있는지 확인한다. 있다면 service mysqld stop 으로 종료한다.
백그라운드로 다음과 같이 실행시킨다.
```
/usr/bin/mysqld_safe --skip-grant-tables &
```
이후에 다음과 같이 mysql에 접속한다.
```
mysql -u root mysql
```

사용자 정보로 접속

사용자 아이디와 비밀번호를 이용하여 MySQL 프롬프트로 접속

[lab@sirna1 ~]$ mysql -ulab -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.66 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

다음과 같이 데이터베이스 명을 따로 써줄 수도 있다. 이는 접속되자마자 해당 데이터베이스를 사용하도록 선택한다. 마치 use test; 를 입력한 효과와 동일하다.

[lab@sirna1 ~]$ mysql -ulab -p test
Enter password:
...
mysql>

종료하는 방법은 두 가지가 있다. MySQL 프롬프트 상에서 exit; 이라고 입력하거나 Ctrl+D 를 누르면 Bye 라고 출력되면서 종료된다.

mysql> exit
Bye
[root@sirna1 ~]#

mysql> Bye
[root@sirna1 ~]#

(3 단계) MySQL 을 실행하면서 mysql 이라는 이름의 데이터베이스로 들어간다.

4. 비밀번호 설정

mysqladmin 은 mysqld(MySQL 데몬)의 관리자 프로그램이다. mysqld 의 접속 사용자 설정을 할 수 있으며, 데이터데이스 추가 및 삭제 등의 일을 처리할 수 있다. mysql 초기 실행에서는 비밀번호가 잡혀있지 않기 때문에 아래와 같이 비밀번호 없이도 설정이 가능하다.

# mysqladmin -u root password 123456

비밀번호가 설정된 상태에서 새로운 비밀번호로 바꾸려면 다음과 같이 써주면 된다.

# mysqladmin -u root -p password '123456new'

5. 사용자 추가

mysql 데이터베이스로 접근하여 현재 user 테이블의 상태를 살펴보기

mysql> use mysql;
Database changed

mysql> select host, user, password from user;
+------------------+------+-------------------------------------------+
| host             | user | password                                  |
+------------------+------+-------------------------------------------+
| localhost        | root | *32F6FA2954C783520A91B974F38xxxxxxxxxx2D2 |
...
+------------------+------+-------------------------------------------+
5 rows in set (0.00 sec)

사용자를 추가하면서 비밀번호까지 설정

mysql> create user lab@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec)

환경 설정

MySQL 의 환경설정 파일: my.cnf

MySQL monitor 를 실행될 때 환경설정 파일인 my.cnf 를 읽어들인다. 그러나 환경설정 파일이 서버 내 여러군데에 있어 어느 환경설정 파일부터 읽어들이는지 궁금할 때가 있다. 이럴때는 mysql --help 를 입력하면 출력되어 나오는 메세지 중 “Default options are …” 라고 시작되는 부분을 읽어보면 알 수 있다.

$ mysql --help
...
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

여기서는 /etc/mysql/my.cnf, /etc/my.cnf, ~/.my.cnf 파일의 순서로 환경설정을 로드한다는 것을 알 수 있다.

환경 설정, 문자셋 변경

기본 문자셋이 항상 latin1 으로 되어있어 불편을 겪는다. 따라서 문자셋을 다음과 같이 변경할 수 있다. 환경 설정 파일 /etc/my.cnf 을 열어 다음과 같이 수정한다.

[client]
default-character-set = utf8

[mysqld]
init_connect = "SET collation_connection = utf8_general_ci"
init_connect = "SET NAMES utf8"
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci

[mysqldump]
default-character-set = utf8

[mysql]
default-character-set = utf8

마찬가지로 .php 파일에 mysql 접속하는 부분에서 utf-8로 선언을 하여 이용해야 데이터를 입력하거나 뽑아낼 때 인코딩 문제가 발생하지 않는다.

if(!$connect) $connect = @mysql_connect($hostname, $id, $passwd);
mysql_query("set names utf8");    <-- 이부분
@mysql_select_db($db, $connect);
$row = mysql_fetch_array(mysql_query("select * from member_profile where seq='$seq'"));

CRUD (Create, Read, Update, Delete)

데이터베이스 생성, 조회, 선택

데이터베이스를 생성하기 위해서는 create database {database name}; 명령을 이용한다. test 라는 이름을 가진 데이터베이스 생성해 보자.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

또는 생성과 동시에 각종 속성들을 넣어줄 수 있다.

mysql> create database test default character set utf8 default collate utf8_general_ci;

데이터베이스 목록 조회는 show databases; 명령을 이용한다. 앞서 생성한 데이터베이스가 만들어 졌는지 한번 확인해 보자. 다음과 같이 현재 MySQL 에 있는 데이터베이스들의 목록을 살펴볼 수 있다.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

데이터베이스 선택

형식: use {db_name};

mysql> use test;
Database changed
mysql> 

테이블 생성

create table member_table (
seq      int(20)   NOT NULL AUTO_INCREMENT,
user_id  char(20)  NOT NULL,
password char(41)  NOT NULL,
name     char(20)  NOT NULL,
level    int(2)    NOT NULL DEFAULT '6',
email    char(255) DEFAULT NULL,
phone    char(20)  DEFAULT NULL,
belonging char(40) DEFAULT NULL,
PRIMARY KEY (seq),
INDEX idx_level (level),
constraint fk_level foreign key (level) references level_table (seq) on delete cascade on update cascade
) type=InnoDB;

create table level_table (
    seq int(2) not null,
    dsc char(20) default null,
    primary key (seq)
) type=InnoDB;

테이블 확인

테이블이 제대로 만들어졌는지 현재 데이터베이스 내의 테이블 목록을 조회해 보자.

mysql> show tables;

그리고 해당 테이블의 구조도 한번 확인해 보자.

mysql> describe members;

테이블 삭제

mysql> drop table member_table;
mysql> drop table level_table;

레코드 삽입

세 가지의 방법이 존재한다.

insert into member (id, name) values (1, 'Sukjun Kim');
insert into member values (1, 'Sukjun Kim');
insert into member (name) values ('Sukjun Kim');

insert into level_table (seq, dsc) values (1,'관리자');
insert into level_table (seq, dsc) values (2,'정회원');
insert into level_table (seq, dsc) values (9,'인증대기');

insert into member_table (user_id, password, name, level, jumin, email, phone, belonging)
    values ('admin',md5('aabbccdd'),'관리자',1,'123456-1234567','webmaster@vbrc.snu.ac.kr','','');

레코드 조회

mysql> select * from member;

select count(*) ALIAS from member where sex=f;  // 조건에 맞는 데이터의 수 출력

레코드 변경

update (테이블명) set (컬럼명1) = 'value1' where (컬럼명2) = 'value2';
update level_table set seq = 9 where seq = 7;

레코드 삭제

delete from (테이블명) where (조건)
delete from member_table where seq=5;

데이터베이스 백업

mysqldump 를 이용해 데이터베이스를 백업하는 방법을 알아본다.

(mysqld 작동중)

mysqldump -u root -p {database_name} > {output_file}

mysqldump -u root -p www > db_www.sql

(mysqld_safe 작동중)

mysqldump -u root www > db_www.sql

mysqld_safe

mysql root 계정의 비밀번호 변경

계정정보가 있는 데이터베이스는 mysql, 테이블은 user 이다. 비밀번호를 암호화 시켜주는 함수는 password 이다.

update user set password=password('새로운비밀번호') where User='root';

mysqld_safe 권한없이 실행

먼저 mysql 데몬이 실행되고 있는지 확인한다. 있다면 service mysqld stop 으로 종료한다.
백그라운드로 다음과 같이 실행시킨다.
```
/usr/bin/mysqld_safe --skip-grant-tables &
```
이후에 다음과 같이 mysql에 접속한다.
```
mysql -u root mysql
```

yum 패키지 설치 (지정된 repository 이용)

yum –enablerepo=c5-testing install mysql

3. 접속 및 종료

yum 설치된 패키지 제거

yum remove (패키지명)

yum remove mysql

자주 접하는 에러들

root 계정 패스워드 분실

자주 사용하지 않다 보면 root 계정 패스워드가 기억이 나지 않아 MySQL 접속을 하지 못하는 경우가 있다.

(1 단계) 현재 가동중인 MySQL 데몬 중지

[root@sirna1 ~]# service mysqld stop
Stopping mysqld:            [  OK  ]

(2 단계) 무인증 안전모드 mysqld 실행 (mysqld_safe –skip-grant)

[root@sirna1 ~]# mysqld_safe --skip-grant
161020 18:43:27 mysqld_safe Logging to '/var/log/mysqld.log'.
161020 18:43:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

(3 단계) MySQL 을 실행하면서 mysql 이라는 이름의 데이터베이스로 들어간다.

[root@sirna1 ~]# mysql -uroot mysql
...
mysql> 

(4 단계) user 테이블에 들어있는 root 계정의 패스워드를 새로 지정

mysql> update user set password=password('123456') 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.00 sec)

mysql> quit
Bye
[root@sirna1 ~]#

(5 단계) 실행 중이던 mysqld_safe 는 종료하고 MySQL 데몬 시작

[root@sirna1 ~]# service mysqld restart
Stopping mysqld:               [  OK  ]
Starting mysqld:               [  OK  ]

(6 단계) 패스워드를 이용하여 정상적인 접속 가능

[root@sirna1 ~]# mysql -uroot -p123456
...
mysql>

ERROR 1045 (28000): Access denied for user

계정의 패스워드가 맞지 않는 경우에 접근 거부 (Access denied) 에러가 나타난다.

[lab@sirna1 ~]$ mysql -ulab -p123456
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket

접속시 다음과 같이 socket 을 찾지 못하는 오류가 나는 경우가 있다. (1) MySQL 데몬이 현재 가동중이지 않거나 (2) 설정 파일(my.cnf)에 오류가 있는 경우이다.

[lab@sirna1 ~]$ mysql -ulab -p123456
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

mysql.sock 파일이 없는 것은 현재 시스템에서 MySQL 데몬이 실행중이지 않기 때문이다. 따라서 실행중인지의 여부를 판단해 보고, 실행되고 있지 않다면 MySQL 데몬을 실행시키면 된다. 데이터베이스의 상태를 확인하고 꺼져있으면 다시 시작한다.

[root@sirna1 ~]# service mysqld status
mysqld is stopped
[root@sirna1 ~]# service mysqld restart
Initializing MySQL database:  Installing MySQL system tables... OK
Filling help tables... OK