[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.4.10 database server
[root@localhost ~]# firewall-cmd --permanent --add-service=mysql
success
[root@localhost ~]# firewall-cmd --reload
success
firewall-cmd 명령어는 여기를 참고하면 된다.
# DB 관리자의 이름을 root로 설정하고 비밀번호는 1234로 설정했다.
[root@localhost ~]# mysqladmin -u root password '1234'
# 외부에서 접속하기 위해 아래의 명령어를 입력 후 비밀번호를 지정한다.
[root@localhost ~]# mysql -h localhost -u root -p
Enter password:
C:\Windows\system32>cd "c:\Program Files\MariaDB 10.4\bin"
c:\Program Files\MariaDB 10.4\bin>mysql
ERROR 2002 (HY000): Can't connect to MySQL server on 'localhost' (10061)
c:\Program Files\MariaDB 10.4\bin>mysql -h 192.168.111.100 -u root -p
Enter password: ****
ERROR 1130 (HY000): Host '192.168.111.128' is not allowed to connect to this MariaDB server
결론적으로 두번 다 접속이 거부된 것을 확인할 수 있었는데, MariaDB 서버로 접속하는 MariaDB 클라이언트 명령은
mysql -h 서버호스트이름또는IP주소 -u root -p
명령을 실행하여 접속해야한다.
그러나 위의 접속 시도에 사용한 명령어인
mysql -h 192.168.111.100 -u root -p
는 WinClient에서 입력한 root 사용자 이름이 root@WinClientIP 주소로 root@192.168.111.128인 것이다.
MariaDB 서버에 접속이 허용된 DB 사용자는root@localhost 또는 root@127.0.0.1
이므로 192.168.111.128의 IP를 가진 WinClient에서는 접속이 거부된 것이다.
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.4.10-MariaDB MariaDB Server
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)]>
# 추가 전 상태 조회
MariaDB [none]> USE mysql
MariaDB [mysql]> SELECT user, host FROM user WHERE user NOT LIKE '';
# 출력 결과
+-------+-----------+
| User | Host |
+-------+-----------+
| mysql | localhost |
| root | localhost |
+-------+-----------+
# 192.168.111로 시작하는 모든 IP가 접속 가능하도록 추가
MariaDB [mysql]> GRANT ALL ON *.* TO winuser@'192.168.111.%' IDENTIFIED BY '4321';
MariaDB [mysql]> SELECT user, host FROM user WHERE user NOT LIKE '';
# 출력 결과
+---------+---------------+
| User | Host |
+---------+---------------+
| winuser | 192.168.111.% |
| mysql | localhost |
| root | localhost |
+---------+---------------+
c:\Program Files\MariaDB 10.4\bin>mysql -h 192.168.111.100 -u winuser -p
Enter password: ****
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.4.10-MariaDB MariaDB Server
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)]>
Server의 mysql에서 winuser에 대한 사용자를 추가한 후 WinClient에서 mysql에 접속을 했을 때 비로소 접속이 가능한 것을 확인할 수 있다.
MariaDB [(none)]> create database school_db;
MariaDB [(none)]> show databases;
# 출력 결과
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school_db |
| test |
+--------------------+
MariaDB [(none)]> use school_db;
Database changed
MariaDB [school_db]> create table student (name VARCHAR(40) NOT NULL PRIMARY KEY, grade INT, class INT, phone CHAR(11));
MariaDB [school_db]> create table teacher (no INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40), subject VARCHAR(40), grade INT, class INT);
# 테이블 구조 확인
MariaDB [school_db]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(40) | NO | PRI | NULL | |
| grade | int(11) | YES | | NULL | |
| class | int(11) | YES | | NULL | |
| phone | char(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.001 sec)
MariaDB [school_db]> desc teacher;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| no | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(40) | YES | | NULL | |
| subject | varchar(40) | YES | | NULL | |
| grade | int(11) | YES | | NULL | |
| class | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)
레코드 추가는 그냥 끌리는대로 했다.
MariaDB [school_db]> insert into student values ('kimsangwoo', 3, 1, '01011112222');
Query OK, 1 row affected (0.006 sec)
MariaDB [school_db]> insert into student values ('kiminsoo', 3, 2, '01012345678');
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> insert into student values ('honggildong', 3, 3, '01010100101');
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> insert into student values ('horangyee', 3, 4, '01011001100');
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> insert into teacher values (null, 'hakseangjuim', 'art', 3, 2);
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> insert into teacher values (null, 'gyojang', 'mathematic', null, null);
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> insert into teacher values (null, 'bunpiljangin', 'chemical', 3, 1);
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> insert into teacher values (null, 'TMI', 'english', 3, 3);
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> insert into teacher values (null, 'ingywang', 'physics', 3, 4);
Query OK, 1 row affected (0.002 sec)
MariaDB [school_db]> select * from student;
+-------------+-------+-------+-------------+
| name | grade | class | phone |
+-------------+-------+-------+-------------+
| honggildong | 3 | 3 | 01010100101 |
| horangyee | 3 | 4 | 01011001100 |
| kiminsoo | 3 | 2 | 01012345678 |
| kimsangwoo | 3 | 1 | 01011112222 |
+-------------+-------+-------+-------------+
4 rows in set (0.001 sec)
MariaDB [school_db]> select * from teacher;
+----+--------------+------------+-------+-------+
| no | name | subject | grade | class |
+----+--------------+------------+-------+-------+
| 1 | hakseangjuim | art | 3 | 2 |
| 2 | gyojang | mathematic | NULL | NULL |
| 3 | bunpiljangin | chemical | 3 | 1 |
| 4 | TMI | english | 3 | 3 |
| 5 | ingywang | physics | 3 | 4 |
+----+--------------+------------+-------+-------+
5 rows in set (0.001 sec)
MariaDB와 Visual Studio를 연동 후 ODBC를 설정하면 홈페이지를 통해서 도 데이터베이스 서버와 연결하여 데이터베이스를 조회할 수 있다.