How to avoid "use <database>" statement on mysqldump backups?

Question

I'm using this command syntax :

mysqldump -u<username> -p<password> --no-create-db --databases mydatabase > /var/backups_DB/MyDatabase-$(date +"%d-%m-%Y-%H:%M:%S").sql

But I could not find how to prevent the line "use mydatabase" to be inserted in the generated file. Which option should I use?

Planking

What do you want exactly? Do you want an export ? If yea, try this: $ mysqldump -u [uname] -p[pass] db_name > db_backup.sql – Nickels

@Sajad, the command I'm using is creating the backup file correctly. But the line USE 'mydatabase'; is inserted too and I would like to avoid it. I managed to remove the CREATE DATABASE statement using --no-create-db option, but can't get the other statement out. – Planking

How big of a problem is this – Sundaysundberg

@AsConfused, if I want to restore the backup to another database, I'll always need to open the file and remove or manually modify the database that will be used before using it, rather than simply call directly on the command line for which database I wish to restore the backup. – Planking

I know. So what, write a 20 line program to change it and it is all automated and rejoice in the fact that sqldump allows for 100 options. As you know it allows for dumping of multiple db's into one file. I would love to see how that would be handled otherwise – Sundaysundberg

Remove the --databases option. – Vendetta

@Vendetta It works. Tks ! – Planking

Answer 1

Exactly. Remove --databases...

https://www.computerhope.com/unix/mysqldum.htm

--databases
-B
Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. CREATE DATABASE and USE statements are included in the output before each new database.

Spiroid

This still seems to be the only solution, because there is a --no-create-db flag available but the use <database> is always included if it was dumped using the --databases flag. I guess it makes sense, because it's meant for a multi-database environment, but it would be nice if a new flag --no-use-db or something was supported... forums.asp.net/t/… – Letters

The option --databases doesnt adds the use <database> statements if is combined with --tables option and --no-create-info – Phox

Answer 2

As @wchiquito said in comments, in order to avoid to have the "USE databasename" statement in dump file you must remove the --databases option.

If you don't have the choice, you can remove it afterwards using a Unix sed command :

cat sourcefile.sql | sed '/USE `databasename`;/d' > destfile.sql

Thomas

Mid

Answer 3

Similar to tdaget's answer, you can import a DB dump, for example from a backup with

cat sourcefile.sql | sed '/^CREATE DATABASE/d' | sed '/^USE/d' | mysql newdbname

to a new database.

That would remove the CREATE DATABASE and USE lines.

Abaft