[MySQL/MariaDB] 명령어

MySQL/MariaDB: 명령어 및 관리에 대한 내용

1. 연결 설정

1.1 루트 연결

루트 사용자로 연결하려면 MySQL bin 폴더로 이동하고 다음 명령을 실행한다.

./mysql -u root -p

1.2 사용자 목록

다음 SQL 쿼리로 사용자 및 해당 호스트 목록을 확인한다.

use mysql;
select user, host from user;

1.3 비밀번호 변경

mysqladmin 명령을 사용하여 사용자의 비밀번호를 변경한다.

mysqladmin -uID -pOldPassword password NewPassword

2. 데이터베이스 작업

2.1 데이터베이스 목록

사용 가능한 데이터베이스 목록을 확인한다.

show databases;

2.2 인덱스 초기화

테이블의 자동 증가 인덱스를 재설정한다.

ALTER TABLE [테이블 이름] AUTO_INCREMENT = 1;

3. 사용자 관리

3.1 사용자 추가

특정 호스트 액세스를 가진 새로운 사용자를 만든다.

# 외부 액세스
create user 'userid'@'%' identified by 'user password';

# 내부 액세스
create user 'userid'@'localhost' identified by 'user password';

3.2 사용자 제거

사용자를 삭제하거나 제거한다.

drop user 'userid';
delete from user where user = 'userid';

3.3 비밀번호 관리

mysql.user 테이블에서 사용자의 비밀번호를 업데이트 한다.

update user set password = password('new password') where user = 'username';

4. 권한 설정

4.1 MySQL 외부 액세스 권한

IP 구성에 기반한 다양한 액세스 권한을 부여한다.

# {db}.{table}에 대한 모든 권한을 {user}@{ip}에게 부여
grant all privileges on *.* to 'root'@'%' identified by 'root의 비밀번호';

4.2 모든 IP 허용

INSERT INTO mysql.user (host,user,password) VALUES ('%', 'root', password('password'));
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

4.3 IP 대역 허용

INSERT INTO mysql.user (host,user,password) VALUES ('111.222.%', 'root', password('password'));
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.%';
FLUSH PRIVILEGES;

4.4 특정 IP 허용

INSERT INTO mysql.user (host,user,password) VALUES ('111.222.33.44', 'root', password('password'));
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.33.44';
FLUSH PRIVILEGES;

4.5 권한 주기

#전체권한 (SELECT, DELETE, UPDATE, INSERT)
grant all privileges on dbname.table to userid@host identified by 'password';
grant select, insert, update on dbname.table to userid@host identified by 'password';
grant select, insert, update on dbname.table to userid@'192.168.%' identified by 'password';

4.6 권한 삭제

revoke all on dbname.table from userid@host

4.7 원복

DELETE FROM mysql.user WHERE Host='%' AND User='root';
FLUSH PRIVILEGES;

5. 오류 처리

5.1 오류 코드: 1175

세이프 모드를 비활성화하고 변경사항을 적용한다.

set sql_safe_updates = 0; -- 안전 모드 비활성화
set sql_safe_updates = 1; -- 안전 모드 적용

5.2 변경사항 적용

직접 MySQL 서버에 연결하여 변경 사항을 수행하면 다음 명령을 사용하여 적용한다.

flush privileges;

6. 모니터링 및 문제 해결

6.1 권한 확인

특정 호스트에 대한 사용자 권한을 확인한다.

show grants for userid@host

6.2 잠금 확인

데이터베이스 잠금 및 잠금 대기를 확인한다.

# 잠금 ID
select * from information_schema.innodb_locks;

# 잠금 대기 상태
select * from information_schema.innodb_lock_waits;

6.3 트랜잭션 진행 확인

진행 중인 트랜잭션을 모니터하기.

# trx_mysql_thread_id
select * from information_schema.innodb_trx;

6.4 잠금 해제

특정 잠금을 해제하기. (잠금 ID 사용)

kill {lock_id};