9. Mysql
8. [MySQL] mysql 데이터샘플 밀어 넣기 & 백업복구
미니대왕님
2020. 4. 18. 12:43
7. [MySQL] MySQL 리플리케이션 UUID 관련 에러
8. [MySQL] mysql 데이터샘플 밀어 넣기 & 백업복구
9. [MySQL]Workbench & DA# 모델링 그리기
mysql data sample (mysql 데이터샘플) 만들어 보자
1. 공식사이트
https://github.com/datacharmer/test_db에서 내려받을 수 있고 아래와 같이 간단하게 import 할 수 있다.
2. 아래와 같이 실행해 봅니다.
[root@192 testdata]# wget https://github.com/datacharmer/test_db.git
--2020-04-16 04:27:08-- https://github.com/datacharmer/test_db.git
Resolving github.com (github.com)... 52.78.231.108
Connecting to github.com (github.com)|52.78.231.108|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://github.com/datacharmer/test_db [following]
--2020-04-16 04:27:09-- https://github.com/datacharmer/test_db
Reusing existing connection to github.com:443.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: ‘test_db.git’
[ <=> ] 111,403 --.-K/s in 0.01s
2020-04-16 04:27:09 (9.01 MB/s) - ‘test_db.git’ saved [111403]
3. 깃허브에서 필요 데이터 다운받기
[root@192 testdata]# git clone https://github.com/datacharmer/test_db.git
-bash: git: command not found
[root@192 testdata]# yum install clone
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.kaist.ac.kr
* extras: ftp.kaist.ac.kr
* updates: ftp.kaist.ac.kr
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
mysql-connectors-community | 2.5 kB 00:00:00
mysql-tools-community | 2.5 kB 00:00:00
mysql57-community | 2.5 kB 00:00:00
updates | 2.9 kB 00:00:00
No package clone available.
Error: Nothing to do
[root@192 testdata]# yum install git
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: ftp.kaist.ac.kr
* extras: ftp.kaist.ac.kr
* updates: ftp.kaist.ac.kr
Resolving Dependencies
--> Running transaction check
---> Package git.x86_64 0:1.8.3.1-21.el7_7 will be installed
--> Processing Dependency: perl-Git = 1.8.3.1-21.el7_7 for package: git-1.8.3.1-21.el7_7.x86_64
--> Processing Dependency: rsync for package: git-1.8.3.1-21.el7_7.x86_64
--> Processing Dependency: perl(Term::ReadKey) for package: git-1.8.3.1-21.el7_7.x86_64
--> Processing Dependency: perl(Git) for package: git-1.8.3.1-21.el7_7.x86_64
--> Processing Dependency: perl(Error) for package: git-1.8.3.1-21.el7_7.x86_64
--> Running transaction check
---> Package perl-Error.noarch 1:0.17020-2.el7 will be installed
---> Package perl-Git.noarch 0:1.8.3.1-21.el7_7 will be installed
---> Package perl-TermReadKey.x86_64 0:2.30-20.el7 will be installed
---> Package rsync.x86_64 0:3.1.2-6.el7_6.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
====================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================
Installing:
git x86_64 1.8.3.1-21.el7_7 updates 4.4 M
4. employees.sql 데이터 Table 밀어 넣기
[root@192 test_db]# mysql -u root -p < employees.sql
Enter password:
[root@192 test_db]# mysql -u root -p < employees_partitioned.sql
Enter password:
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:57
[root@192 test_db]# mysql -uroot -p -D employees
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
5. 백업하기
1) DB별로 백업
| mysqldump -u root -p DB명 > 파일명.sql |
2) 전체 백업
| mysqldump -u root -p –all-databases > 파일명.sql |
3) 캐릭터셋 옵션을 이용하여 백업
| mysqldump -u root -p –default-character-set=euckr DB명 > 파일명.sql (euckr, utf8 등이 있음) |
4) 특정 테이블만 덤프
| mysqldump -u root -p DB명 테이블명 > 파일명.sql |
mysqldump -u root -p -B DB명 –tables 테이블명1 테이블명2 테이블명3 > 파일명.sql
5) 테이블 구조만 백업
| mysqldump -u root -p –no-data DB명 > 파일명.sql |
6) XML 파일로 백업
| mysqldump -u root -p –xml DB명 > 파일명.sql |
6. 복구 하기
1) DB별로 복구
| mysql -u root -p DB명 < 파일명.sql |
2) 전체 복구
| mysql -u root -p < 파일명.sql |
3) 캐릭터셋 옵션을 이용하여 백업 복구
| mysqldump -u root -p –default-character-set=euckr DB명 < 파일명.sql (euckr, utf8 등이 있음) |
mysqldump로 DB 백업시 아래와 같은 오류 메세지가 발생하며 중단되는 경우가 있습니다.
- mysqldump: got errer : 1146 : table '테이블명' doesn't exist when using LOCK TABLES
원인 : 해당 table에 lock이 걸려 있어 덤프가 안되는 문제 입니다.
조치 : mysqldump에 --lock-all-table 옵션을 추가 하여 진행 하면 정상적으로 백업 됩니다.
Ex) mysqldump -u [User] -p --lock-all-tables [DBName] > [dump file name].sql
4) 특정 테이블 복구
[root@localhost ~]# mysql -u root -p < 2200821.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p ccc tablename > 2020088210152.sql
Enter password:
[root@localhost ~]# mysql -u root -p ccc < 2020088210152.sql
Enter password:
[root@localhost ~]#
mysql> use ccc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_ccc |
+---------------+
| tablename |
+---------------+
1 row in set (0.00 sec)
mysql> drop table tablename;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_ccc |
+---------------+
| tablename |
+---------------+
1 row in set (0.00 sec)
mysql>
7. [MySQL] MySQL 리플리케이션 UUID 관련 에러
9. [MySQL]Workbench & DA# 모델링 그리기