保持 MySQL 暫存數據庫在同一台伺服器上從生產更新

我剛剛將我的 MySQL 5.5.20 從 EC2 上的 MySQL 遷移到 Amazon RDS。我遇到的一件事是如何讓我的暫存數據庫從生產中更新。以前，我只是每晚執行一次mysqlhotcopy以覆蓋生產中的暫存，一切都很好。但是，RDS 沒有給我 shell 訪問權限，所以我認為我需要一個純 SQL 解決方案來避免mysqldump對另一台伺服器執行操作，然後恢復到 RDS 伺服器，這似乎沒有必要，因為我只想複製到數據庫到-同一台伺服器上的數據庫。我知道 RDS 支持只讀副本，但我需要一個可寫副本，並且我不想為我的暫存數據庫啟動另一個實例，因為它看不到很多操作。

用純 SQL 在同一台伺服器上完成數據庫到數據庫複製的最佳方法是什麼？

我可以看到 mysqldump 有點痛苦，因為將數據傳輸出去然後再輸入。

假設您正在將數據從 proddb 複製到 stagedb。以下是創建用於橫向複製每個表的腳本的查詢：

select
concat
(
   'drop table if exists ',db2,'.',tbl,'; ',
   'create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ',
   'insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';'
)
from (select table_schema db1,table_name tbl
from information_schema.tables where table_schema='stack_dev') tbls,
(select 'stack_test' db2) dbname;

只需將腳本生成為 SQL 文本文件並將文本文件導入目標伺服器：

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" > /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

這對 MyISAM 應該很好用。如果沒有約束，它也應該適用於 InnoDB。如果有限制，您可能必須為導入會話禁用它們：

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
echo "SET FOREIGN_KEY_CHECKS=0;" > /root/CopyFromProdToStage.sql
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" >> /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

更新 2012-03-09 17:14 EST

我有一個儲存過程來完成這個。僅執行一次以下命令：

DROP DATABASE IF EXISTS utility;
CREATE DATABASE utility;
DELIMITER $$
DROP PROCEDURE IF EXISTS `utility`.`CopyDB` $$
CREATE PROCEDURE `utility`.`CopyDB` (sourceDB VARCHAR(64),targetDB VARCHAR(64))
TheStoredProcedure:BEGIN

   DECLARE found_count,ndx,ndx_last INT;
   DECLARE sqlcmd VARCHAR(1024);

   SELECT COUNT(1) INTO found_count
   FROM information_schema.tables
   WHERE table_schema = sourceDB;
   IF found_count = 0 THEN
       LEAVE TheStoredProcedure;
   END IF;

   DROP TABLE IF EXISTS DBTablesToCopy;
   CREATE TABLE DBTablesToCopy
   (
       id INT NOT NULL AUTO_INCREMENT,
       src  VARCHAR(64),
       tgt  VARCHAR(64),
       tbl  VARCHAR(64),
       PRIMARY KEY (id)
   ) ENGINE=MyISAM;
   DROP TABLE IF EXISTS SQLScriptToCopyTables;
   CREATE TABLE SQLScriptToCopyTables
   (
       id INT NOT NULL AUTO_INCREMENT,
       sqltext VARCHAR(1024),
       PRIMARY KEY (id)
   ) ENGINE=MyISAM;

   INSERT INTO DBTablesToCopy (src,tgt,tbl)
   SELECT sourceDB,targetDB,table_name
   FROM information_schema.tables
   WHERE table_schema = sourceDB;

   INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
   (CONCAT('DROP DATABASE IF EXISTS ',targetDB));
   INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
   (CONCAT('CREATE DATABASE ',targetDB));
   SELECT MAX(id) INTO ndx_last FROM DBTablesToCopy;
   SET ndx = 0;
   WHILE ndx < ndx_last DO
       SET ndx = ndx + 1;
       INSERT INTO SQLScriptToCopyTables (sqltext)
       SELECT CONCAT('CREATE TABLE ',tgt,'.',tbl,' LIKE ',src,'.',tbl)
       FROM DBTablesToCopy WHERE id = ndx;
   END WHILE;
   SET ndx = 0;
   WHILE ndx < ndx_last DO
       SET ndx = ndx + 1;
       INSERT INTO SQLScriptToCopyTables (sqltext)
       SELECT CONCAT('INSERT INTO ',tgt,'.',tbl,' SELECT * FROM ',src,'.',tbl)
       FROM DBTablesToCopy WHERE id = ndx;
   END WHILE;

   SELECT MAX(id) INTO ndx_last FROM SQLScriptToCopyTables;
   SET ndx = 0;
   WHILE ndx < ndx_last DO
       SET ndx = ndx + 1;
       SELECT sqltext INTO @stmt FROM SQLScriptToCopyTables WHERE id = ndx;
       PREPARE s1 FROM @stmt;
       EXECUTE s1;
       DEALLOCATE PREPARE s1;
   END WHILE;

END $$
DELIMITER ;

然後，只要您想複製stack_dev到stack_test，請執行以下操作：

call utility.copydb('stack_dev','stack_test');

就這樣。

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/14594