Linux/CentOS
[CentOS 7] MariaDB 데이터베이스 서버 " 2 "
`작은거인`
2017. 8. 19. 16:41
이번 절에서는 MariaDB 서버가 제공하는 사용자 관리 방법을 설명한다.
5. MariaDB 사용자 관리
5.1 사용자 생성과 삭제
먼저 사용자를 생성, 변경, 삭제하는 방법을 살펴본다.
a. 사용자 생성
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | # CREATE USER를 사용해 패스워드와 함께 사용자 KIM을 생성한다. localhost는 접속할 때의 출발지 주소이고, 호스트 OR IP 사용할 수 있다. MariaDB [(none)]> CREATE USER 'kim'@'localhost' IDENTIFIED BY 'kim1234'; # 로컬 호스트에 접속하는 사용자 kim에게 GRANT를 사용해 모든 권한을 부여하는데, *.*는 모든 DB와 Table에 대한 권한을 부여한다. MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'kim'@'localhost' WITH GRANT OPTION; # localhost 대신 '%'를 사용했는데, 이는 사용자 kim이 모든 호스트에서의 접속이 가능하다는 의미이다. MariaDB [(none)]> CREATE USER 'kim'@'%' IDENTIFIED BY 'kim1234'; # localhost 대신 '%'를 사용해 모든 권한을 부여한다. WITH GRANT OPTION는 다른 사용자에게도 동일한 부여 가능하다는 의미다. MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'kim'@'%' WITH GRANT OPTION; # 이번에는 사용자 lee를 패스워드와 함께 생성한다. MariaDB [(none)]> CREATE USER 'lee'@'localhost' IDENTIFIED BY 'lee1234'; # 사용자 lee에게 권한을 부여하는데, 사용 가능한 명령어를 지정하고 데이터베이스도 sales 내의 모든 테이블만 사용 가능하게 제한한다. MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON sales.* TO 'lee'@'localhost'; # 사용자 park을 생성하는데, 이 사용자는 단지 호스트가 node1에서만 접속이 가능하다. MariaDB [(none)]> CREATE USER 'park'@'node1.chul.com' IDENTIFIED BY 'park1234'; # 사용자 park에게 권한을 부여하는데, 지정된 명령어와 데이터베이스 expenses 내의 모든 테이블만 사용 가능하게 제한한다. MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO 'park'@'node.chul.com'; # kim에 대한 권한을 확인하기 위해 사용한다. MariaDB [(none)]> SHOW GRANTS FOR 'kim'@'localhost'; +---------------------------------------------------------------------------------------------------------------------------------------+ | Grants for kim@localhost | +---------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'kim'@'localhost' IDENTIFIED BY PASSWORD '*576036912888CE03769B7D8214AB0D19C4C09B06' WITH GRANT OPTION | +---------------------------------------------------------------------------------------------------------------------------------------+ # 모든 사용자와 호스트 및 패스워드 정보를 데이터베이스 mysql과 user 테이블에서 확인한다. MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +------+----------------+-------------------------------------------+ | user | host | password | +------+----------------+-------------------------------------------+ | root | localhost | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | root | 127.0.0.1 | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | root | ::1 | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | kim | % | *576036912888CE03769B7D8214AB0D19C4C09B06 | | kim | localhost | *576036912888CE03769B7D8214AB0D19C4C09B06 | | lee | localhost | *29F14E987EB06F0EBC7EE553BD49D1F24DD962EC | | park | node1.chul.com | *BEF60B6665BA433A084B209B12A30B5BBF32EFC0 | | park | node.chul.com | | +------+----------------+-------------------------------------------+ | cs |
b. 사용자 변경과 삭제
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | # RENAME ~ TO를 사용하여 이름을 변경하는데, 기존 사용자가 삭제되는 것이 아니라 새로운 이름을 가진 사용자가 추가된다. MariaDB [(none)]> RENAME USER 'kim' TO 'kim2'@'localhost'; # mysql.user에서 확인하면 kim,kim2 동시에 저장된 것을 확인할 수 있다. MariaDB [(none)]> select user,host,password from mysql.user; +------+----------------+-------------------------------------------+ | user | host | password | +------+----------------+-------------------------------------------+ | root | localhost | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | root | 127.0.0.1 | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | root | ::1 | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | kim2 | localhost | *576036912888CE03769B7D8214AB0D19C4C09B06 | | kim | localhost | *576036912888CE03769B7D8214AB0D19C4C09B06 | | lee | localhost | *29F14E987EB06F0EBC7EE553BD49D1F24DD962EC | | park | node1.chul.com | *BEF60B6665BA433A084B209B12A30B5BBF32EFC0 | +------+----------------+-------------------------------------------+ # DROP USER를 사용해 사용자를 삭제할 수 있다. MariaDB [(none)]> DROP USER 'kim2'@'localhost'; MariaDB [(none)]> select user,host,password from mysql.user; +------+----------------+-------------------------------------------+ | user | host | password | +------+----------------+-------------------------------------------+ | root | localhost | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | root | 127.0.0.1 | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | root | ::1 | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | kim | localhost | *576036912888CE03769B7D8214AB0D19C4C09B06 | | lee | localhost | *29F14E987EB06F0EBC7EE553BD49D1F24DD962EC | | park | node1.chul.com | *BEF60B6665BA433A084B209B12A30B5BBF32EFC0 | +------+----------------+-------------------------------------------+ | cs |
5.2 패스워드 관리
a. 패스워드 설정과 변경
사용자 생성 시에 패스워드를 설정하거나 기존 패스워드를 새로운 패스워드로 변경하는 방법은 다음과 같다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | # 사용자 생성 시에 IDENTIFIED BY 다음에 사용자 패스워드를 설정한다. MariaDB [(none)]> CREATE USER 'kim2'@'localhost' IDENTIFIED BY 'kim1234'; # SET PASSWORD를 사용해 사용자 kim의 패스워드를 변경한다. MariaDB [(none)]> SET PASSWORD FOR 'kim2'@'localhost' = PASSWORD ('kim5678'); # 현재 사용자의 패스워드를 변경하기 위해 사용자의 이름을 확인한다. MariaDB [(none)]> SELECT current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ # 현재 사용자의 패스워드를 변경할 경우 사용자의 이름을 지정하지 않아도 변경이 가능하다. MariaDB [(none)]> SET PASSWORD = PASSWORD('mypass'); # 변경 후에 기존 패스워드로 접속하면 접근이 불가하다는 메시지가 출력된 것을 볼 수 있다. [root@master ~]# mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) # 명령어 UPDATE를 사용해 패스워드를 변경하는 방법이다. MariaDB [mysql]> UPDATE user SET password=PASSWORD("kim1234") WHERE user = 'kim2'; Rows matched: 1 Changed: 1 Warnings: 0 # 위의 내용들이 모두 적용되도록 하기 위해 테이블 정보를 다시 읽어 들인다. MariaDB [mysql]> FLUSH PRIVILEGES; # 사용자 kim2으로 서버 로그인을 해 성공한다. [root@master ~]# mysql -u kim -p Welcome to the MariaDB monitor. MariaDB [(none)]> | cs |
b. 패스워드 에이징 설정
패스워드 에이징(Aging)이란 사용자가 사용할 수 있는 패스워드의 기간을 설정하는 방법이다. 기본적으로 기간에 제한을 두지 않으며, 일정한 기간을 설정할 경우 에이징을 사용할 수 있다. 참고로 MariaDB-10.2버전부터는 SQL 명령어 ALTER USER를 사용해 패스워드 에이징을 지원하고 있다.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # MariaDB 서버 설정 파일을 vi로 열어서 default~lifetime을 180일로 설정하고 재시작한다. [root@master ~]# vi /etc/my.cnf.d/server.cnf [mysqld] default_password_lifetime=180 [root@master ~]# systemctl restart mariadb # ALTER USER를 사용해 패스워드 에이징을 시도했지만 버전이 5.5.2에서는 지원하지 않는다. MariaDB-10.2부터 지원 가능하다. MariaDB [(none)]> ALTER USER 'kim'@'localhost' PASSWORD EXPIRE DEFAULT; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'kim'@'localhost' PASSWORD EXPIRE DEFAULT' at line 1 MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1 | cs |
6. MariaDB 백업과 복구
6.1 논리적 백업과 물리적 백업
백업의 종류를 구분해보면 크게 논리적 백업과 물리적 백업으로 구분된다. 논리적 백업이란 CREATE DATABASE/TABLE처럼 데이터 복구 시에 SQL 구문이 필요한 경우를 말하고, 물리적 백업이란 데이터가 저장된 각각의 파일이나 디렉토리를 복사하는 경우를 의미한다.
논리적 백업 |
물리적 백업 |
유연성이 뛰어나기 때문에 다른 하드웨어나 DBMS 또는 다른 MariaDB 버전에서 사용 가능 |
유연성이 부족해서 다른 하드웨어나 다른 DBMS 또는 다른 MariaDB 버전에서 사용되지 않을 수 있다 |
백업은 데이터베이스와 테이블 수준에서 이뤄지는데, 데이터와 명령어를 포함한 텍스트 파일로 저장 |
백업이 디렉토리/파일 수준에서 이뤄지고, InnoDB스토리지 엔진의 경우 여러 테이블 정보가 하나의 파일에 저장되므로 테이블별 백업이 불가능하다. |
동일한 데이터를 백업하는 경우 물리적 백업보다 백업 데이터의 크기가 더 커지게 된다. |
크기가 논리적 백업보다 더 작다. |
백업과 복구에 소요되는 시간이 더 오래 걸린다. |
논리적 백업보다 더 적은 시간이 소요된다. |
로그 및 설정 파일은 논리적 백업에 포함되지 않음 |
로그 및 설정 파일을 포함시킨다. |
6.2 백업
세 가지의 클라이언트 툴을 사용해 논리적 백업과 물리적 백업 방법을 살펴보자.
a. mysqldump 이용
mysqldump는 논리적 백업을 수행하며, 비교적 데이터 크기가 작은 경우 백업과 복구를 위해 사용되는 매우 유연한 클라이언트 프로그램이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | # mysqldump에 백업할 데이터베이스 business를 지정하고 이를 business.sql파일로 저장한다. [root@master ~]# mysqldump -u root -p business > business.sql [root@master ~]# file business.sql business.sql: ASCII text, with very long lines # database 옵션을 사용하면 여러 개의 데이터베이스를 동시에 백업할 수 있다. [root@master ~]# mysqldump -u root -p --databases business helloworld > jeong.sql # 모든 데이터베이스를 백업할 경우 all-database 옵션을 사용한다. [root@master ~]# mysqldump -u root -p --all-databases > database.sql # 특정 테이블만 선택해서 백업도 가능하다. 데이터베이스 business 내의 테이블 두 개를 선택해 백업을 한다. [root@master ~]# mysqldump -u root -p business customers orders > table.sql [root@master ~]# file table.sql table.sql: ASCII text, with very long lines | cs |
b. xtrabackup 사용
XtraBackup은 데이터베이스가 실행 중이지만 성능에는 영향을 미치지 않는 핫백업(Hot-Backup)을 신속하게 수행하기 위해 사용되는 프로그램으로서 XtraDB나 InnoDB 데이터베이스 엔진을 위해 특별히 디자인됐다. 하지만 다른 스토리지 엔진을 위해서도 사용이 가능하며, 기본적으로 MariaDB 패키지에 포함되지 않아 별도 설치가 필요하다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | # xtrabackup 패키지는 기본적으로 포함되지 않아 ELEP 저장소를 설치해야 한다. [root@master ~]# yum install -y epel-release [root@master ~]# yum list --enablerepo=[epel] percona-xtrabackup Available Packages percona-xtrabackup.x86_64 2.3.6-1.el7 epel # yum을 이용해서 xtrabackup 패키지를 설치한다. [root@master ~]# yum install -y percona-xtrabackup # 백업 데이터를 저장할 디렉토리 명령어 mkdir로 생성한다. [root@master ~]# mkdir /backup # 데이터베이스가 저장된 디렉토리를 입력하고, 위에 생성한 디렉토리를 목적지로 설정해 백업을 진행한다. [root@master /]# xtrabackup --user=root --password=mypass --backup --datadir=/var/lib/mysql/ --target-dir=/backup/ 170814 09:52:27 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /var/lib/mysql/mysql.sock Using server version 5.5.52-MariaDB xtrabackup version 2.3.6 based on MySQL server 5.6.24 Linux (x86_64) (revision id: ) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql/ xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 5242880 170814 09:52:27 >> log scanned up to (1649057) xtrabackup: Generating a list of tablespaces 170814 09:52:27 [01] Copying ./ibdata1 to /backup/ibdata1 170814 09:52:27 [01] ...done 170814 09:52:28 >> log scanned up to (1649057) 170814 09:52:28 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 170814 09:52:28 Executing FLUSH TABLES WITH READ LOCK... 170814 09:52:28 Starting to backup non-InnoDB tables and files 170814 09:52:28 [01] Copying ./mysql/db.frm to /backup/mysql/db.frm 170814 09:52:28 [01] ...done ... xtrabackup: Stopping log copying thread. .170814 09:52:29 >> log scanned up to (1649057) 170814 09:52:29 Executing UNLOCK TABLES 170814 09:52:29 All tables unlocked 170814 09:52:29 Backup created in directory '/backup/' 170814 09:52:29 [00] Writing backup-my.cnf 170814 09:52:29 [00] ...done 170814 09:52:29 [00] Writing xtrabackup_info 170814 09:52:29 [00] ...done xtrabackup: Transaction log of lsn (1649057) to (1649057) was copied. 170814 09:52:29 completed OK! # 백업된 데이터를 사용해 복구를 할 경우 명령어 rsync를 사용해 목적지로 데이터를 이동한다. [root@master backup]# rsync -avrP /backup/ /var/lib/mysql/ # 소유권을 mysql로 변경한다. [root@master backup]# chown -R mysql:mysql /var/lib/mysql/ | cs |
c. mysqlhotcopy 사용
mysqlhotcopy는 물리적 백업을 수행하는 Perl 스크립트로서, 특히 MyISAM처럼 테이블이 여러 개의 파일로 저장되는 스토리지 엔진에 유용하다. 동일한 컴퓨터에서의 백업만 지원하고 원격 컴퓨터로의 백업은 지원하지 않는다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | # 백업 데이터를 저장할 디렉토리를 생성한다. [root@master business]# mkdir /backup/mariadb2 # 인증 정보를 입력하고 백업할 데이터베이스를 선택하며, 백업 데이터를 저장할 목적지 디렉토리를 지정한다. allow 옵션은 목적지 디렉토리에 # 동일한 이름을 가진 데이터가 있는 경우 그 이름에 확장자 old를 추가하라는 의미고, keeplold는 이전 파일을 삭제하지 말라는 옵션이다. [root@master mariadb2]# mysqlhotcopy -u root -p mypass business /backup/mariadb2/ --allowold --keepold Flushed 6 tables with read lock (`business`.`customers`, `business`.`orderitems`, `business`.`orders`, `business`. `productnotes`, `business`.`products`, `business`.`vendors`) in 0 seconds. Locked 0 views () in 0 seconds. Copying 19 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 6 tables (19 files) in 0 seconds (0 seconds overall). [root@master mariadb2]# ll total 4 drwxr-x---. 2 mysql mysql 4096 Aug 14 10:17 business [root@master mariadb2]# ls /backup/mariadb2/business/ customers.frm db.opt orderitems.MAI orders.MAI productnotes.MAI products.MAI vendors.MAI customers.MAD orderitems.frm orders.frm productnotes.frm products.frm vendors.frm customers.MAI orderitems.MAD orders.MAD productnotes.MAD products.MAD vendors.MAD # 백업된 데이터를 모두 확인할 수 있는데, 복구의 경우 이 디렉토리를 MariaDB 서버의 디렉토리로 복사만 하면 사용할 수 있다. [root@master mariadb2]# ll /backup/mariadb2/business/ -rw-rw----. 1 mysql mysql 8906 Aug 11 02:40 customers.frm -rw-rw----. 1 mysql mysql 16384 Aug 11 09:22 customers.MAD -rw-rw----. 1 mysql mysql 16384 Aug 11 09:22 customers.MAI -rw-rw----. 1 mysql mysql 65 Aug 11 02:15 db.opt -rw-rw----. 1 mysql mysql 8728 Aug 11 02:40 orderitems.frm -rw-rw----. 1 mysql mysql 16384 Aug 11 09:22 orderitems.MAD -rw-rw----. 1 mysql mysql 24576 Aug 11 09:22 orderitems.MAI -rw-rw----. 1 mysql mysql 8648 Aug 11 02:40 orders.frm -rw-rw----. 1 mysql mysql 16384 Aug 11 09:22 orders.MAD -rw-rw----. 1 mysql mysql 24576 Aug 11 09:22 orders.MAI | cs |
6.3 복구
백업한 데이터로부터 복구하는 방법은 다음과 같다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | # 백업했던 business.sql 파일을 사용하기 위해 SQL 명령어를 사용해 미리 복구할 데이터베이스를 생성한다. MariaDB [(none)]> CREATE DATABASE business_backup; MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | business | | business_backup | | helloworld | | mysql | | performance_schema | +--------------------+ # 인증 정보를 입력하고 데이터베이스 business_backup 백업 데이터를 입력하게 되면 모든 데이터가 복구된다. [root@master tmp]# mysql -u root -p business_backup < business.sql MariaDB [business_backup]> show tables; +---------------------------+ | Tables_in_business_backup | +---------------------------+ | customers | | orderitems | | orders | | productnotes | | products | | vendors | +---------------------------+ # 백업 데이터를 원격지의 데이터베이스로 바로 복구하는 경우 사용하는 방법이다. 원격지 호스트 node에서 미리 business를 생성해야 한다. [root@master tmp]# mysqldump -u root -pwoghks0652 business | mysql -u root -pwoghks0652 --host=node1.chul.com -C business | cs |
7. MariaDB 데이터 복제
MariaDB 복제(Replication)란 여러 시스템에서 동일한 데이터를 복사해서 사용하는 방법으로 데이터를 자동으로 복사해서 분배하는 시스템을 마스터 서버, 복사된 데이터를 받아 저장하는 시스템을 슬레이브 서버라고 한다. 한 데이터베이스가 작동이 중지되면 클라이언트는 다른 슬레이브 서버의 데이터에 접속할 수 있다. 또한 한 시스템에서 하드웨어 및 데이터베이스 문제로 인해 데이터를 분실할 경우를 대비하기 위해 복제 기능이 사용된다.
7.1 마스터 서버 설정(master)
a. 설정 파일 수정
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 설정 파일을 열어서 복제에 사용할 바이너리 로그 파일의 이름을 지정함으로써 이 기능의 사용을 활성화 한다. [root@master ~]# vi /etc/my.cnf [root@master ~]# cat /etc/my.cnf | egrep '(log-bin|server_id)' log-bin=mysql-bin (이 파일은 데이터 변경에 사용되는 모든 내용을 기록하며 백업과 복제에 사용되는 파일이다) server_id=1 (각 MariaDB 마스터 및 슬레이브 서버 구별을 위해 사용할 ID를 설정한다) # 위 변경 사항이 적용되도록 서버를 재시작 한다. [root@master ~]# systemctl restart mariadb # 지정한 복제 로그 파일이 MariaDB 데이터 디렉토리에 생성된 것을 확인할 수 있다. [root@master ~]# ll /var/lib/mysql/ | grep mysql-bin -rw-rw----. 1 mysql mysql 245 Aug 15 14:24 mysql-bin.000001 -rw-rw----. 1 mysql mysql 19 Aug 15 14:24 mysql-bin.index | cs |
참고로 위 설정에서는 기본적으로 모든 데이터베이스에 대한 복제를 허용하고 있는데, 특정 데이터베이스의 복제를 허용할 경우 replicate-do-db에 이름을 지정해 사용한다.
b. 데이터베이스 설정
복제 기능을 사용하기 위한 데이터베이스 계정을 생성하고 이 서버가 마스터 서버로 작동하는지 확인해야 한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 복제를 위해 슬레이브가 마스터로 접속할 때 필요한 계정 정보, 이름과 패스워드, 권한을 할당한다. MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'slave1234' -> WITH GRANT OPTION; MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +-------+----------------+-------------------------------------------+ | user | host | password | +-------+----------------+-------------------------------------------+ | root | localhost | *CDAB284871D9CA52F1205DAFD015E39F24EB447B | | slave | % | *DAE180E15782177EEC0753479C7060AC1198C2F5 | +-------+----------------+-------------------------------------------+ # 설정이 적용되도록 서버를 다시 읽어 들인다. MariaDB [(none)]> FLUSH PRIVILEGES; # 모든 데이터베이스 백업을 위해 모든 테이블에 잠금 기능을 설정해 테이블에 쓰기 기능을 임시로 차단한다. MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; # 마스터 서버가 로그 파일명과 그 위치를 알기 위해 사용한다. 슬레이브는 나중에 이 정보를 사용해 복제 과정을 진행한다. MariaDB [(none)]> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 245 | | | +------------------+----------+--------------+------------------+ | cs |
c. 백업 데이터 생성
마스터에서 마지막 설정 단계로, 마스터 서버가 가진 모든 데이터베이스를 백업해서 슬레이브 호스트로 전달해야 한다.
1 2 3 4 5 6 7 8 9 10 11 | # 데이터베이스의 테이블 잠금 기능 옵션과 함께 모든 데이터베이스를 mysql_dump.sql파일로 백업한다. [root@master ~]# mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql # 데이터베이스 백업을 모두 마쳤으므로 테이블에 대한 쓰기 차단 기능을 해제한다. MariaDB [(none)]> UNLOCK TABLES; # scp 명령어를 사용해 백업 파일을 슬레이브 호스트 node1으로 복사한다. [root@master ~]# sudo scp mysql_dump.sql 192.168.80.6:/tmp/ root@192.168.80.6's password: mysql_dump.sql 100% 528KB 528.2KB/s 00:00 | cs |
9.7 슬레이브 호스트 설정
a. 설정 파일 수정
먼저 슬레이브 호스트도 마스터 서버처럼 설정 파일을 수정해야 한다.
1 2 3 4 5 6 7 8 9 10 | # vi로 my.cnf 설정 파일을 열어서 아래 설정을 추가한다. [root@node1 ~]# cat /etc/my.cnf | grep -v "^#" | grep -v "^ *$" [mysqld] log-bin=mysql-bin (마스터와 동일하게 바이너리 로그파일 생성에 대한 기능 활성화한다) server_id=2 (슬레이브 서버가 사용할 서버 ID번호다. 슬레이브 서버가 여러대인 경우 각각 구별해야한다) read_only=1 (슬레이브 서버가 업데이트 정보를 마스터 서버에서만 받고 다른 클라이언트로부터는 받지 않는다) report-host=node1.chul.com (슬레이브 서버의 호스트 이름을 지정한다) # 변경 사항이 적용되도록 MariaDB를 재시작 한다. [root@node1 ~]# systemctl restart mariadb | cs |
b. 데이터베이스 수정
마지막 단계로 슬레이브 서버의 데이터베이스로 접속해서 마스터 서버에 대한 정보를 추가해줘야 한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | # 마스터 서버에서 복사한 백업 파일을 슬레이브 데이터베이스 서버로 복구한다. [root@node1 ~]# mysql -u root -p < /tmp/mysql_dump.sql # MariaDB 서버로 접속한 다음 슬레이브 서버가 마스터 서버 연결에 필요한 파라미터 값을 변경 [root@node1 ~]# mysql -u root -p MariaDB [(none)]> change master to -> master_host='192.168.80.5', (마스터 서버 IP주소) -> master_user='slave', (복제에 필요한 사용자 이름) -> master_password='slave1234', (패스워드) -> master_log_file='mysql-bin.000002', (슬레이브 서버가 마스터 서버로부터 가져올 데이터에 대한 좌표 정보) -> master_log_pos=245; (마스터 서버에서 SHOW MASTER STATUS에서 확인했던 정보로 포지션 지정) # 슬레이브 서버로서의 역할을 시작한다. MariaDB [(none)]> START SLAVE; # 상태를 확인해 위의 설정한 정보가 맞는지 확인한다. MariaDB [(none)]> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.80.5 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000003 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 245 Relay_Log_Space: 1180 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 | cs |
7.3 복제 테스트
마스터 서버에서 데이터를 입력하거나 변경한 경우 변경된 정보가 슬레이브 서버에 바로 전달돼 저장되는지 테스트하겠다.
a. 마스터 테스트 설정 (master)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | # 사용할 데이터베이스를 선택한다. MariaDB [(none)]> USE helloworld; # INSERT 구문을 사용해 기존 customers 테이블에 새로운 로우를 입력한다. MariaDB [helloworld]> INSERT INTO customers -> VALUES (1010,"Hamin Cho","Jeju","hamin@gamil.com",123461); MariaDB [helloworld]> SELECT * FROM customers; +---------+-----------+--------------+-----------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+-----------------+----------+ | 1010 | Hamin Cho | Jeju | hamin@gamil.com | 123461 | +---------+-----------+--------------+-----------------+----------+ # 기존 customers 테이블을 이용해 새 테이블 customers5를 생성한다. MariaDB [helloworld]> CREATE TABLE customers6 SELECT * FROM customers; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 | cs |
b. 슬레이브 테스트 결과 확인 (node1)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | # 확인할 데이터베이스를 선택한다. MariaDB [(none)]> USE helloworld; Database changed # customers 테이블에서 cust_id=1010을 검색하면 마스터 서버에서 입력한 정보와 동일한 데이터를 확인할 수 있다. MariaDB [helloworld]> SELECT * FROM customers WHERE cust_id=1010; +---------+-----------+--------------+-----------------+----------+ | cust_id | cust_name | cust_address | cust_email | cust_zip | +---------+-----------+--------------+-----------------+----------+ | 1010 | Hamin Cho | Jeju | hamin@gamil.com | 123461 | +---------+-----------+--------------+-----------------+----------+ # 새로운 customers6 테이블을 확인할 수 있다. MariaDB [helloworld]> SHOW TABLES; +----------------------+ | Tables_in_helloworld | +----------------------+ | customers | | customers1 | | customers2 | | customers3 | | customers4 | | customers5 | | customers6 | +----------------------+ | cs |
8. MariaDB 보안: SSL 적용
MariaDB 서버와 이를 사용하는 클라이언트 간의 연결을 암호화할 경우 적용하는 프로토콜이 SSL이다. 데이터베이스 서버와 클라이언트를 서로 다른 호스트에서 사용하는 경우 안전한 연결을 위해 SSL을 적용할 수 있다. 테스트를 위해 서버 설정은 MariaDB 서버로서 master 호스트를 사용하고, 테스트는 클라이언트로 사용되는 node1에서 진행한다.
8.1 MariaDB 서버 설정(master)
master호스트에서 SSL을 사용하기 위한 인증서를 생성하는 방법과 이를 설정 파일에 추가하는 방법을 살펴보자.
a. MariaDB 키와 인증서 생성
첫 번째로 키와 인증서를 생성하는 방법을 알아본다. 키와 인증서 생성을 위해 기본적으로 디렉토리 /etc/pki/tls/certs를 사용한다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | [root@master ~]# cd /etc/pki/tls/certs/ # openssl을 사용해 CA(Certificat Authority)가 사용할 4096비트 크기의 개인 키를 생성한다. [root@master certs]# openssl genrsa -out master-ca.key 4096 Generating RSA private key, 4096 bit long modulus .........................................................++ e is 65537 (0x10001) # 위에서 생성한 개인 키를 사용해 CA가 사용하는 인증서를 생성한다. # 인증서에는 CA의 공개 키와 그 정보가 포함되는데, 이 CA가 사용할 인증서를 통해 다른 인증서에 대한 서명을 추가할 수 있다. [root@master certs]# openssl req -x509 -new -nodes -days 365 -key master-ca.key -out master-ca.pem You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:KR State or Province Name (full name) []:Seoul Locality Name (eg, city) [Default City]:Seoul Organization Name (eg, company) [Default Company Ltd]:Chul Com Organizational Unit Name (eg, section) []:Education Common Name (eg, your name or your server''s hostname) []:chul.com Email Address []:sepiros62@naver.com # 이 서버에서 사용할 개인 키를 동일하게 명령어 openssl을 사용해 생성한다. [root@master certs]# openssl genrsa -out database-server.key 4096 Generating RSA private key, 4096 bit long modulus ...........................................................................................++ e is 65537 (0x10001) # 위에서 생성한 개인 키를 사용해 CA로부터 서명 받을 인증서를 생성하기 위해 서버의 정보가 포함된 CSR 파일을 생성한다. [root@master certs]# openssl req -new -key database-server.key -out database-server.csr ----- Country Name (2 letter code) [XX]:KR State or Province Name (full name) []:Seoul Locality Name (eg, city) [Default City]:Seoul Organization Name (eg, company) [Default Company Ltd]:Chul Com Organizational Unit Name (eg, section) []:Education Common Name (eg, your name or your server''s hostname) []:master.chul.com Email Address []:sepiros62@naver.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: # 인증서는 위에서 생성한 CSR 파일에 대해 CA의 개인 키와 인증서를 사용해 서명을 하게 된다. # 인증서 내부에는 서버의 공개 키와 CA의 공개키 정보, 그리고 위에서 작성한 서버의 정보가 포함돼 있다. [root@master certs]# openssl x509 -req -set_serial 01 -days 365 -CA master-ca.pem -CAkey master-ca.key -in database-server.csr -out database-server.pem Signature ok subject=/C=KR/ST=Seoul/L=Seoul/O=Jeong Com/OU=Education/CN=master.chul.com/emailAddress=sepiros62@naver.com Getting CA Private Key | cs |
b. SSL 설정과 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | # 설정 파일을 열어서 mysqld 섹션에 SSL 관련 설정을 추가한다. [root@master certs]# cat /etc/my.cnf | grep ssl ssl-ca = /etc/pki/tls/certs/master-ca.pem (CA가 사용하는 인증서가 위치한 경로) ssl-key = /etc/pki/tls/certs/database-server.key (이 서버가 사용하는 개인 키의 경로) ssl-cert = /etc/pki/tls/certs/database-server.pem (이 서버가 CA로 서명 받은 인증서의 경로를 지정) # SSL을 적용할 사용자를 생성하는데, 마지막 옵션에 REQUIRE SSL을 통해 이 사용자는 SSL를 사용하게 정의한다. MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' -> IDENTIFIED BY 'ssluser' REQUIRE SSL; MariaDB [(none)]> FLUSH PRIVILEGES; # 변경 사항들이 적용되도록 서버를 재시작 한다. [root@master certs]# systemctl restart mariadb # SSL이 적용됐는지 확인하기 위해 서버에 접속해 변수에서 SSL값을 찾아보면 YES로 설정돼 있다. # 현재 MariaDB서버가 SSL을 통한 안전한 연결을 지원한다는 의미다. [root@master certs]# systemctl restart mariadb MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_ssl | YES | +---------------+-------+ # SSL로 연결된 세션 정보를 명령어 STATUS를 통해 확인할 수 있다. MariaDB [(none)]> SHOW STATUS LIKE 'SSL_session%'; +-----------------------------+--------+ | Variable_name | Value | +-----------------------------+--------+ | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | SERVER | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 128 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | +-----------------------------+--------+ | cs |
8.2 MariaDB 클라이언트 설정과 테스트 (node1)
이제 MariaDB 클라이언트로 사용되는 node1 호스트에서 SSL을 사용하기 위한 설정과 테스트를 살펴본다.
a. MariaDB 클라이언트 설정
먼저 키 및 인증서 생성, 그리고 클라이언트 설정에 대한 설명이다. 서버와 마찬가지로 사용하는 디렉토리는 동일하다. 그리고 키와 인증서를 생성하는 과정도 이름만 다를뿐 내용은 서버에서와 동일하다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | # openssl을 사용해 클라이언트에서 사용할 개인키를 생성한다. [root@node1 certs]# openssl genrsa -out database-client.key 4096 Generating RSA private key, 4096 bit long modulus .............................................................................................++ e is 65537 (0x10001) # 생성한 개인 키를 사용해 클라이언트의 정보가 포함된 CSR파일을 생성한다. [root@node1 certs]# openssl req -new -key database-client.key -out database-client.csr ----- Country Name (2 letter code) [XX]:KR State or Province Name (full name) []:Seoul Locality Name (eg, city) [Default City]:Seoul Organization Name (eg, company) [Default Company Ltd]:Chul Com Organizational Unit Name (eg, section) []:Education Common Name (eg, your name or your server''s hostname) []:node.chul.com Email Address []:sepiros62@naver.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: # 생성한 CSR 파일에 CA의 서명이 추가된 인증서를 생성하기 위해 개인 키 및 인ㅇ증서를 마스터 서버로부터 scp를 이용해 복사한다. [root@node1 certs]# scp 192.168.80.5:/etc/pki/tls/certs/master-ca.* . root@'192.168.80.5's password: master-ca.key 100% 3243 3.2KB/s 00:00 master-ca.pem # CA의 개인 키와 인증서를 사용해 서명이 추가된 클라이언트의 인증서를 생성한다. [root@node1 certs]# openssl x509 -req -set_serial 02 -days 365 -CA master-ca.pem -CAkey master-ca.key -in database-client.csr -out database-client.pem Signature ok subject=/C=KR/ST=Seoul/L=Seoul/O=Chul Com/OU=Education/CN=node.chul.com/emailAddress=sepiros62@naver.com Getting CA Private Key # vi로 설정 파일을 열어서 CA의 인증서 경로, 클라이언트가 사용할 개인 키 경로, 서명이 추가된 클라이언트의 인증서의 경로를 추가한다. [root@node1 certs]# cat /etc/my.cnf | grep -v '^ *$' | grep -v '^#' | grep ssl [mysql] ssl-ca = /etc/pki/tls/certs/master-ca.pem ssl-key = /etc/pki/tls/certs/database-client.key ssl-cert = /etc/pki/tls/certs/database-client.pem | cs |
b. SSL 적용 테스트
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | # 서버에서 SSL 옵션이 적용돼 생성된 사용자 ssluser로 로그인을 시도하면 성공한다. [root@node1 ~]# mysql -u ssluser -p -h 192.168.80.5 # 연결 후에 상태를 확인하면, SSL 연결을 위해 사용된 암호화 알고리즘을 확인할 수 있다. MariaDB [(none)]> STATUS -------------- mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 3 Current database: Current user: ssluser@node1.chul.com SSL: Cipher in use in DHE-RSA-AES256-GCM-SHA384 Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.5.52-MariaDB MariaDB Server Protocol version: 10 Connection: Master via TCP/IP ~ # 연결 시에 SSL을 사용하지 않으려면 skip-ssl 옵션을 추가하는데, 현재 사용자 ssluser의 경우 SSL을 사용하게 정의돼 있어 옵션이 적용되지 않는다. [root@node1 ~]# mysql -u ssluser -p -h 192.168.80.5 --skip-ssl Enter password: ERROR 1045 (28000): Access denied for user 'ssluser'@'node1.chul.com' (using password: YES) | cs |
9. MariaDB GUI 툴 사용
MariaDB 서버를 관리하기 위한 두 가지 GUI 프로그램 phpMyadmin과 Database Workbench를 소개한다. 지금까지는 MariaDB 서버를 관리하기 위해 커맨드라인만을 사용했는데, 여기서 소개할 GUI 프로그램을 통해 더 편리하게 데이터베이스 서버를 관리할 수 있다.
9.1 phpMyAdmin
phpMyAdmin은 웹 인터페이스 제공을 위해 MariaDB 및 MySQL 서버를 관리하기 위한 PHP 기반의 GUI 프로그램이다. phpMyAdmin은 기본적인 데이터베이스와 테이블 관리뿐 아니라 SQL구문을 사용한 직접적인 데이터 변경까지도 모두 지원한다. 또한 복제에 대한 설정도 phpMyAdmin을 이용해 모두 가능하다. 웹 기반에서 사용가능하기 때문에 웹 서버가 운영되고 있어야 한다.
a. 패키지 설치와 설정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | # yum을 이용해 패키지를 설치한다. [root@master ~]# yum install phpMyAdmin -y # 설치 된 패키지 버전 정보를 확인한다. [root@master ~]# rpm -qa | grep phpMyAdmin phpMyAdmin-4.4.15.10-2.el7.noarch # phpMyAdmin이 사용하는 설정파일을 열어서 접속을 허용할 네트워크 정보를 설정한다. [root@master ~]# vim /etc/httpd/conf.d/phpMyAdmin.conf <IfModule mod_authz_core.c> # Apache 2.4 <RequireAny> Require ip 127.0.0.1 Require ip 192.168.80.0/24 # Require all granted (모든 네트워크에서 접근을 허용할 경우 사용) </RequireAny> # 변경된 정보 적용을 위해 웹 서버를 재시작 한다. [root@master ~]# systemctl restart httpd | cs |
b. phpMyAdmin 접속
브라우저에서 phpMyAdmin으로 접속해 데이터베이스 서버를 사용하는 방법을 살펴보자.
1. 접속하기
브라우저에서 웹 서버의 IP 주소나 도메인 정보와 phpMyAdmin 디렉토리로 접속을 시도하면 인증 정보를 입력해야 하는데, 여기서의 인증 정보는 MariaDB 서버에 등록된 사용자 이름과 패스워드를 의미한다.
2. 로그인 성공하기
인증 정보를 사용해 로그인해 성공하면 왼편 메뉴에서 지금까지 사용한 데이터베이스를 모두 확인할 수 있다.
3. 데이터베이스 사용하기
왼쪽 메뉴에서 데이터베이스와 위의 메뉴에서 SQL을 선택하고 SELECT 구문을 사용해 customers 테이블에서 정보를 검색하고 그 결과를 확인할 수 있다.
9.2 Database Workbench
Database Workbench는 데이터베이스 디자인과 개발, 그리고 관리 및 테스트를 위해 MariaDB와 MySQL을 비롯한 다양한 RDBMS를 지원하는 윈도우 기반의 프로그램이다. 웹사이트 www.upscene.com에서 무료로 트라이얼 버전의 다운로드가 가능하고 상용 버전도 판매하고 있다.
a. Workbench 실행
다운로드한 트라이얼 버전을 설치하고 실행하면 다음 화면을 볼 수 있다. 먼저 데이터베이스 서버로의 연결을 위해 중앙의 Register Server를 클릭한다. 연결할 서버 타입으로 MariaDB를 선택하고 Next를 누른다
b. 서버 연결
서버 연결을 위해 정보를 왼쪽처럼 입력하고, 성공하면 현재 사용 중인 데이터베이스 목록을 확인할 수 있다. 현재 서버 IP 주소는 192.168.80.5를 사용하고 있고, 접속 클라이언트는 192.168.80.x를 사용하는데 이럴 경우 서버에서 원격지의 접근을 허용하는 설정이 미리 돼 있어야 한다.
