mariadb 설정

명령어

• 데이터베이스 스키마 덤프

$ mysqldump -u <User명> -p --routines -d <Database명> > <스키마파일명>.sql

• 데이터베이스 덤프 (데이터 포함)

$ mysqldump -u <User명> -p <Database명> > <스키마파일명>.sql

• 데이터베이스 복원

$ mysql -u <User명> -p -D <Database명> < <스키마파일명>.sql

• mysqldump option

  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.
  --add-drop-database Add a 'DROP DATABASE' before each create.
  --add-drop-table    Add a 'drop table' before each create.
  --add-locks         Add locks around insert statements.
  -B, --databases     To dump several databases. Note the difference in usage;
                      In this case no tables are given. All name arguments are
                      regarded as databasenames. 'USE db_name;' will be
                      included in the output.
  --default-character-set=name
                      Set the default character set.
  --delayed-insert    Insert rows with INSERT DELAYED;
  -f, --force         Continue even if we get an sql-error
  -x, --lock-all-tables
                      Locks all tables across all databases. This is achieved
                      by taking a global read lock for the duration of the
                      whole dump. Automatically turns --single-transaction and
                      --lock-tables off.
  -l, --lock-tables   Lock all tables for read.
  --no-autocommit     Wrap tables with autocommit/commit statements.
  -n, --no-create-db  'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will
                      not be put in the output. The above line will be added
                      otherwise, if --databases or --all-databases option was
                      given.}.
  -t, --no-create-info
                      Don't write table creation info.
  -d, --no-data       No row information.
  -R, --routines      Dump stored routines (functions and procedures).
  --set-charset       Add 'SET NAMES default_character_set' to the output.
                      Enabled by default; suppress with --skip-set-charset.
  --tables            Overrides option --databases (-B).
  --triggers          Dump triggers for each dumped table
  -X, --xml           Dump a database as well formed XML

상태 확인

• 테이블 상태 및 lock 여부 확인

show open tables;        //MySQL 전체 테이블 잠금여부
show open tables from 'db명';        //해당 DB의 테이블 잠금 여부
show open tables from 'db명' like '테이블명';    //해당 DB의 특정 테이블의 잠금 정보

• 프로세스 체크

show processlist

• innodb 상태 확인

SHOW ENGINE INNODB STATUS\G

• status 명령

mysqladmin -uhive -p -i1 status
mysqladmin -uhive -p -i1 extended-status