Вопрос или проблема
У меня есть основная база данных MySQL, которая содержит следующие столбцы в одной из таблиц автомобилей:
+---------------+-----------------+------+
|carId |name |model |
+---------------+-----------------+------+
|1 |ford |focus |
|2 |Toyota |Yaris |
+---------------+-----------------+------+
Вот что я хочу достичь:
Я хочу создать новую базу данных на другом сервере MySQL и создать ту же таблицу, что и выше, и добавить следующие столбцы в новую таблицу автомобилей:
+---------------+-----------------+------------+-----------+------------+
|carId |name |model |make |workHistory |
+---------------+-----------------+------------+-----------+------------+
|1 |ford |focus |2001 |yes |
|2 |Toyota |Yaris |2002 |no |
+---------------+-----------------+------------+-----------+------------+
- Я хочу, чтобы новая таблица базы данных получала данные для столбцов
carId,nameиmodelиз основной таблицы автомобилей базы данных SQL. И хотел бы вставить данные только для столбцов (make,workHistory) в новую таблицу автомобилей сам. Таким образом, эта новая таблица автомобилей будет сочетанием данных из основной базы данных MySQL и новой базы данных. - Если будут какие-либо изменения в основной таблице автомобилей, я хочу, чтобы эти изменения синхронизировались с таблицей автомобилей в новой базе данных. Например, если мы удалим ID в основной таблице автомобилей, он должен быть удален и из новой таблицы автомобилей.
Дело не в репликации — просто используйте дополнительную таблицу:
CREATE TABLE car_more (
carId INT NOT NULL,
make INT,
workHistory
PRIMARY KEY (carId)
);
Если базы данных, о которых вы говорите (вы имеете в виду базы данных или таблицы?) находятся на отдельных экземплярах MySQL, то используйте федеративный движок для предоставления удаленного доступа к существующей таблице. Затем вы можете получить доступ к данным с помощью….
SELECT a.carId, a.name, a.model, b.make, b.workHistory
FROM car a
LEFT JOIN car_more b
ON (a.carId=b.carId);
Если они находятся на одном экземпляре MySQL, просто добавьте префикс именам таблиц с именами баз данных:
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);
(вы можете использовать MySQL репликацию мастер-майстер, чтобы поддерживать локальную копию таблицы автомобилей — но это намного сложнее)
Во-первых, проектирование базы данных. Почему вы хотите, чтобы в базе данных-ведомом были только столбцы make и workHistory? Мне кажется, что они связаны и находятся в отношении «один к одному» с данными в других столбцах. Логически эти данные принадлежат вместе… почему их разделять?
Управление базой данных будет проще, если будет одна каноническая копия всех данных, которые будут реплицироваться.
Во-вторых, вы собираетесь записывать во вторую базу данных или только читать из нее? Чтение только — намного проще для управления.
Предполагая, что вы хотите ведомую базу данных только для чтения, настройте репликацию в обычном режиме и убедитесь, что разрешения на ведомом сервере позволяют только SELECT.
Если вы не хотите дополнительных столбцов в основной таблице, создайте вторую таблицу со столбцами carId, model и workHistory и создайте представление, которое объединяет их по carId.
Если вы хотите записывать в ведомую базу данных, вам потребуется репликация с несколькими мастерами или что-то подобное. Это более сложно. Я добавлю больше, если это то, что вы хотите.
Ответ или решение
Чтобы синхронизировать подмножество таблиц между двумя базами данных на разных MySQL серверах, вы можете использовать несколько подходов. В вашем конкретном случае у вас есть основные требования по интеграции данных из главной базы данных и возможности добавления собственных данных в новой базе данных. Вот поэтапный подход к реализации вашего запроса.
Шаг 1: Проектирование базы данных
Прежде всего, создайте структуру таблицы в новой базе данных с учетом дополнительных колонок, которые вы хотите добавить. Для этого можно использовать следующий SQL-запрос:
CREATE TABLE new_car (
carId INT NOT NULL,
name VARCHAR(255),
model VARCHAR(255),
make INT,
workHistory VARCHAR(255),
PRIMARY KEY (carId)
);Эта таблица будет хранить данные о автомобилях, включая поля make и workHistory, которые вы будете заполнять самостоятельно.
Шаг 2: Использование FEDERATED Storage Engine
Так как базы данных расположены на различных MySQL серверах, рекомендуется использовать механизм FEDERATED. Этот механизм позволяет сократить сложность, предоставляя возможность обращаться к удаленной таблице как к локальной. Убедитесь, что FEDERATED включен в конфигурации вашей MySQL.
Создание FEDERATED таблицы
На новом сервере создайте таблицу следующим образом:
CREATE TABLE car_federated (
carId INT NOT NULL,
name VARCHAR(255),
model VARCHAR(255),
PRIMARY KEY (carId)
) ENGINE=FEDERATED
CONNECTION='mysql://user:password@main_db_server:port/main_database/car';Замените user, password, main_db_server, port и main_database на соответствующие параметры вашего основного сервера баз данных.
Шаг 3: Заполнение новой таблицы
Теперь можно заполнять новую таблицу new_car, объединяя данные из вашей основной таблицы и добавляя новые данные:
INSERT INTO new_car (carId, name, model, make, workHistory)
SELECT carId, name, model, NULL, NULL FROM car_federated;Здесь вы выбираете данные из таблицы car_federated. Поскольку вы не хотите переносить данные о make и workHistory, вы просто указали NULL для этих колонок.
Шаг 4: Настройка синхронизации данных
Для автоматической синхронизации изменений в главной базе данных вам потребуется настроить триггеры или задачи, которые будут отслеживать изменения в таблице car на основном сервере и отражать их на новом сервере. Например, при удалении записи в основной таблице вы можете использовать триггер, чтобы удалить соответствующую запись в таблице new_car:
Создание триггера
CREATE TRIGGER after_car_delete
AFTER DELETE ON car
FOR EACH ROW
BEGIN
DELETE FROM new_car WHERE carId = OLD.carId;
END;Важно отметить, что триггеры необходимо будет создать на сервере, где находится ваша основная таблица. Это позволит автоматизировать удаление записей.
Шаг 5: Обновление и мониторинг
Гарантируйте, что вы настраиваете регулярную проверку целостности данных между этими двумя базами данных. Это может быть выполнено с помощью различных методов, например, с использованием периодических SQL-запросов, которые будут выявлять расхождения и предлагать решение.
Заключение
Ваша задача по синхронизации подмножества таблиц между двумя MySQL базами данных требует четкого проектирования базы данных, использования механизма FEDERATED для уменьшения сложности доступа к удаленным данным и настройки триггеров для автоматического отражения изменений. Обязательно протестируйте все этапы процесса, чтобы гарантировать корректность синхронизации данных и минимизировать потенциальные ошибки.