MySQL batch insert

CREATE TABLE t_user_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(30) NOT NULL,
  action_ts TIMESTAMP NOT NULL,
  action_type VARCHAR(30) NOT NULL,
  KEY `idx_user_logs_uniq` (`username`,`action_ts`)
);

Upsert

INSERT INTO t_user_logs
  (username, action_ts, action_type)
VALUES
  ('user1', now(), 'LOGIN'), ('user2', now() 'LOGIN)
ON DUPLICATE KEY UPDATE
  action_type=VALUES(action_type);

Replace

REPLACE INTO t_user_logs
  ( username, action_ts, action_type)
VALUES
  ('user1', now(), 'LOGIN');

Insert ignore

대용량 넣을 때 반복데이터가 들어올 때가 간혹 있는 경우

INSERT IGNORE INTO t_user_logs
  (username, action_ts, action_type)
VALUES
  ('user1', '2000-01-01 11:30:10', 'LOGIN'), ('user2', '2000-01-01 11:30:11', 'LOGIN');

Insert Batch

ignore안하고 해도 됨

INSERT INTO t_user_logs   (username, action_ts, action_type) VALUES   ('user1', '2000-01-01 11:30:10', 'LOGIN'), ('user2', '2000-01-01 11:30:11', 'LOGIN');

참고

  • https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query
  • https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/
  • https://www.techbeamers.com/mysql-upsert/
  • https://number1.co.za/mysql-insert-if-not-exists/

Error : Mysql Google RDS에서 사용시 function 생성오류

Message : you *might* want to use the less safe log_bin_trust_function_creators variable

메시지를 카피를 제대로 안해놔서…. 어쨌든 권한부족 관련 메세지가 뜬다.

MySQL, Triggers and Amazon RDS

 

웹콘솔에서 DB에 들어가서 – 수정 – 데이터베이스 플래그 추가

log_bin_trust_function_creators = on(1)

저장 후 재시작

 

Error: MySQL 5.7 이상 Group by – this is incompatible with sql_mode=only_full_group_by

MySQL 5.7이상에서 Group By 쿼리 사용시 발생하는 오류

0 12 12:27:17 select tp1.idx, tp1.category, tp1.contents, tp1.view_yn, tp1.status, tp1.recordid, max(tp1.recordtime)
  from TBL_PAGE tp1 group by tp1.RECORDTIME
  LIMIT 0, 1000 Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'solarbridge_dev.tp1.IDX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 12 12:27:17 select tp1.idx, tp1.category, tp1.contents, tp1.view_yn, tp1.statu' at line 5	0.000 sec


Error Code: 1140. In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'wordpressdb_test.TC.IDX'; this is incompatible with sql_mode=only_full_group_by

 

Solution.1)
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Solution.2)
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

ANY_VALUE라고 싸줌.

MySql 백업Backup & 복구|복원Restore

백업의 대상

  1. 데이터베이스 파일
  2. 오류 로그 파일, 슬로쿼리 로그 파일
  3. 바이너리 로그 파일
  4. 설정파일 my.cnf
  5. 실행 파일
  6. 기타 공유 라이브러리, 지원 스크립트, 테스트 툴

 

콜드 백업

1. 디비 정지
$ mysqladmin shutdown -uroot
2. 디렉토리 백업
$ tar cvf /var/lib/mysql ~/mysqlbak.tar
3. 디비 재기동
$myqld_safe &

바이너리 로그 이용

바이너리 로그 활성화
my.cnf
log-bin을 추가

호스트이름.0000001 이라는 바이너리 파일 생성

mysqlbinlog사용
$ mysqlbinlog hostname.000011 > recover.sql
$ mysql -uroot < recover.sql
(mysqlbinlog는 쿼리문 마지막에 rollback를 넣는다. 문제의 소지가 있으니 참고)

온라인 백업 mysqldump

잠금걸고 온라인 백업
$ mysqldump -uroot --lock-all-tables -master-data=2 > dump.sql
내용을 보고 바이너리 로그의 위치정보 찾기
$ more dump.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='hostname.00042', MASTER_LOG_POS=98;

백업파일 복구
$ mysql -uroot < dump.sql
바이너리로그적용
$ mysqlbinlog --position--98 hostname.000042 > binlog.sql
$ mysql -uroot < binlog.sql

새로운 백업후 바이너리로그 삭제

mysql> PURGE MASTER LOGS TO 'hostname.000042'
(42번 이전의 바이너리 로그를 모두 삭제하는 명령)

 

기타 백업

lvm스냅샷

전체 디비 공유잠금
mysql> FLUSH TABLES WITH READ LOCK;

바이너리 로그의 위치 정보를 알아냄
mysql> SHOW MASTER STSTUS;

디스크에 기록되지 않은 정보를 기록
$ sync

스냅샷을 취득
LVM이라면 lvcreate커맨드

공유 잠금을 해제
mysql> UNLOCK TABLES;

취득한 스냅샷을 테이프 등에 복사
cp..tar...등

innodb의 경우 Xtrabackup