안녕하세요 이번에는 MariaDB에서 Mysql로 데이터 이관 테스트를 진행해보았습니다.
Mysqldump 유틸리티를 이용했습니다. 호환성이 좋아서 이관에 크게 문제는 없었습니다~
테스트 환경
|
|
DB version |
OS
|
|
AS-IS
|
10.2.32-MariaDB-log
|
Centos 7.3
|
|
TO-BE
|
Mysql 8.0.28
|
Centos 7.3
|
■ As-Is 이관 대상 확인
tuning이란 데이터베이스만 이관 예정
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kim |
| mysql |
| percona_schema |
| performance_schema |
| test |
| tuning |
+--------------------+
## database 크기
MariaDB [(none)]> select table_schema,round(sum(data_length+index_length)/1024/1024,1) from information_schema.tables where table_schema='tuning' group by 1;
+--------------+--------------------------------------------------+
| table_schema | round(sum(data_length+index_length)/1024/1024,1) |
+--------------+--------------------------------------------------+
| tuning | 270.6 |
+--------------+--------------------------------------------------+
## 테이블 count
MariaDB [(none)]> select table_schema,count(*) from information_schema.tables where table_schema='tuning' group by table_schema;
+--------------+----------+
| table_schema | count(*) |
+--------------+----------+
| tuning | 10 |
+--------------+----------+
## Index 수 확인
MariaDB [(none)]> select database_name,count(*) as index_count from mysql.innodb_index_stats where database_name='tuning' group by database_name;
+---------------+-------------+
| database_name | index_count |
+---------------+-------------+
| tuning | 70 |
+---------------+-------------+
## view 수 확인
MariaDB [(none)]> select table_schema, count(*) as view_count from information_schema.views where table_schema='tuning' group by table_schema;
+--------------+------------+
| table_schema | view_count |
+--------------+------------+
| tuning | 2 |
+--------------+------------+
## procedure, function 확인
MariaDB [tuning]> select count(ROUTINE_NAME)procedure_count,ROUTINE_TYPE,routine_schema FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE in ("PROCEDURE","FUNCTION") and routine_schema='tuning' group by ROUTINE_TYPE,routine_schema;
+-----------------+--------------+----------------+
| procedure_count | ROUTINE_TYPE | routine_schema |
+-----------------+--------------+----------------+
| 4 | PROCEDURE | tuning |
+-----------------+--------------+----------------+
## trigger 확인
MariaDB [tuning]> select TRIGGER_SCHEMA,count(*) TRIGGER_count from INFORMATION_SCHEMA.TRIGGERS where trigger_schema='tunning' group by trigger_schema;
+----------------+---------------+
| TRIGGER_SCHEMA | TRIGGER_count |
+----------------+---------------+
| tuning | 2 |
+----------------+---------------+
■ AS-IS backup & backup파일 전송
[root@tunning ~]# mysqldump --routines --triggers --single-transaction -uroot -proot tuning > backup.sql
[root@tunning ~]# scp -rp backup.sql root@101.101.218.225:/root
■ TO-BE 측 database 생성 & import
[root@to-be ~]# chown mysql.mysql backup.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.21 sec)
mysql> create database tuning;
Query OK, 1 row affected (0.02 sec)
## import시 에러 발생.
mysql5.7 이후로는 sql_mode에 no_auto_create_user가 제거되어 에러 발생.
[root@to-be ~]# mysql -uroot -p tuning < backup.sql
Enter password:
ERROR 1231 (42000) at line 4579492: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
※ NO_AUTO_CREATE_USER
인증 정보가 지정되어 있지 않으면 GRANT 문이 자동으로 새 사용자 계정을 작성하지 못하도록합니다 (그렇지 않은 경우). 이 명령문은 IDENTIFIED BY 또는 IDENTIFIED WITH 사용하는 인증 플러그인을 사용하여 비어 있지 않은 암호를 지정해야합니다.
GRANT 아닌 CREATE USER MySQL 계정을 생성하는 것이 바람직합니다. MySQL 5.7.6부터, NO_AUTO_CREATE_USER 는 더 이상 사용되지 않습니다. 5.7.7부터 기본 SQL 모드에는 NO_AUTO_CREATE_USER 포함되며 sql_mode 를 DEFAULT 설정하는 할당을 제외하고 NO_AUTO_CREATE_USER 모드 상태를 변경하는 sql_mode 에 대한 할당이 경고를 생성합니다. NO_AUTO_CREATE_USER 는 향후 MySQL 릴리스에서 제거 될 것이며, 그 시점에서 그 효과는 항상 활성화 될 것입니다 (GRANT 는 계정을 생성하지 않습니다).
## backup파일의 문자 체인지
[root@to-be ~]# sed 's/,NO_AUTO_CREATE_USER//g' -i backup.sql
[root@to-be ~]# sed 's/\sDEFINER=[^]*@[^]*//g' -i backup.sql
## 다시 import하니 다른 에러 발생. table이름이 한글로 되어 에러나는 거 같다. 아래에 해당 view와 procedure는 수동으로 생성해주었습니다.
[root@to-be ~]# mysql -uroot -p tuning < backup.sql
ERROR 1064 (42000) at line 4579501: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@`localhost` PROCEDURE `Get`()
begin
select 사원번호, 시작일자, 종료웧 at line 1
## AS-IS sql_mode설정에서 NO_AUTO_CREATE_USER 제거
MariaDB [tuning]> set global sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
MariaDB [(none)]> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------+
## TO-BE에 metadata만 따로 뽑아서 다시 import
[root@tunning ~]# mysqldump -uroot -proot --no-data --routines tuning > meta.sql
[root@to-be ~]# sed 's/,NO_AUTO_CREATE_USER//g' -i meta.sql
[root@to-be ~]# mysql -uroot -proot tuning < meta.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
■ TO-BE view 수동 생성
import시에 에러나서 view가 덜 들어갔습니다. 해당 view들은 수동으로 as-is와 동일하게 생성하였습니다.
mysql> select TABLE_NAME, TABLE_TYPE from information_schema.tables where table_schema='tuning';
+---------------------+------------+
| TABLE_NAME | TABLE_TYPE |
+---------------------+------------+
| kk | BASE TABLE |
| newview | BASE TABLE | => 이 두개는 view인데 import되며 에러가 발생하면서 데이터가 안들어갔다.
| testview | BASE TABLE | =>
| 급여 | BASE TABLE |
| 부서 | BASE TABLE |
| 부서관리자 | BASE TABLE |
| 부서사원_매핑 | BASE TABLE |
| 사원 | BASE TABLE |
| 사원출입기록 | BASE TABLE |
| 직급 | BASE TABLE |
+---------------------+------------+
## AS-IS 위 두개의 view는 수동으로 넣어줍니다.
MariaDB [tuning]> show create view newview;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| newview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `newview` AS select `부서`.`부서번호` AS `부서번호`,`부서`.`부서명` AS `부서명`,`부서`.`비고` AS `비고` from `부서` | utf8 utf8_general_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.000 sec)
MariaDB [tuning]> show create view testview;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| testview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS select `급여`.`사원번호` AS `사원번호`,`급여`.`연봉` AS `연봉`,`급여`.`시작일자` AS `시작일자`,`급여`.`종료일자` AS `종료일자`,`급여`.`사용여부` AS `사용여부` from `급여` | utf8 | utf8_general_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.000 sec)
## TO-BE
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `newview` AS select `부서`.`부서번호` AS `부서번호`,`부서`.`부서명` AS `부서명`,`부서`.`비고` AS `비고` from `부서`;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS select `급여`.`사원번호` AS `사원번호`,`급여`.`연봉` AS `연봉`,`급여`.`시작일자` AS `시작일자`,`급여`.`종료일자` AS `종료일자`,`급여`.`사용여부` AS `사용여부` from `급여`;
Query OK, 0 rows affected (0.00 sec)
mysql> select TABLE_NAME, TABLE_TYPE from information_schema.tables where table_schema='tuning';
+---------------------+------------+
| TABLE_NAME | TABLE_TYPE |
+---------------------+------------+
| kk | BASE TABLE |
| newview | VIEW |
| testview | VIEW |
| tt | BASE TABLE |
| 급여 | BASE TABLE |
| 부서 | BASE TABLE |
| 부서관리자 | BASE TABLE |
| 부서사원_매핑 | BASE TABLE |
| 사원 | BASE TABLE |
| 사원출입기록 | BASE TABLE |
| 직급 | BASE TABLE |
+---------------------+------------+
■ TO-BE object count 비교
## database 크기
mysql> select table_schema,round(sum(data_length+index_length)/1024/1024,1) from information_schema.tables where table_schema='tuning' group by 1;
+--------------+--------------------------------------------------+
| TABLE_SCHEMA | round(sum(data_length+index_length)/1024/1024,1) |
+--------------+--------------------------------------------------+
| tuning | 279.6 |
+--------------+--------------------------------------------------+
## 테이블 count
mysql> select table_schema,count(*) from information_schema.tables where table_schema='tuning' group by table_schema;
+--------------+----------+
| TABLE_SCHEMA | count(*) |
+--------------+----------+
| tuning | 10 |
+--------------+----------+
## Index 수 확인
mysql> select database_name,count(*) as index_count from mysql.innodb_index_stats where database_name='tuning' group by database_name;
+---------------+-------------+
| database_name | index_count |
+---------------+-------------+
| tuning | 70 |
+---------------+-------------+
## view 수 확인
mysql> select table_schema, count(*) as view_count from information_schema.views where table_schema='tuning' group by table_schema;
+--------------+------------+
| TABLE_SCHEMA | view_count |
+--------------+------------+
| tuning | 2 |
+--------------+------------+
## procedure, function 확인
mysql> select count(ROUTINE_NAME)procedure_count,ROUTINE_TYPE,routine_schema FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE in ("PROCEDURE","FUNCTION") and routine_schema='tuning' group by ROUTINE_TYPE,routine_schema;
+-----------------+--------------+----------------+
| procedure_count | ROUTINE_TYPE | routine_schema |
+-----------------+--------------+----------------+
| 4 | PROCEDURE | tuning |
+-----------------+--------------+----------------+
## trigger 수 확인
mysql> select TRIGGER_SCHEMA,count(*) TRIGGER_count from INFORMATION_SCHEMA.TRIGGERS where trigger_schema='tunning' group by trigger_schema;
+----------------+---------------+
| TRIGGER_SCHEMA | TRIGGER_count |
+----------------+---------------+
| tuning | 2 |
+----------------+---------------+'DataBase > MySQL & MariaDB' 카테고리의 다른 글
| [MySQL - NCP & OCI 이기종 플랫폼] part 2. 무중단 이관 (0) | 2022.07.30 |
|---|---|
| [MySQL - NCP & OCI 이기종 플랫폼] part 1. Replication (0) | 2022.07.30 |
| [MySQL - InnoDB cluster] part 4 Router설치 (0) | 2022.05.14 |
| [MySQL - InnoDB cluster] part 3 복구 테스트 (0) | 2022.05.14 |
| [MySQL - InnoDB cluster] part 2 cluster생성/인스턴스 추가 (0) | 2022.05.14 |