Transfer Database

Trong quá trình phát triển và bảo trì dự án, nhiệm vụ 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á là phổ biến.

Chúng ta thường sẽ sử dụng câu truy vấn sau trong việc xuất dữ liệu sang định dạng CSV:

SELECT
   ...
INTO OUTFILE '...'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ...

Tuy nhiên, khi thực hiện câu truy vấn trên từ AWS RDS , chúng ta thường gặp phải vấn đề đó là không có quyền truy cập vào server trong việc thực hiện xuất dữ liệu sang định dạng CSV.

Để giải quyết vấn đề này, chúng ta sẽ sử dụng một phương pháp thay thế để có thể xuất dữ liệu sang định dạng CSV, đó là thực hiện thông qua lệnh mysql và chuyển hướng đầu ra của dữ liệu thành CSV:

mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch -e "select * from yourtable" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename

Dựa vào phương pháp thay thế 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à TransferMySQLToMySQL sẽ hỗ trợ chúng ta trong việc di chuyển dữ liệu giữa các cơ sở dữ liệu MySQL trong Laravel trở nên nhanh chóng và hiệu quả hơn bao giờ hết, tiết kiệm thời gian và công sức trong quá trình quản lý dữ liệu.

Thực hiện TransferMySQLToMySQL 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 TransferMySQLToMySQL

Mở tập tin app/Console/Commands/TransferMySQLToMySQL.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;

/**
 * Suggests an alternative approach as well via exporting your data in CSV format by selecting the data in the MySQL command line client and piping the output to reformat the data as CSV, like so:
 * mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch -e "select * from yourtable" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename
 *
 * Supposedly simpler approach, if you know and specify the fields upfront:
 * mysql -uroot -ppassword --database=dbtest-e "select concat(field1,',',field2,',',field3) FROM tabletest" > tabletest.csv
 *
 * I recently encountered some larger and more complex outputs where "sed" was not enough and decided to come up with a simple utility to do exactly that.
 * I build a module called sql2csv that can parse the output of the MySQL CLI:
 * mysql my_db -e "SELECT * FROM some_mysql_table" | sql2csv
 * sql2csv -u root -p "secret" -d my_db --query "SELECT * FROM some_mysql_table;"
 *
 * To quote strings (recommended), you'll need to do a bit of extra work in your query:
 * SELECT id, CONCAT('"', REPLACE(text_column, '"', '""'), '"'), float_column FROM my_table
 *
 * The REPLACE escapes any double-quote characters in the text_column values. I would also suggest using iso8601 strings for datetime fields, so:
 * SELECT CONCAT('"', DATE_FORMAT(datetime_column, '%Y%m%dT%T'), '"') FROM my_table
 *
 * https://stackoverflow.com/questions/9536224/exporting-a-table-from-amazon-rds-into-a-csv-file
 */
class TransferMySQLToMySQL extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'transfer:mysql-to-mysql';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Transfers data from one MySQL database to another 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(
        "mysql -h %s -P %s -u %s -p%s --batch --quick -e \"%s\" | sql2csv > %s",
            $this->source_system_host,
            $this->source_system_port,
            $this->source_system_username,
            $this->source_system_password,
            $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 = "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 | sed -E 's/.*_part_([0-9]+)\.csv/\1 \0/' | sort -k1,1n | cut -d' ' -f2-

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 với lệnh TransferMySQLToMySQL, 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 thêm thư viện sql2csv, hỗ trợ phân tích cú pháp đầu ra của MySQL CLI, rất hữu ích khi làm việc với dữ liệu lớn và phức tạp.

# Use the latest Ubuntu base image
FROM ubuntu:24.10

# 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

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 CKEditor 5 Image Upload

Laravel CKEditor 5 Image Upload

CKEditor 5CKEditor 5 là một trình soạn thảo văn bản phong phú JavaScript với nhiều tính năng và khả năng tùy chỉnh. CKEditor 5 có kiến trúc MVC hiện đại, mô hình dữ liệu tùy chỉnh và DOM ảo, mang...

Defer in Laravel: Push Tasks to the Background

Defer in Laravel: Push Tasks to the Background

Deferred Functions trong Laravel Các phiên bản Laravel trước version 11, chúng ta thường sử dụng Queued Jobs cho phép thực hiện sắp xếp các tác vụ xử lý background . Nhưng đôi khi có những tác v...

Integrating Google Gemini AI in Laravel

Integrating Google Gemini AI in Laravel

Google Gemini Gemini là một mô hình trí tuệ nhân tạo mới mạnh mẽ từ Google không chỉ có khả năng hiểu văn bản mà còn có thể hiểu cả hình ảnh, video và âm thanh. Gemini là một mô hình đa phương ti...

Laravel Controllers

Laravel Controllers

Trong mô hình MVC, chữ "C" là từ viết tắt của Controller và nó đóng vai trò rất quan trọng để phân tích các logic business. Khi người dùng truy cập vào trình duyệt, nó sẽ đi đến route đầu tiên, sau đó...

Eloquent Methods: whereDoesntHaveRelation and whereMorphDoesntHaveRelation

Eloquent Methods: whereDoesntHaveRelation and whereMorphDoesntHaveRelation

New Laravel 11.37: Eloquent Methods Laravel cung cấp cho chúng ta khả năng xây dựng các truy vấn dữ liệu mạnh mẽ với Eloquent ORM, giúp chúng ta có thể xử lý các truy vấn cơ sở dữ liệu phức tạp một...

Laravel customize your API Versioning Route File

Laravel customize your API Versioning Route File

Trong khuôn khổ của Laravel, các route của api được tách thành một file duy nhất, đó là file api.php nằm trong thư mục routes . Nếu chúng ta muốn thêm version vào route api thì chúng ta sẽ làm như...

Integrating AI Assistant with CKEditor 5 in Laravel using Vite

Integrating AI Assistant with CKEditor 5 in Laravel using Vite

OpenAI OpenAI là một công ty nghiên cứu và triển khai trí tuệ nhân tạo, nổi tiếng với việc phát triển các mô hình AI tiên tiến. Mục tiêu của OpenAI là đảm bảo rằng trí tuệ nhân tạo tổng quát (AGI...

Laravel Custom Eloquent Casts

Laravel Custom Eloquent Casts

Trước đây, chúng ta bị giới hạn cast mặc định do Laravel cung cấp. Mặc dù, có một số gói thư viện có thể  giúp chúng ta custom được nhưng chúng có một nhược điểm lớn. Bởi vì, chúng ghi đề phương thức...

ManhDanBlogs