Mysql – How to sync a subset of tables between two databases on the differnet thesql database server

MySQLsynchronization

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.

Best Answer

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)

Related Topic