[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)