Copy duplicate database without using mysqldump

Question

Without local access to the server  is there any way to duplicate clone a MySQL db  with content and without content  into another without using mysqldump   I am currently using MySQL 4 0

User · Answer

You can duplicate a table without data by running:

CREATE TABLE x LIKE y;

(See the MySQL CREATE TABLE Docs)

You could write a script that takes the output from SHOW TABLES from one database and copies the schema to another. You should be able to reference schema+table names like:

CREATE TABLE x LIKE other_db.y;

As far as the data goes, you can also do it in MySQL, but it's not necessarily fast. After you've created the references, you can run the following to copy the data:

INSERT INTO x SELECT * FROM other_db.y;

If you're using MyISAM, you're better off to copy the table files; it'll be much faster. You should be able to do the same if you're using INNODB with per table table spaces.

If you do end up doing an INSERT INTO SELECT, be sure to temporarily turn off indexes with ALTER TABLE x DISABLE KEYS!

EDIT Maatkit also has some scripts that may be helpful for syncing data. It may not be faster, but you could probably run their syncing scripts on live data without much locking.

User · Answer

Note there is a mysqldbcopy command as part of the add on mysql utilities     https   dev mysql com doc mysql-utilities 1 5 en utils-task-clone-db html

User · Answer

Mysqldump isn t bad solution  Simplest way to duplicate database   mysqldump -uusername -ppass dbname1   mysql -uusername -ppass dbname2  Also  you can change storage engine by this way   mysqldump -uusername -ppass dbname1   sed  s InnoDB RocksDB     mysql -uusername -ppass dbname2

User · Answer

an SQL that shows SQL commands  need to run to duplicate a database from one database to another  for each table there is create a table statement and an insert statement  it assumes both databases are on the same server   select  fromdb   crm   select  todb   crmen    SET group concat max len 100000000    SELECT  GROUP CONCAT  concat  CREATE TABLE     todb       table name    LIKE     fromdb       table name     n    INSERT INTO     todb       table name    SELECT   FROM     fromdb       table name         SEPARATOR   n n    as sqlstatement  FROM information schema tables where table schema  fromdb and TABLE TYPE  BASE TABLE

User · Answer

Actually i wanted to achieve exactly that in PHP but none of the answers here were very helpful so here s my     pretty straightforward     solution using MySQLi      Database variables   DB HOST    localhost    DB USER    root    DB PASS    1234     DB SRC    existing db    DB DST    newly created db         MYSQL Connect   mysqli   new mysqli   DB HOST   DB USER   DB PASS   or die   mysqli- gt error          Create destination database   mysqli- gt query   CREATE DATABASE  DB DST    or die   mysqli- gt error          Iterate through tables of source database   tables    mysqli- gt query   SHOW TABLES FROM  DB SRC    or die   mysqli- gt error     while   table    tables- gt fetch array       TABLE    table 0            Copy table and contents in destination database       mysqli- gt query   CREATE TABLE  DB DST  TABLE LIKE  DB SRC  TABLE    or die   mysqli- gt error         mysqli- gt query   INSERT INTO  DB DST  TABLE SELECT   FROM  DB SRC  TABLE    or die   mysqli- gt error      endwhile

User · Answer

The best way to clone database tables without mysqldump    Create a new database  Create clone-queries with query   SET  NewSchema    your new db   SET  OldSchema    your exists db   SELECT CONCAT  CREATE TABLE    NewSchema     table name    LIKE    TABLE SCHEMA      table name   INSERT INTO    NewSchema     table name   SELECT   FROM    TABLE SCHEMA      table name       FROM information schema TABLES where TABLE SCHEMA    OldSchema AND TABLE TYPE     VIEW    Run that output     But note  script above just fast clone tables - not views  triggers and user-functions  you can fast get structure by mysqldump --no-data --triggers -uroot -ppassword   and then use to clone only insert statement    Why it is actual question  Because uploading of mysqldumps is ugly slow if DB is over 2Gb  And you can t clone InnoDB tables just by copying DB files  like snapshot backuping

User · Answer

I don t really know what you mean by  local access   But for that solution you need to be able to access over ssh the server to copy the files where is database is stored   I cannot use mysqldump  because my database is big  7Go  mysqldump fail  If the version of the 2 mysql database is too different it might not work  you can check your mysql version using mysql -V   1  Copy the data from your remote server to your local computer  vps is the alias to your remote server  can be replaced by root 1 2 3 4    ssh vps  etc init d mysql stop scp -rC vps  var lib mysql   tmp var lib mysql ssh vps  etc init d apache2 start   2  Import the data copied on your local computer   etc init d mysql stop sudo chown -R mysql mysql  tmp var lib mysql sudo nano  etc mysql my cnf - gt   mysqld  - gt  datadir  tmp var lib mysql  etc init d mysql start   If you have a different version  you may need to run   etc init d mysql stop mysql upgrade -u root -pPASSWORD --force  that step took almost 1hrs  etc init d mysql start

User · Answer

I can see you said you didn t want to use mysqldump  but I reached this page while looking for a similar solution and others might find it as well  With that in mind  here is a simple way to duplicate a database from the command line of a windows server    Create the target database using MySQLAdmin or your preferred method   In this example  db2 is the target database  where the source database db1 will be copied  Execute the following statement on a command line    mysqldump -h  server  -u  user  -p password  db1   mysql -h  server  -u  user  -p password  db2  Note  There is NO space between -p and  password

User · Answer

In PHP   function cloneDatabase  dbName   newDbName       global  admin       db check    mysql select db    dbName         getTables       admin- gt query  SHOW TABLES            tables     array        while  row   mysql fetch row  getTables             tables          row 0              createTable        mysql query  CREATE DATABASE   newDbName  DEFAULT CHARACTER SET utf8 COLLATE utf8 general ci    or die mysql error         foreach  tables as  cTable            db check        mysql select db    newDbName             create          admin- gt query  CREATE TABLE  cTable LIKE    dbName      cTable           if   create                 error      true                     insert          admin- gt query  INSERT INTO  cTable SELECT   FROM    dbName      cTable             return  isset  error          usage  clone    cloneDatabase  dbname   newdbname        first  toCopy  second  new database

User · Answer

If you are using Linux  you can use this bash script   it perhaps needs some additional code cleaning but it works     and it s much faster then mysqldump mysql      bin bash  DBUSER user DBPASSWORD pwd DBSNAME sourceDb DBNAME destinationDb DBSERVER db example com  fCreateTable    fInsertData    echo  Copying database      may take a while       DBCONN  -h   DBSERVER  -u   DBUSER  --password   DBPASSWORD   echo  DROP DATABASE IF EXISTS   DBNAME     mysql   DBCONN  echo  CREATE DATABASE   DBNAME     mysql   DBCONN  for TABLE in  echo  SHOW TABLES    mysql  DBCONN  DBSNAME   tail -n  2   do         createTable  echo  SHOW CREATE TABLE   TABLE   mysql -B -r  DBCONN  DBSNAME tail -n  2 cut -f 2-          fCreateTable    fCreateTable      createTable           insertData  INSERT INTO   DBNAME    TABLE  SELECT   FROM   DBSNAME    TABLE           fInsertData    fInsertData      insertData   done  echo   fCreateTable    fInsertData    mysql  DBCONN  DBNAME

User · Answer

All of the prior solutions get at the point a little  however  they just don t copy everything over  I created a PHP function  albeit somewhat lengthy  that copies everything including tables  foreign keys  data  views  procedures  functions  triggers  and events  Here is the code      This function takes the database connection  an existing database  and the new database and duplicates everything in the new database     function copyDatabase  c   oldDB   newDB            creates the schema if it does not exist      schema    CREATE SCHEMA IF NOT EXISTS   newDB         mysqli query  c   schema           selects the new schema     mysqli select db  c   newDB           gets all tables in the old schema      tables    SELECT table name                FROM information schema tables                WHERE table schema      oldDB                  AND table type    BASE TABLE         results   mysqli query  c   tables           checks if any tables were returned and recreates them in the new schema  adds the foreign keys  and inserts the associated data     if  mysqli num rows  results   gt  0                recreates all tables first         while   row   mysqli fetch array  results                  table    CREATE TABLE   newDB    row 0   LIKE   oldDB    row 0                 mysqli query  c   table                         resets the results to loop through again         mysqli data seek  results  0               loops through each table to add foreign key and insert data         while   row   mysqli fetch array  results                     inserts the data into each table              data    INSERT IGNORE INTO   newDB    row 0   SELECT   FROM   oldDB    row 0                 mysqli query  c   data                   gets all foreign keys for a particular table in the old schema              fks    SELECT constraint name  column name  table name  referenced table name  referenced column name                     FROM information schema key column usage                     WHERE referenced table name IS NOT NULL                     AND table schema      oldDB                       AND table name      row 0                   fkResults   mysqli query  c   fks                   checks if any foreign keys were returned and recreates them in the new schema                Note  ON UPDATE and ON DELETE are not pulled from the original so you would have to change this to your liking             if  mysqli num rows  fkResults   gt  0                    while   fkRow   mysqli fetch array  fkResults                          fkQuery    ALTER TABLE   newDB    row 0                                                                 ADD CONSTRAINT   fkRow 0                                   FOREIGN KEY    fkRow 1    REFERENCES   newDB    fkRow 3     fkRow 1                                    ON UPDATE CASCADE                                 ON DELETE CASCADE                        mysqli query  c   fkQuery                                                              gets all views in the old schema      views    SHOW FULL TABLES IN   oldDB  WHERE table type LIKE  VIEW                         results   mysqli query  c   views           checks if any views were returned and recreates them in the new schema     if  mysqli num rows  results   gt  0            while   row   mysqli fetch array  results                  view    SHOW CREATE VIEW   oldDB    row 0                  viewResults   mysqli query  c   view                viewRow   mysqli fetch array  viewResults               mysqli query  c  preg replace   CREATE     VIEW     CREATE VIEW   str replace  oldDB   newDB   viewRow 1                              gets all triggers in the old schema      triggers    SELECT trigger name  action timing  event manipulation  event object table  created                  FROM information schema triggers                  WHERE trigger schema      oldDB                           results   mysqli query  c   triggers           checks if any triggers were returned and recreates them in the new schema     if  mysqli num rows  results   gt  0            while   row   mysqli fetch array  results                  trigger    SHOW CREATE TRIGGER   oldDB    row 0                  triggerResults   mysqli query  c   trigger                triggerRow   mysqli fetch array  triggerResults               mysqli query  c  str replace  oldDB   newDB   triggerRow 2                             gets all procedures in the old schema      procedures    SHOW PROCEDURE STATUS WHERE db      oldDB          results   mysqli query  c   procedures           checks if any procedures were returned and recreates them in the new schema     if  mysqli num rows  results   gt  0            while   row   mysqli fetch array  results                  procedure    SHOW CREATE PROCEDURE   oldDB    row 1                  procedureResults   mysqli query  c   procedure                procedureRow   mysqli fetch array  procedureResults               mysqli query  c  str replace  oldDB   newDB   procedureRow 2                             gets all functions in the old schema      functions    SHOW FUNCTION STATUS WHERE db      oldDB          results   mysqli query  c   functions           checks if any functions were returned and recreates them in the new schema     if  mysqli num rows  results   gt  0            while   row   mysqli fetch array  results                  function    SHOW CREATE FUNCTION   oldDB    row 1                  functionResults   mysqli query  c   function                functionRow   mysqli fetch array  functionResults               mysqli query  c  str replace  oldDB   newDB   functionRow 2                             selects the old schema  a must for copying events      mysqli select db  c   oldDB           gets all events in the old schema      query    SHOW EVENTS               WHERE db      oldDB           results   mysqli query  c   query           selects the new schema again     mysqli select db  c   newDB           checks if any events were returned and recreates them in the new schema     if  mysqli num rows  results   gt  0            while   row   mysqli fetch array  results                  event    SHOW CREATE EVENT   oldDB    row 1                  eventResults   mysqli query  c   event                eventRow   mysqli fetch array  eventResults               mysqli query  c  str replace  oldDB   newDB   eventRow 3

[mysql] Copy/duplicate database without using mysqldump

Examples related to mysql