MySQL File On Desktop
 

I have a large dated (07/05/04) file that is a backup from a MySQL application on our server. This file now is residing on my desktop. I need to get some entries off of the file. There are approximately 6600 list members that need to be extracted from 50,000 list members.

The file is too big to use Apple Works. To extract the 6600 members I need to do a sort or query on the file. I was hoping I could have something on my desktop that would do the job. This is a one time thing due to a computer problem with the server software last week.

I created a new database in phpMyAdmin on the server and chose SQL and entered restore ListMembers and I used the option below the input box to browse the file. However, when I press Go nothing happens even after 5 minutes.

Please tell me what solutions can be used to extract these names out of the big list.

I have searched this forum and several others but either I don't understand the answers or its not spelled out. I looked at the documentation but it suggests restore ListMembers and the path to the file. With it on the desktop how do I specify the path to the file?

Any help will be greatly appreciated.

Thanks in advance,

Randal

I'm not familiar with the format of mySQL backup files but it is probably something that can only be read by mySQL itself. I.e. you need to have the mySQL database server installed & running.
This is a non-trivial task. There have been many threads on these forums about getting mySQL up and running.

I have MySQL up an running I am just trying to get the data into a database I created on my MySQL databases running on the server. If I knew how to reference a file residing on the desktop that would be helpful.

The documentation says restore [File Name] FROM 'path to the file'

I just need to know the 'path to the file' if I need to use MySQL after all.

Thanks,

Randal

I'm not sure what kind of file path SQL is expecting, but on the guess that it is expecting an absolute Unix path, the path to your file on the Desktop would be:

/Users/your_username/Desktop/name_of_file

Hello,

I tried the url you suggested and it appeared to work but it indicates nothing has been transferred. MySQL query is below that I am using.

RESTORE TABLE ListMembers FROM '/Users/rjw/Desktop/1089048410alter.txt'

When I use that command I get this message:

*********

Showing rows 0 - 0 (1 total, Query took 0.0221 sec)

SQL-query*:*[ Edit ]*[ Create PHP Code ]
RESTORE TABLE ListMembers FROM '/Users/rjw/Desktop/1089048410alter.txt'

*********

Here is an excerpt of the file:

-- MySQL dump 8.23
--
-- Host: localhost Database: 12all
---------------------------------------------------------
-- Server version 3.23.58
--
-- Table structure for table `ListMembers`
--

CREATE TABLE ListMembers (
id int(20) NOT NULL auto_increment,
sip text NOT NULL,
comp text NOT NULL,
sdate date NOT NULL default '0000-00-00',
email varchar(250) NOT NULL default '',
name varchar(250) NOT NULL default '',
bounced int(10) NOT NULL default '0',
bounced_d date NOT NULL default '0000-00-00',
active int(10) NOT NULL default '0',
nl int(25) NOT NULL default '0',
field1 varchar(250) NOT NULL default '',
field2 varchar(250) NOT NULL default '',
field3 varchar(250) NOT NULL default '',
field4 varchar(250) NOT NULL default '',
field5 varchar(250) NOT NULL default '',
field6 varchar(250) NOT NULL default '',
field7 varchar(250) NOT NULL default '',
field8 varchar(250) NOT NULL default '',
field9 varchar(250) NOT NULL default '',
field10 varchar(250) NOT NULL default '',
stime time NOT NULL default '00:00:00',
respond varchar(250) NOT NULL default '',
PRIMARY KEY (id),
KEY email (email),
KEY nl (nl),
KEY email_2 (email),
KEY active (active),
KEY id (id)
) TYPE=MyISAM;

--
-- Dumping data for table `ListMembers`
--


INSERT INTO ListMembers VALUES (1,'imported','imported','2004-04-28','email address here','',0,'0000-00-00',0,1,'','','','','','','','','','','00:00:00','');
INSERT INTO ListMembers VALUES (52064,'64.12.116.73','Mozilla/4.0 (compatible; MSIE 6.0; CS 2000 6.0; Wal-Mart Connect 6.0; Windows NT 5.1)','2004-06-09','email address here','',0,'0000-00-00',0,3,'','','','','','','','','','','15:08:22',' ,2,');

*******

I removed the email addresses but the rest of the information is correct.

This file is 6.4 MB in size and has about 50,000 records. As I mentioned, I need to get about 6,000 records extracted from the group that is why I was trying to load them into the table in the database I setup so I could manipulate the data to extract the information I need.

If I am going about this wrong please tell me.

Thanks in advance,

Randal

mySQL dump (what you have) is a complete set of instructions for restoring the database. But it looks like you only dumped a table, not the entire database.

If you are using phpMyAdmin, isn't there a 'Browse' box for files? any filenames you enter would be local on the server unless there's an upload box.

If you have shell access in the server you can do it the fast way. Open the terminal, connect to the server and type :
if you get an error after the `mysql` line, you have a bigger problem - broken mysql.

Unless I've made a syntax boo-boo (doing this from memory) it should load in a couple of minutes.

You can then query it in the usual manner

Thanks for the code. However, it needed to have the password for the DB to be specified so I added the following line.

mysql --user=[add user name] --password=[add password] [name of database]

using the rest of the code you gave me I get the following error

*************

mysql> RESTORE TABLE ListMembers From '/Users/rjw/Desktop/1089048410alter.txt';+-------------+---------+----------+--------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+--------------------------+
| ListMembers | restore | error | Failed copying .frm file |
+-------------+---------+----------+--------------------------+
1 row in set (0.00 sec)

*************

I am not sure how to proceed from here. I have the whole file with a .sql prefix. I had just shortened the file with the .txt prefix.

I actually tried both ways and I got the same error on both.

Any help will be appreciated.

Randal

Reading my previous post reveals an obvious error. If you are connecting to the server via ssh, and the file is on YOUR desktop it's no wonder it's belching out an error.

Copy the file to the server using your method of choice (ftp, scp, finder drag 'n drop, doesn't matter.) , change the path as appropriate and try again.

File extensions are a windows thing - unix generally doesn't care what you call stuff.

Hi,

I copied the file to the server but I still get the same error.

mysql> use old12all
Database changed
mysql> RESTORE TABLE ListMembers FROM '/home/virtual/thefrugallife.com/var/www/html/12all/backups/1089048410_Mon,05-07-2004.sql';
+-------------+---------+----------+--------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+--------------------------+
| ListMembers | restore | error | Failed copying .frm file |
+-------------+---------+----------+--------------------------+
1 row in set (0.77 sec)

Could it be something about the location. I am not sure where to start from so I started from the root?

Thanks,

Randal

starting from root is good.

the comma in the filename bothers me somehow, get rid of it.

also, there was something last week about mysql and permissions. run

chmod 644 filename

to make it readable by all and try again.

I'm gone until the weekend now - keep at it.

Thanks for the help. However, I made the changes to the file and removed the comma and I get the same error.

did you change the permissions?

ok, I got to my own server now (and refreshed the old brain)

forget about RESTORE.

Put the backup file on the same machine as the mySQL client.

Thanks for getting back to me. I did change the permissions like you asked and also I changed the permissions on the local file to owner, group and others to all read and write.

When I shell into mysql and run the statement you gave me I get an error 2 Failed to open file. I tried several variations on /Users/rjw/Desktop/Backup/csvfile.sql thinking the whole path may not be needed after all. However, there was no change.

On the csvfile.sql I changed the name to so it would be easier to remember.

Randal

in the Terminal, post the results of the following commands: (most lines will produce some output). You can type one by one then copy and post the results in their entirety. Don't edit the contents - post the whole works.

cd Desktop/Backup
pwd

ls -l csvfile.sql # adjust as needed

head -75 csvfile.sql

which mysql

mysql -V

status;

I sent the details in a separate email due to the email address that showed up. I didn't want any spiders to get that address.

I also left out the following command.

[emac:~/Desktop/Backup] rjw% status;
status: Command not found.
[emac:~/Desktop/Backup] rjw%


Thanks Again!

There's nothing wrong with your backup file - it loaded perfectly on my server.

However, the `status` command was fairy important. Obviously you don't have a mysql client installed.

Begin a new terminal session, connect to mysql as you usually do, and type the following after you see the `mysql>` prompt: Send the entire terminal session the same way as last time. I'm 75% sure your problem is coming down to paths and permissions.

status;

show databases; #you can edit out any database that is not yours

quit;

pwd

Quick and easy to reload a MySQL dump
 

When I first started with MySQL, I was utterly stumped until I finally understood the issues that MySQL has with UNIX permissions. Using load data infile, restore and others always ended in disaster until that point.

It isn't just the file or the folder that needs RW permissions, it's the entire tree path to the file that needs RW permissions for MySQL to be able to get to it! UGH! It drove me nuts until I figured that out. So I ended up creating a new directory at root "/" and gave it wide open permissions. Finally I could load files again...

Well- that's a security risk. It's inconvenient- Well, to a degree. But there is a MUCH easier way to deal with this: the good 'ole mysql line command!

First, you need to create a database to contain the tables. That has to be done manually:

There, now you have a database named temporary. Next, take your MySQL dump file and stuff it into temporary like this:

A few seconds later (probably longer with a lot of data), your data has been repopulated to the MySOL database temporary and is ready for your perusal. All you need to do is enter your queries to search the data.

I copied the data you had posted above, pasted it into a file named dump.sql and did the above steps. That's the easiest way I can think of to restore a MySQL database without having to jump through the flaming hoops of permissions.

For people who are new to this, in the above commands the "-p" demands a password for MySQL, and the "-e" flag commands MySQL to execute the following command contained in the quotes.

The first line is pretty obvious. The second line isn't. The most important part in that second line is the "<" symbol. This tells UNIX which way to flow the data. In this case, take the data in dump.sql and pipe it into the MySQL command. As the MySQL command is opening the database temporary, it takes the commands contained within dump.sql acts upon them within that database.

I've also had no problems loading the sample data (although I didn't do it the same way). Note, though, that the dump is apparently a table dump only, otherwise we could simply use mysql < file.

The problem here, I think, is that the dump file and the server are on two different machines (post #9?). Using local paths won't work, so I've requested some more detailed info.

Oop. My bad. I should have read through the thread more carefully. I have full access to the server, so I haven't had to do things remotely like this.

On the issue of permissions, MySQL needs a completely clear path to the data file in order to read it. That means every directory in the path has to be wide open to MySQL to get to it -- from / right down to the file itself. (A condition I find disturbing where security is concerned). Otherwise, you will get file access errors.

To get around this, I've put a directory with wide open RW permissions at the top of the tree and allowed MySQL to access this. When I was done, I would wipe the data and directory. Though, in this condition, I was drawing data from a Comma-separated file, not from a MySQL dump.

Second to permissions, could it be a privileges issue on the MySQL side? Could it be as simple as entering these two commands from the server command line?
I am inclined to agree with an earlier suggestion by Acme to copy your data to the server and do your DB admin work there. At least that will get it done. Sadly, I have to confess that I can't think of anything more to offer. (So, I'll just shut up and listen for now.) :)

Thanks for the answers. I am a bit confused about using the word terminal.

I am using a Macintosh and we have a program called terminal that does similar to shelling a server. When I gave you the answers I gave them from the terminal on the Mac.

Are you saying I have to have a copy of MySQL on the Mac? I put the file on the server but in an earlier post (see below) I got the same error.

****************

I copied the file to the server but I still get the same error.

mysql> use old12all
Database changed
mysql> RESTORE TABLE ListMembers FROM '/home/virtual/thefrugallife.com/var/www/html/12all/backups/1089048410_Mon,05-07-2004.sql';
+-------------+---------+----------+--------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+--------------------------+
| ListMembers | restore | error | Failed copying .frm file |
+-------------+---------+----------+--------------------------+
1 row in set (0.77 sec)

Could it be something about the location. I am not sure where to start from so I started from the root?

***********

Once we clarify which device to use shell or terminal, just let me know what answers you need.

I appreciate all your help.

Randal

I think I see the problem now. You are using the RESTORE command to read a DUMP file. RESTORE looks for two files, so it fails looking for `*.frm`

Replace the RESTORE line with

And yes, when anyone here says "Terminal" we mean the shell program of that name - although you can run any shell you want within that window.

Additionally, MacOS X does have MySQL installed. Well, depending on the version of the OS you are running. However, that doesn't necessarily mean that MySQL is actually running.

typing ps -caux | grep mysql should result in a line showing the process mysqld running if MySQL is active. If you have rebooted your machine, MySQL will not be active by default.

To start MySQL, as root user type mysqld_safe &.

There are several threads on MacOSXHints about working with MySQL on a Mac. I only bring it up on this thread because it may help you in working this issue out on your Mac before loading it into a live database.

Acme, I was wholly unaware of the source command in MySQL and I was unable to find reference to it in the mysql.com online reference manual. If you could point out any references I could read, I'd really appreciate it. Thanks!

I tried it again on Randall's sample and can confirm that it worked as expected.

I can't find it in the online manual either - just a billion references to source code.

However, if we return to the Dark Ages and simply type `help` at the mysql prompt, there it is.

source (\.) Execute a SQL script file. Takes a file name as an argument.

Pretty stone-simple - start where we are, do what's in the file as if it was typed at the prompt.

The source command did the trick and it loaded the entire dump which was more than just the one table - Quick!

Thanks so much for all of your help.

Randal