lee@mysql-m:~$ lee@mysql-m:~$ mysql_config_editor print --all lee@mysql-m:~$ lee@mysql-m:~$ mysql_config_editor set --login-path=rootconn --host=localhost --user=root --password Enter password: lee@mysql-m:~$ lee@mysql-m:~$ lee@mysql-m:~$ ls -al 합계 48 drwxr-xr-x 6 lee lee 4096 1월 16 21:43 . drwxr-xr-x 3 root root 4096 1월 13 01:00 .. -rw------- 1 lee lee 106 1월 14 19:46 .Xauthority -rw------- 1 lee lee 1416 1월 16 17:57 .bash_history -rw-r--r-- 1 lee lee 220 1월 13 01:00 .bash_logout -rw-r--r-- 1 lee lee 3771 1월 13 01:00 .bashrc drwx------ 11 lee lee 4096 1월 13 01:29 .cache drwx------ 11 lee lee 4096 1월 14 18:21 .config drwx------ 3 lee lee 4096 1월 13 01:14 .gnupg drwxr-xr-x 3 lee lee 4096 1월 13 01:14 .local -rw------- 1 lee lee 136 1월 16 21:44 .mylogin.cnf lee@mysql-m:~$ mysql_config_editor print --all [rootconn] user = "root" password = ***** host = "localhost" lee@mysql-m:~$
mysql_config_editor 명령으로 credential 을 생성하면 유저 홈 디렉토리에 ".mylogin.cnf" 파일이 생성이 된다.
이후부터는 mysql 로긴시 패스워드를 입력할 필요없이 login-path 옵션을 붙여주면 된다.
2-2) Replication 유저 생성 @master sudo mysql -u root -p1111
-- slave ip 로 생성한다. CREATE USER 'REP_ADM'@'192.168.56.102' IDENTIFIED WITH mysql_native_password BY '1111'; GRANT REPLICATION SLAVE ON *.* TO 'REP_ADM'@' 192.168.56.102';
SHOW MASTER STATUS;
File, Position 값을 확인
@slave sudo mysql -u root -p1111
-- master IP 를 넣는다 CHANGE MASTER TO MASTER_HOST='192.168.56.101', MASTER_USER='REP_ADM', MASTER_PASSWORD='1111', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=2391;
START SLAVE;
SHOW SLAVE STATUS \G => [ERROR] : equal MySQL server UUIDs
sudo rm -rf /var/lib/mysql/auto.cnf sudo service mysql restart
START SLAVE;
3) Replicatin 연결 확인
-- 복제확인 sudo mysql --login-path=rootconn -e 'show slave status \G'|egrep "Master_Host|Master_User|Master_Log_File|Master_Log_Pos|ReplicateIgnore_DB|Running|IO_Err|SQL_Err|Seconds_Behind_Master|SQL_Delay"
** 아래 메세지가 보여야 정상 복제중 Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
-- 에러확인 show variables like '%log_error%'; => tail -100f /var/log/mysql/error.log
4) Replication 에러 메시지별 대응
[ERROR] [MY-013117] [Repl] Replica I/O for channel '': Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: MY-013117 => VM 을 복제하면서 동일 uuid 문제 => auto.cnf 삭제
[ERROR] [MY-013124] [Repl] Replica SQL for channel '': Replica failed to initialize applier metadata structure from the repository, Error_code: MY-013124 => slave 를 리셋해준다. stop slave; reset slave; stop slave;
[ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'REP_ADM@192.17.7.5:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061 => rep user error : Authentication 확인 => mysql.user 의 plugin 확인 후 변경 => WITH mysql_native_password
[ERROR] [MY-010584] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000003, end_log_pos 342; Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test', Error_code: MY-001007 => 트랜잭션 실패 : 대상 object 가 존재하지 않거나 실행할수 없어서 발생. skip 또는 object 동일하게 만들고 slave 재시작
** 트랜잭션 실패로 인한 에러메세지가 아는 내용이고 굳이 적용할 필요 없으면 아래명령으로 skip 한다. stop slave; set global sql_slave_skip_counter=1; -- skip 할 error 갯수 start slave; show slave status\G
1) TABLES - show tables from dbname like '%ABC' => 테이블 리스트 - show create table TABLE_NAME; => DDL 확인 - show columns from TABLE_NAME; == desc TABLE_NAME - show table status from dbname like '%ABC' => table info * 참조제약조건 select constraint_name, table_name from information_schema.referential_constraints where constraint_schema='SCHEMA_NAME' and referenced_table_name='TABLE_NAME';
2) USER - select * from mysql.user => 유저리스트 - show grants for 'user'@'ip'; => 권한 확인
3) DB - show variables like '%server%' - select database(); => 현재접속 DB - select user(); => 현재접속 user - select version(); => 버전정보 == show variables like 'version'. - show processlist => select * from performance_schema.processlist;
- show events => select * from information_schema.events;
운영 상태에서 인덱스를 생성, 변경시 online 옵션은 매우 유용하지만 가끔 의도치 않는 상황을 만들기도 한다.
의도치 않거나 혹은 의도해서 해당 프로세스를 kill 했을때 uncommit 된 트랜잭션이 정리가 되지 않는 상태로 오래 지속될수가 있다.
08104, 00000, "this index object %s is being online built or rebuilt" // *Cause: the index is being created or rebuild or waited for recovering // from the online (re)build // *Action: wait the online index build or recovery to complete
CREATE TABLE TEST_TAB(A INT, B VARCHAR2(10)); insert into TEST_TAB select dbms_random.value(1,1000000), 'A' from dual connect by level < 1000000;
CREATE INDEX IDX_TEST ON TEST_TAB(A)online; => cancel
DROP INDEX IDX_TEST;
=> ORA-08104
08104, 00000, "this index object %s is being online built or rebuilt" // *Cause: the index is being created or rebuild or waited for recovering // from the online (re)build // *Action: wait the online index build or recovery to complete
