使用 phpMyAdmin 的跟踪机制迁移数据库 [英] Migrating databases using phpMyAdmin's tracking mechanism

问题描述

在开发数据库中，我在所有表上都启用了 phpMyAdmin 跟踪.它记录了我对表结构所做的所有更改(在这种情况下，我对数据跟踪不感兴趣.)到目前为止一切顺利.

In a development database, I have phpMyAdmin Tracking enabled on all tables. It logs all the changes I make to the tables' structures (in this case I'm not interested in data tracking.) So far so good.

然后我想要做的是为所有跟踪表取出一份报告，其中包含从特定版本(或日期甚至可以工作)所做的更改，以便我可以在我的生产数据库上运行生成的 SQL, 升级到新版本时，请确保数据库相同，而不必担心手动处理时会出现错误.

What I want to do then is to take out a report, for ALL tracked tables, with the changes made from a specific version (or a date would even work,) so that I can run the resulting SQL on my production database, when upgrading to new versions, and make sure that the databases are identical, without the worry of the errors that come with manual handling of this.

但是，我找不到可以生成此类报告的函数.所有的跟踪报告都是针对单个表的，如果我必须点击所有表(20+)，它就会失去这个功能的好处.所有表都不会改变，但我不想跟踪发生了什么变化，这就是我希望 phpMyAdmin 为我做的事情.

However, there is no function that I can find that generates such a report. All the tracking reports are for individual tables, and if I have to click through all tables (20+) it takes away the benefit of this function. All tables don't change, but I don't want to keep track of what's changed, that's what I want phpMyAdmin to do for me.

我尝试对存储更改的 pma_tracking 表进行自己的查询，并取得了部分成功.问题是一个版本的所有更改都存储为一个 BLOB，并且每个新版本都会创建一个 DROP TABLE/CREATE TABLE 语句，我不能删除生产数据库上的表，因为那里有数据(我是不是每次都重新创建数据库，只添加增量更改).我只想升级结构，唯一需要 CREATE TABLE 语句的时间是我在数据库中实际创建新表时.所以我想我可以用 SQL 过滤掉那些，但后来它被存储为一个博客，然后我将不得不解析和弄乱看起来过于复杂的 blob 文本.

I have tried to make my own query against the pma_tracking table where the changes are stored, and had partial success. The problem is that all changes for one version are stored as one BLOB, and with each new version a DROP TABLE / CREATE TABLE statement is made, and I can't drop tables on the production db since there is data there (I'm not recreating the database every time, only adding incremental changes). I just want to upgrade the structure, and the only time I want CREATE TABLE statements is when I actually create a new table in the database. So I thought I could filter those out with SQL, but then it's stored as a blog, and then I would have to parse and mess with the blob text which seems overly complicated.

所以，作为总结，这就是我要寻找的:

So, as a summary, this is what I'm looking for:

• 一种自动跟踪系统/工作流，可记录所有结构更新，并可从某个版本或时间点为整个数据库创建增量 SQL 报告.
• 如果可能，我不想使用任何其他第三方应用程序(我只想使用 phpMyAdmin 或 MySQL)

此外，如果有人有更好的想法，我希望对工作流程发表评论.任何帮助表示赞赏.

Also, I would love comments on the workflow, if someone has ideas of a better one. Any help appreciated.

推荐答案

解析pma_tracking"表的BLOB字段的算法位于getTrackedData 方法 PMA_Tracker 类.
从该代码开始，我编写了一个简单的 PHP 脚本来从pma_tracking"表中提取所有数据定义语句(DROP TABLE"语句除外).
例如，假设您要获取test"数据库自版本1"以来所有表的所有更改列表:

The algorithm for parsing the BLOB field of the "pma_tracking" table is located in the getTrackedData method of the PMA_Tracker class, in the libraries/Tracker.class.php source file.
Starting from that code, I've written a simple PHP script to extract all the data definition statements (except the "DROP TABLE" statements) from the "pma_tracking" table.
For example, suppose that you want to get the list of all the changes of all the tables of the "test" database since version "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, "
"));
            if (substr($statement, 0, 11) != "DROP TABLE ") {
                echo "{$statement}
";
            }
        }
    }
}

?>

通过重定向文件上的脚本输出，您将获得一个 SQL 命令文件，其中包含(几乎)在目标(例如生产)数据库上复制架构更改所需的所有语句；必须通过指定-f"(强制)MySQL 选项来执行此文件:

By redirecting the script output on a file, you'll obtain a SQL commands file with (almost) all the statements needed to replicate the schema changes on the target (eg. production) database; this file must be executed by specifying the "-f" (force) MySQL option:

-f, --force 即使我们收到 SQL 错误也继续.

-f, --force Continue even if we get an SQL error.

这样做，MySQL 将忽略每次遇到现有表的 CREATE TABLE 语句时抛出的所有表已存在"错误，从而只创建仍然存在的表目标数据库中不存在.
这种方式显然有一些弊端:

By doing so, MySQL will ignore all the "Table already exists" error that will be thrown each time that a CREATE TABLE statement for an existing table is encountered, thus creating only the tables that still does'nt exist in the target database.
This kind of approach obviously has some drawbacks:

1. ALL DROP TABLE 命令将被忽略(不仅是那些从 phpMyAdmin 自动插入的命令)，因此，如果您删除了源数据库中的表，该表将获胜'不会在目标数据库中删除.
   
2. 所有脚本错误将被忽略，因此它可能不是 100% 负担得起的.

1. ALL the DROP TABLE commands will be ignored (not only those automatically inserted from phpMyAdmin) so, if you have deleted a table in the source database, that table won't be deleted in the target database.
   
2. ALL the script errors will be ignored, so it may not be 100% affordable.

最后一句忠告:在继续之前，请务必对目标数据库进行完整备份！

这篇关于使用 phpMyAdmin 的跟踪机制迁移数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！