Duplicate a mysql database from within mysql?

Question

This is probably a longshot but.. is it possible to duplicate a whole database layout (tables, view, procedures, everything) with a query or multiple queries in a stored procedure?

Basically looking for the same functionality as if I would use mysqldump like this

# mysqldump -u root -ppassword --no-data --routines dbname > file
  //create database copyofdbname
# mysql -u root -ppassword copyofdbname < file

Is it possible to do this in a mysql procedure without any external tools?

I guess I could do the tables by using 'show tables' and then a iterate over the results to get the 'create table' statatements for each table and forward them into the new database.
This is just speculation though, no idea how I would copy the stored procedures from the database this way.

score 3 · Accepted Answer · answered Jun 20 '11 at 21:45

3

Basically no, MYSql does not have a duplicate database feature. Some external tools might, but none that I'm aware of. I did this process using php, but I'm sure it can be done using stored procedures or anything other middle tier application. Here's the steps I followed in high level. I assume you know the details of how to do each step.

Create the new DB
Query all the tables of that db, I had access to information_schema so I just did a select from there.
Loop over the tables.
1. run something like this CREATE TABLE dbnew.tableA LIKE dbold.tableA It will duplicate the structure of the table perfectly
2. Insert Select into your new db/table selecting * from old db/table
have lunch or, depending on your database size, watch a movie or re-runs of "the IT crowd"
enjoy your copied database.

Amendment: In my research doing this before, I found that there was a version of mySql that had a duplicate database command, but it was buggy and taken out in future versions. Even if you are running the version with that command available, you are still better off following these steps than using that command.

answered Jun 20 '11 at 21:45

invertedSpear

10,626
5
35
74

Looks good, I didn't know copying tables would be so simple. Thought I'd have to get the 'SHOW CREATE TABLE tblname' and make something fugly. But how about copying stored procedures from the old db to new db? Are they stored in a table in the information schema or something. – Steinthor.palsson Jun 20 '11 at 22:54
Not an expert on stored procedures, but if you have access to the information schema it looks like they are stored in the "routines" table. So you should be able to query them out, loop on the results, and do create statements for the new db within that loop. – invertedSpear Jun 20 '11 at 23:07
I'm trying to get this working, but I'm stuck on one thing. I can't create a database named from a variable. 'CREATE DATABASE in_dbname;' will create a database that is actually named 'in_dbname' instead of the value of that variable. – Steinthor.palsson Jun 22 '11 at 21:07
Is this variable in mysql or are you using a middle tier for your process? (CF, PHP, etc) – invertedSpear Jun 23 '11 at 16:39
It's a mysql variable. I'm passing it to the stored procedure as a VARCHAR. Did some reading and seems that databases can't be created from variables directly. Found something about it being possible using prepare statements, which is sort of a 'string format' in mysql to construct queries, but I couldn't get it to work. I'll post some of my attempts when I get home. – Steinthor.palsson Jun 23 '11 at 17:50
You may want to post them as another question. 1) because specifically what your doing there is not what the original topic of this question is about, and 2) I seem to be the only person (other than you) that is still active in this question and you are venturing into territory I am not experienced in. I rely on the middle tier way too much. – invertedSpear Jun 23 '11 at 20:45
Yeah I figured, just thought I'd see if you had some insight. Thanks though – Steinthor.palsson Jun 23 '11 at 23:30
In addition to using information_schema.procedures you'll need to user information_schema.parameters to get the parameter information for the stored procedures and functions, including the returns clause. – SteveC Dec 20 '15 at 15:11
Views, stored procedures, triggers, constraints (Foreign keys) doesn't copy. – raiserle Jul 31 '16 at 14:21

score 1 · Answer 2 · edited Sep 16 '12 at 20:51

Copy table works for me with this procedure (new db name as argument)

BEGIN
    DECLARE done1 INT DEFAULT FALSE; 
    DECLARE tablename TEXT; 
    DECLARE cursortable CURSOR FOR (
        SELECT table_name
        FROM information_schema.tables
        WHERE
            table_schema='sansentinel'
        ORDER BY table_name ASC
    ); 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
    START TRANSACTION;
    # create db
    set @createinstance := concat("CREATE DATABASE `",INSTANCENAME,"`"); 
    prepare createinstance from @createinstance; 
    execute createinstance;

    OPEN cursortable; 
    read_loop: LOOP 
            FETCH cursortable INTO tablename; 
            IF done1 THEN 
                    LEAVE read_loop; 
            END IF; 

            set @createtable := concat("CREATE TABLE `",INSTANCENAME,"`.`",tablename,"` LIKE `sansentinel`.`",tablename,"`"); 
            prepare createtable from @createtable; 
            execute createtable;
    END LOOP; 
    CLOSE cursortable;
    COMMIT;
END

And where are the rest of the objects, views, procedures, etc? Supply a more complete answer if possible — Yaroslav, Sep 19 '12 at 10:30
Views, stored procedures, triggers, constraints (Foreign keys) doesn't copy. — raiserle, Jul 31 '16 at 14:20

Duplicate a mysql database from within mysql?

2 Answers2