MySQL 관련 DBA 명령어 모음

MySQL 상태값 Diff 출력

-- // 5초 단위로 상태값들의 Diff값을 10번까지 출력함

mysqladmin extended-status --relative --sleep=5 --count=10 --user=root -p

슬레이브의 에러 쿼리 1개 무시

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;

start slave;

show slave status\G

Binary로그 활성화된 장비에서 함수 생성

SET GLOBAL LOG_BIN_TRUST_FUNCTION_CREATORS = 1;

Binary로그 활성화된 장비에서, 현재 세션의 쿼리를 Binary로그에 기록하지 않도록 변경

set sql_log_bin=off;

InnoDB의 Lock 모니터링  

-- // 모니터링 테이블 생성  

CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;  

-- // innotop 프로그램 실행  

innotop --host=127.0.0.1 --port=20306 --user=root --delay=5 --color --password=$1  

• T)  InnoDB 트랜잭션 목록
• L)  InnoDB Locks 정보
• I)  InnoDB I/O 정보
• B)  InnoDB Buffers 정보
• S)  Variables & Status 값 조회
• R)  InnoDB 레코드 조작 상황 조회
• Q)  실행중인 Query 목록 조회
• C)  Command 실행 상태 조회 (Com_xxx 상태값 조회)

MySQL Binary 로그 퍼지(Purge)

PURGE BINARY LOGS BEFORE '2010-08-05 00:00:00';

AutoIncrement 값 변경

ALTER TABLE 테이블명 AUTO_INCREMENT=100000;

MySQLBinLog 명령시 default-character-set 오류 발생시

mysqlbinlog --no-defaults NVSRHDM1502-relay-bin.000549 

MySQL Clean shutdown

set global innodb_fast_shutdown=0;

mysqladmin -uroot -p shutdown

MySQL의 테이블명 및 컬럼명 변경

-- // 테이블명 변경

rename table ${OLD_TABLE_NAME} to ${NEW_TABLE_NAME};

-- // 컬럼명 변경

alter table ${TABLE_NAME} change ${OLD_COLUMN_NAME} ${NEW_COLUMN_NAME} char(1);

Replication 재 설정

stop slave;  

reset slave;  

change master to master_host='${mysql-host-ip}',master_port=${mysql-port},master_user='${replication-user}',master_password='${replication-user-password}',master_log_file='${binary-log-file-of-master}',master_log_pos=${binary-log-position};

MySQL 시작시 Slave thread 시작하지 않도록 설정

skip-slave-start (my.cnf 파일에 설정)

DB 사용자 권한

GRANT SELECT, INSERT, UPDATE, DELETE ... ON *.* TO '${new-user-id}'@'${client-host}' IDENTIFIED BY PASSWORD '*777735CA4B06D03725B9DB1BE15F1AA3D2E94B95';  

SHOW GRANTS FOR '${new-user-id}'@'${client-host}';

SET PASSWORD FOR '${new-user-id}'@'${client-host}' = PASSWORD('new-password');

MySQL 테이블 & 루틴 전부 덤프

mysqldump -uroot -p --opt --single-transaction --hex-blob --master-data=2 --routines --triggers --default-character-set=utf8 --databases ${db1} ${db2} > mysqldump`hostname`_`date '+%y%m%d'`.data

MySQL의 루틴만 덤프  

mysqldump --user=root --password --routines --no-create-info --no-data --no-create-db --skip-opt --databases ${db1} ${db2} > routines.sql

SELECT INTO OUTFILE ==> LOAD DATA INFILE로 데이터 마이그레이션

    -- // SELECT ... INTO OUTFILE로 덤프 실행

    SELECT fd1, fd2, fd3

    INTO OUTFILE "/home/mysql/test_table.dat"

    FIELDS TERMINATED BY ','

    FROM test_table;

    -- // 데이터를 적재할 임시 테이블 생성

    CREATE TABLE test_table(

      fd1 char(8) NOT NULL default '',

      fd2 bigint NOT NULL default '0',

      fd3 int NOT NULL default '0',

      PRIMARY KEY  (fd1, fd2)

    ) ENGINE=InnoDB;

    -- // 생성된 임시 테이블에 데이터 적재

    LOAD DATA INFILE '/home/mysql/test_table.dat'

    INTO TABLE test_table

    FIELDS TERMINATED BY ','

      (fd1, fd2, fd3);

    -- // 추가로 필요한 인덱스 생성

    alter table test_table add index ix_fd2_fd1 (fd2, fd1);

    -- // 기존 운영용 테이블을 백업하고, 적재된 테이블을 운영용 테이블로 이름 변경

    rename table real_table to real_table_backup, test_table to real_table;

MySQL 설치시 필요한 명령어

cd /home/mysql/MyHome

mkdir -p ./logdata/innodblog

mkdir -p ./logdata/innodbdata

mkdir -p ./tmp

chown -R root  .

chown -R mysql logdata

chown -R mysql tmp

chgrp -R mysql .

./bin/mysql_install_db  --defaults-file=./etc/my.cnf

chown -R root  .

chown -R mysql data

chown -R mysql logdata

chown -R mysql tmp

chgrp -R mysql .

cp -p ./share/mysql/mysql.server /etc/init.d/mysql

## // MySQL 초기 데이터베이스 생성

mysql_install_db --user=mysql --defaults-file=${MYSQL_HOME}/etc/my.cnf

## // MySQL 서버를 Bootup-script 없이 수동 실행 (Angel process 없이 실행)

${MYSQL_HOME}/bin/mysqld --defaults-file=${MYSQL_HOME}/etc/my.cnf &

## // MySQL 서버를 Bootup-script 없이 수동 실행 (Angel process를 이용해서 실행)

${MYSQL_HOME}/bin/mysqld_safe --defaults-file=${MYSQL_HOME}/etc/my.cnf &