mariadb를 사용하면서 기본적으로 확인하는 명령어
1. 기본 데이터 경로 확인
|
1 2 3 4 5 6 7 |
MariaDB [mysql]> select @@datadir; +-------------------+ | @@datadir | +-------------------+ | /var/lib/mysql/ | +-------------------+ 1 row in set (0.000 sec) |
2. 기본 소켓 확인
|
1 2 3 4 5 6 |
MariaDB [mysql]> SHOW VARIABLES LIKE 'socket'; +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | socket | /var/lib/mysql/mysql.sock | +---------------+-----------------------------+ |
3. 언어확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
MariaDB [mysql]> SHOW VARIABLES LIKE 'character\_set\_%'; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | character_set_client | utf8mb3 | | character_set_connection | utf8mb3 | | character_set_database | utf8mb3 | | character_set_filesystem | binary | | character_set_results | utf8mb3 | | character_set_server | utf8mb3 | | character_set_system | utf8mb3 | +--------------------------+---------+ 7 rows in set (0.001 sec) |
|
1 2 3 4 5 6 7 |
MariaDB [mysql]> SHOW VARIABLES LIKE 'character_set_server'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | character_set_server | utf8mb3 | +----------------------+---------+ 1 row in set (0.003 sec) |
4. 지원하는 engine 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.009 sec) |
|
1 2 3 4 5 6 7 8 9 10 |
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.001 sec) |
innodb 사용시 inndb 파일을 생성하는 방식인지 즉 전체 파일 ibdata1 전체에 기록이 되는지 아니면 테이블별 ibd 파일이 존재하게 구성하는지
ON : 테이블별 ibd 파일이 존재
OFF : 테이블별 ibd 파일 존재가 가인 ibdata1 파일 1개로 전체 관리
|
1 2 3 4 5 6 7 |
MariaDB [mysql]> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.001 sec) |
xinet 데이터베이스에 테이블 생성시
|
1 2 3 4 5 |
Bye [root@localhost ~]# ll /var/lib/mysql/xinet/ 합계 72 -rw-rw---- 1 mysql mysql 1265 7월 2 01:28 Users.frm -rw-rw---- 1 mysql mysql 65536 7월 2 01:28 Users.ibd |
만약 ibbdata1파일 1개로 관리하려면 [root@localhost ~]# vi /etc/my.cnf.d/server.cnf
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[root@localhost ~]# vi /etc/my.cnf.d/server.cnf [mysqld] innodb_file_per_table = OFF ### mariadb 재시작 [root@localhost ~]# systemctl restart mariadb MariaDB [mysql]> show global variables like '%innodb_file_per_table%' ; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.001 sec) |
기본값 ON으로 설정하는게 좋다
5. max_connections 확인
|
1 2 3 4 5 6 7 |
MariaDB [mysql]> SHOW VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1000 | +-----------------+-------+ 1 row in set (0.022 sec) |
6. sql_mode 확인
|
1 2 3 4 5 6 7 |
MariaDB [mysql]> SHOW VARIABLES LIKE 'sql_mode'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 1 row in set (0.001 sec) |
7. 데이터베이스에 사용자 추가 및 권한 설정
|
1 2 3 4 5 6 7 8 |
MariaDB [mysql]> CREATE USER 'xinet'@'localhost' IDENTIFIED BY 'Qac13@1'; Query OK, 0 rows affected (0.011 sec) MariaDB [mysql]> GRANT ALL PRIVILEGES ON xinet.* TO 'xinet'@'localhost'; Query OK, 0 rows affected (0.012 sec) MariaDB [mysql]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.012 sec) |
8. 만약 외부에서도 접속할수 있게 구성하려면 % 구성
|
1 2 3 4 5 6 7 8 |
MariaDB [mysql]> CREATE USER 'xinet'@'%' IDENTIFIED BY 'Qac13@1'; Query OK, 0 rows affected (0.005 sec) MariaDB [mysql]> GRANT ALL PRIVILEGES ON xinet.* TO 'xinet'@'%'; Query OK, 0 rows affected (0.004 sec) MariaDB [mysql]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.007 sec) |
유저 확인
|
1 2 3 4 5 6 7 8 |
MariaDB [mysql]> select Host,User,password from user where user='xinet'; +-----------+-------+-------------------------------------------+ | Host | User | Password | +-----------+-------+-------------------------------------------+ | localhost | xinet | *F11934C0E1C4D82644AAA078D72CA11C910DA9D9 | | % | xinet | *F11934C0E1C4D82644AAA078D72CA11C910DA9D9 | +-----------+-------+-------------------------------------------+ 2 rows in set (0.002 sec) |
9. 사용자 패스워드 변경
|
1 2 3 4 5 |
MariaDB [mysql]> ALTER USER 'xinet'@'localhost' IDENTIFIED BY 'Qazc123'; Query OK, 0 rows affected (0.003 sec) MariaDB [mysql]> ALTER USER 'xinet'@'%' IDENTIFIED BY 'Qazc123'; Query OK, 0 rows affected (0.005 sec) |
10. root 패스워드가 지정되어 있다면
|
1 2 3 4 5 6 7 |
MariaDB [mysql]> select Host,User,password from user where user='root'; +-----------+------+-------------------------------------------+ | Host | User | Password | +-----------+------+-------------------------------------------+ | localhost | root | *8EA9D362916A13AFFE39F15CEC2EA4645A149139 | +-----------+------+-------------------------------------------+ 1 row in set (0.002 sec) |
root 패스워드 변경
|
1 2 3 4 5 |
MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Q345123QQ'; Query OK, 0 rows affected (0.002 sec) MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.002 sec) |
좀더 디테일하게 확인하려면 plugin 방식도 같이 확인
|
1 2 3 4 5 6 |
MariaDB [mysql]> select Host,User,password,plugin from user where user='root'; +-----------+------+-------------------------------------------+-----------------------+ | Host | User | Password | plugin | +-----------+------+-------------------------------------------+-----------------------+ | localhost | root | *8EA9D362916A13AFFE39F15CEC2EA4645A149139 | mysql_native_password | +-----------+------+-------------------------------------------+-----------------------+ |
사용자삭제
|
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) |
테이블 생성이 어떻게 만들어졌는지 확인하는 명령어
|
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 |
MariaDB [mysql]> use sourcetest; 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 [sourcetest]> show tables; +----------------------+ | Tables_in_sourcetest | +----------------------+ | Users | | idol | +----------------------+ 2 rows in set (0.000 sec) MariaDB [sourcetest]> show create table Users; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Users | CREATE TABLE `Users` ( `id` int(3) NOT NULL, `name` varchar(20) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `country` varchar(20) DEFAULT NULL, `password` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.011 sec) |
데이터베이스가 어떻게 생셩되었는지
|
1 2 3 4 5 6 7 |
MariaDB [sourcetest]> show create database sourcetest; +------------+---------------------------------------------------------------------------------------------------+ | Database | Create Database | +------------+---------------------------------------------------------------------------------------------------+ | sourcetest | CREATE DATABASE `sourcetest` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci */ | +------------+---------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) |