How do I sync a subset of tables between two databases on the differnet mysql database server?

Question

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.

Answer 1

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)

Answer 2

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.