o/s : rocky 8~9
Mariadb : 10.11
1. mariadb를 쉽게 설치하기 위해서 repo 파일을 구성
|
1 |
[root@rocky8-web3 ~]# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.11" |
2. 파일 확인
|
1 2 |
[root@rocky8-web3 ~]# ls -l /etc/yum.repos.d/mariadb.repo -rw-r--r--. 1 root root 781 Oct 17 00:54 /etc/yum.repos.d/mariadb.repo |
3. yum 명령어로 설치 진행
|
1 |
[root@rocky8-web3 ~]# yum install MariaDB-client MariaDB-connect-engine mariadb-devel mariadb-server |
4. server.cnf 파일 수정 [mysqld] 부분에 내용 추가
|
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 |
[root@rocky8-web3 ~]# vi /etc/my.cnf.d/server.cnf [mysqld] #dns query skip-name-resolve ### remote conncetion bind-address=0.0.0.0 #connection max_connections = 1000 max_connect_errors = 1000 wait_timeout= 60 ### character character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci skip-character-set-client-handshake symbolic-links=0 ##timestamp explicit_defaults_for_timestamp ### sql_mode sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
5. 서비스 시작 및 서비스 등록
|
1 2 3 4 |
[root@rocky8-web3 ~]# systemctl start mariadb [root@rocky8-web3 ~]# systemctl enable mariadb Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service. |
6. mysql 접속 후 root 패스워드 지정
|
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 |
[root@rocky8-web3 ~]# mysql -u root -p mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.11.9-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)]> \s -------------- mysql Ver 15.1 Distrib 10.11.9-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 3 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.11.9-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 37 sec ## root 패스워드 설정 MariaDB [mysql]> alter user root@localhost identified via mysql_native_password using password("qwer1234"); Query OK, 0 rows affected (0.034 sec) |
기타확인
|
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 |
### 언어확인 MariaDB [mysql]> SHOW VARIABLES LIKE 'character\_set\_%'; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | +--------------------------+---------+ 7 rows in set (0.003 sec) MariaDB [mysql]> SHOW VARIABLES LIKE 'character_set_server'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | character_set_server | utf8mb4 | +----------------------+---------+ 1 row in set (0.002 sec) ### 엔진확인 MariaDB [mysql]> SELECT engine, support FROM information_schema.engines; +--------------------+---------+ | engine | support | +--------------------+---------+ | CSV | YES | | MRG_MyISAM | YES | | MEMORY | YES | | Aria | YES | | MyISAM | YES | | CONNECT | YES | | SEQUENCE | YES | | InnoDB | DEFAULT | | PERFORMANCE_SCHEMA | YES | +--------------------+---------+ 9 rows in set (0.001 sec) MariaDB [mysql]> SHOW VARIABLES LIKE '%engine'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | | | enforce_storage_engine | | | storage_engine | InnoDB | +----------------------------+--------+ 4 rows in set (0.003 sec) |
7. 데이터베이스 생성 및 유저 권한 추가
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
MariaDB [mysql]> create database xinet; Query OK, 1 row affected (0.001 sec) MariaDB [mysql]> CREATE USER 'xinet'@'localhost' IDENTIFIED BY 'Qac13@1'; Query OK, 0 rows affected (0.031 sec) MariaDB [mysql]> GRANT ALL PRIVILEGES ON xinet.* TO 'xinet'@'localhost'; Query OK, 0 rows affected (0.025 sec) ### 외부에서 접속시 %사용 MariaDB [mysql]> CREATE USER 'xinet'@'%' IDENTIFIED BY 'Qac13@1'; Query OK, 0 rows affected (0.032 sec) MariaDB [mysql]> GRANT ALL PRIVILEGES ON xinet.* TO 'xinet'@'%'; Query OK, 0 rows affected (0.038 sec) MariaDB [mysql]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec) |
8. 사용자로 데이터베이스 접속 후 테이블 생성 및 데이터 입력
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[root@rocky8-web3 ~]# mysql -u xinet -p xinet Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 10.11.9-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 [xinet]> create table Users (id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20)); Query OK, 0 rows affected (0.313 sec) MariaDB [xinet]> INSERT INTO Users (id, name, email, country, password) VALUES (1, 'Pankaj', 'pankaj@apple.com', 'India', 'pankaj123'); Query OK, 1 row affected (0.028 sec) MariaDB [xinet]> INSERT INTO Users (id, name, email, country, password) VALUES (4, 'David', 'david@gmail.com', 'USA', 'david123'); Query OK, 1 row affected (0.026 sec) MariaDB [xinet]> INSERT INTO Users (id, name, email, country, password) VALUES(5, 'Raman', 'raman@google.com', 'UK', 'raman123'); Query OK, 1 row affected (0.042 sec) |
### 기타 root 패스워드 변경시
|
1 |
MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'qqVCdasd33'; |
사용자 패스워드 변경시
|
1 2 3 4 5 6 7 |
### localhost 사용자 MariaDB [mysql]> ALTER USER 'xinet'@'localhost' IDENTIFIED BY 'q113aadf'; Query OK, 0 rows affected (0.038 sec) ### 원격사용자 MariaDB [mysql]> ALTER USER 'xinet'@'%' IDENTIFIED BY 'q113aadf'; Query OK, 0 rows affected (0.038 sec) |
유저 삭제
|
1 2 3 4 5 |
MariaDB [mysql]> drop user xinet@localhost; Query OK, 0 rows affected (0.009 sec) MariaDB [mysql]> drop user xinet@'%'; Query OK, 0 rows affected (0.002 sec) |