CODEWITHSUNDEEP
mysqlsqldatabase
Genadinik
Genadinik

Reputation: 18629

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 the 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?

Upvotes: 3

Views: 9000

Answers (4)

bw_üezi
bw_üezi

Reputation: 4564

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

Upvotes: 0

John Parker
John Parker

Reputation: 54425

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425291

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.

Upvotes: 8

jschorr
jschorr

Reputation: 3054

mysql -u usernamehere -p original_db_name_test < yourdumpfilehere.sql

Upvotes: 2

Related Questions