터미널에서 which mysqldump
datagrip에서 export
mysqldump 경로를 입력하면 됨.
스키마만 덤프하고 싶으면 Multiple rows inserts, Export schema without data 체크 해제
auto_increment 등 테이블 제약사항 유지하고 싶다면 MySQL create table options 체크
db에 이미 trigger가 있으면 drop하기
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data. -> --single-transaction or --lock-all-tables or --master-data 옵션 작성
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)AWS RDS의 GTID 관련 문제
dump용으로 생성한 .sql을 열고 아래 구문이 있으면 주석처리
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;OperationalError(1449, "The user specified as a definer ('xxx'@'%') does not exist")
-> xxx user가 없어서 생기는 문제, dump .sql의 xxx를 다른 존재하는 사용자로 변경
/*!50003 CREATE*/ /*!50017 DEFINER=`xxx`@`%`*/ /*!50003 trigger auto_delete_board_of_reporters
.../*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 trigger auto_delete_board_of_reporters
...또는 권한 부여된 유저 생성
로컬이 아닌 환경에서 password 정책 낮추면 안됨
show variables like '%validate%';
set global validate_password_policy =LOW;
set global validate_password_length =4;
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'xxx'@'%' IDENTIFIED BY 'xxx';특정 테이블의 데이터만 export 하기
파일로 추출해서 run 하면 됨. target db, schema, file 지정.
https://aws.amazon.com/ko/about-aws/whats-new/2018/10/Amazon-RDS-MySQL-supports-global-transaction-identifiers-gtids/
https://velog.io/@cataiden/sqldump-in-datagrip
https://blog.naver.com/niee/220002570626
