MariaDB/MySQL 뷰 백업/복원 가이드

MariaDB/MySQL 물리적인 백업 (파일 시스템 백업)

백업 절차

DB 서버 중지
systemctl stop mariadb

데이터베이스 파일 복사
cp -av /var/lib/mysql/* /your/backup/directory

 

복원 절차

DB 서버 중지
systemctl stop mariadb

데이터베이스 파일 정리
rm -rf /var/lib/mysql/*

백업 파일 복사
cp -av /your/backup/directory/* /var/lib/mysql

 

MariaDB/MySQL 논리적인 백업 (온라인 백업)

ㅡ mysqldump 백업 툴 사용

백업 절차

특정 DB 백업
mysqldump -u 계정 -p dbname > filename.sql

모든 DB 백업
mysqldump -u root -p --all-databases > filename.sql

특정 테이블 백업
mysqldump -u root -p dbname table_name > filename.sql

여러 테이블 백업
mysqldump -u root -p dbname --tables table_name_1 table_name_2 ... > filename.sql

여러 DB 백업
mysqdump -u root -p -B dbname1 dbname2 ... > filename.sql

테이블 구조만 백업
mysqldump -u root -p --no-data dbname > filename.sql

복원 절차

MySQL 서버 접속
mysql -u root -p

DB 선택
USE dbname;

백업 파일 적용
SOURCE /your/path/filename.sql;

 

물리적인 백업 실습

[root@localhost mysql]# 백업 받을 디렉터리 생성
bash: 백업: command not found...
[root@localhost mysql]# mkdir /var/db_backup
[root@localhost mysql]# cp -a /var/lib/mysql/* /var/db_backup/
[root@localhost mysql]# ls
aria_log.00000001  example         ib_logfile0        mydb                performance_schema
aria_log_control   haksa           ibdata1            mysql               sample
employees          ib_buffer_pool  multi-master.info  mysql_upgrade_info  test
[root@localhost mysql]# ls /var/db_backup/
aria_log.00000001  example         ib_logfile0        mydb                performance_schema
aria_log_control   haksa           ibdata1            mysql               sample
employees          ib_buffer_pool  multi-master.info  mysql_upgrade_info  test
[root@localhost mysql]# rm -rf /var/lib/mysql/*
[root@localhost mysql]# ls /var/lib/mysql/*
ls: cannot access '/var/lib/mysql/*': 그런 파일이나 디렉터리가 없습니다
[root@localhost mysql]# ls /var/lib/mysql
[root@localhost mysql]# mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@localhost mysql]# systemctl start mariadb
[root@localhost mysql]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> exit
Bye
[root@localhost mysql]# systemctl stop mariadb
[root@localhost mysql]# rm -rf /var/lib/mysql/*
[root@localhost mysql]# cp -a /var/db_backup/* /var/lib/mysql
[root@localhost mysql]# ls /var/lib/mysql
aria_log.00000001  example         ib_logfile0        mydb                performance_schema
aria_log_control   haksa           ibdata1            mysql               sample
employees          ib_buffer_pool  multi-master.info  mysql_upgrade_info  test
[root@localhost mysql]# systemctl start mariadb
[root@localhost mysql]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| example            |
| haksa              |
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sample             |
| test               |
+--------------------+
9 rows in set (0.003 sec)

 

이게 정석이다!!

논리적인 모든 db 백업

newdb.sql

rm -rf /var/lib/mysql/*

로그인 안된다.

systemctl stop

rm -rf /var/lib/mysql/*

systemctl start

로그인 하면 된다.