SQL; Show databases; from my USB key?

Jay D

Hi there,
I have a file on my USB key and want to use it on my laptop MySQL.

Basically it's a database I use on a desktop PC at college. But for revision purposes I need to work on it from home.

I've spent quite a while today searching for solutions. Any help greatly appreciated.

I've tried copying the file into the MySQL folder located in Program files but when I type the show databases command, it doesn't come up. Just the basic ones that came with MySQL.

Colonel Panic

MySQL databases typically aren't one file and not easily portable between systems. The accepted way of doing it is dumping the database to a file on one machine and using that to create the database on another.

How did this file get generated? What extension does it have and is does it contain text of binary data? What did you search for when looking for solutions?

cython

Jay D wrote: »

Hi there,
I have a file on my USB key and want to use it on my laptop MySQL.

Basically it's a database I use on a desktop PC at college. But for revision purposes I need to work on it from home.

I've spent quite a while today searching for solutions. Any help greatly appreciated.

I've tried copying the file into the MySQL folder located in Program files but when I type the show databases command, it doesn't come up. Just the basic ones that came with MySQL.

Why don't you look at something like XAMPP for a portable install of MySQL? To the best of my knowledge you will have issues in simply copying in the datafiles of a DB as many systems have "management" information stored in files outside the storage files of a given schema, and this data includes stuff like the schemas present, etc. In the case of XAMPP you could just plug in the drive and start up the server on any machine you are using. Another solution would be to potentially do a dump and import of the DB but that's a lot of overhead if you want to make changes in each place and copy them back out.

Neon_Lights

could find a dummy site to host the sql database and then dump it when you get home?

Colonel Panic

You guys are getting a bit ahead of yourselves there. We don't even know what this file is he's trying to use!

Jay D

cython wrote: »

Why don't you look at something like XAMPP for a portable install of MySQL? To the best of my knowledge you will have issues in simply copying in the datafiles of a DB as many systems have "management" information stored in files outside the storage files of a given schema, and this data includes stuff like the schemas present, etc. In the case of XAMPP you could just plug in the drive and start up the server on any machine you are using. Another solution would be to potentially do a dump and import of the DB but that's a lot of overhead if you want to make changes in each place and copy them back out.

Thank you. I am going to try that XAMPP. After reading both your advice I'm not very optimistic at all now tbh.
The file size is 5.25kb.

Colonel Panic wrote: »

MySQL databases typically aren't one file and not easily portable between systems. The accepted way of doing it is dumping the database to a file on one machine and using that to create the database on another.

How did this file get generated? What extension does it have and is does it contain text of binary data? What did you search for when looking for solutions?

It's a .sql file extension. I am not sure how it was generated. It contains text data.
I searched for transferring databases between PCs, opening database from usb from my laptop, show databases from my USB key, loads of stuff that didn't really help.

Neon_Lights

Open the file in notepad CTRL + C CTRL + V

Jay D

Neon_Lights wrote: »

Open the file in notepad CTRL + C CTRL + V

Ok I am (probably obviously) a complete beginner with this. Where do I paste it to in order for it to become a database I can show when using MySQL?

Colonel Panic

Paste the contents or part thereof into a post in this thread. Then we can tell if it's a dump you can restore to your instance of MySQL.

Neon_Lights

well using the source code you're effectively replicating the database, then all you need to do is regenerate the files on the other end. You just create all the relevant files on your end, test the DB to see if it works, shows the same and bam, sorted.

maccored

surely you need to create a mysql database and import the data from the one in the usb key? - http://dev.mysql.com/doc/refman/5.1/en/loading-tables.html

Colonel Panic

That really depends on the contents of the file the OP's got on his USB key.

Jay D

Just a makey uppy college database.

# Dream Home Database Creation Script
#
#Created By: Eamon Nolan
#Date: 25 September2012
#
#INSTRUCTIONS:
#
#The script creates a database called DreamHome which has 6 tables corresponding to the tables in the lecture notes.
#
#The script can be executed from the MySql Client program as shown. The script should be placed in a folder called MySql on your home drive
#
#To run the script while using the MySQL Client program use the following command: source H:/MySql/Dream_Home.txt
#


DROP DATABASE IF EXISTS DreamHome;

CREATE DATABASE DreamHome;

USE DreamHome;

CREATE TABLE Branch(
Bno VARCHAR(4) NOT NULL,
Street VARCHAR(40),
Area VARCHAR(20),
City VARCHAR(20),
Pcode VARCHAR(9) NOT NULL,
Tel_No VARCHAR(12) NOT NULL,
Fax_No VARCHAR(20),
PRIMARY KEY (BNo)
);

INSERT INTO Branch VALUES (
"B5","22 Deer Rd","Sidcup","London","SW1 2EH","0171-8861212","0171-8861214"
);

INSERT INTO Branch VALUES (
"B3","163 Main St","Partick","Glasgow","G11 9QX","0141-3392178","0141-3394439"
);

INSERT INTO Branch VALUES (
"B4","32 Manse Rd","Leigh","Bristol","BS99 1NZ","0117-9161170","0117-7761114"
);

INSERT INTO Branch VALUES (
"B2","56 Clover Dr",NULL,"London","NW10 6EU","0181-9631030","0181-4537992"
);


CREATE TABLE Staff(
Sno VARCHAR(4) NOT NULL,
Fname VARCHAR(16) NOT NULL,
Lname VARCHAR(16) NOT NULL,
Address VARCHAR(40),
TelNo VARCHAR(20),
Position VARCHAR(20) NOT NULL,
Sex VARCHAR(1) NOT NULL,
DOB DATE NOT NULL,
SALARY DECIMAL(9,2),
NIN VARCHAR(9) NOT NULL,
Bno VARCHAR(4) NOT NULL,
PRIMARY KEY (Sno)
);

INSERT INTO Staff VALUES (
"SL21","John","White","19 Taylor St,London","0171-8445112","Manager","M","1945-10-01",30000,"WK442011B","B5"
);


INSERT INTO Staff VALUES (
"SG37","Ann","Beech","81 George St,Glasgow","0141-8483345","Senior Assistant","F","1960-01-10",12000,"Wl432514C","B3"
);

INSERT INTO Staff VALUES (
"SG14","David","Ford","63 Ashby St,Glasgow","0141-3392177","Deputy","M","1958-03-24",18000,"WL220658D","B3"
);


INSERT INTO Staff VALUES (
"SA9","Mary","Howe","2 Elm Place,Aberdeen",NULL,"Assistant","F","1970-02-19",9000,"WM532187D","B7"
);

INSERT INTO Staff VALUES (
"SG5","Susan","Brand","5 Gt Western Rd,Glasgow","0141-3342001","Manager","F","1940-06-03",24000,"WK588932E","B3"
);

INSERT INTO Staff VALUES (
"SL41","Julie","Lee","28 Malvern St, Kilburn","0181-5543541","Assistant","F","1965-06-13",9000,"WA290573K","B5"
);


CREATE TABLE Property_For_Rent(
Pno VARCHAR(4) NOT NULL,
Street VARCHAR(40),
Area VARCHAR(20),
City VARCHAR(20),
Pcode VARCHAR(9) NOT NULL,
Type VARCHAR(9),
Rooms SMALLINT,
Rent DECIMAL(7,2),
Ono VARCHAR(4),
Sno VARCHAR(4),
Bno VARCHAR(4),
PRIMARY KEY (Pno)
);

INSERT INTO Property_For_Rent VALUES (
"PA14","16 Hollyhead","Dee","Aberdeen","AB7 5SU","House",6,650,"CO46","SA9","B7"
);

INSERT INTO Property_For_Rent VALUES (
"PL94","6 Argyll St","Kilburn","London","NW2","Flat",4,400,"CO87","SL41","B5"
);


INSERT INTO Property_For_Rent VALUES (
"PG4","6 Lawrence St","Partick","Glasgow","G11 9QX","Flat",3,350,"CO40","SG14","B3"
);

INSERT INTO Property_For_Rent VALUES (
"PG36","2 Manor Rd",NULL,"Glasgow","G32 4QX","Flat",3,375,"CO93","SG37","B3"
);

INSERT INTO Property_For_Rent VALUES (
"PG21","18 Dale Rd","Hyndland","Glasgow","G12","House",5,600,"CO87","SG37","B3"
);

INSERT INTO Property_For_Rent VALUES (
"PG16","5 Novar Dr","Hyndland","Glasgow","G12 9AX","Flat",4,450,"CO93","SG14","B3"
);


CREATE TABLE Renter(
Rno VARCHAR(4) NOT NULL,
Fname VARCHAR(16) NOT NULL,
Lname VARCHAR(16) NOT NULL,
Address VARCHAR(40),
Tel_No VARCHAR(20) NOT NULL,
Pref_Type VARCHAR(12),
Max_Rent DECIMAL(7,2),
PRIMARY KEY (Rno)
);

INSERT INTO Renter VALUES (
"CR76","John","Kay","56 High St, Putney, London, SW1 4EH","0171-774-5632","Flat",425
);


INSERT INTO Renter VALUES (
"CR56","Aline","Stewart","64 Fern Dr. Pollock, Glasgow, G42 OBL","0141-848-1825","Flat",350
);


INSERT INTO Renter VALUES (
"CR74","Mike","Ritchie","18 Tain St, Gourock., PA1G 1YQ","01475-392178","House",750
);


INSERT INTO Renter VALUES (
"CR62","Mary","Tregear","5 Tarbot Rd,Kidary, Aberdeen, AB9 3ST","01224-196720","Flat",600
);


CREATE TABLE Owner(
Ono VARCHAR(4) NOT NULL,
Fname VARCHAR(16) NOT NULL,
Lname VARCHAR(16) NOT NULL,
Address VARCHAR(40),
Tel_No VARCHAR(20) NOT NULL,
PRIMARY KEY (Ono)
);

INSERT INTO Owner VALUES(
"CO46","Joe","Keogh","2 Fergus Dr, Banchory, Aberdeen AB2 7SZ","01224-861212"
);

INSERT INTO Owner VALUES(
"CO87","Carol","Farrel","6 Achray St, Glasgow, G32 9DX","0141-357-7419"
);

INSERT INTO Owner VALUES(
"CO40","Tina","Murphy","63 Well St, Shawlands, Glasgow G42","0141-934-1728"
);

INSERT INTO Owner VALUES(
"CO93","Tony","Shaw","12 Park Pl, Hillhead, Glasgow G4 OQR","0141-225-7025"
);


CREATE TABLE Viewing(
Rno VARCHAR(4) NOT NULL,
Pno VARCHAR(4) NOT NULL,
Date DATE NOT NULL,
Comment VARCHAR(40),
PRIMARY KEY (Rno,Pno)
);

INSERT INTO Viewing Values(
"CR56","PA14","1995-05-24","Too Small"
);

INSERT INTO Viewing Values(
"CR76","PG4","1995-04-20","Too Remote"
);

INSERT INTO Viewing Values(
"CR56","PG4","1995-05-26",NULL
);

INSERT INTO Viewing Values(
"CR62","PA14","1995-05-14","No Dining Room"
);


INSERT INTO Viewing Values(
"CR56","PG36","1995-04-28",NULL
);

Colonel Panic

Okay, that's a dump of your database generated using mysqldump.

It seems like you have MySQL installed. How are you logging into it? Via a command line window, PhpMyAdmin or some other application?

You need to run that script file to set the database up on your database server.

Neon_Lights

Id assume he's running it locally through command seeing as he has a H: drive file path

Jay D

I have MySQL installed yes. Via a command line window.

Could you please explain how I run that script file?

Colonel Panic

You need to execute the script file from the command line.

http://dev.mysql.com/doc/refman/5.5/en/mysql-batch-commands.html

Something like

mysql < "C:\Your\File\Path\FileName.sql"

might be enough...

Neon_Lights

while your there do this tutorial haha http://sql.learncodethehardway.org/book/

Jay D

Thanks for all your help lads. This has taken up far too much time.