Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly mysqlhotcopy to overwrite staging from production and all was well. However, RDS doesn't give me shell access so I think I need a pure SQL solution to avoid doing a mysqldump to another server, and then restoring back to the RDS server, which seems unnecessary since I just want to copy to database-to-database on the same server. I know RDS supports read replicas, but I need a writable replica, and I don't want to have to fire up another instance for my staging database, which doesn't see a lot of action.

What's the best way to accomplish a database-to-database copy on the same server in pure SQL?

share|improve this question
    
I updated my answer. I scripted a Stored Procedure for you. –  RolandoMySQLDBA Mar 9 '12 at 22:29
    
Created a gist based on @RolandoMySQLDBA's excellent stored procedure code that avoids creating a utility database and drops tables individually rather than the whole target database. Check it out: gist.github.com/2173103 –  SWrobel Mar 26 '12 at 20:30

1 Answer 1

up vote 2 down vote accepted

I can see mysqldump being a little pain in the neck because of transaporting data out and then in.

Let's say you are copying data from proddb to stagedb. Here is a query to create the script for lateral copying of every table:

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;

Just generate the script as a SQL text file and import the text file to the target server:

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

This should work just fine for MyISAM. It should also work for InnoDB if there are no constraints. If there are constraints, you may have to disable them for the import session:

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

UPDATE 2012-03-09 17:14 EST

I have a stored procedure that will accomplish this. Run the following only once:

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 ;

then anytime you feel like copying stack_dev to stack_test, do this:

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

and that's all.

Give it a Try !!!

share|improve this answer
    
Am I missing something? Seems like this creates tables with the same schema as proddb in stagedb but doesn't copy the data over... –  SWrobel Mar 8 '12 at 2:42
    
OMG what was I thinking? I'll put that code in –  RolandoMySQLDBA Mar 8 '12 at 3:12
    
Here's what I came up with select @stmt := ''; select @stmt := concat(@stmt,line,';') from (select concat('drop table if exists ',stage,'.',tbl,';create table ',stage,'.',tbl,' select * from ',prod,'.',tbl) line from (select table_schema prod,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls, (select 'stack_test' stage) dbname) linez; select @stmt; prepare @stmt; deallocate prepare stmt; however it seems like prepare has a limit on the # of chars in the stmt and is cutting it off... Any ideas? –  SWrobel Mar 8 '12 at 3:20
    
OK I just put the code in, sorry !!! –  RolandoMySQLDBA Mar 8 '12 at 3:24
    
You should be able to the run the query as is without usiung any PREPARE statements. –  RolandoMySQLDBA Mar 8 '12 at 3:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.