Hàm import cơ sở dữ liệu vào MySQL

Nếu bạn biết thông tin kết nối đến cơ sở dữ liệu nhưng không có cách nào để import database thì bạn có thể sử dụng hàm PHP để nhập cơ sở dữ liệu vào MySQL Server.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>MySQL Tools by Lai Dinh Cuong</title>
</head>
<body>
<?php
/**
 * Imports a large .sql file into a MySQL database.
 *
 * @param string $filePath Path to the .sql file.
 * @param array $dbConfig Database configuration array with keys: host, username, password, database.
 *
 * @return bool True on success, false on failure.
 */
function importLargeSqlFileToMySQL( $filePath, $dbConfig ) {
    $db_host = $dbConfig['host'] ?? '';

    if ( empty( $db_host ) ) {
        $db_host = 'localhost';
    }

    // Create a new PDO instance
    $dsn = "mysql:host={$db_host};dbname={$dbConfig['database']};charset=utf8";

    try {
        $pdo = new PDO( $dsn, $dbConfig['username'], $dbConfig['password'], [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ] );
    } catch ( PDOException $e ) {
        die( 'Connection failed: ' . $e->getMessage() );
    }

    // Ensure the file exists
    if ( ! file_exists( $filePath ) ) {
        die( 'File not found: $filePath' );
    }

    // Open the SQL file
    $handle = fopen( $filePath, 'r' );

    if ( $handle === false ) {
        die( 'Failed to open file: $filePath' );
    }

    // Read and execute the file line by line
    $query = '';

    try {
        $pdo->beginTransaction();

        while ( ( $line = fgets( $handle ) ) !== false ) {
            $trimmedLine = trim( $line );

            // Skip comments and empty lines
            if ( empty( $trimmedLine ) || strpos( $trimmedLine, '--' ) === 0 || strpos( $trimmedLine, '/*' ) === 0 || strpos( $trimmedLine, '//' ) === 0 ) {
                continue;
            }

            // Append line to current query
            $query .= $line;

            // If query ends with a semicolon, execute it
            if ( substr( trim( $query ), - 1 ) === ';' ) {
                // Check if the query is a CREATE TABLE statement
                if ( preg_match( '/CREATE TABLE `?(\w+)`?/', $query, $matches ) ) {
                    $tableName = $matches[1];
                    // Drop the table if it exists
                    $pdo->exec( "DROP TABLE IF EXISTS `$tableName`" );
                }

                try {
                    $pdo->exec( $query );
                } catch ( PDOException $e ) {
                    // Log error and continue
                    echo 'Error executing query: ' . $e->getMessage() . "\n";
                }

                $query = ''; // Reset query
            }
        }

        $pdo->commit();

        fclose( $handle );

        return true;
    } catch ( PDOException $e ) {
        if ( $pdo->inTransaction() ) {
            $pdo->rollBack();
        }

        // Query to get the size of the database
        $sql = "SELECT table_schema AS `Database`, 
                   SUM(data_length + index_length) AS `Size` 
            FROM information_schema.tables 
            WHERE table_schema = :database 
            GROUP BY table_schema";

        $size = false;

        try {
            $stmt = $pdo->prepare( $sql );
            $stmt->bindParam( ':database', $dbConfig['database'], PDO::PARAM_STR );
            $stmt->execute();
            $result = $stmt->fetch();
            $size   = $result ? (float) $result['Size'] : 0;
        } catch ( PDOException $e ) {
            die( 'Failed to get database size: ' . $e->getMessage() );
        }

        fclose( $handle );

        if ( $size ) {
            return true;
        } else {
            die( 'Import failed: ' . $e->getMessage() );
        }
    }
}

// Example usage
$dbConfig = [
    'host'     => $_GET['db_host'] ?? '',
    'username' => $_GET['db_user'] ?? '',
    'password' => $_GET['db_password'] ?? '',
    'database' => $_GET['db_name'] ?? '',
];

$filePath = $_GET['file'] ?? '';

if ( empty( $filePath ) || ! file_exists( $filePath ) ) {
    $filePath = 'database.sql';
}

if ( importLargeSqlFileToMySQL( $filePath, $dbConfig ) ) {
    echo 'Import successful!';
} else {
    echo 'Import failed.';
}
?>
</body>
</html>