MariaDB Galera 集群，強制同步

我在一個多主 Galera 集群中有三台伺服器。我最近導入了一些舊數據庫，並註意到三個表都在創建，但數據沒有被複製。原來我沒有註意，這些舊數據庫都在使用 MyISAM 表。所以我知道將來我需要將它們轉換為 InnoDB，然後再將它們引入以使它們工作。

但是，我沒有找到同步現有數據的官方方法。執行ALTER TABLE將現有表轉換為 InnoDB 不會同步現有數據。

我的想法是用 轉儲表（現在它已被轉換）mysqldump，然後用mysql -u user -p db < db.sql. 我看不出有什麼不可行的原因，但我想知道是否有更好的方法。

我找不到官方的方法來處理這個問題，所以我想到了單獨轉儲表並重新導入它們的想法。不想手動做，我鞭打了一個 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";
               }
           }
       }
   }

我成功地用它在大約兩分鐘內轉換了幾十個數據庫中的數百個表。

引用自：https://serverfault.com/questions/700405