[MySQL] RENAME DATABASE
- -
0.사전환경
OS : Cent 7.7
Database : MySQL 5.7
[root:LEE:/root > cat /etc/redhat-release CentOS Linux release 7.7.1908 (Core) [root:LEE:/root > mysql --version mysql Ver 14.14 Distrib 5.7.33, for linux-glibc2.12 (x86_64) using EditLine wrapper |
1. TEST Database Download & Import
- github에서 테스트 데이터를 다운로드하여 Import합니다. 간단하게 Employees Database만 Import
Github에서 Test 데이터를 받아오기 위해서 git 설치 하였습니다.
git 없이 https://github.com/datacharmer/test_db 해당 URL에서 ZIP파일 다운로드 가능
[root:LEE:/root > yum -y install git [root:LEE:/root > git clone https://github.com/datacharmer/test_db.git Cloning into 'test_db'... remote: Enumerating objects: 116, done. remote: Counting objects: 100% (11/11), done. remote: Compressing objects: 100% (7/7), done. remote: Total 116 (delta 5), reused 8 (delta 4), pack-reused 105 Receiving objects: 100% (116/116), 74.27 MiB | 11.17 MiB/s, done. Resolving deltas: 100% (59/59), done. [root:LEE:/root/test_db > ll total 168340 -rw-r--r--. 1 root root 964 Aug 10 10:34 Changelog -rw-r--r--. 1 root root 4325 Aug 10 10:34 README.md -rw-r--r--. 1 root root 4193 Aug 10 10:34 employees.sql -rw-r--r--. 1 root root 6276 Aug 10 10:34 employees_partitioned.sql -rw-r--r--. 1 root root 7948 Aug 10 10:34 employees_partitioned_5.1.sql drwxr-xr-x. 2 root root 69 Aug 10 10:34 images -rw-r--r--. 1 root root 250 Aug 10 10:34 load_departments.dump -rw-r--r--. 1 root root 14159880 Aug 10 10:34 load_dept_emp.dump -rw-r--r--. 1 root root 1090 Aug 10 10:34 load_dept_manager.dump -rw-r--r--. 1 root root 17722832 Aug 10 10:34 load_employees.dump -rw-r--r--. 1 root root 39806034 Aug 10 10:34 load_salaries1.dump -rw-r--r--. 1 root root 39805981 Aug 10 10:34 load_salaries2.dump -rw-r--r--. 1 root root 39080916 Aug 10 10:34 load_salaries3.dump -rw-r--r--. 1 root root 21708736 Aug 10 10:34 load_titles.dump -rw-r--r--. 1 root root 4568 Aug 10 10:34 objects.sql drwxr-xr-x. 2 root root 77 Aug 10 10:34 sakila -rw-r--r--. 1 root root 272 Aug 10 10:34 show_elapsed.sql -rwxr-xr-x. 1 root root 1800 Aug 10 10:34 sql_test.sh -rw-r--r--. 1 root root 4711 Aug 10 10:34 test_employees_md5.sql -rw-r--r--. 1 root root 4715 Aug 10 10:34 test_employees_sha.sql -rwxr-xr-x. 1 root root 2013 Aug 10 10:34 test_versions.sh [root:LEE:/root/test_db > mysql -uroot -proot < employees.sql mysql: [Warning] Using a password on the command line interface can be insecure. 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:26 |
2. EMPLOYEES DATABASE Object 확인
-RENAME 이후 EMPLOYEES Database와 Object의 개수가 같은지 확인하기 위해 TABLE / INDEX / VIEW 개수 확인
1.TABLE 개수
mysql> select table_schema,count(*) from information_schema.tables where table_schema not in ('mysql','performance_schema','information_schema','sys') group by table_schema;
+--------------+----------+
| table_schema | count(*) |
+--------------+----------+
| employees | 8 |
+--------------+----------+
2.INDEX 개수
mysql> select database_name,count(*) as index_count from mysql.innodb_index_stats where database_name='employees' group by database_name;
+---------------+-------------+
| database_name | index_count |
+---------------+-------------+
| employees | 34 |
+---------------+-------------+
3.VIEW 개수
mysql> select table_schema,count(*) as view_count from information_schema.views where table_schema='employees' group by table_schema;
+--------------+------------+
| table_schema | view_count |
+--------------+------------+
| employees | 2 |
+--------------+------------+
3.Mysqldump Export
-Rename할 Database를 Export 합니다.
mysqldump -uroot -proot
--routines = procedure/function 같이 Export
--triggers = trigger 같이 Export
--single-transaction = dump를 하나의 트랜잭션으로 실행함으로 InnoDB 스토리지 엔진을 사용하는 테이블에 대해서 Lock없이 일관된 덤프를 받을수 있다
[root:LEE:/root > mysqldump -uroot -proot --routines --triggers --single-transaction employees > employees_0810.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. |
4.Rename Database Import
* Rename 대상 데이터베이스가 존재하지 않을시 Error가 발생하기때문에 Import 전에 Database를 생성해준다.
* Rename 대상 데이터베이스를 만들지 않고 Import할시 Error가 발생한다. [root:LEE:/root > mysql -uroot -proot new_employess < employees_0810.sql mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1049 (42000): Unknown database 'new_employess' mysql> create database new_employees; Query OK, 1 row affected (0.01 sec) [root:LEE:/root > mysql -uroot -proot new_employees < employees_0810.sql mysql: [Warning] Using a password on the command line interface can be insecure. |
5.employees vs new_employees 데이터 비교
mysql> select table_schema,count(*) from information_schema.tables where table_schema not in ('mysql','performance_schema','information_schema','sys') group by table_schema;
+---------------+----------+
| table_schema | count(*) |
+---------------+----------+
| employees | 8 |
| new_employees | 8 |
| test | 2 |
+---------------+----------+
3 rows in set (0.01 sec)
mysql> select database_name,count(*) as index_count from mysql.innodb_index_stats where database_name in ('employees','new_employees') group by database_name;
+---------------+-------------+
| database_name | index_count |
+---------------+-------------+
| employees | 34 |
| new_employees | 34 |
+---------------+-------------+
2 rows in set (0.00 sec)
mysql> select table_schema,count(*) as view_count from information_schema.views where table_schema in ('employees','new_employees') group by table_schema;
+---------------+------------+
| table_schema | view_count |
+---------------+------------+
| employees | 2 |
| new_employees | 2 |
+---------------+------------+
2 rows in set (0.01 sec)
'MySQL & Maria' 카테고리의 다른 글
[MySQL] MySQL User 생성 & 권한 부여 (0) | 2021.08.25 |
---|---|
Slow Query 분석 Website (0) | 2021.08.24 |
Query Cache (1) | 2021.08.05 |
MySQLTunner.pl - MySQL 성능 진단 툴 (0) | 2021.08.05 |
Maria DB Binary Install (0) | 2021.08.04 |
소중한 공감 감사합니다