MariaDB 백업 방법

MariaDB 데이터베이스를 백업하는 방법 중 두 가지 자주 사용하는 백업 방법을 알아봅니다.

1. mysqldump 명령어를 이용한 백업 방법

• mysqldump 명령어를 사용하면 MariaDB 데이터베이스의 백업 파일을 생성할 수 있습니다.
• 다음 명령어를 터미널 또는 명령 프롬프트에서 실행합니다.

# mysqldump -u 사용자이름 -p 데이터베이스이름 > 백업파일.sql

• 사용자이름은 MariaDB에 접속 가능한 계정
• 데이터베이스이름은 백업하려는 데이터베이스이름

1-1. DB 전체 백업

# mysqldump -u 사용자이름 -p -A > full_backup.sql
# mysqldump -u 사용자이름 -p --all-databases > full_backup.sql

1-2. 특정 DB만 백업

# mysqldump -u 사용자이름 -p --databases samsodb > samsodb.sql

• --databases 옵션을 넣어야 CREATE DATABASE 명령어가 포함된다.

1-3. 특정 DB의 특정 테이블만 백업

# mysqldump -u 사용자이름 -p samsodb tb_user > tb_user.sql

2-1. 파일 복사를 통한 백업

• MariaDB 데이터베이스의 데이터 파일을 직접 복사하여 백업하는 방법입니다.
• MariaDB의 데이터 파일은 기본 설치 시 (/var/lib/mysql or /var/mysql 등) 에 저장됩니다.
  [DB/Mysql(MariaDB)] - Linux - MariaDB 설치 (컴파일 / yum) 이 글을 보고 컴파일 설치 했다면?  /SAMSODIR/MariaDB/data 경로가 됩니다.
• 백업을 원하는 데이터베이스 디렉토리를 복사하면 그 파일이 백업 데이터가 됩니다.

MariaDB 복구 방법

MariaDB 데이터베이스를 복구하는 방법 중 위의 두 가지 백업을 통한 복구 방법을 알아봅니다.

1. mysqldump 명령어를 이용한 백업 파일 복구 방법

• mysqldump 백업한 SQL 파일을 사용하여 데이터베이스를 복구할 수 있습니다.
• 다음 명령어를 터미널 또는 명령 프롬프트에서 실행합니다.

# mysql -u 사용자이름 -p 데이터베이스이름 < 백업파일.sql

1-1. DB 전체 복구

# mysql -u 사용자이름 -p < 백업파일.sql

1-2. 특정 DB만 복구 / 특정 DB의 특정 테이블 복구

# mysql -u 사용자이름 -p 데이터베이스이름 < 백업파일.sql

1-3. Source 명령어를 통한 복구

# mysql -u 사용자이름 -p
MariaDB [(none)]> source samsodb.sql;
MariaDB [(none)]> source /root/tb_user.sql;

• MariaDB에 계정 접속한 뒤 source 명령어를 통해 복구할 수 있다.
• MariaDB에 접속한 경로에 samsodb.sql이 있으면 복구되지만 그 경로에 없으면 절대경로 입력!

2-1. 파일 복사를 통한 복구

• 파일 복사를 통해 백업한 데이터 디렉토리를 원래 위치로 복사하여 데이터베이스를 복구합니다.
• 복사한 데이터 디렉토리를 MariaDB의 데이터 디렉토리에 덮어씌웁니다.
• 이후 MariaDB를 재시작하면 데이터베이스가 복구됩니다.

MariaDB 백업 스크립트 / 배치파일

1. MariaDB 백업 스크립트

#!/bin/sh
LANG=ko_KR.UTF-8
export LANG
yy=`date +%Y`
dd=`date +%d`
mm=`date +%m`
FSVR=${yy}${mm}${dd}
FSVR1=`date +%Y%m%d --date '1 days ago'`
BACKUPDIR="/DB_BACKUP"
USER="root"
PASSWORD="samso"
MYSQLDUMP="/SAMSODIR/MariaDB/bin/mysqldump"
DB=samsodb
$MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --master-data=2 --single-transaction --databases $DB --ignore-table=argo.t_report_action_manage_history --ignore-table=argo.t_report_history --ignore-table=argo.t_report_history_chunk --ignore-table=argo.t_token_buy_fail_history --ignore-table=argo.t_token_use_history  > ${BACKUPDIR}/${FSVR}_argo.dump

#tar zcf ${BACKUPDIR}/${FSVR1}_argo.dump.tar ${BACKUPDIR}/${FSVR1}_samsodb.dump
gzip ${BACKUPDIR}/${FSVR1}_samsodb.dump

find ${BACKUPDIR} -name "*.dump.gz" -mtime +7 -exec rm -f {} \;

2. MariaDB 백업 배치파일

@echo off

set TIME2=%TIME: =0%

set TIME3=%TIME2:~0,2%%TIME2:~3,2%

C:\MariaDB\bin\mysqldump -uroot -psamso --master-data=2 --single-transaction --all-databases > C:\DBBACKUP\%DATE%_%TIME3%_full.sql


forfiles /P "C:\DBBACKUP" /M *.sql /D -7 /C "cmd /c del @file"

위의 백업 스크립트 / 배치에 사용된 dump 옵션

• --single-transaction : 
  dump를 하나의 트랜잭션을 이용해서 실행함
  (InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 Lock 없이 일관된 덤프를 받는다)
• --master-data : 
  이 옵션이 명시되면, dump 파일의 헤더 부분에 CHANGE MASTER TO 구문을 포함시키며,
  이 구문에는 덤프 시작 시점의 Binary log 파일명과 위치 정보 및 호스트 정보를 포함하고 있다.
  이 값을 1로 설정하면 CHANGE MASTER TO 구문이 실제 실행 가능한 형태로 포함되며,
  2로 설정되면 SQL 코멘트 형태로 참조만 할 수 있도록 포함된다.
  
  이 옵션은 Replication 설정이 되어 서버 이중화가 되어 있는 경우 slave 복구용으로 사용

서버 업데이트 후 재부팅하는 과정에서 Mysql이 구동되지 않았다.

$ service mysql status

명령어를 통해 상태를 확인해보니 아래와 같았다.

● mysql.service - LSB: Start and stop the mysql database server daemon           

  Loaded: loaded (/etc/init.d/mysql)                                                            

  Active: failed (Result: exit-code) since Thu 2018-02-22 08:28:48 KST; 7s ago       

  Process: 2693 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)

  Process: 2761 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE) 

mysql error log를 확인해 보자.

$ cd /var/log/mysql

$ vi error.log         

맨 아래에 보니 다음과 같은 에러가 있었다.

mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/libexec/mysqld: Incorrect file format 'plugin'
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: Initializing buffer pool, size = 8.0M
InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Started; log sequence number 0 2248162432
[ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'

구글링을 통해 다음과 같이 해결했다.

1. MySQL config 파일을 수정한다. 경로는

CentOS/RHEL/CloudLinux  : /etc/my.cnf 
Ubuntu/Debian               :/etc/mysql/my.cnf

파일을 열면 [mysqld] section에서 skip-grant-tables 를 추가한다.

ex.

2. Mysql 을 재시작한다.

$ service mysql restart