Create MySQL database with sql file import in a single line

Question

I have a sql file that I want to upload from the command line. To do this I first need to go into MySQL and create the database

mysql> create database myDB;

and then call the following from the command line:

mysql -u username -p myDB < myDB.sql

Is there a way to do this all in one line from the command line?

I tried adding the following lines (and various combinations) to the beginning of the myDB.sql file:

create database `myDB`;
use `myDB`;

and then running

mysql -u username -p < myDB.sql

but that did not work.

ERROR 1046 (3D000) at line 7: No database selected

Answer 1

.sql files are generally LITERALLY just a sequence of SQL DDL/DML queries. If it was created by mysqldump, then it should contain EVERYTHING needed to recreate the database from the ground up, including the necessary create db and create table queries.

if they're not there, you can manually add them to the top of the file yourself via simple file concatenation/modification, e.g.

echo 'create database foo;' > new.sql
cat otherstuff.sql >> new.sql
mysql < new.sql

Answer 2

If you generated the mysqldump file with the --databases or --all-databases options, then it puts the correct CREATE DATABASE statements into the dump file.

Answer 3

MySQL reference article Copying MySQL Databases to Another Machine suggests a two-commands solution to this question, creating a database with the MySQL server administration program mysqladmin.

mysqladmin -u username -p create myDB
mysql -u username -p myDB < myDB.sql