How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods?
You can let mysqldump create the dump in such a way that it does not create or select the database.
EXAMPLE : You are dumping the database db1 and loading it into database db2
This will put in the CREATE DATABASE and the USE commands in the dump
mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql
This will not put in the CREATE DATABASE and the USE commands in the dump (this is what you want)
mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
You can load it into another database (such as db2) in one of four(4) ways:
$ mysqldump -u... -p... --routines --triggers db1 | mysql -u... -p... -A -Ddb2
$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql $ mysql -u... -p... -A -Ddb2 < /root/db1.sql
$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql $ mysql -u... -p... -A -Ddb2 mysql> source /root/db1.sql
$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql $ mysql -u... -p... -A mysql> use db2 mysql> source /root/db1.sql
Give it a Try !!!
I am a big fan of dump, edit and insert. but you do not have to open the text file (dump file) to change it (this is especially usefull when it is several milion lines long). if you want to dump the database
mysqldump MYDATABASE > mydump.sql
then use sed to replace the old database name with new one like this
sed -i 's/MYDATABASE/MYNEWDATABASE/g' mydump.sql
then you can simply create the new database and import it again, and it will create all tables with in the new databae MYNEWDATABASE'
mysqladmin create MYNEWDATABASE mysql MYNEWDATABASE < mydump.sql
Edit: Like some nice people in the comments section have pointed out, this can be dangerous, if some of the data is also changed by this above, so, to conctrete ways to avoid this is.
1) Grep for this in the dump, before you change it, like this.
cat mydump.sql | grep "MYDATABASE"
2) we can add some ` to make it safer like this:
sed -i 's/`MYDATABASE`/`MYNEWDATABASE`/g' mydump.sql
if anyone else have concrete suggestions, I am happy to edit my answer in another 4 years.
If the file is what you have in hand and you are manipulating it from a shell / console, I would use
sed to do string replacements on lines starting with
USE and optionnally
-- (mysqldump comments)
Replacing the db name on lines that matches Create Database, Create Table, Use and mysqldump comments
dbfile="yoursqldumpfile.sql"; dbname="current_db_name"; dbnewname="new_db_name"; dbnewfile="/tmp/$dbnewname.sql"; cat $dbfile | sed "/^CREATE DATABASE/ s=$dbname=$dbnewname=" | sed "/^CREATE TABLE/ s=$dbname=$dbnewname=" | sed "/^USE / s=$dbname=$dbnewname=" | sed "/^-- / s=$dbname=$dbnewname=" > $dbnewfile
Of course like many of the answers here mentions, it's easier if the mysqldump backup did not contain a CREATE DATABASE and a USE line. Meaning the options
--databases or their short versions
-B were not used.
If you do a dump using the following two rules:
- Do not use options such as
--database, and simply just put the database name at the end of the command without these options.
- Include the option
If you do the following then mysqldump will create the SQL without referencing the database, then you can use the new database name at the end of your mysql command during import!
mysqldump --no-create-db old_db_name --single-transaction --compress --order-by-primary --host old_db_host -u old_db_user -pOld_db_password | mysql --host new_host -u new_user -pnewpassword new_db_name
Here is the command that would restore only the database named
$BACKUP_FILE backup file, with restored database name
$DB_TO (instead of
(sed '/^-- Current Database: `/q' "$BACKUP_FILE"; sed -n "/^-- Current Database: \`$DB_FROM\`/,/^-- Current Database: \`/p" "$BACKUP_FILE") | sed "s/\`$DB_FROM\`/\`$DB_TO\`/g" | mysql -u root -p
I've answered to a similar question here
If you using macOS and MySQLWorkbench. First export the database into a single .sql file. Then use the "Data import" util. It will show error because the new database name is not matching the exported one. You have to copy the whole excecuting command on the log and:
- replace the --database flag
- remove the --defaults-file flag
Example: sudo /Applications/MySQLWorkbench.app/Contents/MacOS/mysql --protocol=tcp --host=localhost --user=root --port=3306 --default-character-set=utf8 --comments --database=ion_development < "/Users/2359media/dumps/Dump20201110.sql"
You ask if you can "restore mysql database with different name from mysqldump file".
The answer is that you should NOT restore to another database if you have defined triggers with explicit chema name, due to a mysqldump bug, at least not without transforming the dump (see below).
(That is the short answer. You may not like it, you can downvote, but you don't have justification to delete it like because it does answer the question, albeit by the negative, with a critical fact to know about).
When triggers are defined like (CREATE TRIGGER mydbname.mytriggername ...) then mysqldump will include this "mydbname" in the dump regardless of any dump options.
You cannot detect that nuance in the trigger name from the information_schema.triggers in case you would like to repair first.
This makes the dump not restorable on altername db name, or worse, could restore a trigger in the wrong place if such schema exists.
To continue the answer, if you don't have triggers, or they were not using schema names at definition time, then you can restore a dump made without the --databases. (like answers above mentioned).
Otherwise, there is one way to cleanup the dump prior to importing it. You would have to 'sed' it to delete the schema name:
sed 's/\/\*!50003 TRIGGER `mydbname`\./\/\*!50003 TRIGGER /g'
Since practically nobody would inspect the dump for this issue, it is NOT recommended to blindly import dumps from instructions given here; you could damage a prod env.