MySQL myloader remap table – …::: César DBA :::…

A dica de hoje é bem simples, geralmente precisamos atualizar, criar ou copiar uma tabela com um dump lógico. Neste exemplo irei apresentar como realizar o importe remap criando uma nova tabela com dados estrutura e objetos, esta dica pode ser usada no local e na nuvem.

Irei gerar o dump da tabela cadimob_inss localizada no database fin, para ser importado com o nome cadimob_inss_bkp, neste exemplo estou utilizando 8 threads no paralelismo, gravando o dump no diretório /dados/backups/tmp/ e os logs para validação no /dados/backups/logs/:

[root@mysql-hml tmp]# mydumper --user='angelo_carvalho' --password='12345678' --host=192.168.0.1 --database=fin --tables-list=fin.cadimob_inss --rows=10000  --triggers --events --routines --threads=8 --no-locks --verbose=3 --outputdir=/dados/backups/tmp/ --logfile /dados/backups/logs/mydumper_$(date +%Y%m%d).log

Logs da geração do dump:

[root@mysql-hml logs]# cat mydumper_20240315.log
2024-03-15 07:37:10 [INFO] - MyDumper backup version: 0.16.1-2
2024-03-15 07:37:10 [INFO] - Using 8 dumper threads
2024-03-15 07:37:10 [INFO] - Connection via default library settings using password:
        Host: 192.168.0.1
        User: angelo_carvalho
2024-03-15 07:37:10 [INFO] - Connected to MySQL 5.7.44
2024-03-15 07:37:10 [INFO] - Started dump at: 2024-03-15 07:37:10
2024-03-15 07:37:10 [WARNING] - Executing in no-locks mode, snapshot might not be consistent
2024-03-15 07:37:10 [INFO] - conf created
2024-03-15 07:37:10 [INFO] - End job creation
2024-03-15 07:37:10 [INFO] - Creating workers
2024-03-15 07:37:10 [INFO] - Starting Non-InnoDB tables
2024-03-15 07:37:10 [INFO] - Thread 1: connected using MySQL connection ID 41542
2024-03-15 07:37:10 [DEBUG] - Thread 1: Start transaction #0
2024-03-15 07:37:10 [INFO] - Thread 7: connected using MySQL connection ID 41548
2024-03-15 07:37:10 [INFO] - Thread 4: connected using MySQL connection ID 41541
2024-03-15 07:37:10 [INFO] - Thread 3: connected using MySQL connection ID 41545
2024-03-15 07:37:10 [INFO] - Thread 6: connected using MySQL connection ID 41544
2024-03-15 07:37:10 [DEBUG] - Thread 4: Start transaction #0
2024-03-15 07:37:10 [DEBUG] - Thread 7: Start transaction #0
2024-03-15 07:37:10 [DEBUG] - Thread 6: Start transaction #0
2024-03-15 07:37:10 [DEBUG] - Thread 3: Start transaction #0
2024-03-15 07:37:10 [INFO] - Thread 2: connected using MySQL connection ID 41546
2024-03-15 07:37:10 [INFO] - Thread 5: connected using MySQL connection ID 41547
2024-03-15 07:37:10 [INFO] - Thread 8: connected using MySQL connection ID 41543
2024-03-15 07:37:10 [DEBUG] - Thread 5: Start transaction #0
2024-03-15 07:37:10 [DEBUG] - Thread 8: Start transaction #0
2024-03-15 07:37:10 [DEBUG] - Thread 2: Start transaction #0
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [INFO] - Thread 5: Creating Jobs
2024-03-15 07:37:10 [INFO] - Thread 3: Creating Jobs
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [INFO] - Thread 3: dumping db information for `fin`
2024-03-15 07:37:10 [INFO] - Thread 7: Creating Jobs
2024-03-15 07:37:10 [INFO] - Thread 1: Creating Jobs
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [INFO] - Thread 4: Creating Jobs
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [INFO] - Thread 8: Creating Jobs
2024-03-15 07:37:10 [INFO] - Thread 2: Creating Jobs
2024-03-15 07:37:10 [WARNING] - We are not able to determine if the backup will be consistent.
2024-03-15 07:37:10 [INFO] - Thread 6: Creating Jobs
2024-03-15 07:37:10 [INFO] - Waiting database finish
2024-03-15 07:37:11 [INFO] - Thread 1: Schema queue
2024-03-15 07:37:11 [INFO] - Thread 1: dumping schema create for `fin`
2024-03-15 07:37:11 [INFO] - Thread 8: Schema queue
2024-03-15 07:37:11 [INFO] - Thread 8: dumping schema for `fin`.`cadimob_inss`
2024-03-15 07:37:11 [INFO] - Thread 7: Schema queue
2024-03-15 07:37:11 [INFO] - Thread 3: Schema queue
2024-03-15 07:37:11 [INFO] - Thread 6: Schema queue
2024-03-15 07:37:11 [INFO] - Thread 5: Schema queue
2024-03-15 07:37:11 [INFO] - Thread 4: Schema queue
2024-03-15 07:37:11 [INFO] - Thread 2: Schema queue
2024-03-15 07:37:11 [INFO] - Shutdown jobs for less locking enqueued
2024-03-15 07:37:11 [INFO] - Thread 1: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Thread 7: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Thread 5: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Thread 4: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Non-InnoDB tables completed
2024-03-15 07:37:11 [INFO] - Thread 8: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Thread 2: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Thread 8: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - Starting InnoDB tables
2024-03-15 07:37:11 [INFO] - Thread 5: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - Thread 1: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - Thread 3: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Thread 2: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - Thread 7: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - Thread 6: Schema Done, Starting Non-Innodb
2024-03-15 07:37:11 [INFO] - Thread 6: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - Thread 3: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - fin.cadimob_inss has ~237878 rows
2024-03-15 07:37:11 [INFO] - Thread 4: Non-Innodb Done, Starting Innodb
2024-03-15 07:37:11 [INFO] - InnoDB tables completed
2024-03-15 07:37:11 [INFO] - Thread 7: shutting down
2024-03-15 07:37:11 [INFO] - Thread 1: shutting down
2024-03-15 07:37:11 [INFO] - Thread 8: shutting down
2024-03-15 07:37:11 [INFO] - Waiting threads to complete
2024-03-15 07:37:11 [INFO] - Thread 3: shutting down
2024-03-15 07:37:11 [INFO] - Thread 5: shutting down
2024-03-15 07:37:11 [INFO] - Thread 2: dumping data for `fin`.`cadimob_inss`          into /dados/backups/tmp/fin.cadimob_inss.00000.sql| Remaining jobs in this table: 1 All remaining jobs: 0
2024-03-15 07:37:11 [INFO] - Thread 6: shutting down
2024-03-15 07:37:11 [INFO] - Thread 4: shutting down
2024-03-15 07:37:12 [INFO] - Thread 2: shutting down
2024-03-15 07:37:12 [INFO] - Queue count: 0 0 0 0 0
2024-03-15 07:37:12 [INFO] - Main connection closed
2024-03-15 07:37:12 [INFO] - Finished dump at: 2024-03-15 07:37:12
2024-03-15 07:37:12 [INFO] - This is NOT a consistent backup.

Arquivos gerados do backup:

[root@mysql-hml tmp]# ls -lat
total 127440
drwxr-xr-x  2 root  root        135 Mar 15 07:37 .
-rw-r--r--  1 root  root        569 Mar 15 07:37 metadata
-rw-r-----  1 root  root  130479405 Mar 15 07:37 fin.cadimob_inss.00000.sql
-rw-r-----  1 root  root       5447 Mar 15 07:37 fin.cadimob_inss-schema.sql
-rw-r-----  1 root  root        363 Mar 15 07:37 fin-schema-create.sql
drwxr-xr-x. 8 mysql mysql       102 Mar 14 15:13 ..

Realizei o importe com o backup da tabela cadimob_inss criando uma nova tabela com os dados e objetos chamada cadimob_inss_bkp, abaixo os passos para realizar o procedimento do importe:

Fiz a mudança do nome da tabela cadimob_inss para cadimob_inss_bkp no conteúdo dos scripts com extensão .sql:

[root@mysql-hml ]# cd /dados/backups/tmp/
[root@mysql-hml tmp]# sed -i 's/cadimob_inss/cadimob_inss_bkp/g' /dados/backups/tmp/*.sql*

Troquei em todos os scripts .sql o nome da tabela cadimob_inss para cadimob_inss_bkp:

[root@mysql-hml tmp]# mv fin.cadimob_inss-schema.sql fin.cadimob_inss_bkp-schema.sql
[root@mysql-hml tmp]# mv fin.cadimob_inss.00000.sql fin.cadimob_inss_bkp.00000.sql

Importei a nova tabela cadimob_inss_bkp depois de todos os ajustes que realizei:

[root@mysql-hml tmp]# myloader --user='angelo_carvalho' --password='12345678' --host=192.168.0.1 --database=fin --tables-list=fin.cadimob_inss_bkp --rows=10000 --compress-protocol --threads=8 --verbose=3 --directory=/dados/backups/tmp/ -e --logfile /dados/backups/logs/myloader_$(date +%Y%m%d).log

Logs do importe que realizei da tabela cadimob_inss_bkp:

[root@mysql-hml logs]# cat myloader_20240315.log
2024-03-15 07:39:44 [INFO] - Using 8 loader threads
2024-03-15 07:39:44 [INFO] - Connection via default library settings using password:
        Host: 192.168.0.1
        User: angelo_carvalho
2024-03-15 07:39:44 [INFO] - Initializing initialize_worker_schema
2024-03-15 07:39:44 [WARNING] - zstd command not found on any static location, use --exec-per-thread for non default locations
2024-03-15 07:39:44 [INFO] - S-Thread 10: Starting import
2024-03-15 07:39:44 [INFO] - S-Thread 9: Starting import
2024-03-15 07:39:44 [INFO] - S-Thread 12: Starting import
2024-03-15 07:39:44 [INFO] - S-Thread 11: Starting import
2024-03-15 07:39:44 [INFO] - Reading metadata: metadata
2024-03-15 07:39:44 [INFO] - Intermediate queue: Sending END job
2024-03-15 07:39:44 [INFO] - Intermediate thread ended
2024-03-15 07:39:44 [INFO] - Intermediate thread: SHUTDOWN
2024-03-15 07:39:44 [INFO] - Thread 9: restoring table fin.cadimob_inss_bkp from /dados/backups/tmp/fin.cadimob_inss_bkp-schema.sql
2024-03-15 07:39:44 [INFO] - Thread 9: Creating table fin.cadimob_inss_bkp from content in /dados/backups/tmp/fin.cadimob_inss_bkp-schema.sql. On db: fin
2024-03-15 07:39:44 [INFO] - Schema creation enqueing completed
2024-03-15 07:39:44 [INFO] - Table creation enqueing completed
2024-03-15 07:39:44 [INFO] - S-Thread 10: Import completed
2024-03-15 07:39:44 [INFO] - S-Thread 11: Import completed
2024-03-15 07:39:44 [INFO] - S-Thread 12: Import completed
2024-03-15 07:39:44 [INFO] - Thread 9: Table fin.cadimob_inss_bkp created. Tables that pass created stage: 1 of 2
2024-03-15 07:39:44 [INFO] - S-Thread 9: Import completed
2024-03-15 07:39:44 [INFO] - Thread 1: Data import ended
2024-03-15 07:39:44 [INFO] - Thread 7: restoring fin.cadimob_inss_bkp part 1 of 1 from fin.cadimob_inss_bkp.00000.sql. Progress 1 of 1. Tables 1 of 2 completed
2024-03-15 07:39:44 [INFO] - Thread 6: Data import ended
2024-03-15 07:39:44 [INFO] - Thread 3: Data import ended
2024-03-15 07:39:44 [INFO] - Thread 2: Data import ended
2024-03-15 07:39:44 [INFO] - Thread 5: Data import ended
2024-03-15 07:39:44 [INFO] - Thread 4: Data import ended
2024-03-15 07:39:44 [INFO] - Thread 8: Data import ended
2024-03-15 07:39:58 [INFO] - Thread 7: Data import ended
2024-03-15 07:39:58 [DEBUG] - I-Thread 16: ending
2024-03-15 07:39:58 [DEBUG] - I-Thread 15: ending
2024-03-15 07:39:58 [DEBUG] - I-Thread 13: ending
2024-03-15 07:39:58 [DEBUG] - I-Thread 14: ending
2024-03-15 07:39:58 [INFO] - Thread 17: Starting post import task over table
2024-03-15 07:39:58 [DEBUG] - Thread 17: ending
2024-03-15 07:39:58 [INFO] - Errors found:
- Tablespace:   0
- Schema:       0
- Data:         0
- View:         0
- Sequence:     0
- Index:        0
- Trigger:      0
- Constraint:   0
- Post:         0
Retries:        0

Tabela cadimob_inss_bkp criada com sucesso: