aspose file tools*
The moose likes JDBC and the fly likes MySql db: how to backup data only and backup tables only Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySql db: how to backup data only and backup tables only" Watch "MySql db: how to backup data only and backup tables only" New topic
Author

MySql db: how to backup data only and backup tables only

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Hi Guys,

I know this q belongs to the mysql forum but I have always enjoyed to use this one.

Here is the deal:

I have created a database with a Java application to a client of mine. Sometimes I'm upgrading the software and the database as well (say adding a new tables).

The problem:
Say I have added a new tables in the db --> I need to update HIS db as well. since I don't want to use "Create tables statments" I thought about doing this:

My Idea:
1. HE--> Backup his DATA only (insert & select NO TABLES). & send me the file
2. ME --> Create a backup for the new DB --> TABLES only! (this is a new version of the old db but with extra tables)
3. ME--> populating all info from step 1 into the new db
4. ME-->back everything (mysqldump -p -u root db>backup.txt) and send him the file
5.HE--> (mysqldump -p -u root db<backup.txt)

Question:

I don't want to write any "create table" statments in my program but I do want to make sure that everything he entererd to the db will transfer to the new db (the one that I added a new table)
so:
1. What is the command to backup only DATA and Only TABLES?
2. Do you have a better solution for this (again, no create tables on my program)

Thanks for any thoughts
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29219
    
134

Peter,
This is definitely a good place to post the question. In fact, someone (not sure if it was you) asked something similar recently. You may want to search other posts in this forum.

If the new database has the same schema, you can export the data to a comma delimited file and then import it. You could do this in an operating system script if you know what platform the client is on.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Thanks Jeanne but I wonder if you could be more specific.

"you can export the data to a comma delimited file and then import it"

right, this is what I had in mind but how can I export only the DATA (not the tables) and how can I import it to an improved db (the one with an extra tables)

I tried reserch but the only thing came up was:
mysqldump -t -n -u root -ppassword -B DB >backup.txt

didnt work :-(
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29219
    
134

Peter,
You're right! I can't find it either. I haven't used mySql in a while, but I'm suprised it doesn't have an export command. Other databases let you create a comma delimited version of the file. It is tied to the order of columns in the table though. Note that the new tables aren't really a problem since they would be empty.
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
you can dump the entire db with:
mysqldump -p -u root myDB>file.txt

the problem is that it includes the creation of all tables (my problem that I want to populate only the data)

if you find any 'insert into' please advise me.

Thank you Jeanne for your help
Peter
Andrew Eccleston
Ranch Hand

Joined: Jul 07, 2004
Posts: 140
Originally posted by Peter Primrose:
you can dump the entire db with:
mysqldump -p -u root myDB>file.txt

the problem is that it includes the creation of all tables (my problem that I want to populate only the data)

if you find any 'insert into' please advise me.

Thank you Jeanne for your help
Peter


Peter, according to mysql's manual, you should be able to include the -t option in your above statement to remove the create statements.

However, you can also use the mysql command with the -e option to get a tab delimited file like so:



Basically, the -e switch lets you execute any sql statement you need, and you can put this into a shell script. The only concern is security wise regarding the fact that you need to include the password in the command when you run this unattended. It's possible for someone else to use the ps command and see your commandline (includeing the password!), if they have the right permissions.

Hope this helps.

andrew


The statement below is true.<br />-------------------------------<br />The statement above is false.
miguel lisboa
Ranch Hand

Joined: Feb 08, 2004
Posts: 1281
just my 2 cents:

from mysql site you can download a mysql Administrator utility that allows you to view, export/import and so on


java amateur
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
Andrew,

First, thank you for your reply.
Second, your solution is great! you are right about the security issue BUT you can do this:

mysql -p -u username -e "select * from my_table;" database_name > my_backup.txt

(without the password)
then you'll be prompt to give the password.

Thanks!
peter
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySql db: how to backup data only and backup tables only
 
Similar Threads
Backup
problem in pro EJB3 chapter 2 code
export database in mysql
DataInputStream with Runtime.getRuntime
populating info into a new database ?