2

I am working with analysing big data using opencl. I got the data in mysql dump text file (dump.txt). I want to restore the data into mysql database. Then I can connect to the database and do the other stuff related to parallel programming to analysis big data.

I went through some tutorials and questions related this. I found a related question here. link here. But there they mentioned the way to restore .sql dump file. what can we do for dump text files?

Abstract part of my dump text file is here

"94723605719","435035","2013-06-01 23:51:36","2013-06-01","2013","6","1","7","22 ","23","51","36","1","202","-1002409728","1005823215","50000.000",\N,"1613003749 10","50026.000","226","0","0","94723605719","34399725","0","0","0",\N "94722616878","435014","2013-06-01 23:51:52","2013-06-01","2013","6","1","7","22 ","23","51","52","1","202","-1002099361","1005511506","50000.000",\N,"1613002394 31","50127.000","157","0","0","94722616878","34438596","0","0","0",\N "94726556777","435022","2013-06-01 23:51:52","2013-06-01","2013","6","1","7","22 ","23","51","52","1","202","-1002496570","1005910182","50000.000",\N,"1614002967 42","61046.000","226","0","0","94726556777","34399744","0","0","0",\N

Any help is appreciated. Thanks in advance.

Improve this question

3 Answers 3

Reset to default
3

If you have 'real' dump, then you can use:

mysql -u root -p -h local host < your_dump.sql

That said, your example listed in the question seems to hint that you just have the values in a CSV file, in which case you need to first create the table and then load the data into it:

LOAD DATA INFILE 'your_file.txt' 
 INTO TABLE your_table 
 FIELDS TERMINATED BY ',' 
 OPTIONALLY ENCLOSED BY '"';

See MySQL manual for LOAD DATA INFILE for more details.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thanks. It's very helpful.I used following query. LOAD DATA INFILE 'source_event_reload.txt' INTO TABLE source_event_reload FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\N\n' STARTING BY ''; But I could only restore first row of the database. Can you please find where I am fault. Thanks in advance.
LINES TERMINATED BY "\n" would probably be correct. "\N" stands for null while "\n" is used for linefeed. You should also drop the STARTING BY as that's probably irrelevant.
When I remove '\N', It gives error of ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns. Even when I remove STARTING BY, It works as previous(Update one row only). Any suggestion please.
I'm not sure if your example is fully legible, but it seems that you have 29 fields to load. Is that correct? Does your input field have linefeed (\n) as row terminator?
It's actually 28 fields. Every row start in newline.
|
0

I'm used to migrate MySql databases and the best way for me is like this:

Export Database(backup):

mysqldump -u root -p [database_name] >  [dump_filename].sql

Import Database:

mysql -u root -p [database_name] < [dump_filename].sql
Improve this answer

Comments

0

If the database you want to restore doesn't already exist, you need to create it first.

On the command-line, if you're in the same directory that contains the dumped file, use these commands (with appropriate substitutions):

C:\> mysql -u root -p

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;

https://dev.mysql.com/doc/mysql-backup-excerpt/5.6/en/using-mysqldump.html

http://meta.wikimedia.org/wiki/Database_dump_and_restore_examples

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.