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)
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)
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, 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.
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.
The statement below is true.<br />-------------------------------<br />The statement above is false.