Backing up a MySQL database and restoring it under another name

I am trying to do some maintenance on MySQL database data and I created a dump file with th开发者_C百科e backed up current database.

I want to restore all that data into another database called something like original_db_name_test

Is there a command for that?

This depends on how you invoked mysqldump

If you used mysqldump dbname, then your dump contains neither CREATE DATABASE nor USE DATABASE.

Just create the database with the new name and feed the dump to mysql -D new_dbname.

If you used mysqldump --database dbname, then the dump contains CREATE DATABASE and USE DATABASE statements.

You need to comment them out or replace with new_dbname.

mysql -u usernamehere -p original_db_name_test < yourdumpfilehere.sql

If you used mysqldump to create the dump file, simply:

1. Create a new database (use the mysqladmin command line tool - "mysqladmin create [new database name]").

2. Edit the dump file to add a "USE [new database name];" at the top. (There might be an existing use statement that's commented out, so you can change this and un-comment it.)

3. Import the dump into the new table via "mysql -u <user name> -p < [dump file name]".

Incidentally, when creating a dump via mysqldump, I'd be tempted to use the "--add-drop-table" option, as this will cull any existing table with the same name prior to issuing the table creation statement.

you can use the 'MySQL Workbench' Application and do this with a nice gui