LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 12-17-2008, 12:18 PM   #1
Fredde87
Member
 
Registered: Aug 2005
Posts: 158

Rep: Reputation: 30
Mysql table backups


Hi,

I have created a embeedded device for work which uses mysql to store a its settings and data in. I have a upgrade facility for the device which I am working on now. I am just looking into the best way to allow my mysql tables to update if I decided to make changes to them (add columns etc).

I am a but unsure what the easiest way is to alter tables which might have changed but still keeping the data.

All the backup features in mysql administrator seem to first drop all the tables before recreating them, thereby loosing the data in them. I am updating the stored procedures using this method which is easy as there is no harm in dropping them and recreating them, even if they havent changed. However how can I do the same for tables?

Sorry if it is a bit unclear of what I am trying to do. To summerize, I basically want a easy way to generate a big SQL statement which will update my tables. It should in other words add a new column if the column does not exist in the current database or update it if it has changed but still keep the data intackt.



Thanks!
 
Old 12-19-2008, 12:43 AM   #2
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Ubuntu 12.04, Antix19.3
Posts: 3,794

Rep: Reputation: 282Reputation: 282Reputation: 282
It's still a bit confusing. If you talk about update, are you refering to altering the table structures or inserting/updating data or both?

I don't see what the problem is with dropping the table, recreating it and restoring the data (which is what happens when you restore a backup). Can you tell me what you don't like about that?
 
Old 12-19-2008, 04:56 AM   #3
Fredde87
Member
 
Registered: Aug 2005
Posts: 158

Original Poster
Rep: Reputation: 30
Quote:
Originally Posted by Wim Sturkenboom View Post
It's still a bit confusing. If you talk about update, are you refering to altering the table structures or inserting/updating data or both?

I don't see what the problem is with dropping the table, recreating it and restoring the data (which is what happens when you restore a backup). Can you tell me what you don't like about that?
What about if the column has been renamed, will the data still be able to restore to the correct column but with a new name?
 
Old 12-19-2008, 07:29 PM   #4
jlinkels
LQ Guru
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195

Rep: Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043
Quote:
Originally Posted by Fredde87 View Post
To summerize, I basically want a easy way to generate a big SQL statement which will update my tables. It should in other words add a new column if the column does not exist in the current database or update it if it has changed but still keep the data intackt.
That is exactly what you should do, and I am afraid there is no easy shortcut.

In my experience the best thing to do: I assume you have a development version of your MySQL database, or a test environment or you simply perform tests and changes on a copy of your database.

Create a file, say delta.sql.

In your test version you make the alter table statement. If it is succesful, copy it from the terminal and paste it in your delta.sql. Once it is complete, try it on a backed up copy of your live version:

mysql -u user -ppasswd databasename < delta.sql

That does it.

jlinkels
 
Old 12-22-2008, 04:55 AM   #5
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Ubuntu 12.04, Antix19.3
Posts: 3,794

Rep: Reputation: 282Reputation: 282Reputation: 282
After renaming a column, you create a new backup.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Scheduling MySQL database backups with date dependent filenames pepolez Linux - General 1 01-21-2006 09:45 AM
Restoring MySQL backups with mysql-administrator pnellesen Programming 0 04-25-2005 09:53 AM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 12:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM
deleted mysql table in mysql now cant do anything nakkaya Linux - Software 0 03-18-2003 06:03 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 11:53 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration