This week's book giveaway is in the HTML Pages with CSS and JavaScript forum.
We're giving away four copies of Testing JavaScript Applications and have Lucas da Costa on-line!
See this thread for details.
Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

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

 
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 40035
809
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Posts: 40035
809
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
 
Ranch Hand
Posts: 1282
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
just my 2 cents:

from mysql site you can download a mysql Administrator utility that allows you to view, export/import and so on
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Don't sweat petty things, or pet sweaty things. But cuddle this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic