'MariaDB Admin Guide'에 해당되는 글 4건

  1. [MariaDB Install] How to Install MariaDB Server 10 by using yum (2)
  2. [MariaDB] Session Kill
  3. [MariaDB] About Binary Log
  4. [MariaDB] 데이터베이스 생성 / 권한 부여 / 접속






How to Install MariaDB Server 10 by using yum

서버 구성환경

Virtual Machine

VMware Workstation 10.0.0

OS

Cent OS 7.0

Devices

Memory : 1 GB

Processors : 1

Hard Disk(SCSI) : 10 GB

Network Adapter : NAT

 DB Link

Download MariaDB


MariaDB Server 10 버전을 CentOS 7에 yum 패키지를 사용하여 설치하는 방법을 알아보겠습니다.


Create MariaDB yum repo file

각 OS, DB 환경에 맞게 MariaDB Repositories 정보를 입력해야 합니다.

## /etc/yum.repos.d/MariaDB.repo 파일을 신규 생성합니다.
[root@localhost ~]# vi /etc/yum.repos.d/MariaDB.repo

## 아래 내용을 복사하여 붙여 넣습니다.
--
# MariaDB 10.0 CentOS repository list - created 2014-10-16 08:00 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
--


Importing MariaDB Signing Key

## MariaDB Key 값을 Import 시킵니다.
[root@localhost ~]# rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB


Install MariaDB Server and Client

## yum package를 사용하여 MariaDB Server 와 Client를 설치합니다.
[root@localhost ~]# yum install MariaDB-server MariaDB-client MariaDB-devel MariaDB-shared
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-client.x86_64 0:10.0.14-1.el7.centos will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.0.14-1.el7.centos.x86_64
---> Package MariaDB-devel.x86_64 0:10.0.14-1.el7.centos will be installed
---> Package MariaDB-server.x86_64 0:10.0.14-1.el7.centos will be installed
--> Processing Dependency: perl(DBI) for package: MariaDB-server-10.0.14-1.el7.centos.x86_64
---> Package MariaDB-shared.x86_64 0:10.0.14-1.el7.centos will be installed
--> Running transaction check
...
... ## 중간 중간 Is this ok [y/N] 메시지 표시 -> y 선택
...
Transaction Summary
================================================================================================================================================================================================================================================================
Install  4 Packages (+7 Dependent packages)

Total download size: 69 M
Installed size: 307 M
Is this ok [y/d/N]: y
Downloading packages:
(1/11): MariaDB-10.0.14-centos7_0-x86_64-common.rpm                                                                                                                                                                                      |  23 kB  00:00:01     
(2/11): MariaDB-10.0.14-centos7_0-x86_64-devel.rpm                                                                                                                                                                                       | 6.2 MB  00:00:05     
(3/11): MariaDB-10.0.14-centos7_0-x86_64-client.rpm                                                                                                                                                                                      | 9.9 MB  00:00:19     
(4/11): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm                                                                                                                                                                                   |  32 kB  00:00:00     
(5/11): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm                                                                                                                                                                                    |  57 kB  00:00:00     
(6/11): perl-Net-Daemon-0.48-5.el7.noarch.rpm                                                                                                                                                                                            |  51 kB  00:00:00     
(7/11): perl-IO-Compress-2.061-2.el7.noarch.rpm                                                                                                                                                                                          | 260 kB  00:00:00     
(8/11): perl-PlRPC-0.2020-14.el7.noarch.rpm                                                                                                                                                                                              |  36 kB  00:00:00     
(9/11): MariaDB-10.0.14-centos7_0-x86_64-shared.rpm                                                                                                                                                                                      | 1.2 MB  00:00:01     
(10/11): perl-DBI-1.627-4.el7.x86_64.rpm                                                                                                                                                                                                 | 802 kB  00:00:06     
(11/11): MariaDB-10.0.14-centos7_0-x86_64-server.rpm                                                                                                                                                                                     |  50 MB  00:00:29     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                                                           1.9 MB/s |  69 MB  00:00:36     
Running transaction check
Running transaction test


Transaction check error:
  file /etc/my.cnf from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/Index.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/armscii8.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/ascii.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/cp1250.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/cp1256.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/cp1257.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/cp850.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/cp852.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
  file /usr/share/mysql/charsets/cp866.xml from install of MariaDB-common-10.0.14-1.el7.centos.x86_64 conflicts with file from package mariadb-libs-1:5.5.35-3.el7.x86_64
...
...
...
Error Summary
-------------

## 진행 중 트랜잭션 검사 오류가 나타납니다. 이 경우 아래 방법에 따라 해결하세요.
## mariadb-libs-1:5.5.35-3.el7.x86_64 패키지가 MariaDB 서버를 설치하는 동안에 충돌이 일어나기 때문이다.
## postfix 10 나중에 설치되어야 합니다. (mariadb-libs-1:5.5.35-3.el7.x86_64 우선적 설치)
[root@localhost ~]# yum remove postfix
Loaded plugins: fastestmirror, langpacks
Resolving Dependencies
--> Running transaction check
---> Package postfix.x86_64 2:2.10.1-6.el7 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================================================================================================
 Package                                                     Arch                                                       Version                                                             Repository                                                     Size
================================================================================================================================================================================================================================================================
Removing:
 postfix                                                     x86_64                                                     2:2.10.1-6.el7                                                      @anaconda                                                      12 M

Transaction Summary
================================================================================================================================================================================================================================================================
Remove  1 Package

Installed size: 12 M
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Erasing    : 2:postfix-2.10.1-6.el7.x86_64                                                                                                                                                                                                                1/1 
  Verifying  : 2:postfix-2.10.1-6.el7.x86_64                                                                                                                                                                                                                1/1 

Removed:
  postfix.x86_64 2:2.10.1-6.el7                                                                                                                                                                                                                                 

Complete!

[root@localhost ~]# rpm -ev mariadb-libs-5.5.35-3.el7.x86_64 
Preparing packages...
mariadb-libs-1:5.5.35-3.el7.x86_64


## 이제 MariaDB 설치를 다시 진행합니다.
[root@localhost ~]# yum install MariaDB-server MariaDB-client MariaDB-devel MariaDB-shared
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-client.x86_64 0:10.0.14-1.el7.centos will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.0.14-1.el7.centos.x86_64
---> Package MariaDB-devel.x86_64 0:10.0.14-1.el7.centos will be installed
---> Package MariaDB-server.x86_64 0:10.0.14-1.el7.centos will be installed
--> Processing Dependency: perl(DBI) for package: MariaDB-server-10.0.14-1.el7.centos.x86_64
---> Package MariaDB-shared.x86_64 0:10.0.14-1.el7.centos will be installed
...
... ## 중간 중간 Is this ok [y/N] 메시지 표시 -> y 선택
...
  Installing : MariaDB-devel-10.0.14-1.el7.centos.x86_64                                                                                                                                                                                                  10/11 
  Installing : MariaDB-shared-10.0.14-1.el7.centos.x86_64                                                                                                                                                                                                 11/11 
  Verifying  : MariaDB-devel-10.0.14-1.el7.centos.x86_64                                                                                                                                                                                                   1/11 
  Verifying  : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                                                                                                                                                                                                  2/11 
  Verifying  : perl-Net-Daemon-0.48-5.el7.noarch                                                                                                                                                                                                           3/11 
  Verifying  : MariaDB-common-10.0.14-1.el7.centos.x86_64                                                                                                                                                                                                  4/11 
  Verifying  : MariaDB-server-10.0.14-1.el7.centos.x86_64                                                                                                                                                                                                  5/11 
  Verifying  : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                                                                                                                                                                                                 6/11 
  Verifying  : MariaDB-shared-10.0.14-1.el7.centos.x86_64                                                                                                                                                                                                  7/11 
  Verifying  : perl-DBI-1.627-4.el7.x86_64                                                                                                                                                                                                                 8/11 
  Verifying  : MariaDB-client-10.0.14-1.el7.centos.x86_64                                                                                                                                                                                                  9/11 
  Verifying  : perl-PlRPC-0.2020-14.el7.noarch                                                                                                                                                                                                            10/11 
  Verifying  : perl-IO-Compress-2.061-2.el7.noarch                                                                                                                                                                                                        11/11 

Installed:
  MariaDB-client.x86_64 0:10.0.14-1.el7.centos                    MariaDB-devel.x86_64 0:10.0.14-1.el7.centos                    MariaDB-server.x86_64 0:10.0.14-1.el7.centos                    MariaDB-shared.x86_64 0:10.0.14-1.el7.centos                   

Dependency Installed:
  MariaDB-common.x86_64 0:10.0.14-1.el7.centos   perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7   perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7   perl-DBI.x86_64 0:1.627-4.el7   perl-IO-Compress.noarch 0:2.061-2.el7   perl-Net-Daemon.noarch 0:0.48-5.el7  
  perl-PlRPC.noarch 0:0.2020-14.el7             

Complete!

## MariaDB 설치가 잘 되었는지 rpm 관련 패키지를 rpm 명령어로 찾을 수 있습니다.
[root@localhost ~]# rpm -qa|grep -i maria
MariaDB-common-10.0.14-1.el7.centos.x86_64
MariaDB-server-10.0.14-1.el7.centos.x86_64
MariaDB-client-10.0.14-1.el7.centos.x86_64
MariaDB-devel-10.0.14-1.el7.centos.x86_64
MariaDB-shared-10.0.14-1.el7.centos.x86_64

## 이제 다시 postfix를 설치합니다. 
[root@localhost ~]# yum install postfix
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 postfix.x86_64 2:2.10.1-6.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================================================================================================
 Package                                                      Arch                                                        Version                                                               Repository                                                 Size
================================================================================================================================================================================================================================================================
Installing:
 postfix                                                      x86_64                                                      2:2.10.1-6.el7                                                        base                                                      2.4 M

Transaction Summary
================================================================================================================================================================================================================================================================
Install  1 Package

Total download size: 2.4 M
Installed size: 12 M
Is this ok [y/d/N]: y
Downloading packages:
postfix-2.10.1-6.el7.x86_64.rpm                                                                                                                                                                                                          | 2.4 MB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : 2:postfix-2.10.1-6.el7.x86_64                                                                                                                                                                                                                1/1 
  Verifying  : 2:postfix-2.10.1-6.el7.x86_64                                                                                                                                                                                                                1/1 

Installed:
  postfix.x86_64 2:2.10.1-6.el7                                                                                                                                                                                                                                 

Complete!

MariaDB root's password reset

보안 이슈로 MariaDB의 루트 암호를 재 설정할 것을 권고합니다.

암호 변경 방법은 mysql_secure_installation, mysqladmin, login into MariaDB console 세가지 방법이 있습니다. 현재는 mysql_secure_installation으로 설정하겠습니다.

## 우선 MariaDB 서비스를 실행합니다. (Cent OS 7 명령어는 매뉴얼을 참조하세요.)
[root@localhost ~]# systemctl start mysql.service
[root@localhost ~]# ps -ef | grep mysql
root     64845     1  0 01:11 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/master.example.com.pid
mysql    64919 64845  8 01:11 ?        00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/master.example.com.err --pid-file=/var/lib/mysql/master.example.com.pid
root     64948 63777  0 01:11 pts/1    00:00:00 grep --color=auto mysql

## mysql_secure_installation를 실행합니다.
[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!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): ## Enter
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] ## y
New password:  ## Password 입력
Re-enter new password:  ## Password 재 입력
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] ## y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] ## y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] ## y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] ## y
 ... 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!


저작자 표시 비영리 동일 조건 변경 허락
신고

[MariaDB] Session Kill





Session Kill

운영을 하다가 Hang에 걸린 Session이나 불 특정 불 필요 Session 들이 존재하게 됩니다.
이럴 경우, 현재 실행중인 Process를 강제적으로 종료함으로써 해당 작업을 정지 시킬 수 있습니다.


-- 현재 실행중인 process 목록을 나타냅니다.
MariaDB [orademo]> show processlist;
+----+--------+--------------------+---------+---------+------+-------+------------------+----------+
| Id | User   | Host               | db      | Command | Time | State | Info             | Progress |
+----+--------+--------------------+---------+---------+------+-------+------------------+----------+
| 14 | root   | localhost          | orademo | Query   |    0 | NULL  | show processlist |    0.000 |
| 15 | ecsees | 192.168.11.1:50612 | orademo | Sleep   |    2 |       | NULL             |    0.000 |
| 16 | ecsees | 192.168.11.1:50613 | orademo | Sleep   |    2 |       | NULL             |    0.000 |
+----+--------+--------------------+---------+---------+------+-------+------------------+----------+
3 rows in set (0.00 sec)

-- 해당 id를 파라미터로 입력하여 세션을 종료합니다.

MariaDB [orademo]> kill "15";
Query OK, 0 rows affected (0.02 sec)

MariaDB [orademo]> kill "16";
Query OK, 0 rows affected (0.00 sec)

-- process 목록으로 kill session 을 확인합니다.
MariaDB [orademo]> show processlist;
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| Id | User | Host      | db      | Command | Time | State | Info             | Progress |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
| 14 | root | localhost | orademo | Query   |    0 | NULL  | show processlist |    0.000 |
+----+------+-----------+---------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)


저작자 표시 비영리 동일 조건 변경 허락
신고

'MariaDB Admin Guide > Admin How To' 카테고리의 다른 글

[MariaDB] Session Kill  (0) 2014.06.11
[MariaDB] About Binary Log  (0) 2014.06.05
[MariaDB] 데이터베이스 생성 / 권한 부여 / 접속  (0) 2014.05.26

[MariaDB] About Binary Log





Binary Log

Binary Log는 각 명령문에 걸린 시간, 데이터베이스에 변경 데이터, 구조 등의 모든 기록이 포함됩니다.
Binary Log는 바이너리 로그 파일과 인덱스의 집합으로 구성되어 있습니다. 
여기에는 ALTER, INSERT, UPDATE를 만들고 삭제하는 등의 문을 기록하지만, 이러한 것들이 데이터에 영향을 주지는 않습니다.
Binary Log의 목적은 데이터가 바이너리 로그의 내용을 기초로 하나 이상의 슬레이브 서버에 하나 이상의 마스터로부터 송신되는 것을 복제 뿐만 아니라 백업하는 용도입니다.
Binary Log는 Binary에 저장되어 일반 텍스트 편집기로 볼 수 없습니다. 
MariaDB에는 바이너리 로그 파일을 처리하기 위한 mysqlbinlog 유틸리티가 존재합니다.

Binary Log를 쌓아도 되지 않는 환경에서는 Binary Log를 삭제하므로써 디스크 공간의 여유를 확보할 수 있습니다.


Binary Log 삭제

1) Replication 환경에서의 삭제
 - Master Server
-- mysql -u root -p를 통하여 접속 후
MariaDB [(none)]>  RESET MASTER;
 - Slave Server
-- mysql -u root -p를 통하여 접속 후
MariaDB [(none)]>  RESET MASTER;
2) Binary Log Sequence Number 또는 특정 일자로 삭제
-- mysql -u root -p를 통하여 접속 후
MariaDB [(none)]> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000017 | 1073744893 |
| mysql-bin.000018 |  770822303 |
+------------------+------------+
2 rows in set (0.08 sec)

MariaDB [(none)]> purge master logs to 'mysql-bin.000017';
Query OK, 0 rows affected (0.18 sec)

-- 특정 일자로 삭제
MariaDB [(none)]> purge master logs before '2014-06-05 00:00:00';
Query OK, 0 rows affected (0.06 sec)

-- 또는

MariaDB [(none)]> purge master logs before data_sub(CURRENT_DATE. INTERVAL 31 DAY);
Query OK, 0 rows affected (0.06 sec)
3) mysqladmin flush-logs 명령어를 사용하여 삭제

[root@sys4u ~]# /mysqladmin -u root -p flush-logs


Binary Log 주기 설정 및 설정 방지

1) /etc/my.cnf 파일 수정

/etc/my.cnf 파일을 수정합니다.
binlog_cache_size      =  1M         # binlog cache 사이즈 
max_binlog_size        =  500M       # bin로그 maximum 사이즈 
expire_logs_days        = 3            # 로그 만료기간

2) 콘솔에서 삭제

-- mysql -u root -p를 통하여 접속 후 expire_logs_days 항목을 확인
MariaDB [(none)]> SHOW VARIABLES LIKE '%expire%';

-- 보관주기 설정
MariaDB [(none)]> set global expire_logs_days=3;

3) /etc/my.cnf 파일을 수정하여 Binary Log 설정 방지

/etc/my.cnf 파일에 log-bin 라인을 주석 처리합니다.

# config server and data path
basedir=/usr/local/mysql
datadir=/home/data/mariadb-data
tmpdir=/home/data/mariadb-tmp
# 주석처리 log_bin=/home/data/mariadb-binlog/mysql-bin
relay_log=/home/data/mariadb-binlog/mysql-relay
innodb_data_home_dir=/home/data/mariadb-data
innodb_log_group_home_dir=/home/data/mariadb-iblog


Binary Log를 이용하여 쿼리문 내역 추출

위에서 거론된 mysqlbinlog를 이용하여 특정 시간대 수행한 쿼리문 내역을 추출할 수 있습니다.

# 전체 데이터베이스의 쿼리문 추출
/usr/local/mysql/bin/mysqlbinlog --start-datetime="2014-06-01 09:00:00" --stop-datetime='2014-06-03 09:59:59' /usr/local/mysql/var/mysql-bin.000017 > /home/data/mariadb-binlog/database_all.sql

# 특정 데이터베이스의 쿼리문 추출
/usr/local/mysql/bin/mysqlbinlog --start-datetime="2014-06-01 09:00:00" --stop-datetime='2014-06-03 09:59:59' --database="orademo" /usr/local/mysql/var/mysql-bin.000017 > /home/data/mariadb-binlog/orademo.sql


저작자 표시 비영리 동일 조건 변경 허락
신고

'MariaDB Admin Guide > Admin How To' 카테고리의 다른 글

[MariaDB] Session Kill  (0) 2014.06.11
[MariaDB] About Binary Log  (0) 2014.06.05
[MariaDB] 데이터베이스 생성 / 권한 부여 / 접속  (0) 2014.05.26





Create Maria Database

아래와 같은 방법으로 데이터베이스를 생성합니다.
[root@sys4u ~]# /usr/local/mysql/bin/mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.30-MariaDB-log Source distribution

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

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

MariaDB [(none)]> use mysql
Database changed

MariaDB [mysql]> create database orademo;
Query OK, 1 row affected (0.00 sec)

-- User를 생성하고 권한을 부여합니다.
MariaDB [orademo]> create user 'ecsees'@'localhost' identified by 'xxxxx';
Query OK, 0 rows affected (0.00 sec)

-- 혹시라도 외부에서 접속하려면 localhost가 아닌 '%'로 추가 생성하여야 합니다.
MariaDB [orademo]> grant all privileges on orademo.* to ecsees@localhost;
Query OK, 0 rows affected (0.00 sec)

-- 확인합니다.
MariaDB [orademo]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [orademo]> exit
Bye

[root@sys4u ~]# /usr/local/mysql/bin/mysql -u ecsees -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.30-MariaDB-log Source distribution

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| orademo            |
| test               |
+--------------------+
3 rows in set (0.01 sec)


Maria DB Connection

데이터베이스에 접속하기 위하여 다음의 명령을 입력하여 접속합니다.

mysql -u username -p -h host databasename 
username : 접속하고자 하는 데이터베이스 사용자명으로 변경 
host : 데이터베이스 서버를 위한 URI로 변경 (데이터베이스가 로컬 호스트에 존재하는 경우 'localhost'를 입력하면 됩니다.) 

databasename : 접속하고자 하는 데이터베이스명으로 변경 (암호를 물어 보는 경우 암호를 입력하면 됩니다.)


-h host 옵션을 생략하는 경우 로컬 호스트를 의미합니다.

-u username 옵션을 생략하는 경우 현재 서버에 로그인한 사용자 이름을 의미합니다.

[root@sys4u ~]# /usr/local/mysql/bin/mysql -u ecsees -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.30-MariaDB-log Source distribution

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| orademo            |
| test               |
+--------------------+
3 rows in set (0.01 sec)


Maria DB Authorization

아래와 같은 방법으로 해당 유저에 권한을 부여합니다.

MariaDB [mysql]> select host, user, password from user;
+-------------+------+----------+
| host        | user | password |
+-------------+------+----------+
| localhost   | root |          |
| sys4u.co.kr | root |          |
| 127.0.0.1   | root |          |
| ::1         | root |          |
| localhost   |      |          |
| sys4u.co.kr |      |          |
+-------------+------+----------+
6 rows in set (0.00 sec)

MariaDB [mysql]> grant all privileges on *.* to 'root'@'%' identified by 'xxxx';
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> select host, user, password from user;
+-------------+------+-------------------------------------------+
| host        | user | password                                  |
+-------------+------+-------------------------------------------+
| localhost   | root |                                           |
| sys4u.co.kr | root |                                           |
| 127.0.0.1   | root |                                           |
| ::1         | root |                                           |
| localhost   |      |                                           |
| sys4u.co.kr |      |                                           |
| %           | root | *7CFE0F9598C6E65BA068ADFDA6162087820AEAD7 |
+-------------+------+-------------------------------------------+
7 rows in set (0.00 sec)

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


저작자 표시 비영리 동일 조건 변경 허락
신고

'MariaDB Admin Guide > Admin How To' 카테고리의 다른 글

[MariaDB] Session Kill  (0) 2014.06.11
[MariaDB] About Binary Log  (0) 2014.06.05
[MariaDB] 데이터베이스 생성 / 권한 부여 / 접속  (0) 2014.05.26