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

14.2k views Asked by At

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?

3

There are 3 answers

0
tdaget On

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

0
Alex On

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.

2
nink On

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.