DB Library

1. DB 접속 후, into ~ outfile 형식

SELECT order_id,product_name,qty 
FROM orders 
INTO OUTFILE '/tmp/orders.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'


 * Access deniey 발생 시
 ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
 
 GRANT FILE ON *.* TO 'kccfres'@'localhost';
 
 
 * secure-file-priv 처리
 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
 
 1. Mysql client 로 연결후 SHOW variable 로 MySQL 환경 변수 확인
   mysql> SHOW VARIABLES LIKE "secure_file_priv";
  +------------------+-----------------------+
  | Variable_name    | Value                 |
  +------------------+-----------------------+
  | secure_file_priv | /var/lib/mysql-files/ |
  +------------------+-----------------------+
  1 row in set (0.02 sec)

2. INTO OUTFILE 의 저장 경로를 위의 secure_file_priv 폴더로 변경
  INTO OUTFILE '/tmp/orders.csv' →  INTO OUTFILE '/var/lib/mysql-files/orders.csv' 
  
3. 쿼리 재실행

2. DB 접속하지 않은 상태에서 실행

mysql -user -pass -e "select cols from orders" dbname > /tmp/orders.csv

//리모트인 경우 
mysql -h {아이피} -user -pass -e "select cols from orders" dbname > /tmp/orders.csv

참고

https://www.lesstif.com/dbms/mysql-client-query-csv-text-54951965.html

https://xn--lg3bu39b.xn--mk1bu44c/77

Version - 5.7

-- 계정 생성

insert into user(host, user, authentication_string, ssl_cipher, x509_issuer, x509_subject)
values('%', 'account_srv', password('StrongPassword'), '', '', '');

--권한
grant all privileges on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;
grant select, insert, update, delete, execute on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;

flush privileges;

--- 비밀 번호 변경
UPDATE mysql.user SET Password=PASSWORD('StrongPassword') WHERE User='account_srv' AND Host='%';

SET PASSWORD FOR 'account_srv'@'%' = PASSWORD('StrongPassword');

ALTER USER 'account_srv'@'%' identified by 'StrongPassword';

flush privileges;

-- 권한 확인
# show grants for 'user'@'접속위치';
show grants for 'account_srv'@'%';

-- 계정 삭제
# drop user '계정아이디'@'접속위치';
drop user 'account_srv'@'%';

-- 권한 삭제
# revoke all on DB이름.테이블 FROM '계정아이디'@'접속위치';
revoke all on *.* FROM 'account_srv'@'%';

Version - 8.0

-- 사용자 정보 조회 
SELECT user,authentication_string,plugin,host FROM mysql.user; 

-- 사용자 생성 
create user 'account_srv'@'%' identified by 'StrongPassword'; 

-- 권한 부여 
GRANT ALL PRIVILEGES ON *.* TO 'account_srv'@'%'; 
GRANT GRANT OPTION ON *.* TO 'account_srv'@'%'; 

-- 비밀번호 plugin 변경 
ALTER USER 'account_srv'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword';

SELECT GROUP_CONCAT(NAME) AS GCN
     , GROUP_CONCAT(NAME SEPARATOR ' ') AS GCN2
     , GROUP_CONCAT(AGE ORDER BY AGE DESC SEPARATOR '-') AS GCA
FROM TB1

GROUP_CONCAT(컬럼명) : 해당 테이블 컬럼 데이터를 ',' 단위로 하나의 열로 모아주는 기능
GROUP_CONCAT(컬럼명 SEPARATOR '값') : 저정된 단위로 여러 컬럼을 하나의 열로 모아주는 기능
GROUP_CONCAT(컬럼명 ORDER BY 값 ASC/DESC) : 오름/내림 차순 결과를 하나의 열로 모아주는 기능

참고자료

https://wakestand.tistory.com/577?category=835537