В базе данных разработки у меня есть phpMyAdmin Tracking включен во всех таблицах. Он регистрирует все изменения, которые я вношу в структуры таблиц (в этом случае меня не интересует отслеживание данных). Пока все хорошо.
То, что я хочу сделать, это вывести отчет для ВСЕХ отслеживаемых таблиц с изменениями, сделанными из определенной версии (или датой, даже работающей), чтобы я мог запустить полученный SQL в моей производственной базе данных при обновлении к новым версиям и убедитесь, что базы данных идентичны, не беспокоясь об ошибках, связанных с ручным управлением этим.
Однако нет никакой функции, которую я могу найти, которая генерирует такой отчет. Все отчеты отслеживания предназначены для отдельных таблиц, и если мне нужно щелкнуть по всем таблицам (20+), это уберет преимущество этой функции. Все таблицы не меняются, но я не хочу отслеживать, что изменилось, вот что я хочу сделать phpMyAdmin для меня.
Я попытался сделать свой собственный запрос против таблицы pma_tracking, где изменения сохранены и имели частичный успех. Проблема состоит в том, что все изменения для одной версии сохраняются как один BLOB, и с каждой новой версией выполняется оператор DROP TABLE / CREATE TABLE, и я не могу отбросить таблицы на производственном db, поскольку там есть данные (я не воссоздавая базу данных каждый раз, добавляя только дополнительные изменения). Я просто хочу обновить структуру, и единственный раз, когда я хочу, чтобы инструкции CREATE TABLE были, когда я фактически создаю новую таблицу в базе данных. Поэтому я подумал, что могу отфильтровать их с помощью SQL, но затем он хранится в виде блога, а затем мне придется разбираться и смешивать текст blob, который кажется слишком сложным.
Итак, в качестве резюме, это то, что я ищу:
Кроме того, мне бы хотелось комментировать рабочий процесс, если у кого-то есть идеи лучшего. Любая помощь оценивается.
Алгоритм анализа поля BLOB таблицы «pma_tracking» находится в методе getTrackedData класса PMA_Tracker в исходном файле libraries/Tracker.class.php .
Начиная с этого кода, я написал простой PHP-скрипт, чтобы извлечь все инструкции определения данных (кроме операторов «DROP TABLE») из таблицы «pma_tracking».
Например, предположим, что вы хотите получить список всех изменений всех таблиц «тестовой» базы данных с версии «1»:
<?php $link = mysqli_init(); // Adjust hostname, username, password and db name before use! $db = mysqli_real_connect($link, "localhost", "myuser", "mypass", "phpmyadmin") or die(mysqli_connect_error()); // Adjust also target db name and tracking version $db_name = "test"; $version = "1"; $sql = "SELECT schema_sql FROM pma_tracking WHERE db_name='{$db_name}' AND version>='{$version}' ORDER BY version,date_created"; $result = mysqli_query($link, $sql) or die(mysqli_error($link)); while ($myrow = mysqli_fetch_assoc($result)) { $log_schema_entries = explode('# log ', $myrow['schema_sql']); foreach ($log_schema_entries as $log_entry) { if (trim($log_entry) != '') { $statement = trim(strstr($log_entry, "\n")); if (substr($statement, 0, 11) != "DROP TABLE ") { echo "{$statement}\n"; } } } } ?>
Перенаправив вывод сценария в файл, вы получите файл команд SQL с (почти) всеми операциями, необходимыми для репликации изменений схемы в целевой (например, производственной) базе данных; этот файл должен быть выполнен, указав параметр «-f» (force) MySQL:
-f, –force Продолжить, даже если мы получим ошибку SQL.
Таким образом, MySQL будет игнорировать всю ошибку «Таблица уже существует», которая будет вызываться каждый раз, когда встречается оператор CREATE TABLE для существующей таблицы, тем самым создавая только таблицы, которые все еще не существуют в целевой базе данных.
Такой подход, очевидно, имеет некоторые недостатки:
DROP TABLE будут игнорироваться (не только те, которые автоматически вставлены из phpMyAdmin), поэтому, если вы удалили таблицу в исходной базе данных, эта таблица не будет удалена в целевой базе данных. Последнее слово совета: всегда делайте полную резервную копию своей целевой базы данных, прежде чем продолжить!
Я не знаю, как вы могли решить эту проблему с помощью phpMyAdmin, но есть и другие инструменты, которые могут помочь вам добиться эффекта, который вы ищете. Liquibase – один из них. Я использовал его несколько раз в прошлом, и это было очень хорошо. Потребуется немного, чтобы понять это, но я думаю, что это может вам помочь.
Я не слишком хорошо знаком с инструментами SQL, поэтому я не могу рекомендовать что-либо, чтобы помочь вам там, но я могу попробовать и помочь с помощью настраиваемого рабочего процесса …
Это сделает папку версий на вашем сервере. Вы можете вручную запустить скрипт print_structure.php всякий раз, когда захотите, и сравнить его с журналом последней версии, который у вас есть в вашей папке сервера, чтобы узнать, была ли ваша база данных, на которой вы только что запускали ее, такая же, как в последний раз, когда была проверена проверка версии.
У меня был некоторый успех с MySQL Workbench :
Импортируйте (обратный инженер) вашу базу данных dev в workbench. Вы можете сделать это, экспортировав вашу схему в файл SQL и загрузив ее в workbench, или workbench получит схему непосредственно с сервера.
Затем создайте файл diff с параметром «Синхронизировать модель». Вы выбираете производственную базу данных, затем таблицы для синхронизации, а workbench генерирует файл SQL, который вы можете запустить для синхронизации обеих моделей.
Слово предостережения: в первый раз, вероятно, будет довольно много явно необработанных изменений, в то время как БД обновляется до «стиля» рабочего стола. Для последующих обновлений инструмент довольно надежный, хотя я бы никогда не позволил автоматическому инструменту иметь свободный диапазон над моей производственной БД ðŸ˜‰
Всегда проверяйте файл SQL на наличие ошибок, в некоторых случаях удаляя столбец, а затем добавляя другое одноименное имя, но другой тип будет генерировать столбец alter, который будет терпеть неудачу.
У меня нет ничего, что создает инкрементный diff между двумя базами данных, но вот сценарий, который я использую для сравнения двух баз данных MySQL:
<?php //------------------------------------------------------------------------------ // Define the variables we'll be using. //------------------------------------------------------------------------------ $db1_con = NULL; $db1_constraints = array(); $db1_dbname = 'db1'; $db1_host = 'localhost'; $db1_password = 'password1'; $db1_tables = array(); $db1_username = 'username1'; $db2_con = NULL; $db2_constraints = array(); $db2_dbname = 'db2'; $db2_host = '123.123.123.123'; $db2_password = 'password2'; $db2_tables = array(); $db2_username = 'username2'; //------------------------------------------------------------------------------ // Connect to the databases. //------------------------------------------------------------------------------ try{ $db1_con = new PDO("mysql:host=$db1_host;dbname=information_schema", $db1_username, $db1_password); $db1_con->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); // Try to use the driver's native prepared statements. $db1_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Let's use exceptions so we can try/catch errors. }catch(PDOException $e){ echo "<p>Connection failed for $db1_host: " . $e->getMessage() . '</p>'; exit; } try{ $db2_con = new PDO("mysql:host=$db2_host;dbname=information_schema", $db2_username, $db2_password); $db2_con->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); // Try to use the driver's native prepared statements. $db2_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Let's use exceptions so we can try/catch errors. }catch(PDOException $e){ echo "<p>Connection failed for $db2_host: " . $e->getMessage() . '</p>'; exit; } if (NULL !== $db1_con && NULL !== $db2_con){ echo "<h2>Column Analysis</h2>"; $sql = 'SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME, ORDINAL_POSITION'; $statement1 = $db1_con->prepare($sql); $statement1->bindValue(1, $db1_dbname); $statement2 = $db2_con->prepare($sql); $statement2->bindValue(1, $db2_dbname); if (TRUE === $statement1->execute()){ while ($row = $statement1->fetch(PDO::FETCH_ASSOC)){ $db1_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']] = array(); foreach ($row AS $key => $value){ $db1_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value; } } } if (TRUE === $statement2->execute()){ while ($row = $statement2->fetch(PDO::FETCH_ASSOC)){ $db2_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']] = array(); foreach ($row AS $key => $value){ $db2_tables[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value; } } } foreach ($db1_tables AS $table => $info){ if (!isset($db2_tables[$table])){ echo "<p>Table <strong>$table</strong> does not exist in the SECOND database!</p>"; }else{ foreach ($info AS $column => $data){ if (!isset($db2_tables[$table][$column])){ echo "<p>Column <strong>$column</strong> does not exist in table <strong>$table</strong> in the SECOND database!</p>"; }else{ if (count($data)){ foreach ($data AS $key => $value){ if ($db1_tables[$table][$column][$key] !== $db2_tables[$table][$column][$key]){ echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db1_tables[$table][$column][$key] ." vs. ". $db2_tables[$table][$column][$key] .")</p>"; } } } } } } } foreach ($db2_tables AS $table => $info){ if (!isset($db1_tables[$table])){ echo "<p>Table <strong>$table</strong> does not exist in the FIRST database!</p>"; }else{ foreach ($info AS $column => $data){ if (!isset($db1_tables[$table][$column])){ echo "<p>Column <strong>$column</strong> does not exist in table <strong>$table</strong> in the FIRST database!</p>"; }else{ if (count($data)){ foreach ($data AS $key => $value){ if ($db2_tables[$table][$column][$key] !== $db1_tables[$table][$column][$key]){ echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db2_tables[$table][$column][$key] ." vs. ". $db1_tables[$table][$column][$key] .")</p>"; } } } } } } } echo "<h2>Constraint Analysis</h2>"; $sql = 'SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ? ORDER BY TABLE_NAME, ORDINAL_POSITION'; $statement1 = $db1_con->prepare($sql); $statement1->bindValue(1, $db1_dbname); $statement2 = $db2_con->prepare($sql); $statement2->bindValue(1, $db2_dbname); if (TRUE === $statement1->execute()){ while ($row = $statement1->fetch(PDO::FETCH_ASSOC)){ foreach ($row AS $key => $value){ $db1_constraints[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value; } } } if (TRUE === $statement2->execute()){ while ($row = $statement2->fetch(PDO::FETCH_ASSOC)){ foreach ($row AS $key => $value){ $db2_constraints[$row['TABLE_NAME']][$row['COLUMN_NAME']][$key] = $value; } } } foreach ($db1_constraints AS $table => $info){ foreach ($info AS $column => $data){ if (isset($db2_constraints[$table][$column])){ if (count($data)){ foreach ($data AS $key => $value){ if ('CONSTRAINT_NAME' !== $key && $db1_constraints[$table][$column][$key] !== $db2_constraints[$table][$column][$key]){ echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db1_constraints[$table][$column][$key] ." vs. ". $db2_constraints[$table][$column][$key] .")</p>"; } } } }else{ echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> is missing a constraint in the SECOND database!</p>"; } } } foreach ($db2_constraints AS $table => $info){ foreach ($info AS $column => $data){ if (isset($db1_constraints[$table][$column])){ if (count($data)){ foreach ($data AS $key => $value){ if ('CONSTRAINT_NAME' !== $key && $db2_constraints[$table][$column][$key] !== $db1_constraints[$table][$column][$key]){ echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> has differing characteristics for <strong>$key</strong> (". $db2_constraints[$table][$column][$key] ." vs. ". $db1_constraints[$table][$column][$key] .")</p>"; } } } }else{ echo "<p>Column <strong>$column</strong> in table <strong>$table</strong> is missing a constraint in the FIRST database!</p>"; } } } } ?>
Отредактировано для добавления кода, который также показывает различия в ограничениях.