Transfer Database

Trong quá trình phát triển và bảo trì dự án, việc di chuyển cơ sở dữ liệu từ hệ thống này sang hệ thống khác là một nhiệm vụ khá phổ biến.

Giả sử bạn cần di chuyển dữ liệu từ SQL Server sang MySQL, bạn sẽ thực hiện nhiệm vụ này như thế nào?

Trong bài viết này, tôi sẽ di chuyển dữ liệu bằng tập tin CSV bằng cách sử dụng lệnh SQLCMD để thực hiện xuất dữ liệu ra tập tin CSV từ SQL Server và sau đó nhập dữ liệu tập tin CSV vào MySQL.

Đầu tiên, hãy xem qua câu lệnh cơ bản để xuất dữ liệu ra tập tin CSV bằng SQLCMD:

sqlcmd -S server -d database -E -Q "select col1, col2, col3 from table" -o "data.csv" -h-1 -s"," -w 700

Dựa vào phương pháp trên, chúng ta sẽ tích hợp nó vào command của Laravel để thực hiện việc di chuyển cơ sở dữ liệu đơn giản hơn.

Command này chúng ta sẽ đặt cho nó một cái tên là TransferSQLServerToMySQL sẽ hỗ trợ chúng ta trong việc chuyển dữ liệu từ SQL Server sang MySQL trong Laravel trở nên nhanh chóng và hiệu quả hơn bao giờ hết.

Thực hiện TransferSQLServerToMySQL trong Laravel

Đầu tiên, bạn sẽ tạo một command mới bằng cách sử dụng Artisan CLI của Laravel. Mở Terminal và chạy lệnh sau:

php artisan make:command TransferSQLServerToMySQL

Mở tập tin app/Console/Commands/TransferSQLServerToMySQL.php. Bạn sẽ thấy một class cơ bản đã được tạo sẵn. Bây giờ chúng ta sẽ cập nhật nội dung xử lý như sau:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;

class TransferSQLServerToMySQL extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'transfer:sqlserver-to-mysql';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Transfers data from SQL Server to MySQL using CSV export and import';

    /**
     * Source system configuration details.
     *
     */
    protected $source_system_host     = '';
    protected $source_system_port     = '';
    protected $source_system_username = '';
    protected $source_system_password = '';

    /**
     * Destination system configuration details.
     *
     */
    protected $destination_system_host     = '';
    protected $destination_system_port     = '';
    protected $destination_system_username = '';
    protected $destination_system_password = '';
    protected $destination_system_database = '';

    /**
     * Table and query configuration.
     *
     */
    protected $table          = '';
    protected $columns        = '';
    protected $local_infile   = '';
    protected $local_infiles  = [];
    protected $query          = '';
    protected $lines_per_file = 0;

    /**
     * Execute the console command.
     */
    public function handle()
    {
        // Measure the start time of the entire process
        $start_time           = microtime(true);
        $start_time_formatted = now()->format('Y-m-d H:i:s');
        // Configure source system connection parameters
        $this->setupSourceSystem();
        // Configure destination system connection parameters
        $this->setupDestinationSystem();
        // Configure export settings
        $this->configureExportSettings();
        // Export data from the old system to a CSV file
        $this->exportToCSV();
        // Split the exported CSV file into smaller parts.
        $this->splitCSVIntoParts();
        // Import data from a CSV file to the new system
        $this->importFromCSV();
        // Deletes the CSV import files.
        $this->deleteCSVImport();
        // Measure the end time of the entire process
        $end_time           = microtime(true);
        $end_time_formatted = now()->format('Y-m-d H:i:s');
        $execution_time     = round($end_time - $start_time, 1);
        print("Execution time: {$start_time_formatted} - {$end_time_formatted} ({$execution_time} seconds)\n");
    }

    /**
     * Configure the source system connection parameters.
     */
    protected function setupSourceSystem()
    {
        $this->source_system_host     = '';
        $this->source_system_port     = '';
        $this->source_system_username = '';
        $this->source_system_password = '';
    }

    /**
     * Configure the destination system connection parameters.
     */
    protected function setupDestinationSystem()
    {
        $this->destination_system_host     = '';
        $this->destination_system_port     = '';
        $this->destination_system_username = '';
        $this->destination_system_password = '';
        $this->destination_system_database = '';
    }

    /**
     * Configure export settings.
     */
    protected function configureExportSettings()
    {
        $this->table        = 'table';
        $this->columns      = ['column'];
        $this->local_infile = storage_path('transfers/table.csv');
        $this->query        = "SELECT * FROM table";
    }

    /**
     * Export data from the old system to a CSV file.
     */
    protected function exportToCSV()
    {
        $command = sprintf(
            "sqlcmd -S %s,%s -U %s -P %s -C -Q \"SET NOCOUNT ON; PRINT '%s'; %s SET NOCOUNT OFF\" -o %s -h -1 -W -s \",\"",
            $this->source_system_host,
            $this->source_system_port,
            $this->source_system_username,
            $this->source_system_password,
            implode(',', $this->columns),
            $this->query,
            $this->local_infile
        );
        exec($command, $output, $status);

        $command = "awk 'NR > 1 { print t } { t = \$0 } END { ORS = \"\"; print t }' $this->local_infile > temp.csv && mv temp.csv $this->local_infile";
        exec($command, $output, $status);

        return $status;
    }

    /**
     * Import data from a CSV file to the new system.
     */
    protected function importFromCSV()
    {
        $columns = implode(',', array_map(function($col) {
            return '\\`' . $col . '\\`';
        }, $this->columns));

        $ignore_lines = $this->lines_per_file < 1 ? "IGNORE 1 LINES" : "";

        foreach ($this->local_infiles as $local_infile) {
            $command = sprintf(
            "mysql -h %s -P %s -u %s -p%s -D %s --local-infile -e \"LOAD DATA LOCAL INFILE %s INTO TABLE %s FIELDS TERMINATED BY ',' ENCLOSED BY '\\\"' LINES TERMINATED BY '\\n' %s (%s);\"",
                $this->destination_system_host,
                $this->destination_system_port,
                $this->destination_system_username,
                $this->destination_system_password,
                $this->destination_system_database,
                escapeshellarg($local_infile),
                $this->table,
                $ignore_lines,
                $columns
            );

            exec($command, $output, $status);
        }
    }

    /**
     * Splits the CSV file into multiple parts based on the specified number of lines per file.
     */
    protected function splitCSVIntoParts()
    {
        if ($this->lines_per_file < 1) {
            $this->local_infiles = [$this->local_infile];
            return 0;
        }
        $script  = base_path('split_csv.sh');
        $command = "$script $this->local_infile $this->lines_per_file";
        exec($command, $output, $status);

        $this->local_infiles = $output;

        return $status;
    }

    /**
     * Deletes the main CSV file and its parts from storage.
     */
    protected function deleteCSVImport()
    {
        if ($this->lines_per_file > 1) {
            unlink($this->local_infile);
        }
        foreach ($this->local_infiles as $local_infile) {
            unlink($local_infile);
        }
    }
}

Khi bạn xem xét kỹ hơn, bạn sẽ thấy chúng ta đã thêm hàm splitCSVIntoParts để thực hiện nhiệm vụ chia nhỏ dữ liệu CSV.

Đối với các tệp CSV quá lớn, thì trong quá trình truyền tải và xử lý chúng ta có thể gặp nhiều khó khăn.

Việc chia nhỏ dữ liệu CSV giúp việc truyền tải và xử lý trở nên dễ dàng hơn, đồng thời giảm thiểu nguy cơ gặp lỗi do kích thước tập tin quá lớn.

Bên dưới là nội dung của tập tin split_csv.sh, bạn hãy tạo split_csv.sh trong thư mục Root của dự án:

#!/bin/bash

# Check if the CSV file path is provided
if [ -z "$1" ]; then
    echo "Please provide the path to the CSV file."
    exit 1
fi

# Read input parameters
file="$1"
num_splits="${2:-100}" # Default to 100 if the second parameter is not provided
path=$(dirname "$file")
output_prefix=$(basename "$file" .csv)

# Change to the directory containing the CSV file
cd "$path" || { echo "Cannot change to directory $path"; exit 1; }

# Execute the mlr command with the specified parameters
mlr --csv --from "$file" split -n "$num_splits" --prefix "${output_prefix}_part"

# Print the absolute paths of the created part files
find "$PWD" -name "${output_prefix}_part_*" -print

Dockerfile

Nếu dự án của bạn đang sử dụng Docker, bạn cũng có thể tham khảo Dockerfile bên dưới để tích hợp lệnh TransferSQLServerToMySQL, giúp việc triển khai và quản lý trở nên dễ dàng hơn.

Dưới đây là Dockerfile đã được cài đặt sẵn mssql-tools18, để hỗ trợ chúng ta việc thực hiện xuất dữ liệu từ SQL Server thành tập tin CSV.

# Use the latest Ubuntu base image
FROM ubuntu:24.10

# Install necessary packages and add Microsoft package sources
RUN apt-get update -y && \
    apt-get install -y curl apt-transport-https gnupg2 && \
    curl -sSL https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl -sSL https://packages.microsoft.com/config/ubuntu/22.04/prod.list | tee /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && \
    ACCEPT_EULA=Y apt-get install -y msodbcsql18 mssql-tools18 unixodbc-dev && \
    echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc

# Set the correct path
ENV PATH="$PATH:/opt/mssql-tools18/bin"
# Update and install necessary packages
RUN apt-get update -y \
    && apt upgrade -y \
    && DEBIAN_FRONTEND=noninteractive apt-get install -y \
        miller \
        mysql-server \
        php8.3 \
        php8.3-cli \
        php8.3-mysql \
        php8.3-xml \
        php8.3-bcmath \
        php8.3-curl \
        php8.3-common \
        php8.3-mbstring \
        php8.3-tokenizer \
        php8.3-zip \
        curl \
        git \
        unzip \
        python3 \
        python3-pip \
        python3-venv  # Install python3-venv for creating virtual environments

# Install PHP-FPM and enable configuration
RUN apt install -y php8.3-fpm && a2enconf php8.3-fpm

# Install Composer
RUN curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/local/bin --filename=composer

# Create a directory for the virtual environment and install sql2csv inside it
RUN python3 -m venv /opt/venv
RUN /opt/venv/bin/pip install sql2csv

# Expose the default port for MySQL
EXPOSE 3306

# Add CMD command to start MySQL, activate the virtual environment, and run sql2csv
CMD ["bash", "-c", "service mysql start && source /opt/venv/bin/activate && sql2csv --help && tail -f /dev/null"]

Ngoài ra, chúng ta cũng có thể có thể di chuyển dữ liệu sang Amazon S3 bằng Amazon DMS từ bất kỳ nguồn cơ sở dữ liệu nào được hỗ trợ.

Khi sử dụng Amazon S3 làm mục tiêu trong tác vụ Amazon DMS, dữ liệu sẽ được ghi theo định dạng phân tách bằng dấu "," (.csv) theo mặc định.

Để lưu trữ nhỏ gọn hơn và có tùy chọn truy vấn nhanh hơn, bạn cũng có tùy chọn ghi dữ liệu vào định dạng Apache Parquet (.parquet).

Using Amazon S3 as a target for AWS Database Migration Service

CÓ THỂ BẠN QUAN TÂM

Implementing Private User Folders with elFinder in Laravel

Implementing Private User Folders with elFinder in Laravel

elFinder elFinder là một trình quản lý tập tin mã nguồn mở dành cho web, được viết bằng JavaScript sử dụng jQuery UI. elFinder được phát triển dựa trên cảm hứng từ sự tiện lợi và đơn giản của chư...

Laravel Has Many Through Eloquent Relationship

Laravel Has Many Through Eloquent Relationship

Has Many Through Relationship hơi phức tạp để hiểu một cách đơn giản, nó sẽ cung cấp cho chúng ta một con đường tắt để có thể truy cập dữ liệu của một quan hệ xa xôi thông qua một mối quan hệ trung gi...

Laravel Upload File Using Trait

Laravel Upload File Using Trait

Hiện nay, đa số các dự án đều có chức năng upload file, nên tôi đã thử xây dựng một lớp Trait Upload File, để chúng ta dễ dàng sao chép qua các dự án khác để sử dụng, nhằm rút ngắn thời gian phát triể...

Laravel Custom Request

Laravel Custom Request

Nếu bạn có một form để người dùng nhập dữ liệu và bạn muốn kiểm tra dữ liệu đầu vào trước khi lưu xuống database chẳng hạn thì bạn có 2 cách sau đây: Cách 1: Bạn thêm validate trực tiếp vào hàm sto...

Laravel One to One Eloquent Relationship

Laravel One to One Eloquent Relationship

Mối quan hệ một-một là một mối quan hệ rất cơ bản. Trong hướng dẫn này, tôi sẽ hướng dẫn bạn cách tạo dữ liệu và truy xuất dữ liệu bằng Eloquent Model. Trong hướng dẫn này, tôi sẽ tạo hai bảng là u...

Laravel Authentication With Laravel UI

Laravel Authentication With Laravel UI

Laravel UI Laravel UI cung cấp một cách nhanh chóng để mở rộng các route và view cần thiết cho chức năng Authentication và bao gồm các cài đặt liên quan cho Bootstrap, React hoặc Vue. Mặc dù nó v...

Laravel Queues and Jobs

Laravel Queues and Jobs

Các công ty có thẻ gặp khó khăn trong việc quản lý các dịch vụ hoặc ứng dụng của họ. Ví dụ, các công ty các thực hiện gửi email cho hàng triệu người dùng hoặc thực hiện sao lưu dữ liệu. Tất cả các hoạ...

Laravel Socialite Login With Facebook

Laravel Socialite Login With Facebook

Ngoài xác thực dựa trên biểu mẫu điển hình, Laravel cũng cung cấp một cách đơn giản và thuận tiện để sử dụng Laravel Socialite để xác thực với các nhà cung cấp OAuth. Socialite hiện hỗ trợ xác thực qu...

Implementing Private User Folders with CKFinder in Laravel

Implementing Private User Folders with CKFinder in Laravel

CKFinder 3 CKFinder 3 là trình quản lý tập tin được tích hợp với CKEditor 4 và CKEditor 5. Nó giúp bạn dễ dàng đưa các tập tin và hình ảnh vào nội dung của Editor một cách an toàn. Đây là một tín...

ManhDanBlogs