2. [MySQL] 기본 명령어 익히기

1. [MySQL] 설치(CentOS)

2. [MySQL] 기본 명령어 익히기

3. [MySQL] 외부에서 접근하는방법

4. [MySQL]워크벤치 설치하기

5. [MySQL]리플리케이션 방법

6. [MySQL]my.conf 환경 설정하기

7. [MySQL] MySQL 리플리케이션 UUID 관련 에러

8. [MySQL] mysql 데이터샘플 밀어 넣기  & 백업복구

9. [MySQL]Workbench & DA# 모델링 그리기

 

 

 

 

1. 비밀번호 변경

MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.

다음 세가지 방법으로 비밀번호를 변경 할 수 있다.

 

# mysqladmin 이용
mysqladmin -u root password 새비밀번호

# update문 이용
mysql -u root mysql
----
mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
mysql> FLUSH PRIVILEGES;

# Set Password 이용
mysql> SET PASSWORD FOR root=password('새비밀번호');

일단 root 비밀번호가 설정된 상태에서는 mysql이나 mysqladmin 명령을 실행할 때 -p 옵션을 붙여주고 기존 비밀번호를 입력해야만 한다.

1. 비밀번호 변경

MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.

다음 세가지 방법으로 비밀번호를 변경 할 수 있다.

 

2. 사용자 확인

-- 현재 사용자 목록
use mysql;
select User,Host from user;

-- 권한 확인 'username'@'hostname'
SHOW GRANTS FOR 'username'@'%' 

 

3. 사용자 추가 / 권한

• MySQL :: Security in MySQL :: 5.2 Adding User Accounts

• MySQL :: MySQL 5.6 Reference Manual :: 13.7.1.6 REVOKE Syntax

-- 생성 후 필수 권한만 부여. - 실서비스 계정은 가급적 이렇게 필요 권한만 부여할 것.
CREATE USER username@localhost IDENTIFIED BY 'userpassword'
GRANT SELECT,INSERT,UPDATE,DELETE ON dbname.* TO 'username'@'localhost';
 
-- 생성과 전체 권한 일괄 부여
GRANT ALL PRIVILEGES ON dbname.* TO username@localhost IDENTIFIED BY 'password';

username 이라는 사용자를 password라는 비밀번호를 갖도록 하여 추가한다. username은 dbname이라는 데이타베이스에 대해 모든 권한을 가지고 있다. username 사용자는 로칼 호스트에서만 접속할 수 있다. 다른 호스트에서 접속하려면

 

GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';

위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.

특정 권한만 주려면, 아래와 같은 형태로 권한을 나열한다.

GRANT INSERT, UPDATE, SELECT, DELETE,SHOW DATABASES, SHOW VIEW ON dbname.* TO username@'localhost' IDENTIFIED BY 'pwd';

 

-- 권한 뺐기
REVOKE CREATE,DROP,ALTER,CREATE VIEW,ALTER ROUTINE,CREATE ROUTINE
  ON dbname.* 
  FROM 'user'@'%';

MySQL error 1045(28000): Access denied for user ...에 따르면 '%'란 localhost 를 제외한 모든 호스트를 뜻한다고 한다. 즉, localhost에 대해서는 명시적으로 따로 권한을 지정해야 한다.

CREATE USER를 통한 사용자 생성이 안될 경우에는 mysql - ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost' 참조하여, 먼저 생성하려던 사용자를 drop 하고 재시도 해본다. 이 현상이 나타나는 이유는, CREATE USER/GRANT 명령으로 사용자와 권한을 추가/관리해야 하는데 mysql.db, mysql.user 테이블을 직접 조작하다가 일관성이 깨졌기 때문이다. 가급적 mysql의 계정/권한 테이블에 대한 직접 조작은 하지 말아야 한다.

drop user admin@localhost;
flush privileges;
create user admin@localhost identified by 'admins_password'

4. 불필요한 사용자 삭제는

mysql> DLETE FROM USER WHERE USER='username';
mysql> FLUSH PRIVILEGES;

5. 데이터베이스 생성/보기

# 데이터베이스를 생성하고,
mysql> CREATE DATABASE dbname;
 
mysql> CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
# 현재 존재하는 데이터베이스 목록을 보여준다.
mysql> SHOW DATABASES;
 
# 특정 데이타베이스를 사용하겠다고 선언한다.
mysql> USE dbname;
 
# 쓸모 없으면 과감히 삭제한다.
mysql> DROP DATABASE [IF EXISTS] dbname;

IF EXISTS 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.

 

6. 테이블 생성/보기

mysql> CREATE TABLE tablename (
  column_name1 INT PRIMARY KEY AUTO_INCREMENT,
  column_name2 VARCHAR(15) NOT NULL,
  column_name3 INT
) ENGINE=INNODB;

현재 데이타베이스의 테이블 목록을 보고

mysql> SHOW TABLES;

mysql> EXPLAIN tablesname;
혹은
mysql> DESCRIBE tablename;

7. 이름을 변경

mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];

8. 삭제 삭제한다.

mysql> DROP TABLE [IF EXISTS] tablename;

9. 상태 보기

mysql> STATUS
 
--------------
mysql Ver 11.18 Distrib 3.23.58, FOR pc-linux (i686)
 
Connection id: 26
CURRENT DATABASE: study
CURRENT USER: study@localhost
CURRENT pager: stdout
USING OUTFILE: ''
Server version: 3.23.58
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: euc_kr
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 hours 9 MIN 59 sec
 
Threads: 1 Questions: 160 Slow queries: 0 Opens: 28 FLUSH TABLES: 1
OPEN TABLES: 1 Queries per SECOND avg: 0.021

10. 호스트별 커넥션 상황

SELECT SUBSTRING_INDEX(HOST,':',1) AS host, COUNT(*) AS cnt
FROM information_schema.processlist
GROUP BY SUBSTRING_INDEX(HOST,':',1);

• INSERT

mysql> INSERT INTO tablename VALUES(값1, 값2, ...); 
혹은 
mysql> INSERT INTO tablename (col1, col2, ...) VALUES(값1, 값2, ...);

• SELECT

mysql> SELECT * FROM tablename ORDER BY col1 DESC;
mysql> SELECT col1, korean + math english AS '총점' FROM tablename ORDER BY '총점' ASC;

• UPDATE

mysql> UPDATE tablename SET col1=새값 WEHER 조건

• DELETE

mysql> DELETE FROM tablename WEHRE 조건

-- DELETE 바로 뒤에 삭제할 테이블을 적는다.
-- INNER JOIN
DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;
 
-- LEFT JOIN
DELETE T1
FROM T1 
LEFT JOIN T2 ON T1.key = T2.key 
WHERE T2.key IS NULL;

11. 오류나 경고 보기

mysql> SHOW errors; 
mysql> SHOW warnings;

show processlist

• 접속한 커넥션의 상태를 보여준다.

SHOW FULL processlist; 
SHOW FULL processlist\G;

12. MySQL 버전 알아보기

SHOW VARIABLES LIKE "%version%";

13. CREATE TABLE 구문 보기

SHOW CREATE TABLE [테이블이름]\G

-- 전체 테이블 정보를 주석까지 포함해 보여준다.
SHOW TABLE STATUS;

SHOW TABLE STATUS like 'member%'; -- 테이블 이름 매칭 조건

-- 테이블의 컬럼 정보를 주석까지 포함해 보여준다.
SHOW FULL COLUMNS FROM [테이블이름];

14. 테이블/컬럼의 주석(Comment) 지정

-- 테이블의 주석 변경
ALTER TABLE [테이블이름] COMMENT = '테이블설명';

-- 컬럼의 주석 변경
ALTER TABLE [테이블이름] CHANGE COLUMN [컬럼이름] [새컬럼이름] ...여러 속성... COMMENT '새 주석';

15.테이블과 컬럼의 전체 정보

information_schema 의 TABLES, COLUMNS를 쿼리하여 테이블, 컬럼 정보를 살펴볼 수 있다.

select t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_TYPE, c.COLUMN_COMMENT from `TABLES` t
	inner join `COLUMNS` c on t.TABLE_NAME = c.TABLE_NAME
order by t.TABLE_SCHEMA, t.TABLE_NAME

16. Group By 의 비 Grouping 컬럼 정보 concat

SELECT keyword, GROUP_CONCAT( syn SEPARATOR  '-' )  -- syn 컬럼들이 '-' 로 묶인 문자열로 나온다.
FROM syn_common
GROUP BY keyword

17. GROPU BY, DISTINCT

GROUP BY를 하면서, COUNT(DISTINCT colName) 을 하면, 동일 그룹에 속하는 colName 이 중복은 제거 상태로 카운팅 된다.

1. [MySQL] 설치(CentOS)

2. [MySQL] 기본 명령어 익히기

3. [MySQL] 외부에서 접근하는방법

4. [MySQL]워크벤치 설치하기

5. [MySQL]리플리케이션 방법

6. [MySQL]my.conf 환경 설정하기

7. [MySQL] MySQL 리플리케이션 UUID 관련 에러

8. [MySQL] mysql 데이터샘플 밀어 넣기

9. [MySQL]Workbench & DA# 모델링 그리기