Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

extarct only certain tables from dayabase

Status
Not open for further replies.

foxup

Programmer
Dec 14, 2010
328
CA
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
 
easiest/quickest way is to use mysqldump
Code:
mysqldump --opt --host=localhost --user=username --password=mypassword mydatabase table1 table2 table3 > mySqlOutput.sql

 
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
 
I wonder whether you have tried what I posted
 
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.
 
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!
 
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...
 
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!
 
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!
 
It is frustrating not to be given the full problem before answering. Not once did you mention where clauses.

See the mysqldump manual.
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.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top