리눅스에 MariaDB 설치

Posted on: 2018년 09월 25일 2018년 09월 25일
Categories: 강의자료, 리눅스

설치
1. sudo apt-get update
2. sudo apt-get upgrade
3. sudo apt-get install mariadb-server
4. sudo mysql_secure_installation
  1. Enter current password for root : root 계정의 암호 입력(그냥 Enter)
  2. Set root password : DB의 root 암호 설정(y 입력후 암호 설정)
  3. Remove anonymouse users : 익명사용자 제거(y)
  4. Disallow root login remotely : 원격 접속 불가(n 원격접속 가능)
  5. Remove test database and access to it : 테스트 DB 삭제(y)
  6. Reload privilege tables now : 권한 테이블 reload하여 적용(y)
5. sudo service mysql restart
  1. or sudo /etc/init.d/mysql restart
MariaDB 설정 변경 : sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
1. character-set-server = utf8mb4 #collation-server = utf8mb4_general_ci collation-server = utf8mb4_unicode_ci
  
  1
  2
  3
  
  character-set-server  = utf8mb4
  #collation-server      = utf8mb4_general_ci
  collation-server      = utf8mb4_unicode_ci
  1. character-set-server = utf8mb4 :
  2. collation-server = utf8mb4_unicode_ci : 한글, 일어와 같은 비 라틴계 언어의 정렬을 위해서
MySql 사용하기(참조 : http://link2me.tistory.com/431 )
1. sudo mysql -u root -p : MySql 접속하기
2. 사용자 명령
  1. use mysql; : MySql 데이터베이스 선택하기
    1. mysql 데이터베이스의 user 테이블에 사용자 정보 있음
  2. select host, user, password from user; : user 테이블 보기
  3. 사용자 생성 명령들
    1. create user ‘사용자ID’@’%’ identified by ‘비밀번호’;
      1. 비밀번호도 작은 따옴표 안에 써야 함
      2. 오라클은 작은따옴표 없음
        
        CREATE USER 사용자ID IDENTIFIED BY 비밀번호
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp;
    2. create user 사용자ID;
    3. create user 사용자ID@localhost identified by ‘비밀번호’;
    4. INSERT INTO user (Host, User, Password) VALUES (‘localhost’, ‘계정아이디’, password(‘비밀번호’));
    5. INSERT INTO user (Host, User, Password) VALUES (‘%’, ‘계정아이디’, password(‘비밀번호’));
  4. drop user ‘사용자ID’@’%’; : 사용자 삭제
  5. FLUSH privileges;
3. 데이터베이스 명령
  1. show databases; : 데이터베이스 목록 보기
  2. create database DB이름; : 데이터베이스 만들기
  3. drop
4. 사용자에게 데이터베이스 사용권한 부여
  1. GRANT ALL privileges ON DB명.* TO ‘사용자ID’@’%’;
  2. GRANT ALL privileges ON DB명.* TO ‘사용자ID’@’%’ IDENTIFIED BY ‘비밀번호’;
  3. GRANT ALL privileges ON DB명.* TO 사용자ID@locahost;
  4. GRANT ALL privileges ON DB명.* TO 사용자ID@locahost IDENTIFIED BY ‘비밀번호’;
5. exit : MySql 나가기

사용자 계정으로 MySql 제어하기

계정 만들기(whan)

whan@Linux02:~$ sudo mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.1.34-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'whan'@'%' IDENTIFIED BY 'whan';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SELECT User, Host, Plugin FROM mysql.user;
+------+-----------+-------------+
| User | Host      | Plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
| whan | %         |             |
+------+-----------+-------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> SHOW GRANTS FOR whan;
+--------------------------------------------------------------------------------------------------------------+
| Grants for whan@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'whan'@'%' IDENTIFIED BY PASSWORD '*8B4B4B0F8157D0EC5F4ABDDBB8EAA253A8543A4B' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

whan@Linux02:~$ sudo mysql -u root

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 38

Server version: 10.1.34-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

MariaDB [(none)]> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'whan'@'%' IDENTIFIED BY 'whan';

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SELECT User, Host, Plugin FROM mysql.user;

+------+-----------+-------------+

| User | Host | Plugin |

+------+-----------+-------------+

| root | localhost | unix_socket |

| whan | % | |

+------+-----------+-------------+

2 rows in set (0.00 sec)

MariaDB [mysql]> SHOW GRANTS FOR whan;

+--------------------------------------------------------------------------------------------------------------+

| Grants for whan@% |

+--------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'whan'@'%' IDENTIFIED BY PASSWORD '*8B4B4B0F8157D0EC5F4ABDDBB8EAA253A8543A4B' |

+--------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

계정 사용해보기
1. mysql -u whan -p
혹시 로그인이 되지 않는다면 아래 4단계를 실행
1. sudo msyql -u root
2. use mysql;
3. update user set password=password(‘사용할암호’) where user=’whan’
  1. 또는 SET PASSWORD FOR ‘whan’@’%’ = PASSWORD(‘사용할암호’);
4. flush privileges;

root 암호를 잊었다면
1. sudo service mysql stop
2. sudo mysqld_safe –skip-grant &
3. mysql
  1. use mysql;
  2. UPDATE user SET password=password(‘xxxxx’) WHERE user=’root’;
  3. flush privileges;
  4. exit
4. sudo kill -9 ps -ef | grep mysql | awk '{print $2}'
5. sudo service mysql start
6. sudo mysql -u root -p
………………….
root 계정 사용 문제

sudo를 사용하지 않고 MySql 접속할 수 있도록 설정

mysql -u root -p : MySql 접속하기

whan@Linux02:~$ mysql -u root -p Enter password: ERROR 1698 (28000): Access denied for user 'root'@'localhost'

1
2
3

whan@Linux02:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
1. 오류발생 : ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

sudo mysql -u root -p

whan@Linux02:~$ sudo mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 34
Server version: 10.1.34-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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)]> SELECT User, Host, Plugin FROM mysql.user;
+------+-----------+-------------+
| User | Host      | Plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+
1 row in set (0.00 sec)

whan@Linux02:~$ sudo mysql -u root -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 34

Server version: 10.1.34-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

MariaDB [(none)]> SELECT User, Host, Plugin FROM mysql.user;

+------+-----------+-------------+

| User | Host | Plugin |

+------+-----------+-------------+

| root | localhost | unix_socket |

+------+-----------+-------------+

1 row in set (0.00 sec)

Plugin 값이 unix_socket이면 계정 쉘인증 안 됨

쉘인증 포기 방법 : root의 plugin을 unix_socket에서 mysql_native_password로 수정하면 됨

whan@MoakLinux:~$ sudo mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.1.41-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SELECT user, host, plugin FROM mysql.user;
+------+-----------+-------------+
| user | host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+
1 row in set (0.00 sec)

MariaDB [mysql]> update user set plugin='mysql_native_password' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SELECT user, host, plugin FROM mysql.user;
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+
1 row in set (0.01 sec)

MariaDB [mysql]> exit
Bye

whan@MoakLinux:~$ sudo mysql -u root -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 41

Server version: 10.1.41-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

MariaDB [(none)]> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> SELECT user, host, plugin FROM mysql.user;

+------+-----------+-------------+

| user | host | plugin |

+------+-----------+-------------+

| root | localhost | unix_socket |

+------+-----------+-------------+

1 row in set (0.00 sec)

MariaDB [mysql]> update user set plugin='mysql_native_password' where user='root';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> SELECT user, host, plugin FROM mysql.user;

+------+-----------+-----------------------+

| user | host | plugin |

+------+-----------+-----------------------+

| root | localhost | mysql_native_password |

+------+-----------+-----------------------+

1 row in set (0.01 sec)

MariaDB [mysql]> exit

Bye

root 계정에 대한 쉘 인증을 포기하지 말고 사용자 계정을 생성하여 사용하는 것이 가장 좋은 방법임

whan0623

517