MySQL Cloning a MySQL database on the same MySql instance

Question

I would like to write a script which copies my current database sitedb1 to sitedb2 on the same mysql database instance  I know I can dump the sitedb1 to a sql script   mysqldump -u root -p sitedb1  gt   db name sql   and then import it to sitedb2   Is there an easier way  without dumping the first database to a sql file

User · Answer

A simple way to do so if you installed phpmyadmin   Go to your database  select  operation  tab  and you can see the  copy database to  block  Use it and you can copy the database

User · Answer

Best and easy way is to enter these commands in your terminal and set permissions to the root user  Works for me             gt  mysqldump -u root -p db1  gt  dump sql     gt  mysqladmin -u root -p create db2     gt  mysql -u root -p db2  lt  dump sql

User · Answer

This statement was added in MySQL 5 1 7 but was found to be dangerous and was removed in MySQL 5 1 23  It was intended to enable upgrading pre-5 1 databases to use the encoding implemented in 5 1 for mapping database names to database directory names  However  use of this statement could result in loss of database contents  which is why it was removed  Do not use RENAME DATABASE in earlier versions in which it is present   To perform the task of upgrading database names with the new encoding  use ALTER DATABASE db name UPGRADE DATA DIRECTORY NAME instead  http   dev mysql com doc refman 5 1 en alter-database html

User · Answer

In addition to Greg s answer  this is the easiest and fastest way if the new db name doesn t yet exist     echo  create database new db name    mysql -u  lt user gt  -p  lt pwd gt   mysqldump -u  lt user gt  -p  lt pwd gt  db name   mysql -u  lt user gt  -p  lt pwd gt  new db name

User · Answer

As mentioned in Greg s answer  mysqldump db name   mysql new db name is the free  safe  and easy way to transfer data between databases  However  it s also really slow   If you re looking to backup data  can t afford to lose data  in this or other databases   or are using tables other than innodb  then you should use mysqldump   If you re looking for something for development  have all of your databases backed up elsewhere  and are comfortable purging and reinstalling mysql  possibly manually  when everything goes wrong  then I might just have the solution for you   I couldn t find a good alternative  so I built a script to do it myself  I spent a lot of time getting this to work the first time and it honestly terrifies me a little to make changes to it now  Innodb databases were not meant to copied and pasted like this  Small changes cause this to fail in magnificent ways  I haven t had a problem since I finalized the code  but that doesn t mean you won t   Systems tested on  but may still fail on     Ubuntu 16 04  default mysql  innodb  separate files per table Ubuntu 18 04  default mysql  innodb  separate files per table   What it does   Gets sudo privilege and verifies you have enough storage space to clone the database Gets root mysql privileges Creates a new database named after the current git branch Clones structure to new database Switches into recovery mode for innodb Deletes default data in new database Stops mysql Clones data to new database Starts mysql Links imported data in new database Switches out of recovery mode for innodb Restarts mysql Gives mysql user access to database Cleans up temporary files   How it compares with mysqldump  On a 3gb database  using mysqldump and mysql would take 40-50 minutes on my machine  Using this method  the same process would only take  8 minutes   How we use it  We have our SQL changes saved alongside our code and the upgrade process is automated on both production and development  with each set of changes making a backup of the database to restore if there s errors  One problem we ran into was when we were working on a long term project with database changes  and had to switch branches in the middle of it to fix a bug or three   In the past  we used a single database for all branches  and would have to rebuild the database whenever we switched to a branch that wasn t compatible with the new database changes  And when we switched back  we d have to run the upgrades again   We tried mysqldump to duplicate the database for different branches  but the wait time was too long  40-50 minutes   and we couldn t do anything else in the meantime   This solution shortened the database clone time to 1 5 the time  think coffee and bathroom break instead of a long lunch    Common tasks and their time  Switching between branches with incompatible database changes takes 50  minutes on a single database  but no time at all after the initial setup time with mysqldump or this code  This code just happens to be  5 times faster than mysqldump   Here are some common tasks and roughly how long they would take with each method   Create feature branch with database changes and merge immediately    Single database   5 minutes Clone with mysqldump  50-60 minutes Clone with this code   18 minutes   Create feature branch with database changes  switch to master for a bugfix  make an edit on the feature branch  and merge    Single database   60 minutes Clone with mysqldump  50-60 minutes Clone with this code   18 minutes   Create feature branch with database changes  switch to master for a bugfix 5 times while making edits on the feature branch inbetween  and merge    Single database   4 hours  40 minutes Clone with mysqldump  50-60 minutes Clone with this code   18 minutes   The code  Do not use this unless you ve read and understood everything above      bin bash set -e    This script taken from  https   stackoverflow com a 57528198 526741  function now       date    H  M  S        Leading space sets messages off from step progress  echosuccess          printf   e 0 32m  s   s e 0m n     now     1      sleep  1   echowarn          printf   e 0 33m  s   s e 0m n     now     1      sleep  1   echoerror          printf   e 0 31m  s   s e 0m n     now     1      sleep  1   echonotice          printf   e 0 94m  s   s e 0m n     now     1      sleep  1   echoinstructions          printf   e 0 104m  s   s e 0m n     now     1      sleep  1   echostep          printf   e 0 90mStep  s of 13  e 0m n    1      sleep  1    MYSQL CNF PATH   etc mysql mysql conf d recovery cnf  OLD DB  YOUR DATABASE NAME  USER  YOUR MYSQL USER     You can change NEW DB to whatever you like   Right now  it will append the current git branch name to the existing database name BRANCH  git rev-parse --abbrev-ref HEAD  NEW DB    OLD DB    BRANCH   THIS DIR   site upgrades DB CREATED false  tmp file          printf   THIS DIR  NEW DB  s    1    sql on new db          mysql  NEW DB --unbuffered --skip-column-names -u root -p PASS 2 gt  gt    tmp file  errors log      general cleanup          echoinstructions  Leave this running while things are cleaned up          if   -f   tmp file  errors log      then         echowarn  Additional warnings and errors           cat   tmp file  errors log       fi      for f in  THIS DIR  NEW DB    do         echonotice  Deleting temporary files created for transfer             rm -f  THIS DIR  NEW DB           break     done      echonotice  Done       echoinstructions  You can close this now        error cleanup          exitcode           Just in case script was exited while in a prompt     echo      if     exitcode      0     then         echoerror  Script exited prematurely  but exit code was  0        fi      echoerror  The following command on line   BASH LINENO 0   exited with code  exitcode       echo                BASH COMMAND       if     DB CREATED    true    then         echo         echonotice  Dropping database    NEW DB   if created             echo  DROP DATABASE    NEW DB       sql on new db    echoerror  Could not drop database    NEW DB    see warnings       fi      general cleanup      exit  exitcode    trap error cleanup EXIT  mysql path          printf   var lib mysql     old db path          printf   s s      mysql path     OLD DB    new db path          printf   s s      mysql path     NEW DB    get tables           sudo find  var lib mysql  OLD DB -name    frm  -printf   f n     cut -d    -f1   sort    STEP 0   authenticate          printf   e 0 104m      sudo ls  amp  gt   dev null     printf   e 0m      echonotice  Authenticated     echostep      STEP   authenticate  TABLE COUNT  get tables   wc -l  SPACE AVAIL  df -k --output avail   mysql path    tail -n1  SPACE NEEDED   sudo du -s   old db path    SPACE ERR  echo   SPACE AVAIL- SPACE NEEDED    bc  SPACE WARN  echo   SPACE AVAIL- SPACE NEEDED 3    bc  if    SPACE ERR -lt 0    then     echoerror  There is not enough space to branch the database       echoerror  Please free up some space and run this command again       SPACE AVAIL FORMATTED  printf    d   SPACE AVAIL      SPACE NEEDED FORMATTED  printf       SPACE AVAIL FORMATTED d   SPACE NEEDED      echonotice   SPACE NEEDED FORMATTED bytes needed to create database branch      echonotice   SPACE AVAIL FORMATTED bytes currently free      exit 1 elif    SPACE WARN -lt 0    then     echowarn  This action will use more than 1 3 of your available space       SPACE AVAIL FORMATTED  printf    d   SPACE AVAIL      SPACE NEEDED FORMATTED  printf       SPACE AVAIL FORMATTED d   SPACE NEEDED      echonotice   SPACE NEEDED FORMATTED bytes needed to create database branch      echonotice   SPACE AVAIL FORMATTED bytes currently free      printf   e 0 104m      read -p     now   Do you still want to branch the database   y n    -n 1 -r CONFIRM     printf   e 0m      echo     if       CONFIRM      Yy       then         echonotice  Database was NOT branched          exit 1     fi fi  PASS  badpass  connect to db          printf   e 0 104m  s  MySQL root password   e 0m     now       read -s PASS     PASS   PASS -badpass      echo     echonotice  Connecting to MySQL       create db          echonotice  Creating empty database         echo  CREATE DATABASE    NEW DB   CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    mysql -u root -p PASS 2 gt  gt    tmp file  errors log       DB CREATED true   build tables          echonotice  Retrieving and building database structure         mysqldump  OLD DB --skip-comments -d -u root -p PASS 2 gt  gt    tmp file  errors log     pv --width 80  --name     now    gt    tmp file  dump sql       pv --width 80  --name     now     tmp file  dump sql     sql on new db   set debug 1          echonotice  Switching into recovery mode for innodb         printf   mysqld  ninnodb file per table   1 ninnodb force recovery   1 n    sudo tee  MYSQL CNF PATH  gt   dev null   set debug 0          echonotice  Switching out of recovery mode for innodb         sudo rm -f  MYSQL CNF PATH   discard tablespace          echonotice  Unlinking default data                   echo  USE    NEW DB             echo  SET foreign key checks   0           get tables   while read -r line              do echo  ALTER TABLE    line   DISCARD TABLESPACE  SELECT  Table    line   imported              done         echo  SET foreign key checks   1          gt    tmp file  discard tablespace sql       cat   tmp file  discard tablespace sql     sql on new db   pv --width 80 --line-mode --size  TABLE COUNT --name     now    gt   dev null   import tablespace          echonotice  Linking imported data                   echo  USE    NEW DB             echo  SET foreign key checks   0           get tables   while read -r line              do echo  ALTER TABLE    line   IMPORT TABLESPACE  SELECT  Table    line   imported              done         echo  SET foreign key checks   1          gt    tmp file  import tablespace sql       cat   tmp file  import tablespace sql     sql on new db   pv --width 80 --line-mode --size  TABLE COUNT --name     now    gt   dev null   stop mysql          echonotice  Stopping MySQL         sudo  etc init d mysql stop  gt  gt    tmp file  log     start mysql          echonotice  Starting MySQL         sudo  etc init d mysql start  gt  gt    tmp file  log     restart mysql          echonotice  Restarting MySQL         sudo  etc init d mysql restart  gt  gt    tmp file  log     copy data          echonotice  Copying data         sudo rm -f   new db path   ibd     sudo rsync -ah --info progress2   old db path  --include    ibd  --exclude       new db path    give access          echonotice  Giving MySQL user    USER   access to database    NEW DB        echo  GRANT ALL PRIVILEGES ON    NEW DB     to  USER localhost    sql on new db    echostep      STEP   connect to db  EXISTING TABLE  echo  SELECT SCHEMA NAME FROM INFORMATION SCHEMA SCHEMATA WHERE SCHEMA NAME     NEW DB     mysql --skip-column-names -u root -p PASS 2 gt  gt    tmp file  errors log    if     EXISTING TABLE       NEW DB        then         echoerror  Database    NEW DB   already exists          exit 1 fi  echoinstructions  The hamsters are working  Check back in 5-10 minutes   sleep 5  echostep      STEP   create db echostep      STEP   build tables echostep      STEP   set debug 1 echostep      STEP   discard tablespace echostep      STEP   stop mysql echostep      STEP   copy data echostep      STEP   start mysql echostep      STEP   import tablespace echostep      STEP   set debug 0 echostep      STEP   restart mysql echostep      STEP   give access  echo echosuccess  Database    NEW DB   is ready to use   echo  trap general cleanup EXIT   If everything goes smoothly  you should see something like

User · Answer

First create the duplicate database   CREATE DATABASE duplicateddb    Make sure the permissions etc are all in place and   mysqldump -u admin -p originaldb   mysql -u backup -p password duplicateddb

User · Answer

You could use  in pseudocode    FOREACH tbl IN db a      CREATE TABLE db b tbl LIKE db a tbl      INSERT INTO db b tbl SELECT   FROM db a tbl    The reason I m not using the CREATE TABLE     SELECT     syntax is to preserve indices  Of course this only copies tables  Views and procedures are not copied  although it can be done in the same manner   See CREATE TABLE

User · Answer

Using MySQL Utilities  The MySQL Utilities contain the nice tool mysqldbcopy which by default copies a DB including all related objects     tables  views  triggers  events  procedures  functions  and database-level grants     and data from one DB server to the same or to another DB server  There are lots of options available to customize what is actually copied   So  to answer the OP   s question   mysqldbcopy       --source root your password localhost       --destination root your password localhost       sitedb1 sitedb2

User · Answer

mysqladmin create DB name -u DB user --password DB pass  amp  amp            mysqldump -u DB user --password DB pass DB name             mysql     -u DB user --password DB pass -h DB host DB name

User · Answer

If you have triggers in your original database  you can avoid the  Trigger already exists  error by piping a replacement before the import   mysqldump -u olddbuser -p -d olddbname   sed  s  olddbname    newdbname       mysql -u newdbuser -p -D newdbname

User · Answer

You can do something like the following   mysqldump -u username  -p password  database name for clone     mysql -u username  -p password  new database name

User · Answer

As the manual says in Copying Databases you can pipe the dump directly into the mysql client   mysqldump db name   mysql new db name   If you re using MyISAM you could copy the files  but I wouldn t recommend it   It s a bit dodgy   Integrated from various good other answers  Both mysqldump and mysql commands accept options for setting connection details  and much more   like   mysqldump -u  lt user name gt  --password  lt pwd gt   lt original db gt    mysql -u  lt user name gt  -p  lt new db gt    Also  if the new database is not existing yet  you have to create it beforehand  e g  with echo  create database new db name    mysql -u  lt dbuser gt  -p

User · Answer

I don t think there is a method to do this   When PHPMyAdmin does this  it dumps the DB then re-inserts it under the new name

User · Answer

You need to run the command from terminal   command prompt    mysqldump -u  lt user name gt  -p  lt pwd gt   lt original db gt    mysql -u  lt user name gt   lt pwd gt   lt new db gt    e g  mysqldump -u root test db1   mysql -u root test db2  This copies test db1 to test db2 and grant the access to  root   localhost

[mysql] MySQL: Cloning a MySQL database on the same MySql instance

Examples related to mysql

Examples related to database

Examples related to copy

Examples related to clone