MySQL 전체 테이블 대상 삭제, 생성, SELECT-INSERT, 권한 관련 쿼리

기본조건

USER ID : user_id

ORIGINAL DATABASE : orignal_database

NEWER DATABASE : newer_database

1) 현재 데이터베이스에 존재하는 전체 테이블을 대상으로 DELETE 쿼리 생성

SELECT
  CONCAT('DROP TABLE ', '`', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;') AS STMT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'newer_database';

Result

DROP TABLE  `newer_database`.`alarm`;
DROP TABLE  `newer_database`.`alarm_history`;
DROP TABLE  `newer_database`.`attch`;
DROP TABLE  `newer_database`.`board`;
DROP TABLE  `newer_database`.`code`;

2) 현재 데이터베이스에 존재하는 전체 테이블을 대상으로 CREATE 쿼리 생성

SELECT
  CONCAT('CREATE TABLE ', '`', 'newer_database', '`.`', TABLE_NAME, '` LIKE `', TABLE_SCHEMA, '`', '.`', TABLE_NAME, ';') AS CR_SQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'orignal_database';

Result

CREATE TABLE `newer_database`.`alarm` LIKE `TableTemplate`.`alarm`;
CREATE TABLE `newer_database`.`alarm_history` LIKE `TableTemplate`.`alarm_history`;
CREATE TABLE `newer_database`.`attch` LIKE `TableTemplate`.`attch`;
CREATE TABLE `newer_database`.`board` LIKE `TableTemplate`.`board`;
CREATE TABLE `newer_database`.`code` LIKE `TableTemplate`.`code`;

3) 타 데이터베이스간 데이터 복사(SELECT-INSERT) 쿼리 생성

SELECT
  CONCAT('INSERT INTO `newer_database`.`', TABLE_NAME, '`', ' SELECT * FROM `', TABLE_SCHEMA, '`.', '`', TABLE_NAME, '`;') AS STMT
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'orignal_database';

Result

INSERT INTO  `newer_database`.`alarm` SELECT * FROM `orignal_database`.`alarm`;
INSERT INTO  `newer_database`.`alarm_history` SELECT * FROM `orignal_database`.`alarm_history`;
INSERT INTO  `newer_database`.`attch` SELECT * FROM `orignal_database`.`attch`;
INSERT INTO  `newer_database`.`board` SELECT * FROM `orignal_database`.`board`;
INSERT INTO  `newer_database`.`code` SELECT * FROM `orignal_database`.`code`;

4) 권한 확인

SQL> SHOW GRANTS for 'user_id'@'%';

설명:

user id user_id의 권한 확인

Result

GRANT USAGE ON *.* TO 'user_id'@'%' IDENTIFIED BY PASSWORD '*C6627A7C9969CE495910A57E2F35BB8DBC663F9E'
GRANT ALL PRIVILEGES ON `orignal_database`.* TO 'user_id'@'%'
GRANT ALL PRIVILEGES ON `orignal_database_bsplt`.* TO 'user_id'@'%'
GRANT ALL PRIVILEGES ON `newer_database`.* TO 'user_id'@'%'

4) 유저에게 데이터베이스 권한 부여

SQL> GRANT ALL PRIVILEGES on newer_database.* to user_id@'%' identified by 'newer_password!!';

설명:

user id user_id에 newer_database 데이터 베이스의 모든 테이블에 대한 권한을 all privileges 으로 설정하고,

identified by 'newer_password!!'; 을 추가하면 newer_password!! 으로 비밀번호 변경.

이 부분은 필요 없을 경우 아래와 같이 빼고 사용함

SQL> GRANT ALL PRIVILEGES on newer_database.* to user_id@'%';

4) 권한 제거

SQL> REVOKE ALL PRIVILEGES ON orignal_database.* FROM user_id@'%';

설명:

user id user_id에 orignal_database 데이터베이스의 모든 테이블에 대한 all privileges 권한을 제거.