Вопрос или проблема
У меня есть основная база данных 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 может быть сложной задачей, особенно если необходимо интегрировать данные из разных источников. В этой статье мы рассмотрим, как синхронизировать подмножество таблиц между двумя базами данных, находящимися на разных серверах MySQL, в частности, таблицу car из вашей главной базы данных и новую таблицу в другой базе данных.
1. Спецификация таблиц
Ваша главная таблица car, расположенная в основной базе данных, содержит следующие столбцы:
+---------------+-----------------+------+
| carId | name | model|
+---------------+-----------------+------+
| 1 | ford | focus|
| 2 | Toyota | Yaris|
+---------------+-----------------+------+Вы планируете создать новую таблицу в отдельной базе данных, которая будет иметь дополнительные столбцы make и workHistory:
+---------------+-----------------+------------+-----------+------------+
| carId | name | model | make | workHistory |
+---------------+-----------------+------------+-----------+------------+
| 1 | ford | focus | 2001 | yes |
| 2 | Toyota | Yaris | 2002 | no |
+---------------+-----------------+------------+-----------+------------+2. Создание новой базы данных и таблицы
Для начала создайте новую базу данных и таблицу на другом сервере MySQL:
CREATE DATABASE new_database;
USE new_database;
CREATE TABLE car (
carId INT PRIMARY KEY,
name VARCHAR(255),
model VARCHAR(255),
make INT,
workHistory ENUM('yes', 'no')
);3. Синхронизация данных между таблицами
Чтобы достичь желаемого результата, необходимо организовать синхронизацию данных. Это можно сделать с использованием механизмов, которые позволяют вам извлекать данные из основной базы данных и обновлять новую базу данных.
3.1 Использование MySQL Federated Engine
Если обе базы данных находятся на разных серверах, вы можете использовать Federated Engine для предоставления удаленного доступа к таблице car. Вам нужно будет создать таблицу-ссылку на вашей новой базе данных:
CREATE TABLE car_federated (
carId INT PRIMARY KEY,
name VARCHAR(255),
model VARCHAR(255)
) ENGINE=FEDERATED
CONNECTION='mysql://username:password@mainserver/maindb/car';Эта команда создаст интерфейс для получения данных из таблицы car на главном сервере.
3.2 Объединение данных
Теперь, используя SQL-запрос, вы можете объединить данные из обеих таблиц. Помните о том, что вам также нужно будет добавить данные в новые столбцы make и workHistory:
INSERT INTO new_database.car (carId, name, model, make, workHistory)
SELECT a.carId, a.name, a.model, NULL, NULL
FROM car_federated a
ON DUPLICATE KEY UPDATE name = a.name, model = a.model;Вы можете использовать ON DUPLICATE KEY UPDATE, чтобы обновлять существующие записи, если carId уже существует в новой таблице.
4. Обработка изменений в главной базе данных
Для обеспечения синхронизации данными по мере их изменения в главной таблице, вам нужно организовать механизм отслеживания изменений.
4.1 Удаление записей
Если запись удалена из главной базы данных, это также должно отражаться в новой базе данных. Запрос для удаления записей мог бы выглядеть следующим образом:
DELETE FROM new_database.car
WHERE carId NOT IN (SELECT carId FROM car_federated);Этот запрос удалит все записи из новой таблицы, которые не присутствуют в основной таблице.
5. Планирование задач
Для автоматизации этого процесса вы можете использовать планировщик задач, такой как cron, для запуска SQL-скриптов, включающих операции INSERT, UPDATE и DELETE, на регулярной основе.
6. Заключение
Синхронизация данных между двумя базами данных MySQL может стать важной задачей в управлении данными. Используя Federated Engine, вы сможете интегрировать данные из одной базы данных в другую, даже когда они размещены на разных серверах. Важно помнить, что в процессе синхронизации изменений следует продумать, как обрабатывать всё происходящее, чтобы избежать несоответствия данных.
Подводя итоги, синхронизация — это не только задача по переносу данных, это также требует регулярного мониторинга и управления, чтобы обеспечить целостность данных.