지난 포스팅때 CentOS7에 마리아db를 설치 해보았었다

yum으로 설치한 마리아DB에서 인코딩 설정은 심플하더라... ㅎㅎ

추가로 할 내용은 없다 그냥 설명 시작!!

MariaDB [(none)]> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+
14 rows in set (0.00 sec)

몇가지가 utf8이아닌 latin1 이다

대략 보니 서버쪽 부분만 latin 인코딩인대 이것을 utf8로 변경해보도록 하자

[root@localhost etc]# vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character_set_server = utf8   <- 추가를 해주도록 하자:
# 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

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

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

character_set_server = utf8 추가해준 후 service 재시작을 해보도록 하자

[root@localhost etc]# systemctl stop  mariadb.service
[root@localhost etc]# systemctl start  mariadb.service

별 오류없이 위의 명령어가 동작되었으면 다시한 번 mysql 접속후 show variables like 'c%'; 명령어를 주어 확인해보도록 하자

MariaDB [(none)]> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | utf8_general_ci            |
| collation_server         | utf8_general_ci            |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+
14 rows in set (0.00 sec)

위와같이 정상적으로 UTF8설정이 변경되었다면 성공

한번 리눅스서버에 마리아db를 설치해보고자 한다

마리아DB는 그냥 MySQL이다... 

이름만 틀릴뿐..

CentOS7 기준이라 기존의 MySQL설치는 조금 난해해서 그냥 마리아DB로 설치하고자 한다.

[root@localhost lib]# yum -y install mariadb-server mariadb
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: centos.tt.co.kr
 * extras: centos.tt.co.kr
 * updates: centos.tt.co.kr
Resolving Dependencies
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.37-1.el7_0 will be installed
---> Package mariadb-server.x86_64 1:5.5.37-1.el7_0 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================
 Package                      Arch                 Version                           Repository             Size
=================================================================================================================
Installing:
 mariadb                      x86_64               1:5.5.37-1.el7_0                  updates               8.9 M
 mariadb-server               x86_64               1:5.5.37-1.el7_0                  updates                11 M

Transaction Summary
=================================================================================================================
Install  2 Packages

Total download size: 20 M
Installed size: 104 M
Downloading packages:
(1/2): mariadb-5.5.37-1.el7_0.x86_64.rpm                                                  | 8.9 MB  00:00:02
(2/2): mariadb-server-5.5.37-1.el7_0.x86_64.rpm                                           |  11 MB  00:00:03
-----------------------------------------------------------------------------------------------------------------
Total                                                                            5.8 MB/s |  20 MB  00:00:03
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 1:mariadb-5.5.37-1.el7_0.x86_64                                                               1/2
  Installing : 1:mariadb-server-5.5.37-1.el7_0.x86_64                                                        2/2
  Verifying  : 1:mariadb-server-5.5.37-1.el7_0.x86_64                                                        1/2
  Verifying  : 1:mariadb-5.5.37-1.el7_0.x86_64                                                                                                                                                                                           2/2

Installed:
  mariadb.x86_64 1:5.5.37-1.el7_0                                                                                   mariadb-server.x86_64 1:5.5.37-1.el7_0

Complete!
[root@localhost lib]#

설치 완료하였으면 마리아DB 서비스를 시작해주도록 하자

[root@localhost lib]# systemctl start mariadb.service

에러가 발생하지 않는다면 일반 mysql 접속과 동일하게 접속해보도록하자

[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.37-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]>

초기 비밀번호 설정을 하지 않았다면 패스워드입력없이 엔터만 치면 다음과 같이 접속이 가능하다

root 설정을 해보도록 하자

[root@localhost~]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
.
.
.
Enter current password for root (enter for none): 엔터
OK, successfully used password, moving on...
.
.
.
Set root password? [Y/n] 엔터
New password: 신규생성패스워드입력
Re-enter new password: 신규생성패스워드입력
Password updated successfully!
Reloading privilege tables..
 ... Success!
.
.
.
Remove anonymous users? [Y/n] 엔터
 ... Success!
.
.
.
Disallow root login remotely? [Y/n] 엔터
 ... Success!
.
.
.
Remove test database and access to it? [Y/n] 엔터
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
.
.
.
Reload privilege tables now? [Y/n] 엔터
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@localhost~]#

패스워드 설정까지 마무리된 상태이다.

그럼 다시한번 MariaDB접속을 해보도록 하자

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

패스워드 없이 접속시 위와같이 에러발생하고 패스워드 입력후 접속화면이 뜬다면 성공적으로 설치 완료!!!

방화벽(3306포트) 개방을 해준 후 

외부에서 DB툴로 접속을 해보게 되면 

Error No.1130

Host 'xxx.xx.x.xx' is not allowed to connect to this MariaDB server

라는 에러를 뿜을듯...

이럴경우 maria db를 콘솔에 접속 한 후 다음과 같이 설정을 해주도록 하자

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 host,user,password from user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| 127.0.0.1 | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
| ::1       | root | *A4B6157319038724E3560894F7F932C8886EBFCF |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

위와같이 나오면 host에 외부 아이피가 등록되지 않아서이다.

본인은 전체 접근으로 권한을 설정할 것이므로 grant 설정에 관한 글은 현재 본인 블로그에는 없으므로 구글링을 통하여 적절히 사용하시길....

MariaDB [mysql]> grant all privileges on *.* to 'ROOT계정'@'%' identified by 'ROOT의비밀번호';
Query OK, 0 rows affected (0.00 sec)

위와같이 주었으면 다시한면 외부에서 접속을 해보면 잘 접속이 될것이다.