create database from dump file in MySQL 5.0

Question

i want to create database from dump file name "db_ehr.sql" any one can help me to do so..

Answer 1

in mysql console:

create database test;
use test;
source db_ehr.sql;

Answer 2

The file can be executed using mysql command-line console.

shell>mysql --user=user_name --password=user_password --host=host_name --port=port_number<db_ehr.sql

You can specify full path for the file, e.g. d:\dir1\db_ehr.sql

mysql — The MySQL Command-Line Tool

Answer 3

Your db_ehr.sql should look like:

# DATEI:    db_ehr.sql
# ZWECK:    Kommando-Datei für mysql
#           create dbname - ???
# GEBRAUCH: [eh@xx xx]$ mysql  -u root [-p] < db_ehr.sql

# ------ Create Database

USE mysql;

DROP DATABASE IF EXISTS dbname;

CREATE DATABASE dbname;

# ------ Grant Access

GRANT ALL ON dbname.* TO user@'host';

# ------ Create/Fill Tables

The user (root) should be allowed to create databases and the user granted access should have a password (IDENTIFIED BY)

Added evidence:

Reduced version of script above (which 'works' for me too):

DOS E:\proj\lang\sql\mysql\winxpsp3
type demo00.sql
USE mysql;
show databases;
DROP DATABASE IF EXISTS demo;
CREATE DATABASE demo;
show databases;

Usage: redirection:

DOS E:\proj\lang\sql\mysql\winxpsp3
mysql -u root -p < demo00.sql
Enter password: 
Database
...
classicmodels
mysql
...

Database
...
classicmodels
demo
mysql

Usage: source

mysql -u root -p
...
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)
...
mysql> source demo00.sql
Database changed
+--------------------+
| Database           |
+--------------------+
...
| classicmodels      |
| mysql              |
...
+--------------------+
6 rows in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+--------------------+
| Database           |
+--------------------+
...
| classicmodels      |
| demo               |
| mysql              |
...
+--------------------+
7 rows in set (0.00 sec)

I got instructive error messages, when I tried nasty things:

Trying it as user without necessary rights:

ERROR 1044 (42000): Access denied for user 'eh'@'localhost' to database 'demo'

Trying to create demo twice:

ERROR 1007 (HY000): Can't create database 'demo'; database exists

Trying to create a database named 'not a name':

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'not a name' at line 1

So I'm buffled to hear that your experiments failed without some hint at the reason.