Creating a MySQL database from a .sql file

Question

Though a very similar question has been asked (How to convert .sql file to tables in mysql db), my limited experience (read almost zero) of MySQL server means that that particular answer does not help me.

I have followed the steps from the first answer:

1.Create empty database for which I found the following command:

mysqladmin create myDBname

When I check /var/lib/mysql I can see the folder myDBname with a placeholder db.opt file inside.

2.Read in the .sql file like so:

mysql -u root -p myDBname < dotSQLfile.sql

However, this doesn't appear to do anything. Should I expect the myDBname to be populated in some sense in the /var/lib/mysql/myDBname directory?

When I run mysqlshow it only lists 'information_schema' & 'test' as databases...? Yet if I manually run MySQL and source the .sql file, I can see the database within that environment and run queries:

mysql -u root -p

source dotSQLfile.sql;
show tables;
select * from table1;

but as soon as I quit mysql, it's no longer visible. Am I doing something wrong?

I should add I'm using MariaDB on Fedora19; mysqld is running.

Can you post the structure of your first table in dotSQLfile.sql? — AliBZ, Aug 27 '13 at 23:55
Apologies for the horrible formatting... | Field | Type | Null | Key | Default | Extra | code | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(250) | YES | | NULL | | | title | varchar(250) | YES | | NULL | | | owner | varchar(500) | YES | | NULL | — user2723429, Aug 28 '13 at 00:13
@user2723429 The create and import commands seem ok. Are you sure of the dotSQLfile.sql location and that it is a valid sql file? Also are you running mysqlshow as 'root'? If not, perhaps that is why you can't see myDBname. — ılǝ, Aug 28 '13 at 00:20
also, in general I think you shouldn't be looking at the folder structure, rather inspect your databases through the mysql line or a client, such as MySQL Workbench — ılǝ, Aug 28 '13 at 00:21
@ile thanks. If I run msqlshow as root I can see the DB (thanks btw!), but if I inspect the database on the command line via mysql, it's an empty DB, show tables returns an empty set. It's simply not populating the DB. Also, pretty sure it's a valid sql file since, as above, I can 'source' it on the mysql command line and inspect the DB and run queries as expected. — user2723429, Aug 28 '13 at 07:32
@user2723429 could you upload a link to your sql file for inspection (if it is not confidential)? Also, just to double check, you do select (**'use'** command) the database before running 'show tables;' right? — ılǝ, Aug 28 '13 at 07:39

Creating a MySQL database from a .sql file

0 Answers0