Alex Alex - 1 year ago 84
SQL Question

Show a warning during import, when MySQL dump contains `use`

Let's suppose I have large MySql dump which I want to import to a specific database.

I could use

mysql -D bar --one-database < foo.mysql

has a
use foo;

This command is already doing most of what I want: Ignoring data which would be important to another database than

I could use a
grep -e "^use " foo.mysql
to check if the database dump contains a use statement.

But can I do this also during the import, so I do not have to read the dump twice?

Answer Source

Reading while importing example:

< dump.sql tee >(sed -n '/^USE `[^`]*`;$/ p' 1>&2) | mysql ...

The example will import the file dump.sql into mysql while printing the use-statements as they come by:

USE `blue-racoon`;
USE `funny-basil`;
USE `purple-fish`;

Explanation: If you have a full dump with all databases of the mysql server (--all-databases long option) and you would like to review all SQL USE-statements while the file pipes into mysql, you could make use of tee to duplicate the content on the fly and sed to only print from those duplicated lines if a line is a USE-statement.

Then the filtered output is redirected to STDERR for review while the unfiltered output can be imported as normal by mysql.

I hope this helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download