[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL table export and import

Posted on 2009-02-19
13
Medium Priority
?
1,652 Views
Last Modified: 2013-12-16

I have a large table 'term' in MySQL 5.0 Db  in database 'prod'. Access to Db is password protected.  I want to be able to export this table from my Win XP PC, copy to my flash drive, insert the flash drive in another Fedora Linux PC. Then run the script, so that the exactly same table data gets imported in the MySQL 5.0 Db on the Linux PC in the same 'prod' database. It should also take care that if the table already exists in Linux PC, insert the new rows that are not already on the Linux PC.  This operation would be done on regular basis.

(1)
Please provide the script (both versions - one that need to run on Win XP, and another that need to run on Linux) that would achieve above task.

(2)
Suppose I also have a remote Linux server to which I can log in as root from my Linux local PC. On this server I also have MySQL 5.0 Db running. Please provide the script
that needs to run to import the table into the remote server in 'prod' Db.

0
Comment
Question by:zenguru
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 9

Accepted Solution

by:
Rob Siklos earned 1400 total points
ID: 23683149
Try something like this:

1)
on WinXp:

mysqldump -uUsername -pPassword --skip-add-drop-table --insert-ignore prod > f:\prod.sql

on Linux:
cat /mnt/flash/prod.sql | sed -e 's/^CREATE TABLE/CREATE TABLE IF NOT EXISTS/ig' | mysql -uUsername -pPassword prod
0
 
LVL 9

Assisted Solution

by:Rob Siklos
Rob Siklos earned 1400 total points
ID: 23683171
2)

cat /mnt/flash/prod.sql | sed -e 's/^CREATE TABLE/CREATE TABLE IF NOT EXISTS/ig' | mysql --host=RemoteServerName -uUsername -pPassword prod
0
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 300 total points
ID: 23683220

(1) Please provide the script (both versions - one that need to run on Win XP, and another that need to run on Linux) that would achieve above task.
 
 
from command prompt... 
 
[XP machine] - taking term table dump
cd /path/to/mysql/bin
 
mysqldump -uUsername -pSecretPassword --skip-add-drop-table --insert-ignore prod --tables term > /path/to/flash_drive/prod_term_dump.sql
 
[IMPORTANT STEP]
Once you take dump..then make sure you replace CREATE TABLE with CREATE TABLE IF NOT EXISTS...from the dump file.. this way you can prevent it from droping the table if exists... 
 
[Linux machine] - importing term table into prod db on linux box
 
mysql -uUsername -pSecretPassword prod < /path/to/flash_drive/prod_term_dump.sql
 
 
 
(2) Suppose I also have a remote Linux server to which I can log in as root from my Linux local PC. On this server I also have MySQL 5.0 Db running. Please provide the script
that needs to run to import the table into the remote server in 'prod' Db. 
 
Loading dump file into remote linux box..
 
mysql -hRemote_linux_IP -uUsername -pSecretPassword prod < /path/to/flash_drive/prod_term_dump.sql

Open in new window

0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 300 total points
ID: 23683448
Try these:

A) Exported [dump file] with first statement need to be pruned to contain only DDL for your table as well as data as INSERT script or just INSERT script.

EXPORT:
mysqldump -user [user name] -password=[password] [database name] > [dump file]
IMPORT:
mysql -u [user name] -p [password] [database name] < [dump file]

B) You need to fit in actual paths/file names/values in these scripts. But here only your single table will be exported and imported

EXPORT:
select * into outfile '/tmp/zips.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from zips;
IMPORT:
LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

If your term table contains primary key in target Linux prod database, then only not already existing rows will get inserted. If not, then all rows will get inserted following which you need to remove duplicate records.
0
 

Author Comment

by:zenguru
ID: 23694283
I am still going thru following the steps and putting it all together as I got 4 posts
to my question and thanks.

I have MySQL Administrator GUI application on linux. I can use it to connect with
local MySQL database without any issue.  But I was unable to use it connect with my remote MySQL server.  In the Server Hostname I changed localhost to IP address of my remote server something  like 34.249.178.169 (not the actual ip) and tried
port 3306  and also  3517  but I get error like:

Could not connect to host 34.249.178.169
MySQL Error Nr. 2003
..................

I need this GUI app to get nice view of database schema on the remote MySQL server such as weather tables are InnoDB or MYISAM etc.
0
 
LVL 9

Assisted Solution

by:Rob Siklos
Rob Siklos earned 1400 total points
ID: 23694384
sounds like a firewall issue on the remote server.  Make sure port 3306 is open.
0
 

Author Comment

by:zenguru
ID: 23694565
could you please tell how do I open this port on the remote linux server from my local linux box?
0
 
LVL 9

Assisted Solution

by:Rob Siklos
Rob Siklos earned 1400 total points
ID: 23694823
I'm not sure how to do it from the command line.  Although if you have ssh access to the remote machine you could always set up an ssh tunnel instead of opening the firewall.
0
 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 300 total points
ID: 23700551
It could be privilges issue/problem. Please refer these links to grant all/some privileges to localhost as well as remote systems:
http://dev.mysql.com/doc/refman/5.0/en/grant.html
http://tecfa.unige.ch/guides/mysql/man/manuel_Adding_users.html

For the closed PORT issue, you can turn off the firewall itself or make a exception to the PORT in concern for the firewall. If KDE/GNome desktop are installed and GUI is enabled then it mey be possible to open/close a PORT like in windows.

I am giving solution for PORT 80. Please substitute your PORT.

Since you should not give up your firewall, you will have to add a rule to open this port.

Do:
cd /etc/sysconfig
cp iptables iptables.save_it
vi iptables


You will find lines like this:

-A INPUT -j RH-Lokkit-0-50-INPUT
-A FORWARD -j RH-Lokkit-0-50-INPUT
-A RH-Lokkit-0-50-INPUT -p tcp -m tcp --dport 21 --syn -j ACCEPT
-A RH-Lokkit-0-50-INPUT -p tcp -m tcp --dport 22 --syn -j ACCEPT
-A RH-Lokkit-0-50-INPUT -p tcp -m tcp --dport 23 --syn -j ACCEPT

Enter a line right behind this to open port 80:

-A RH-Lokkit-0-50-INPUT -p tcp -m tcp --dport 80 --syn -j ACCEPT

Save it and restart the service "iptables" as described above and your port 80 will work.

Check this as well:
http://www.dr-chuck.com/csev-blog/000223.html
http://www.linux-noob.com/forums/index.php?showtopic=472

0
 

Author Comment

by:zenguru
ID: 23706996
Thank you for the port tunneling method to make localhost connection to remote MySQL instance. This works.

Now I want to use putty to shh login to my remote linux server as root from within windows xp pc. After successful login, I  would like to run linux shell script  kwrestore.sh  from the command line like to import sql dump file to remore mysql server:
./kwrestore.sh

this script looks like:

mysql -uuser22 -pmypassword prod <  ?

What should I put in place of ?  to reference  mysql dump file kw.sql that resides on C:\sqldb\kw.sql  in the local C drive ?

I know one method is to first transfer kw.sql to remote server via ftp etc. and then do the usual thing. But I would rather be able to directly reference a file from the local hard disk which is much more efficient for me. Is there a way?
0
 
LVL 9

Assisted Solution

by:Rob Siklos
Rob Siklos earned 1400 total points
ID: 23710701
> Is there a way?

The only way I could see was if you had a network share on your PC that you could mount via Samba on the linux machine.  However, this seems like an awful lot of work.

0
 

Author Comment

by:zenguru
ID: 23713687

With IP tunneling setup I am able to connect to remote Mysql database using the GUI MySQL Query Browser app in Win XP.

I wrote this simple script to run on Win XP (.bat file):
mysql -hlocalhost -port=8601 -uuser22 -ppasswd prod < C:\sqldb\autokwds.sql

But unfortunatly above imports data to local mysql database and not remote. Local MysQL instance is on port 3306.

What is so strange when I use Windows MySQL Query Browser Application, with exactly same connection information  (host: localhost,  port:8601, same user name and password as both remote and local have the same user and password), it successfully connects to remote Mysql database.

Can some one explain this?

I hope I can make the above .bat file script to work which is most important to me.

As for Linux, I have no luck as both command line and linux GUI MySQL Query Browser quietly connect to local database and not remote using exactly same information as in Win XP and after successfully setting up port tunneling (to tunnel traffic to port 8601 to remote host on port 3306).
0
 
LVL 9

Assisted Solution

by:Rob Siklos
Rob Siklos earned 1400 total points
ID: 23713796
you need 2 dashes before the "port" argument to the command.

Like this:
mysql -hlocalhost --port=8601 -uuser22 -ppasswd prod < C:\sqldb\autokwds.sql
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Suggested Courses
Course of the Month18 days, 9 hours left to enroll

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question