Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have the main MySQL database which has the following columns in one of the car table:

 +---------------+-----------------+------+
 |carId          |name             |model |   
 +---------------+-----------------+------+
 |1              |ford             |focus |
 |2              |Toyota           |Yaris |
 +---------------+-----------------+------+ 

Here is what I looking to achieve:

I want to create the new Database in some other msql server and want to create the same table as above and want to add following columns in the new car table

+---------------+-----------------+------------+-----------+------------+
|carId          |name             |model       |make       |workHistory |
+---------------+-----------------+------------+-----------+------------+
|1              |ford             |focus       |2001       |yes         |
|2              |Toyota           |Yaris       |2002       |no          |
+---------------+-----------------+------------+-----------+------------+
  1. I want the new Database table to get data for carId, name and model columns from the main sql database car table. And would only like to insert data for columns (make, workHistory) in new database car table myself. So this new car table will combine data from the main MySQL database and new Database.
  2. If there is any change in main database car table, I want those changes to be sync with car table in new database. For example if we deleted the ID in main database car table, it should be deleted from the new car table as well.
share|improve this question
    
Thanks Ladadadada for editing –  user121080 May 15 '12 at 10:31

2 Answers 2

This is not about replication - just use an additional table:

CREATE TABLE car_more (
    carId INT NOT NULL,
    make INT,
    workHistory
    PRIMARY KEY (carId)
);

If the databases you refer to (do you mean databases or tables?) are on seperate instance of mysql, then use the federated engine to provide remote access to the existing table. Then you can access the data using....

SELECT a.carId, a.name, a.model, b.make, b.workHistory
FROM car a
LEFT JOIN car_more b
ON (a.carId=b.carId);

If they are on the same mysq instance, then just prefix the table names with database names:

SELECT a.carId, a.name, a.model, b.make, b.workHistory
FROM maindb.car a
LEFT JOIN alternatedb.car_more b
ON (a.carId=b.carId);

(you could use mysql amster-master replication to maintain a local copy of the car table - but this is a lot more complicated)

share|improve this answer
    
Thanks symcbean, If the databases you refer to (do you mean databases or tables?) ? –  user121080 May 15 '12 at 10:43
    
Thanks symcbean, Yes thats correct the databases are on seperate instance of mysql . Just to clarify , can I use federated engine to triger the changes to be updated on the altername databse(alternatedb.car) if there were any changes on main database(maindb.car) –  user121080 May 15 '12 at 10:54
    
Yes once configured a federated table behaves just the same as if it were local (except that more than one DBMS instance can read/write it) –  symcbean May 15 '12 at 13:04

First, the database design. Why do you only want the make and workHistory columns in the slave database? It looks to me like they are related and in a one-to-one relationship with the data in the other columns. Logically, the data belongs together... why separate it?

It will be easier on the database management side to have one, canonical copy of all the data that gets replicated.

Second, are you intending on writing to the second database or only reading from it? Read-only is much easier to manage.


Assuming you want a read-only slave, set up replication as per normal and make sure permissions on the slave only allow SELECT.

If you don't want the extra columns in the main table, create a second table with carId, model and workHistory and create a view that joins them on the carId.


If you want to write to the slave, you will need multi-master replication or something similar. It's more complicated. I'll add more if that's what you want.

share|improve this answer
    
Thanks mate, The Reason I want (make& work history) columns in the slave database is because I have 2 departments who will be adding car information on 2 separate databases ,main database has already up and having CAR ID and Car name,car MAKE information ,which is managed by department1 . Now we want Database for Department 2 ,who will be adding extra information (Make& work history ) from data taken from the Car table In the main database. Continue to nex comment –  user121080 May 15 '12 at 11:41
    
Department1 database is the main database where they will be adding or changing Car make and car name information, that is the reason Department 2 database relay on the data from department 1 database . Reason we dont want to add car name and make information Manaully to the department 2 database as we are wanting to Sync data from Deparment 1 database . –  user121080 May 15 '12 at 11:46
    
any solution you can Recommend –  user121080 May 16 '12 at 10:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.