PHP pt-online-schema

wen PHP项目 2026-08-04 84

本文目录导读：

What is pt-online-schema-change?
Basic Usage
Common Options
PHP Integration Example
Best Practices
Common Pitfalls and Solutions
Integration with PHP Frameworks

I'll explain how to use pt-online-schema-change (part of Percona Toolkit) to perform online schema changes in MySQL without locking tables. This is a critical tool for production database management.

What is pt-online-schema-change?

pt-online-schema-change (pt-osc) performs ALTER TABLE operations without locking the table during the entire operation. It works by:

Creating a copy of the table
Applying schema changes to the copy
Copying data incrementally
Switching the tables atomically

Basic Usage

# Basic syntax
pt-online-schema-change D=db,t=table --alter "ALTER SPECIFICATION" --execute
# Example: Add a column
pt-online-schema-change D=users,t=users_table \
  --alter "ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT ''" \
  --execute
# Example: Add an index
pt-online-schema-change D=users,t=users_table \
  --alter "ADD INDEX idx_email (email)" \
  --execute
# Example: Multiple changes
pt-online-schema-change D=users,t=users_table \
  --alter "ADD COLUMN last_login DATETIME, ADD INDEX idx_last_login (last_login), MODIFY COLUMN name VARCHAR(255) NOT NULL" \
  --execute

Common Options

Essential Options

# Specify host and credentials
pt-online-schema-change \
  h=localhost,u=user,p=password \
  D=dbname,t=tablename \
  --alter "ADD COLUMN new_col INT" \
  --execute
# Dry run (won't execute)
pt-online-schema-change D=db,t=table \
  --alter "ADD COLUMN new_col INT" \
  --dry-run
# Print only, don't execute
pt-online-schema-change D=db,t=table \
  --alter "ADD COLUMN new_col INT" \
  --print

Safety Options

# Max lag to check replication
pt-online-schema-change D=db,t=table \
  --alter "ADD COLUMN new_col INT" \
  --max-lag=5 \
  --execute
# Chunk size for copying data
pt-online-schema-change D=db,t=table \
  --alter "ADD COLUMN new_col INT" \
  --chunk-size=1000 \
  --chunk-time=2 \
  --execute
# Throttle rate
pt-online-schema-change D=db,t=table \
  --alter "ADD COLUMN new_col INT" \
  --max-load="Threads_running=25,Threads_connected=100" \
  --execute

PHP Integration Example

Basic PHP Wrapper

<?php
class OnlineSchemaChange
{
    private $host;
    private $user;
    private $password;
    private $database;
    private $table;
    public function __construct($host, $user, $password, $database, $table)
    {
        $this->host = $host;
        $this->user = $user;
        $this->password = $password;
        $this->database = $database;
        $this->table = $table;
    }
    public function alterTable($alterStatement, $options = [])
    {
        $cmd = sprintf(
            'pt-online-schema-change h=%s,u=%s,p=%s D=%s,t=%s --alter "%s"',
            $this->host,
            $this->user,
            $this->password,
            $this->database,
            $this->table,
            $alterStatement
        );
        // Add options
        if (isset($options['dry_run']) && $options['dry_run']) {
            $cmd .= ' --dry-run';
        } else {
            $cmd .= ' --execute';
        }
        if (isset($options['max_lag'])) {
            $cmd .= ' --max-lag=' . $options['max_lag'];
        }
        if (isset($options['chunk_size'])) {
            $cmd .= ' --chunk-size=' . $options['chunk_size'];
        }
        // Execute command
        exec($cmd . ' 2>&1', $output, $returnCode);
        if ($returnCode !== 0) {
            throw new Exception("Schema change failed: " . implode("\n", $output));
        }
        return implode("\n", $output);
    }
    public function addColumn($columnName, $definition)
    {
        return $this->alterTable("ADD COLUMN $columnName $definition");
    }
    public function addIndex($indexName, $columns)
    {
        return $this->alterTable("ADD INDEX $indexName ($columns)");
    }
    public function dropColumn($columnName)
    {
        return $this->alterTable("DROP COLUMN $columnName");
    }
    public function modifyColumn($columnName, $newDefinition)
    {
        return $this->alterTable("MODIFY COLUMN $columnName $newDefinition");
    }
}
// Usage
$schema = new OnlineSchemaChange(
    'localhost',
    'db_user',
    'password',
    'my_database',
    'users'
);
try {
    $result = $schema->addColumn('favorite_color', "VARCHAR(50) NOT NULL DEFAULT ''");
    echo "Schema change completed successfully\n";
    echo $result;
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Advanced PHP Implementation

<?php
class MySQLOnlineSchemaChange
{
    private $config;
    private $pdo;
    public function __construct($config)
    {
        $this->config = $config;
        $this->connect();
    }
    private function connect()
    {
        $dsn = "mysql:host={$this->config['host']};dbname={$this->config['database']};charset=utf8mb4";
        $this->pdo = new PDO($dsn, $this->config['user'], $this->config['password']);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    public function runOnlineSchemaChange($table, $alter, $options = [])
    {
        // Default options
        $defaults = [
            'max_lag' => 10,
            'chunk_size' => 1000,
            'timeout' => 86400, // 24 hours
            'log_file' => null,
            'dry_run' => false
        ];
        $options = array_merge($defaults, $options);
        // Build command
        $cmd = [
            'pt-online-schema-change',
            "h={$this->config['host']}",
            "u={$this->config['user']}",
            "p={$this->config['password']}",
            "D={$this->config['database']}",
            "t=$table",
            "--alter=\"$alter\""
        ];
        // Add options
        if ($options['max_lag']) {
            $cmd[] = "--max-lag={$options['max_lag']}";
        }
        if ($options['chunk_size']) {
            $cmd[] = "--chunk-size={$options['chunk_size']}";
        }
        if ($options['timeout']) {
            $cmd[] = "--timeout={$options['timeout']}";
        }
        if ($options['dry_run']) {
            $cmd[] = '--dry-run';
        } else {
            $cmd[] = '--execute';
        }
        if ($options['log_file']) {
            $cmd[] = ">" . $options['log_file'];
        }
        $command = implode(' ', $cmd);
        // Execute
        exec($command . ' 2>&1', $output, $returnCode);
        return [
            'success' => $returnCode === 0,
            'output' => implode("\n", $output),
            'command' => $command
        ];
    }
    public function checkReplicationLag($table)
    {
        // Check replication status
        $stmt = $this->pdo->query("SHOW SLAVE STATUS");
        $status = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$status) {
            return ['lag' => 0, 'is_replica' => false];
        }
        return [
            'lag' => isset($status['Seconds_Behind_Master']) 
                     ? (int)$status['Seconds_Behind_Master'] 
                     : 0,
            'is_replica' => true
        ];
    }
    public function validateTable($table)
    {
        // Check if table exists
        $stmt = $this->pdo->prepare("SHOW TABLES LIKE ?");
        $stmt->execute([$table]);
        return $stmt->rowCount() > 0;
    }
    public function getTableSize($table)
    {
        $stmt = $this->pdo->query(
            "SELECT 
                data_length + index_length as table_size,
                table_rows
             FROM information_schema.tables 
             WHERE table_schema = ? AND table_name = ?"
        );
        $stmt->execute([$this->config['database'], $table]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}
// Usage with error handling
$config = [
    'host' => 'localhost',
    'user' => 'root',
    'password' => 'password',
    'database' => 'production'
];
try {
    $osc = new MySQLOnlineSchemaChange($config);
    // Check table exists
    if (!$osc->validateTable('users')) {
        throw new Exception("Table 'users' does not exist");
    }
    // Get table size for estimation
    $tableInfo = $osc->getTableSize('users');
    echo "Table size: " . round($tableInfo['table_size'] / 1024 / 1024, 2) . "MB\n";
    // Check replication lag
    $replicationStatus = $osc->checkReplicationLag('users');
    if ($replicationStatus['lag'] > 30) {
        throw new Exception("Replication lag is too high: {$replicationStatus['lag']} seconds");
    }
    // Perform schema change
    $result = $osc->runOnlineSchemaChange('users', 
        'ADD COLUMN age INT NULL AFTER email',
        [
            'max_lag' => 5,
            'chunk_size' => 500,
            'dry_run' => false,
            'log_file' => '/var/log/pt-osc.log'
        ]
    );
    if ($result['success']) {
        echo "Schema change completed successfully!\n";
        echo $result['output'];
    } else {
        echo "Schema change failed!\n";
        echo $result['output'];
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Best Practices

Always Test First

# Dry run in production
pt-online-schema-change D=mydb,t=mytable --alter "ADD COLUMN test_col INT" --dry-run
# Test on staging database first
pt-online-schema-change h=staging-host D=mydb,t=mytable --alter "ADD COLUMN test_col INT" --execute

Monitor During Operation

// PHP monitoring script
$command = 'pt-online-schema-change D=mydb,t=mytable --alter "ADD COLUMN v1 INT" --execute --print';
$process = popen($command, 'r');
while (!feof($process)) {
    $line = fgets($process);
    echo "Progress: " . $line;
    // Log to monitoring system
}
pclose($process);

Safe Options Configuration

# Recommended safe setup
pt-online-schema-change D=db,t=table \
  --alter "ADD COLUMN new_col INT" \
  --chunk-size=500 \
  --chunk-time=2 \
  --max-lag=10 \
  --max-load="Threads_running=50,Threads_connected=200" \
  --critical-load="Threads_running=100,Threads_connected=500" \
  --timeout=3600 \
  --kill-long-queries \
  --execute

Handle Edge Cases

// Special handling for specific MySQL features
class SchemaChangeHandler {
    public function addForeignKey($table, $column, $references)
    {
        // Wait for quiet period
        sleep(60);
        $alter = sprintf(
            'ADD CONSTRAINT fk_%s_%s FOREIGN KEY (%s) REFERENCES %s',
            $table, $column, $column, $references
        );
        // Use lower chunk size for FK operations
        return $this->runOnlineSchemaChange($table, $alter, ['chunk_size' => 100]);
    }
    public function changeEngine($table, $newEngine)
    {
        $alter = sprintf('ENGINE=%s', $newEngine);
        // Special handling for engine changes
        return $this->runOnlineSchemaChange($table, $alter, [
            'chunk_size' => 250,
            'max_lag' => 5
        ]);
    }
}

Common Pitfalls and Solutions

NOT NULL Columns

// Bad: Adding NOT NULL column without default
$alter = "ADD COLUMN new_col INT NOT NULL";
// Good: Provide default or make nullable first
$alter = "ADD COLUMN new_col INT NOT NULL DEFAULT 0";
// Or
$alter = "ADD COLUMN new_col INT NULL";

Foreign Key Issues

// Use --alter-foreign-keys-method
pt-online-schema-change D=db,t=table \
  --alter "ADD COLUMN new_col INT" \
  --alter-foreign-keys-method=auto \
  --execute

Large Tables

// For very large tables, increase chunk-time and monitor memory
pt-online-schema-change D=db,t=huge_table \
  --alter "ADD COLUMN new_col INT" \
  --chunk-size=1000 \
  --chunk-time=5 \
  --max-lag=20 \
  --alter-foreign-keys-method=none \
  --execute

Integration with PHP Frameworks

Laravel Example

<?php
namespace App\Services;
use Illuminate\Support\Facades\DB;
class DatabaseMigrationService
{
    public function addColumn($table, $column, $definition)
    {
        $config = config('database.connections.mysql');
        $cmd = sprintf(
            'pt-online-schema-change h=%s,u=%s,p=%s D=%s,t=%s --alter "ADD COLUMN %s %s" --execute',
            $config['host'],
            $config['username'],
            $config['password'],
            $config['database'],
            $table,
            $column,
            $definition
        );
        exec($cmd, $output, $returnCode);
        if ($returnCode !== 0) {
            throw new \Exception("Migration failed: " . implode("\n", $output));
        }
        Log::info("Schema change completed for table: {$table}");
        return true;
    }
}

This comprehensive guide should help you implement pt-online-schema-change effectively in your PHP applications. Remember to always test in a staging environment first and monitor the operation closely in production.

本文地址： https://bmjup.com/post/38550.html