Duplicate Entire MySQL Database

Question

Is it posible to duplicate an entire MySQL database on a linux server   I know I can use export and import but the original database is  25MB so that s not ideal   Is it possible using mysqldump or by directly duplicates the database files

User · Answer

Create a mysqldump file in the system which has the datas and use pipe to give this mysqldump file as an input to the new system. The new system can be connected using ssh command.

mysqldump -u user -p'password' db-name | ssh user@some_far_place.com mysql -u user -p'password' db-name

no space between -p[password]

User · Answer

This won t work for InnoDB  Use this workaround only if you are trying to copy MyISAM databases   If locking the tables during backup  and  possibly  pausing MySQL during the database import is acceptable  mysqlhotcopy may work faster   E g   Backup     mysqlhotcopy -u root -p password db name  path to backup directory   Restore   cp  path to backup directory    var lib mysql db name   mysqlhotcopy can also transfer files over SSH  scp   and  possibly  straight into the duplicate database directory   E g     mysqlhotcopy -u root -p password db name  var lib mysql duplicate db name

User · Answer

Once upon a time in MySQL you could just copy all the table files to another directory in the mysql tree  mysql cli - create database db2  linux cli - cp db1 db2

User · Answer

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

User · Answer

Here s a windows bat file I wrote which combines Vincent and Pauls suggestions   It prompts the user for source and destination names   Just modify the variables at the top to set the proper paths to your executables   database ports      Creates a copy of a database with a different name     User is prompted for Src and destination name     Fair Warning  passwords are passed in on the cmd line  modify the script with -p instead if security is an issue     Uncomment the rem d out lines if you want script to prompt for database username  password  etc      See also  http   stackoverflow com questions 1887964 duplicate-entire-mysql-database   set MYSQL HOME  C  sugarcrm mysql bin   set mysqldump exec  MYSQL HOME  mysqldump  set mysql exec  MYSQL HOME  mysql  set SRC PORT 3306  set DEST PORT 3306  set USERNAME TODO USERNAME  set PASSWORD TODO PASSWORD     COMMENT any of the 4 lines below if you don t want to be prompted for these each time and use defaults above   SET  p USERNAME Enter database username    SET  p PASSWORD Enter database password    SET  p SRC PORT Enter SRC database port  usually 3306     SET  p DEST PORT Enter DEST database port     MYSQL HOME  mysql --user  USERNAME  --password  PASSWORD  --port  DEST PORT  --execute  show databases    IF NOT   ERRORLEVEL       0  GOTO ExitScript   SET  p SRC DB What is the name of the SRC Database     SET  p DEST DB What is the name for the destination database  that will be created       mysql exec  --user  USERNAME  --password  PASSWORD  --port  DEST PORT  --execute  create database  DEST DB     mysqldump exec  --add-drop-table --user  USERNAME  --password  PASSWORD  --port  SRC PORT   SRC DB     mysql exec  --user  USERNAME  --password  PASSWORD  --port  DEST PORT   DEST DB   echo SUCCESSFUL     GOTO ExitSuccess   ExitScript  echo  Failed to copy database   ExitSuccess   Sample output   C  sugarcrm backups SCRIPTS gt copy db bat Enter database username  root Enter database password  MyPassword Enter SRC database port  usually 3306   3308 Enter DEST database port  3308  C  sugarcrm backups SCRIPTS gt  C  sugarcrm mysql bin  mysql --user root --password MyPassword --port 3308 --execute  show databases    --------------------    Database              --------------------    information schema     mysql                  performance schema     sugarcrm 550 pro       sugarcrm 550 ce        sugarcrm 640 pro       sugarcrm 640 ce       --------------------  What is the name of the SRC Database   sugarcrm What is the name for the destination database  that will be created    sugarcrm 640 ce  C  sugarcrm backups SCRIPTS gt  C  sugarcrm mysql bin  mysql --user root --password MyPassword --port 3308 --execute  create database sugarcrm 640 ce    C  sugarcrm backups SCRIPTS gt  C  sugarcrm mysql bin  mysqldump --add-drop-table --user root --password MyPassword --port 3308 sugarcrm      C  sugarcrm mysql bin  mysql --user root --password MyPassword --port 3308 sugarcrm 640 ce SUCCESSFUL

User · Answer

This worked for me with command prompt  from OUTSIDE mysql shell     mysqldump -u root -p password db1  gt  dump sql   mysqladmin -u root -p password create db2   mysql -u root -p password db2  lt  dump sql   This looks for me the best way  If zipping  dump sql  you can symply store it as a compressed backup  Cool  For a 1GB database with Innodb tables  about a minute to create  dump sql   and about three minutes to dump data into the new DB db2    Straight copying the hole db directory  mysql data db1  didn t work for me  I guess because of the InnoDB tables

User · Answer

Making a Copy of a Database    mysqldump -u root -p password db1  gt  dump sql   mysqladmin -u root -p password create db2   mysql -u root -p password db2  lt  dump sql

User · Answer

I sometimes do a mysqldump and pipe the output into another mysql command to import it into a different database   mysqldump --add-drop-table -u wordpress -p wordpress   mysql -u wordpress -p wordpress backup

User · Answer

For me the following lines of code did the trick mysqldump --quote-names -q -u username1 --password  password1  originalDB   mysql -u username2 --password  password2  duplicateDB

User · Answer

To remote server  mysqldump mydbname   ssh host2  mysql mydbcopy    To local server  mysqldump mydbname   mysql mydbcopy

[mysql] Duplicate Entire MySQL Database

Examples related to mysql

Examples related to database

Examples related to linux