我在一个多主Galera集群中有三台服务器。 我最近导入了一些旧的数据库，注意到这三个表都在创build，但数据没有被复制。 事实certificate，我没有注意到，这些旧的数据库都使用MyISAM表。 所以我知道将来我需要把它们转换成InnoDB才能使它们工作。
但是，我没有find官方的方式来同步现有的数据。 运行ALTER TABLE将现有表转换为InnoDB不会同步现有数据。
我的想法是用mysqldump转储表（现在已经转换了），然后用mysql -u user -p db < db.sql把它重新join。 我看不出有什么理由不行，但我想知道是否有更好的方法。
我无法find一个正式的方法来处理这个问题，所以我决定单独倾倒桌子并重新input。 不想手工做，我鞭打一个PHP脚本来为我做。 我在这里发布，以防其他人认为这有用。
/* * InnoDB Convert * Converts existing non-InnoDB tables to InnoDB, then re-imports the * data so that it's replicated across the cluster. */ // Configuration $_config['db'] = array( 'type' => 'mysql', 'host' => 'localhost', 'username' => 'user', 'password' => 'password' ); // Establish database connection try { $pdo = new PDO( $_config['db']['type'] . ':host=' . $_config['db']['host'], $_config['db']['username'], $_config['db']['password'] ); } catch ( PDOException $e ) { echo 'Connection failed: ' . $e->getMessage(); } // Get list of databases $db_query = <<<SQL SHOW DATABASES SQL; $db_result = $pdo->prepare( $db_query ); $db_result->execute(); while ( $db_row = $db_result->fetch( PDO::FETCH_ASSOC )) { // Look through databases, but ignores the ones that come with a // MySQL install and shouldn't be part of the cluster if ( !in_array( $db_row['Database'], array( 'information_schema', 'mysql', 'performance_schema', 'testdb' ))) { $pdo->exec( "USE {$db_row['Database']}" ); $table_query = <<<SQL SHOW TABLES SQL; $table_result = $pdo->prepare( $table_query ); $table_result->execute(); while ( $table_row = $table_result->fetch( PDO::FETCH_ASSOC )) { // Loop through all tables $table = $table_row["Tables_in_{$db_row['Database']}"]; $engine_query = <<<SQL SHOW TABLE STATUS WHERE Name = :table SQL; $engine_result = $pdo->prepare( $engine_query ); $engine_result->execute( array( ':table' => $table )); $engine_row = $engine_result->fetch( PDO::FETCH_ASSOC ); if ( $engine_row['Engine'] != 'InnoDB' ) { // Engine is not equal to InnoDB, let's convert it echo "Converting '$table' on '{$db_row['Database']}' from '{$engine_row['Engine']}' to InnoDB:\n"; echo "Modifying engine..."; $change_query = <<<SQL ALTER TABLE $table ENGINE=InnoDB SQL; $change_result = $pdo->prepare( $change_query ); $change_result->execute(); echo "done!\n"; echo " Exporting table..."; exec( "mysqldump -h {$_config['db']['host']} -u {$_config['db']['username']} -p{$_config['db']['password']} {$db_row['Database']} $table > /tmp/dump-file.sql" ); echo "done!\n"; echo " Re-importing table..."; exec( "mysql -h {$_config['db']['host']} -u {$_config['db']['username']} -p{$_config['db']['password']} {$db_row['Database']} < /tmp/dump-file.sql" ); echo "done!\n"; unlink( '/tmp/dump-file.sql' ); echo "done!\n"; } } } }
我在大约两分钟内成功地使用它在几十个数据库上转换了数百个表。