复制整个 MySQL 数据库

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

To remote server

mysqldump mydbname | ssh host2 "mysql mydbcopy"

To local server

mysqldump mydbname | mysql mydbcopy

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;

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>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>"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>"C:\sugarcrm\mysql\bin"\mysql --user=root --password=MyPassword --port=3308 --execute="create database sugarcrm_640_ce;"


C:\sugarcrm_backups\SCRIPTS>"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!!!

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

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]

Making a Copy of a Database

# mysqldump -u root -p password db1 > dump.sql
# mysqladmin -u root -p password create db2
# mysql -u root -p password db2 < dump.sql

This worked for me with command prompt, from OUTSIDE mysql shell:

# mysqldump -u root -p password db1 > dump.sql
# mysqladmin -u root -p password create db2
# mysql -u root -p password db2 < 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.

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

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