CREATE DATABASE lab_migracao;
USE lab_migracao;

-- Tabela InnoDB (padrão)
CREATE TABLE clientes_innodb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100),
    email VARCHAR(100),
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Tabela MyISAM (legado)
CREATE TABLE pedidos_myisam (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT,
    valor DECIMAL(10,2),
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;

-- Tabela Aria (para teste de incompatibilidade)
CREATE TABLE aria_origem (
    id INT AUTO_INCREMENT PRIMARY KEY,
    descricao VARCHAR(100),
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=Aria;

-- Tabela Aria (será tratada no dump)
CREATE TABLE aria_dump (
    id INT AUTO_INCREMENT PRIMARY KEY,
    descricao VARCHAR(100),
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=Aria;

-- Tabela de log
CREATE TABLE log_clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT,
    acao VARCHAR(50),
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO clientes_innodb (nome, email) VALUES
('Yan', 'yan@email.com'),
('Maria', 'maria@email.com'),
('Joao', 'joao@email.com');

INSERT INTO pedidos_myisam (cliente_id, valor) VALUES
(1, 100.50),
(2, 200.75),
(3, 300.00);

INSERT INTO aria_origem (descricao) VALUES
('Teste Aria 1'),
('Teste Aria 2');

INSERT INTO aria_dump (descricao) VALUES
('Dump Aria 1'),
('Dump Aria 2');

DELIMITER $$

CREATE DEFINER=`root`@`localhost` TRIGGER trg_after_insert_clientes
AFTER INSERT ON clientes_innodb
FOR EACH ROW
BEGIN
    INSERT INTO log_clientes (cliente_id, acao)
    VALUES (NEW.id, 'INSERT');
END$$

DELIMITER ;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE sp_lista_clientes()
BEGIN
    SELECT * FROM clientes_innodb;
END$$

DELIMITER ;

-- Ver engines
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'lab_migracao';

-- Ver procedures
SHOW PROCEDURE STATUS WHERE Db = 'lab_migracao';

-- Ver triggers
SHOW TRIGGERS FROM lab_migracao;

  

#02_inventory_assessment_html.sh
#!/bin/bash

# ============================================================
# Script: 02_inventory_assessment_html.sh
# Objetivo: Gerar inventário e assessment HTML do MariaDB
# Ambiente: MariaDB 10.11.x
# ============================================================

set -u

MYSQL_BIN="/usr/local/mysql/bin/mysql"
MYSQL_USER="migration_user"
MYSQL_SOCKET="/data/mysql/mysql.sock"
OUTPUT_DIR="/root/mariadb_assessment_output"
LAB_SCHEMA="lab_migracao"

TIMESTAMP="$(date +%Y%m%d_%H%M%S)"
HTML_FILE="${OUTPUT_DIR}/mariadb_assessment_${TIMESTAMP}.html"
TMP_DIR="${OUTPUT_DIR}/tmp_${TIMESTAMP}"

mkdir -p "${OUTPUT_DIR}" "${TMP_DIR}"

read -s -p "Digite a senha do usuário ${MYSQL_USER}: " MYSQL_PASS
echo

MYSQL_BASE_CMD=(
  "${MYSQL_BIN}"
  "--protocol=SOCKET"
  "--socket=${MYSQL_SOCKET}"
  "--user=${MYSQL_USER}"
  "--password=${MYSQL_PASS}"
  "--batch"
  "--raw"
  "--skip-column-names"
)

run_query() {
  local outfile="$1"
  local query="$2"
  "${MYSQL_BASE_CMD[@]}" -e "$query" > "${outfile}" 2>> "${TMP_DIR}/errors.log"
}

html_escape() {
  sed \
    -e 's/\&/\&/g' \
    -e 's//\>/g'
}

render_table_from_tsv() {
  local file="$1"
  local title="$2"
  local headers="$3"

  echo "
${title}
"
  echo ""

  echo ""
  IFS='|' read -r -a hdrs <<< "$headers"
  for h in "${hdrs[@]}"; do
    echo ""
  done
  echo ""

  while IFS=$'\t' read -r -a cols; do
    [ -z "${cols[*]}" ] && continue
    echo ""
    for col in "${cols[@]}"; do
      local cell_class=""
      case "$col" in
        Aria|MyISAM)
          cell_class=' class="warn"'
          ;;
        root@localhost)
          cell_class=' class="risk"'
          ;;
      esac
      echo "$(printf '%s' "$col" | html_escape)"
    done
    echo ""
  done < "${file}"

  echo "
$(printf '%s' "$h" | html_escape)
"
}

# =========================
# COLETA
# =========================

run_query "${TMP_DIR}/server_info.tsv" "
SELECT 'hostname', @@hostname
UNION ALL
SELECT 'version', @@version
UNION ALL
SELECT 'version_comment', @@version_comment
UNION ALL
SELECT 'port', @@port
UNION ALL
SELECT 'socket', @@socket
UNION ALL
SELECT 'datadir', @@datadir
UNION ALL
SELECT 'basedir', @@basedir
UNION ALL
SELECT 'log_bin', @@log_bin
UNION ALL
SELECT 'binlog_format', @@binlog_format
UNION ALL
SELECT 'sql_mode', @@sql_mode
UNION ALL
SELECT 'character_set_server', @@character_set_server
UNION ALL
SELECT 'collation_server', @@collation_server;
"

run_query "${TMP_DIR}/databases.tsv" "
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;
"

run_query "${TMP_DIR}/engines_lab.tsv" "
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema='${LAB_SCHEMA}'
ORDER BY table_name;
"

run_query "${TMP_DIR}/all_engines_summary.tsv" "
SELECT IFNULL(engine,'NULL') AS engine, COUNT(*) AS qtd_tabelas
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
GROUP BY engine
ORDER BY engine;
"

run_query "${TMP_DIR}/routines.tsv" "
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DEFINER
FROM information_schema.routines
WHERE ROUTINE_SCHEMA='${LAB_SCHEMA}'
ORDER BY ROUTINE_NAME;
"

run_query "${TMP_DIR}/triggers.tsv" "
SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING, DEFINER
FROM information_schema.triggers
WHERE TRIGGER_SCHEMA='${LAB_SCHEMA}'
ORDER BY TRIGGER_NAME;
"

run_query "${TMP_DIR}/schema_size.tsv" "
SELECT table_schema AS schema_name,
       ROUND(SUM(data_length + index_length)/1024/1024,2) AS size_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
GROUP BY table_schema
ORDER BY size_mb DESC;
"

run_query "${TMP_DIR}/table_size_lab.tsv" "
SELECT table_name,
       engine,
       IFNULL(table_rows,0) AS table_rows,
       ROUND((data_length + index_length)/1024/1024,2) AS size_mb
FROM information_schema.tables
WHERE table_schema='${LAB_SCHEMA}'
ORDER BY table_name;
"

# =========================
# CONTADORES
# =========================

ARIA_COUNT="$("${MYSQL_BASE_CMD[@]}" -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='${LAB_SCHEMA}' AND engine='Aria';" 2>> "${TMP_DIR}/errors.log")"
MYISAM_COUNT="$("${MYSQL_BASE_CMD[@]}" -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='${LAB_SCHEMA}' AND engine='MyISAM';" 2>> "${TMP_DIR}/errors.log")"
ROUTINE_COUNT="$("${MYSQL_BASE_CMD[@]}" -e "SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema='${LAB_SCHEMA}';" 2>> "${TMP_DIR}/errors.log")"
TRIGGER_COUNT="$("${MYSQL_BASE_CMD[@]}" -e "SELECT COUNT(*) FROM information_schema.triggers WHERE trigger_schema='${LAB_SCHEMA}';" 2>> "${TMP_DIR}/errors.log")"
DEFINER_ROOT_ROUTINES="$("${MYSQL_BASE_CMD[@]}" -e "SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema='${LAB_SCHEMA}' AND definer='root@localhost';" 2>> "${TMP_DIR}/errors.log")"
DEFINER_ROOT_TRIGGERS="$("${MYSQL_BASE_CMD[@]}" -e "SELECT COUNT(*) FROM information_schema.triggers WHERE trigger_schema='${LAB_SCHEMA}' AND definer='root@localhost';" 2>> "${TMP_DIR}/errors.log")"
DEFINER_ROOT_COUNT=$((DEFINER_ROOT_ROUTINES + DEFINER_ROOT_TRIGGERS))

# =========================
# HTML
# =========================

{
  echo ""
  echo ""
  echo ""
  echo ""
  echo ""
  echo ""
  echo ""
  echo ""

  echo "
Assessment MariaDB - Inventário e Compatibilidade
"

  echo "
"
  echo "
Resumo executivo
"
  echo "Aria encontradas: ${ARIA_COUNT}"
  echo "MyISAM encontradas: ${MYISAM_COUNT}"
  echo "Routines: ${ROUTINE_COUNT}"
  echo "Triggers: ${TRIGGER_COUNT}"
  echo "Objetos com DEFINER root@localhost: ${DEFINER_ROOT_COUNT}"
  echo "
Leitura inicial: o ambiente possui pontos que exigem atenção para migração ao MySQL 8.4.
"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/server_info.tsv" "Informações do servidor" "item|value"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/databases.tsv" "Databases" "database_name"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/engines_lab.tsv" "Tabelas do schema ${LAB_SCHEMA}" "table_name|engine"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/all_engines_summary.tsv" "Resumo de engines no ambiente" "engine|qtd_tabelas"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/schema_size.tsv" "Tamanho por schema" "schema_name|size_mb"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/table_size_lab.tsv" "Tamanho e volumetria das tabelas do lab" "table_name|engine|table_rows|size_mb"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/routines.tsv" "Routines do schema ${LAB_SCHEMA}" "routine_schema|routine_name|routine_type|definer"
  echo "
"

  echo "
"
  render_table_from_tsv "${TMP_DIR}/triggers.tsv" "Triggers do schema ${LAB_SCHEMA}" "trigger_schema|trigger_name|event_manipulation|event_object_table|action_timing|definer"
  echo "
"

  echo "
"
  echo "
Assessment técnico
"
  echo "
"
  echo "
Aria: incompatível com MySQL 8.4; tratar na origem ou no dump.
"
  echo "
MyISAM: suportada, mas candidata à avaliação para InnoDB.
"
  echo "
DEFINER root@localhost: revisar procedure e trigger na migração.
"
  echo "
Charset/Collation: revisar possíveis heranças de latin1/utf8mb3 no destino.
"
  echo "
"
  echo "
"

  if [ -s "${TMP_DIR}/errors.log" ]; then
    echo "
"
    echo "
Erros encontrados durante a coleta
"
    echo "
"
    cat "${TMP_DIR}/errors.log" | html_escape
    echo "
"

    echo "
"

  fi
  echo ""

} > "${HTML_FILE}"
echo

echo "Assessment concluído com sucesso."

echo "Arquivo gerado: ${HTML_FILE}"

echo

#!/bin/bash

# ============================================================
# Script: 03_dump_mariadb.sh
# Objetivo: Gerar dump lógico do schema lab_migracao no MariaDB
# Ambiente: MariaDB 10.11.x
# ============================================================

set -u
set -o pipefail

MYSQLDUMP_BIN="/usr/local/mysql/bin/mariadb-dump"
MYSQL_USER="migration_user"
MYSQL_SOCKET="/data/mysql/mysql.sock"
SCHEMA_NAME="lab_migracao"
OUTPUT_DIR="/backup"

TIMESTAMP="$(date +%Y%m%d_%H%M%S)"
DUMP_FILE="${OUTPUT_DIR}/${SCHEMA_NAME}_dump_original_${TIMESTAMP}.sql"
DUMP_GZ_FILE="${DUMP_FILE}.gz"
LOG_FILE="${OUTPUT_DIR}/${SCHEMA_NAME}_dump_original_${TIMESTAMP}.log"

mkdir -p "${OUTPUT_DIR}"

read -s -p "Digite a senha do usuário ${MYSQL_USER}: " MYSQL_PASS
echo

MYSQLDUMP_CMD=(
  "${MYSQLDUMP_BIN}"
  "--protocol=SOCKET"
  "--socket=${MYSQL_SOCKET}"
  "--user=${MYSQL_USER}"
  "--password=${MYSQL_PASS}"
  "--databases" "${SCHEMA_NAME}"
  "--routines"
  "--triggers"
  "--events"
  "--single-transaction"
  "--hex-blob"
  "--default-character-set=utf8mb4"
  "--add-drop-table"
)

echo "============================================================" | tee -a "${LOG_FILE}"
echo "Início do dump lógico MariaDB" | tee -a "${LOG_FILE}"
echo "Data/Hora: $(date '+%Y-%m-%d %H:%M:%S')" | tee -a "${LOG_FILE}"
echo "Schema: ${SCHEMA_NAME}" | tee -a "${LOG_FILE}"
echo "Saída: ${DUMP_FILE}" | tee -a "${LOG_FILE}"
echo "============================================================" | tee -a "${LOG_FILE}"

if [ ! -x "${MYSQLDUMP_BIN}" ]; then
  echo "[ERRO] Binário não encontrado ou sem execução: ${MYSQLDUMP_BIN}" | tee -a "${LOG_FILE}"
  exit 1
fi

echo "[INFO] Executando dump..." | tee -a "${LOG_FILE}"

"${MYSQLDUMP_CMD[@]}" > "${DUMP_FILE}" 2>> "${LOG_FILE}"
DUMP_RC=$?

if [ ${DUMP_RC} -ne 0 ]; then
  echo "[ERRO] Falha ao gerar dump. Verifique o log: ${LOG_FILE}" | tee -a "${LOG_FILE}"
  exit 1
fi

echo "[INFO] Dump gerado com sucesso." | tee -a "${LOG_FILE}"

echo "[INFO] Coletando estatísticas do dump..." | tee -a "${LOG_FILE}"
LINE_COUNT="$(wc -l < "${DUMP_FILE}")"
FILE_SIZE="$(du -h "${DUMP_FILE}" | awk '{print $1}')"

echo "[INFO] Linhas no dump: ${LINE_COUNT}" | tee -a "${LOG_FILE}"
echo "[INFO] Tamanho do arquivo: ${FILE_SIZE}" | tee -a "${LOG_FILE}"

echo "[INFO] Compactando dump..." | tee -a "${LOG_FILE}"
gzip -c "${DUMP_FILE}" > "${DUMP_GZ_FILE}"
GZIP_RC=$?

if [ ${GZIP_RC} -ne 0 ]; then
  echo "[ERRO] Falha ao compactar dump." | tee -a "${LOG_FILE}"
  exit 1
fi

GZ_SIZE="$(du -h "${DUMP_GZ_FILE}" | awk '{print $1}')"
echo "[INFO] Dump compactado: ${DUMP_GZ_FILE}" | tee -a "${LOG_FILE}"
echo "[INFO] Tamanho compactado: ${GZ_SIZE}" | tee -a "${LOG_FILE}"

echo "============================================================" | tee -a "${LOG_FILE}"
echo "Dump concluído com sucesso." | tee -a "${LOG_FILE}"
echo "Arquivo SQL : ${DUMP_FILE}" | tee -a "${LOG_FILE}"
echo "Arquivo GZ  : ${DUMP_GZ_FILE}" | tee -a "${LOG_FILE}"
echo "Log         : ${LOG_FILE}" | tee -a "${LOG_FILE}"
echo "============================================================" | tee -a "${LOG_FILE}"

echo
echo "Dump concluído com sucesso."
echo "Arquivo SQL : ${DUMP_FILE}"
echo "Arquivo GZ  : ${DUMP_GZ_FILE}"
echo "Log         : ${LOG_FILE}"
echo

  

<

#!/bin/bash

# ============================================================
# Script: 04_sanitize_dump.sh
# Objetivo: Sanitizar dump MariaDB para MySQL 8.x
#
# IMPORTANTE:
# - Este script mantém o dump original intacto.
# - As regras abaixo são baseadas no assessment do ambiente.
# - Ajuste conforme o seu cenário antes de usar em produção.
# ============================================================

set -euo pipefail

INPUT_DUMP="${1:-}"

if [ -z "${INPUT_DUMP}" ]; then
  echo "Uso: ./04_sanitize_dump.sh "
  exit 1
fi

if [ ! -f "${INPUT_DUMP}" ]; then
  echo "[ERRO] Arquivo não encontrado: ${INPUT_DUMP}"
  exit 1
fi

OUTPUT_DUMP="${INPUT_DUMP%.sql}_sanitized.sql"
LOG_FILE="${INPUT_DUMP%.sql}_sanitize.log"

echo "============================================================" | tee "${LOG_FILE}"
echo "Sanitização de dump iniciada" | tee -a "${LOG_FILE}"
echo "Input : ${INPUT_DUMP}" | tee -a "${LOG_FILE}"
echo "Output: ${OUTPUT_DUMP}" | tee -a "${LOG_FILE}"
echo "============================================================" | tee -a "${LOG_FILE}"

cp "${INPUT_DUMP}" "${OUTPUT_DUMP}"

# ============================================================
# 1. AJUSTE DE ENGINES
# ============================================================
echo "[INFO] Ajustando engines..." | tee -a "${LOG_FILE}"

# Aria -> InnoDB (incompatível com MySQL)
sed -i 's/ENGINE=Aria/ENGINE=InnoDB/g' "${OUTPUT_DUMP}"

# MyISAM -> InnoDB (boa prática moderna)
sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' "${OUTPUT_DUMP}"

# Exemplo para tratar apenas uma tabela específica:
# sed -i '/CREATE TABLE `minha_tabela`/,/ENGINE=/ s/ENGINE=MyISAM/ENGINE=InnoDB/' "${OUTPUT_DUMP}"

# ============================================================
# 2. AJUSTE DE CHARSET
# ============================================================
echo "[INFO] Ajustando charset..." | tee -a "${LOG_FILE}"

sed -i 's/CHARSET=latin1/CHARSET=utf8mb4/g' "${OUTPUT_DUMP}"
sed -i 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8mb4/g' "${OUTPUT_DUMP}"

# Exemplo:
# sed -i 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8mb4/g' "${OUTPUT_DUMP}"

# ============================================================
# 3. AJUSTE DE UTF8 LEGADO
# ============================================================
echo "[INFO] Ajustando utf8 legado..." | tee -a "${LOG_FILE}"

sed -i 's/utf8mb3/utf8mb4/g' "${OUTPUT_DUMP}"

# ============================================================
# 4. AJUSTE DE COLLATION
# ============================================================
echo "[INFO] Ajustando collations..." | tee -a "${LOG_FILE}"

sed -i 's/latin1_swedish_ci/utf8mb4_0900_ai_ci/g' "${OUTPUT_DUMP}"
sed -i 's/utf8mb3_general_ci/utf8mb4_0900_ai_ci/g' "${OUTPUT_DUMP}"

# Exemplo para usar outra collation:
# sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' "${OUTPUT_DUMP}"

# ============================================================
# 5. REMOÇÃO DE DEFINER
# ============================================================
echo "[INFO] Removendo DEFINER..." | tee -a "${LOG_FILE}"

sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' "${OUTPUT_DUMP}"

# Exemplo para substituir DEFINER ao invés de remover:
# sed -i 's/DEFINER=`root`@`localhost`/DEFINER=`app_user`@`%`/g' "${OUTPUT_DUMP}"

# ============================================================
# 6. REMOÇÃO DE OPÇÕES ESPECÍFICAS DO MARIADB
# ============================================================
echo "[INFO] Removendo opções incompatíveis do MariaDB..." | tee -a "${LOG_FILE}"

# PAGE_CHECKSUM não é aceito no MySQL 8.x
sed -i 's/ PAGE_CHECKSUM=1//g' "${OUTPUT_DUMP}"

# Exemplo para outros casos, se aparecerem:
# sed -i 's/ TRANSACTIONAL=0//g' "${OUTPUT_DUMP}"
# sed -i 's/ PAGE_CHECKSUM=[0-9]//g' "${OUTPUT_DUMP}"

# ============================================================
# 7. AJUSTE DE SQL_MODE INCOMPATÍVEL COM MYSQL 8
# ============================================================
echo "[INFO] Removendo sql_mode incompatível..." | tee -a "${LOG_FILE}"

sed -i "s/,NO_AUTO_CREATE_USER//g; s/NO_AUTO_CREATE_USER,//g; s/NO_AUTO_CREATE_USER//g" "${OUTPUT_DUMP}"

# ============================================================
# 8. LIMPEZA FINAL
# ============================================================
echo "[INFO] Ajustando espaços extras..." | tee -a "${LOG_FILE}"

sed -i 's/  / /g' "${OUTPUT_DUMP}"

echo "[INFO] Sanitização concluída." | tee -a "${LOG_FILE}"
echo "============================================================" | tee -a "${LOG_FILE}"
echo "Dump final: ${OUTPUT_DUMP}" | tee -a "${LOG_FILE}"
echo "Log       : ${LOG_FILE}" | tee -a "${LOG_FILE}"
echo "============================================================" | tee -a "${LOG_FILE}"

echo
echo "Sanitização concluída com sucesso."
echo "Arquivo: ${OUTPUT_DUMP}"
echo