[MySQL/MariaDB] MySQL/MariaDB 기본 명령어

※ MySQL / MariaDB 접속 명령

mysql -h 1.1.1.1 -P 23306 -uroot -p

→ 접속서버 IP : 1.1.1.1, 접속포트 : 23306일 경우

1. Mysql 데이터 베이스 확인

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| admin              |

| aone               |

| mysql              |

| performance_schema |

| sme                |

+--------------------+

 

2. Mysql User 확인

mysql> select user,host from mysql.user;

+-------+--------------+

| user  | host         |

+-------+--------------+

| root  | 127.0.0.1    |

| repl  | 172.18.212.% |

| root  | ::1          |

| root  | DB-Linux-Os2 |

| admin | localhost    |

| api   | localhost    |

| batch | localhost    |

| img   | localhost    |

| m     | localhost    |

| root  | localhost    |

| sme   | localhost    |

| www   | localhost    |

| test    | localhost  |

| test    | 127.0.0.1  |

+-------+--------------+

 

2.1 특정 ID select 권한 부여 방법

update mysql.user set Select_priv = 'Y' where User = 'test' and Host = 'localhost';

flush privileges;

 

3. MySQL 테이블 마지막 행 출력(마지막 10라인)

select * from 테이블명 limit 10;

select * from 테이블명 limit 1,100;

-> 1~100번 라인까지 출력

 

select * from 테이블명 order by 필드명 desc limit 1;

select * from 테이블명 order by 필드명 desc limit 10;

-> 마지막 1줄이 표, limit 10 하면 마지막에서 10번째 줄까지 표시

 

4. MySQL Autocommit 확인

MariaDB [snet]> SELECT @@AUTOCOMMIT;

+--------------+

| @@AUTOCOMMIT |

+--------------+

|            1 |

+--------------+

 

5. 특정 테이블 권한 부여

GRANT SELECT ON aaa.readview TO 'readuser'@'192.168.200.88' IDENTIFIED BY '1234QWERasdf';

FLUSH PRIVILEGES;

 

6. 테이블별 사이즈 확인(쿼리 수행 시점)

SELECT

    table_name,

    table_rows,

    round(data_length/(1024*1024),2) as 'DATA_SIZE(MB)',

    round(index_length/(1024*1024),2) as 'INDEX_SIZE(MB)'

FROM information_schema.TABLES

where table_schema = '데이터베이스이름'

GROUP BY table_name

ORDER BY data_length DESC

LIMIT 10;

 

7. Database 별 사이즈 확인(쿼리 수행 시점)

SELECT

    count(*) NUM_OF_TABLE,

    table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows,

    concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,

    concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,

    concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,

    round(sum(index_length)/sum(data_length),2) idxfrac

FROM information_schema.TABLES

GROUP BY table_schema

ORDER BY sum(data_length+index_length) DESC LIMIT 10;

 

 

99. Mysql Master-Slave 확인

(Slave_IO_Running /  Slave_SQL_Running 확인)

 

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Queueing master event to the relay log

                  Master_Host: 172.18.212.153

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 4696

               Relay_Log_File: goldsdb01-relay-bin.004109

                Relay_Log_Pos: 253

        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: 4696

              Relay_Log_Space: 762

              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: 1821

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