Hi every.

I've got two copies of our company database (that we use for our website). It is MySQL and I have a 'laptop' copy and a 'live' copy. They structure of both is almost identical except for the following... I have;

1. Added Indexes to tables
2. Added columns to tables (that are allowed to be NULL)
3. Added Foreign keys

... to the 'laptop' copy. I don't want to effect ANY of the data in the 'live' copy as this is customer information/orders etc. I DO want the extra columns and indexes to be added in identical fashion to how they are on the 'laptop' version.

How can I export/import the structure of the database and all the foreign keys and indexes to my 'live' version without disrupting or deleting any of the data?

Simply by issuing "alter table" and "create index" commands on your productive DB.
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.1/...ate-index.html

As for foreign key, as far as I know, you cannot declare them outside than the table create time.
For that, you would need to create a copy of the original table with a different name, and then issue

insert into newTable(
field1, field2, field3...
)
select 
  field1, field2, field3...
from oldTable

to transfer the datas and create the foreign keys.

But I don't see no way of doing this without offline time...

Originally Posted by tripy

As for foreign key, as far as I know, you cannot declare them outside than the table create time....

Hi Tripy. Thanks for the quick reply. I created the foreign keys using phpmyadmin and didn't do them at the time of creating each table. On the structure page of the relevant table I went to the 'Relation view' link and added foreign keys there.

I forgot to mention that I'm using phpmyadmin - is there a quick way to do this? )

is there a quick way to do this?

I never used it, but surely there must be a way to type your own queries...

Originally Posted by tripy

I never used it, but surely there must be a way to type your own queries...

Yes there is, was just hoping for a quicker solution that typing numerous queries over and over. Never mind

I honnestly don't know. I always have scripted my sql actions, never used a front-end.

I'm a dba, you see..
:-)

you could also export the contents of said table to a csv, edit the csv as you would like to see it in the new database then import the csv.

This would cause no downtime and would be a lot faster in terms of editing each column.

My website actually has a page for exporting MySQL tables to a csv. To import just use LOAD DATA.

Originally Posted by randon

you could also export the contents of said table to a csv, edit the csv as you would like to see it in the new database then import the csv.

This would cause no downtime and would be a lot faster in terms of editing each column.

My website actually has a page for exporting MySQL tables to a csv. To import just use LOAD DATA.

Hi Randon. Thanks for the info - in the end I spent half an hour last night doing the following;

I edited the mysql database connection include file (so no customers could edit data - forcing them to a 'Site Maintenance' page) and copied the data from the live database to my local one. Once that had all been copied correctly (a lot of errors were thrown up regarding the child values of foreign keys etc.) I then used DROP to delete the database online and imported the one from my laptop.

(I then edited the mysql include file so that customers could connect again).