本文目录导读：
- 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.
