powershell export last 100 records per table from mysql db

mysql-last100-export.ps1

# ---------------------------------------------------------------------------
# Script: export_tables.ps1
# Description: Exports last 100 records to CSV with 100-char cell truncation
# Generated for: db_nmsapps_core (Manila Time/GMT+0800)
# ---------------------------------------------------------------------------

# Database Configuration
$Server     = "localhost"
$Port       = "3366" 
$Database   = "db_name"
$User       = "root"
$Password   = "your_password_here"
$OutputFile = "database_export.csv"

# Your specific path to the MariaDB CLI tool
$MysqlPath  = "D:\wamp64\bin\mariadb\mariadb11.2.2\bin\mysql.exe"

if (!(Test-Path $MysqlPath)) {
  Write-Error "Ugh, mysql.exe is missing at $MysqlPath. Fix it."
  return
}

try {
  Write-Host "Connecting to $Database on $Port... this better be the last time." -ForegroundColor Cyan

  # Get the list of tables
  $tableList = & $MysqlPath -h $Server -P $Port -u $User "-p$Password" -D $Database -N -e "SHOW TABLES;"

  if ($null -eq $tableList) {
    Write-Error "Nothing came back. Check your settings."
    return
  }

  # Clear or create the file
  "" | Out-File -FilePath $OutputFile -Encoding utf8

  foreach ($table in $tableList) {
    Write-Host "Processing: $table" -ForegroundColor Yellow

    Add-Content $OutputFile "### TABLE: $table ###"

    # Use -B for batch mode (tab-separated, no borders)
    $query = "SELECT * FROM ``$table`` ORDER BY 1 DESC LIMIT 100;"
    $rawOutput = & $MysqlPath -h $Server -P $Port -u $User "-p$Password" -D $Database -B -e $query

    if ($rawOutput) {
      foreach ($line in $rawOutput) {
        # Split by tab
        $fields = $line -split "`t"
        
        # Truncate each field to 100 chars and wrap in quotes for CSV safety
        $processedFields = $fields | ForEach-Object {
          $val = $_
          if ($null -eq $val) { $val = "" }
          if ($val.Length -gt 100) { $val = $val.Substring(0, 100) }
          # Escape double quotes and wrap
          '"' + ($val -replace '"', '""') + '"'
        }

        # Join with commas and save
        ($processedFields -join ",") | Add-Content $OutputFile
      }
    }

    "`n" | Add-Content $OutputFile
  }

  Write-Host "Fine, it's done. Your file is actually reasonable now." -ForegroundColor Green

} catch {
  Write-Error "Whatever, something broke: $($_.Exception.Message)"
}