INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

extarct only certain tables from dayabase

extarct only certain tables from dayabase

(OP)
hi

i have a file (a file dump .sql file) which contains many large tables and many others of different sizes. i only want to "extract" some specific tables from this database as doing a "source" on the file takes way too long. is there anything that can be done? it takes wayyyyy to long to "extratc" all tables from the huge database . please help.


thnk, foxup

RE: extarct only certain tables from dayabase

easiest/quickest way is to use mysqldump

CODE

mysqldump --opt --host=localhost --user=username --password=mypassword mydatabase table1 table2 table3 > mySqlOutput.sql 

RE: extarct only certain tables from dayabase

(OP)
I use this command:

Source C:/sql/bin/enswitch.sql;

to bring everything back but it's gi-normous. I only want to bring back 1 tabe from that particular database.

pleas help.

thanks,
foxup

RE: extarct only certain tables from dayabase

I wonder whether you have tried what I posted

RE: extarct only certain tables from dayabase

(OP)
it gives me an error :(

RE: extarct only certain tables from dayabase

perhaps you might share the error?

RE: extarct only certain tables from dayabase

in windoze the command would be the same as I posted although you may need to provide the full path to the mysqldump.exe file. you could shorten it to

CODE

mysqldump -u [username] -p myDatabase targetTable > output.sql 
as --opt and --host=localhost are the defaults.

I have not come across any command called Source.exe. i cannot see how it can work without providing the host name, the user credentials and the target database unless there is a configuration file somewhere; or you are very lucky with matching the defaults. Perhaps altering the config file will achieve your aim. However most of the world uses mysqldump or mysqlhotcopy (for isam tables). or as pointed out you could always use phpmyadmin although that may be overkill if all you need is a dump of a single table.

in any event we cannot help further unless you provide details of where you are going wrong and the precise verbatim error messages etc.

RE: extarct only certain tables from dayabase

(OP)
I'm using windows mysql. OK, I got your command to work but the result isn't correct. the 'mysqldump' command does not give the same result as the following:

Create database enswitch;
Use enswitch;
Source C:/sql/bin/enswitch.sql;
SELECT * FROM cdrs INTO OUTFILE 'cdrs.tsv';



any ideas/help?


thanks,
foxup!

RE: extarct only certain tables from dayabase

Why would it give the same output? One is a select into output and the other is a backup utility.

Mysqldump is correct in what it does. If you dont want the create table and indices to be included in the dump then lookup the options available to mysqldump and add them to the command.

Most people who want a backup want ... A backup...

RE: extarct only certain tables from dayabase

(OP)
in the end-result of the mysqldump, I don't want the beginnign part. The beginnig part of the output file which has this:

- MySQL dump 10.13 Distrib 5.1.72, for Win32 (ia32)
--
-- Host: localhost Database: enswitch
-- ------------------------------------------------------
-- Server version 5.1.72-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `cdrs`
--

LOCK TABLES `cdrs` WRITE;
/*!40000 ALTER TABLE `cdrs` DISABLE KEYS */;

I just want to the data. Is there anyway of getting the 'data only'.

thanks,
FOXUP!

RE: extarct only certain tables from dayabase

(OP)
OK, I found the option but I have 1 simple glitch now. The problem is I want to use a "--where" clause and the critiria is set by 2 variables (ex: date1, date2), so I need is:

mysqldump -uroot -ppasswordhere --tab=/sql/bin --where from_unixtime(start) <=@startdate and from_unixtime(@enddate)" enswitch cdrs > cdrs

nay help on this last detail?

thaks,
FOXUP!

RE: extarct only certain tables from dayabase

It is frustrating not to be given the full problem before answering. Not once did you mention where clauses.

See the mysqldump manual.http://dev.mysql.com/doc/refman/5.1/en/mysqldump.h...

Sfaik there is no possibility to use variables in this function. You would need to build a bash script or concoct the variables within an alternative scripting language like php or python.

Of course in any event the where condition must be enquoted as per the manual.

You should also look at database replication as a potential solution to the business requirements you are attempting to fulfil.

RE: extarct only certain tables from dayabase

(OP)
OK, I just want to re-visit this as I'm still having an issue in speed and I've narrowed it down to this particular command:

Source C:/sql/bin/enswitch.sql;


Basically, what I need to do is 'SOURCE' only certain tables in that .SQL file because that's the reason it's so slow is that there are over 200 tables in that database and I only need 5 of them, all the others are 'in the way'. can anybody help me achieve that?


Thanks,
FOXUP

RE: extarct only certain tables from dayabase

You would either need to be selective about what data went INTO the SQL file or you would need to write a script in some server language that would parse the SQL and remove the entries that related to other tables.

You have been given the help you need to do the former.

Or perhaps run the source command under a set of user credentials that had insert access to only certain tables.

Best of all - look up database replication.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close