We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

MySQL table export and import

zenguru
zenguru asked
on
Medium Priority
1,727 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.

Comment
Watch Question

CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Commented:
2)

cat /mnt/flash/prod.sql | sed -e 's/^CREATE TABLE/CREATE TABLE IF NOT EXISTS/ig' | mysql --host=RemoteServerName -uUsername -pPassword prod
UmeshSenior Principal Technical Support Engineer
Top Expert 2009
Commented:

(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

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.

Author

Commented:
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.
CERTIFIED EXPERT
Commented:
sounds like a firewall issue on the remote server.  Make sure port 3306 is open.

Author

Commented:
could you please tell how do I open this port on the remote linux server from my local linux box?
CERTIFIED EXPERT
Commented:
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.
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

Author

Commented:
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?
CERTIFIED EXPERT
Commented:
> 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.

Author

Commented:

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).
CERTIFIED EXPERT
Commented:
you need 2 dashes before the "port" argument to the command.

Like this:
mysql -hlocalhost --port=8601 -uuser22 -ppasswd prod < C:\sqldb\autokwds.sql
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.