mysqdump를 mariadb로 복원하는 중 28시간이 지나도 완료되지 않음

mysqdump를 mariadb로 복원하는 중 28시간이 지나도 완료되지 않음

[해결됨]

MariaDB 백업을 새 데이터베이스 인스턴스로 복원하려고 했는데 덤프가 약 2.5입니다.GB(비압축, 250MB zip 압축).데이터베이스 덤프는 다음 명령을 사용하여 작성되었으며 준비 환경에서 가져온 것입니다.

mysqldump \
      --defaults-extra-file="${MYSQLDUMP_CONFIG}" \
      --host="${DB_HOST}" \
      --port="${DB_PORT}" \
      --opt \
      --skip-extended-insert \
      --single-transaction \
      --routines \
      "$db" | gzip -c >"${backup_output_file}"

(MYSQLDUMP_CONFIG에는 연결에 필요한 사용자 이름과 암호만 포함되어 있습니다.)

이렇게 생성된 데이터베이스를 mariadb 10.6.7 또는 10.5.5로 복원하려고 하면 적절한 시간 내에 완료되지 않습니다.호기심 때문에, 저는 그것이 완료될 것인지 보기 위해 그것을 실행하도록 시도했지만, 28시간 이상이 지나도 여전히 완료되지 않았습니다.

에는 문의덤프다대스포있습다니에 대한 .voipmonitor이는 약 174개의 테이블, 일부 저장 프로시저, 나머지는 데이터입니다. (스키마만 약 534k이고 나머지는 ~900만 행의 데이터)

로컬 테스트를 위해 다음 도커 합성 구성(prod/staging에서 MariaDB galera 클러스터에 기록)을 사용하여 도커 이미지에서 MariaadDB를 실행하고 있습니다.

---
version: '3.6'
services:
  sql:
    image: mariadb:10.6.7
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: compose
    ports:
      - "3306:3306"
    volumes:
      - "./schemas/sql:/docker-entrypoint-initdb.d"
      - "./mariadb-config:/etc/mysql/conf.d"

또한 이에 전달되는 사용자 지정 구성은 다음과 같습니다(아래 구성 없이 기본 이미지 구성만으로 동일한 동작이 발생함).

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4

key_buffer_size=32M
myisam_recover_options=FORCE,BACKUP
skip_host_cache
skip_name_resolve
max_allowed_packet=128M
max_connect_errors=1000000
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now=1
log_bin_trust_function_creators=1
log_bin=mysql-bin
tmp_table_size=32M
max_heap_table_size=32M
# Re-enabling as now works with Maria 10.1.2
query_cache_type=1
query_cache_limit=4M
query_cache_size=256M
max_connections=500
thread_cache_size=50
open_files_limit=65535
table_definition_cache=4096
table_open_cache=4096

innodb=FORCE
innodb_strict_mode=1
# Mandatory per https://github.com/codership/documentation/issues/25
innodb_autoinc_lock_mode=2
# Per https://www.percona.com/blog/2006/08/04/innodb-double-write/
innodb_doublewrite=1
innodb_flush_method=O_DIRECT
innodb_log_files_in_group=2
innodb_log_file_size=128M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
# 80% Memory is default reco.
# Need to re-evaluate when DB size grows
innodb_buffer_pool_size=2G
innodb_file_format=Barracuda

또한 위 백업과 관련된 문제는 덤프에서 스키마만 추출하면(파일에서 모든 삽입문을 삭제하기만 하면) 스키마를 로드할 수 없다는 것입니다.덤프 파일에 이전에 있었던 대부분의 데이터가 포함된 테이블을 아직 통과하지 못했기 때문에 28시간이 지나도 다음 오류가 가져오기에 나타나지 않습니다.

데이터베이스는 다음을 통해 생성됩니다.

 CREATE DATABASE IF NOT EXISTS `voipmonitor`
      DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

하면 다음▁loading▁(▁piping▁me스▁client▁via▁using키either▁the▁nets(▁the▁schema▁or▁mysqlSOURCE schemafile.sql클라이언트):

ERROR 1005 (HY000): Can't create table `voipmonitor`.`sensors` (errno: 150 "Foreign key constraint is incorrectly formed")

해당하는 CREATE TABLE에는 외부 키 제약 조건이 없습니다.다음을 사용하여 스키마를 로드한 경우SOURCE테이블을 아래 데이터 유형의 단일 열로 줄일 수 있으며 외부 키 제약 조건으로 인해 실패합니다.

DROP TABLE IF EXISTS `sensors`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sensors` (
  `id_sensor` int(10) unsigned NOT NULL,
  `name` varchar(256) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `port` int(11) DEFAULT NULL,
  `remote_mysql_host` varchar(256) DEFAULT NULL,
  `remote_mysql_db` varchar(256) DEFAULT NULL,
  `remote_mysql_user` varchar(256) DEFAULT NULL,
  `remote_mysql_pass` varchar(256) DEFAULT NULL,
  `interface` varchar(20) DEFAULT NULL,
  `tcpdump_folder` varchar(256) DEFAULT NULL,
  `tcpdump_mount_folder` varchar(256) DEFAULT NULL,
  `timezone_name` varchar(64) DEFAULT NULL,
  `timezone_offset` int(11) DEFAULT NULL,
  `timezone_save_at` datetime DEFAULT NULL,
  `cloud_router` tinyint(4) DEFAULT NULL,
  `default_for_active_call` tinyint(4) DEFAULT 1,
  `read_timeout` smallint(5) unsigned DEFAULT NULL,
  `color_background` char(10) DEFAULT NULL,
  `color_chart` char(10) DEFAULT NULL,
  `is_server` tinyint(4) DEFAULT NULL,
  `all_clients_in_active_calls` tinyint(4) DEFAULT NULL,
  `override_international_rules` tinyint(4) DEFAULT NULL,
  `international_prefixes` varchar(256) DEFAULT NULL,
  `international_number_min_length` tinyint(4) DEFAULT NULL,
  `international_number_min_length_prefixes_strict` tinyint(4) DEFAULT NULL,
  `country_code_for_local_numbers` char(5) DEFAULT NULL,
  `enable_check_napa_without_prefix` tinyint(4) DEFAULT NULL,
  `min_length_napa_without_prefix` tinyint(4) DEFAULT NULL,
  `skip_prefixes` varchar(256) DEFAULT NULL,
  `override_country_prefixes` tinyint(4) DEFAULT NULL,
  `auto_upgrade` tinyint(4) DEFAULT NULL,
  `auto_upgrade_at` time DEFAULT NULL,
  `auto_upgrade_week_days` char(20) DEFAULT NULL,
  `auto_upgrade_email` varchar(256) DEFAULT NULL,
  `auto_upgrade_enable_beta` tinyint(4) DEFAULT NULL,
  `auto_upgrade_last_run_at` datetime DEFAULT NULL,
  `disable` tinyint(4) DEFAULT NULL,
  `local_spool` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id_sensor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

재미있는 사실은, 위의 표를 사용하여 데이터베이스를 만들고 위의 표만 만들면 잘 작동합니다.그러나 데이터베이스의 전체 스키마에서는 위의 내용과 맞지 않습니다.

소스를 사용하여 스키마를 로드하려고 시도한 후 오류/경고가 표시되면 이를 실행할 수 있지만 동일한 오류가 표시됩니다.

mysql> CREATE TABLE `sensors` (`id_sensor` int(10) unsigned NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ERROR 1005 (HY000): Can't create table `voipmonitor`.`sensors` (errno: 150 "Foreign key constraint is incorrectly formed")

스키마 파일 또는 MariaDB의 무언가가 실패를 야기한다는 것을 의미합니다(이 이전에 정확히 동일한 양식의 테이블 만들기 명령이 149개 있습니다).sensors표)를 선택합니다.

저는 이 두 가지 문제를 해결하기 위해 여러 가지 방법을 시도해 보았지만, 데이터베이스 스키마를 로드할 때마다 깨지지 않는 것은 데이터베이스 로드 명령의 성능을 처리하는 것을 매우 어렵게 만듭니다.

수정에 대한 어떤 제안이나 추천도 좋을 것 같습니다.

편집 #1: 로컬 도커 이미지 테스트 복원에 사용할 수 있는 메모리를 8G로 늘렸고,max_allowed_packet512M까지, 그리고innodb_buffer_pool_size6G까지.

지금까지는 SQL 덤프를 mysql에 파이프로 연결할 때 이 작업이 조금 더 잘 수행되는 것으로 보입니다.그리고 2.5시간 후에 외부 키 문제를 처리합니다.

편집 #2:에 대한 2개의 외부 키 참조가 있습니다.sensors테이블 나중에 로드될 것으로 예상했지만 실제로는 복원 중에 "보류 중" 응용 프로그램이 먼저 로드되어 있습니다.이러한 외부 키 참조는 다음을 사용하여 열 정의에 생성되었습니다.int(11)그들이 참조하고 있는 테이블 열은 유형입니다.int(10) unsigned저는 고장난 외국인 키 참조를 제거했고 나중에 처리할 것입니다.

#3 편집: 스키마에는 참조 중인 테이블에 더 이상 존재하지 않는 열에 대한 외래 키 참조를 정확히 파악하는 재미가 포함되어 있습니다.

스키마의 이전 버전은 다음과 같습니다(예외, 생략할 필요가 없는 자료).

CREATE TABLE `billing_customer_assignment_sensors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_customer_assignment` int(11) NOT NULL,
  `id_sensor` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk__billing_customer_assignment_sensors` (`id_customer_assignment`),
  KEY `fk__billing_customer_assignment_sensors2` (`id_sensor`),
  CONSTRAINT `fk__billing_customer_assignment_sensors` FOREIGN KEY (`id_customer_assignment`) REFERENCES `billing_customer_assignment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk__billing_customer_assignment_sensors2` FOREIGN KEY (`id_sensor`) REFERENCES `sensors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sensors` (
  `id_sensor` int(10) unsigned NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,

그래서 우리가 여기서 볼 수 있는 것은 원래 Foreign Key 참조가 있었다는 것입니다.id열, 그러나 해당 열은 테이블에서 제거되었지만 해당 열에 대한 외부 키는 여전히 존재합니다(누군가가 이 작업을 시도했다면 심각한 오류가 발생할 것으로 예상했습니다).

편집 #4: 위의 모든 편집 후 문제가 해결되고 MariaDB를 조정하여 성능이 향상된 경우 로컬 도커 이미지 가져오기가 3시간 미만으로 완료되어 환경의 실제 데이터베이스 서버에 보다 성능 친화적인 내보내기가 가능해졌습니다.

편집 #5: 업데이트 진행, 22분 만에 전체 DB가 복원됩니다.

언급URL : https://stackoverflow.com/questions/71932491/restoring-mysqldump-into-mariadb-still-not-complete-after-28-hrs