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

问题描述

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

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语句，并且由于存在数据，所以我无法将表删除到生产db上(不会每次都重新创建数据库，而只添加增量更改.我只想升级结构，而我唯一想要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字段的算法位于 例如，假设您要获取版本"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, "\n"));
            if (substr($statement, 0, 11) != "DROP TABLE ") {
                echo "{$statement}\n";
            }
        }
    }
}

?>

通过重定向文件上的脚本输出，您将获得一个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屋！