将IredMail从一个服务器迁移到另一个服务器，同时进行更新(从0.9.0到1.1)

Question

我已经直接在主机上安装了IredMail 0.9.0 (发布于2014年12月31日)，我希望将它迁移到一个新服务器上，该服务器具有坞化邮件 + LetsEncrypt SSL证书+由docker-组合包装的所有内容。

我找到了三种方法可以做到：

1. 按照这里的“升级教程”S的说法，从一个版本逐步更新到另一个版本，然后将DB转换成IredMail --这太长了，有些东西可能会错过，因为我还有12个版本的空白。
2. 按照S的“升级教程”做DB修改
3. 运行已停靠的IredMail并将数据从旧数据库传输到新数据库，因为IredMail已经配置了所有配置(因为完全限定的域名(FQDN)是相同的)，并且已经更新了DB方案。它可以手动完成，也可以通过脚本完成(脚本可以在下面找到答案)。

Answer 1

为了找到更新0.9.0到1.1所需的所有DB更改，我阅读了所有“升级教程”，发现第三个选项对我来说是最简单的，所以我决定编写一个python脚本。

对于那些对Python不太熟悉的人来说，这里需要在Ubuntu上做些什么：

1. $ sudo apt-get install -y python3-dev；
2. $ python3 -m venv ./migration；
3. $ cd ./migration；
4. $ source ./bin/activate；
5. $ pip install wheel mysql-connector-python paramiko；
6. 在migration.py中创建./migration文件并复制粘贴代码；
7. 根据您的需要修改代码；
8. $ python3 ./migration.py (用于测试运行，无传输)或python3 ./migration --do_insert用于实际传输。
9. 迁移后，通过sql编辑器修复db/table中maildir字段的postmaster@your_domain.com值。将其更改为旧服务器中的值(还可以在.../vmail/vmail1/... dir中手动找到正确的路径)。
10. 将新服务器上的vmail文件夹替换为旧服务器上的vmail文件夹。

在我的例子中，我建立了与2个DB的连接：

• 主机上带有密码和SQL服务器的ssh；
• 带有私钥和SQL server的ssh在IredMail Docker中(不要忘记在docker中打开端口以允许从ssh-host连接到docker)。

此外，每件事都有很好的评论。

我想只传输vmail DB就足够了，但我不想实验和传输所有可能的东西，除了DB/Table iredadmin/log。

import argparse
import mysql.connector
import paramiko
import traceback

from sshtunnel import SSHTunnelForwarder


class SshConnection:
    def __init__(self, server_name):
        self.server_name = server_name

    def connect_to_db(self):
        if self.server_name == 'old':
            server_host = '111.111.111.111'
            server_port = 22

            server_params = dict(
                ssh_username='root',
                ssh_password='Super Secret Password')

            mysql_username = 'root'
            mysql_password = 'Super Secret Password'

        elif self.server_name == 'new':
            server_host = '222.222.222.222'
            server_port = 22

            server_params = dict(
                ssh_username='root',
                ssh_pkey="/home/username/.ssh/id_rsa",  # NOT public (ie "id_rsa.pub")
                ssh_private_key_password="passphrase for key")

            mysql_username = 'root'
            mysql_password = 'Super Secret Password'

        # This lines probably you do not need to change
        mysql_host = '127.0.0.1'
        mysql_port = 3306

        self.tunnel = SSHTunnelForwarder(
            (server_host, server_port),
            remote_bind_address=(mysql_host, mysql_port),
            **server_params)

        self.tunnel.start()

        self.connection = mysql.connector.connect(
            host=mysql_host,
            port=int(self.tunnel.local_bind_port),
            connection_timeout=15,
            user=mysql_username,
            passwd=mysql_password
        )

        return self.connection

    def close_connection(self):
        self.connection.close()
        self.tunnel.stop()


def main(parser_args):
    """
    Copy Data from Source Server to Target Server.
    The next algorithm used (Data are copied just when this conditions met):
    1. Target server checks if Source server has needed DB name. If yes...
    2. ... Target server checks if Source DB has needed table name. If yes...
    3. ... Take data from Source table;
    4. Remove columns from taken Source data, which do not exist in Target table;
    5. Transfer taken Source data to the Target server.

    # AFTER MIGRATION FIX VALUE OF "maildir" field for postmaster@your_domain.com
    # IN DB/TABLE "vmail->mailbox" VIA SQL EDITOR. Change it to the value from old
    # server (also you can find correct path manually inside .../vmail/vmail1/... dir).
    """

    do_insert = parser_args.do_insert

    databases_ignore = ('information_schema', 'mysql', 'performance_schema')
    tables_ignore = dict(
            iredadmin=['log']
        )

    # Establich SSH Connection and Connect to DB
    ssh_connection_source_sever = SshConnection('old')
    ssh_connection_target_server = SshConnection('new')

    connection_source_sever = ssh_connection_source_sever.connect_to_db()
    connection_target_server = ssh_connection_target_server.connect_to_db()

    # Get Cursors
    cursor_source_sever = connection_source_sever.cursor()
    cursor_target_server = connection_target_server.cursor()

    cursor_dict_source_sever = connection_source_sever.cursor(dictionary=True)
    cursor_dict_target_server = connection_target_server.cursor(dictionary=True)

    is_error = False

    try:
        # [ START: Get list of Databases ]
        print('\n\nDATABASE NAMES')
        cursor_source_sever.execute('SHOW DATABASES')
        database_names_source_sever = [t[0] for t in cursor_source_sever.fetchall()]  # return data from last query
        print('database_names_source_sever:', database_names_source_sever)

        cursor_target_server.execute('SHOW DATABASES')   
        database_names_target_server = [t[0] for t in cursor_target_server.fetchall()]  # return data from last query
        print('database_names_target_server:', database_names_target_server)
        # [ END: Get list of Databases ]

        # [ START: Handle Database's tables ]
        # "sogo" is a view (not a table) which is based on data from "vmail" db
        # https://www.guru99.com/views.html
        for db_name_target_server in database_names_target_server:
            if (db_name_target_server in databases_ignore) \
                    or (db_name_target_server not in database_names_source_sever) \
                    or db_name_target_server == 'sogo':
                continue

            print(f'\n\nDATABASE NAME: {db_name_target_server}')

            cursor_source_sever.execute(f"USE {db_name_target_server}")  # select the database
            cursor_source_sever.execute("SHOW TABLES")
            table_names_source_sever = [t[0] for t in cursor_source_sever.fetchall()]  #return data from last query
            print('table_names_source_sever:', table_names_source_sever)

            cursor_target_server.execute(f"USE {db_name_target_server}")  # select the database
            cursor_target_server.execute("SHOW TABLES")
            table_names_target_server = [t[0] for t in cursor_target_server.fetchall()]  # return data from last query
            print('table_names_target_server:', table_names_target_server)

            # [ START: Transfer data from Source table ]

            if db_name_target_server == 'roundcubemail':
                # Change position
                for idx, table_name in enumerate(['users', 'contacts', 'contactgroups']):
                    table_names_target_server.remove(table_name)
                    table_names_target_server.insert(idx, table_name)

                print('table_names_target_server CHANGED ORDER:', table_names_target_server)

            for table_name_on_target_server in table_names_target_server:
                if (table_name_on_target_server not in table_names_source_sever) \
                        or (table_name_on_target_server in tables_ignore.get(db_name_target_server, [])):
                    continue

                print(f'\nTransfer data from Source table: {table_name_on_target_server}')

                # [ START: Get list of Source and Target tables' columns ]
                cursor_source_sever.execute(f"SHOW COLUMNS FROM {table_name_on_target_server} FROM {db_name_target_server}")
                table_fields_source_server = [t[0] for t in cursor_source_sever.fetchall()]
                print('-->> table_fields_source_server:', table_fields_source_server)

                cursor_target_server.execute(f"SHOW COLUMNS FROM {table_name_on_target_server} FROM {db_name_target_server}")
                table_fields_target_server = [t[0] for t in cursor_target_server.fetchall()]
                print('-->> table_fields_target_server:', table_fields_target_server)
                # [ END: Get list of Source and Target tables' columns ]

                # Select all table's data
                cursor_dict_source_sever.execute(f"SELECT * FROM {table_name_on_target_server};")  # get table's data
                table_data_source_sever: list = cursor_dict_source_sever.fetchall()

                for row_source_sever_dict in table_data_source_sever:
                    # Del columns from record/row, which do not exist in Target table
                    for column_name in row_source_sever_dict.copy():
                        if column_name not in table_fields_target_server:
                            del row_source_sever_dict[column_name]

                        # Avoid "You have an error in your SQL syntax;" error for "reply-to" field.
                        elif '-' in column_name:
                            row_source_sever_dict[f'`{column_name}`'] = row_source_sever_dict[column_name]
                            del row_source_sever_dict[column_name]

                        # Change filesystem path where mailboxes are stored.
                        if db_name_target_server == 'vmail':
                            time_set = '2020-04-26 21:00:00'

                            if table_name_on_target_server == 'mailbox':
                                if column_name == 'storagebasedirectory':
                                    # /home/antonio/mails
                                    row_source_sever_dict[column_name] = '/var/vmail'

                                elif column_name in ('lastlogindate', 'passwordlastchange', 'modified'):
                                    row_source_sever_dict[column_name] = time_set

                            if table_name_on_target_server in ('alias', 'domain_admins'):
                                if column_name == 'modified':
                                    row_source_sever_dict[column_name] = time_set

                    # Send data to Target table
                    placeholders = ', '.join(['%s'] * len(row_source_sever_dict))
                    columns = ', '.join(row_source_sever_dict.keys())
                    sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table_name_on_target_server, columns, placeholders)

                    if not do_insert:
                        print('row_source_sever_dict to be sent to Target table:', row_source_sever_dict)
                        print('sql:', sql)

                    if do_insert:
                        try:
                            cursor_target_server.execute(sql, list(row_source_sever_dict.values()))
                        except Exception as e:
                            e_str = str(e)

                            if any(s in e_str for s in ('Duplicate entry',
                                                         'Cannot add or update a child row: a foreign key constraint fails',
                                                         'You have an error in your SQL syntax',
                                                         'The target table users of the INSERT is not insertable-into')):
                                print(f'\n!!! ERROR: {e}')
                                print(f'DB name: {db_name_target_server}; Table name: {table_name_on_target_server}')
                                print('transfer data:', row_source_sever_dict)
                                print('transfer sql:', sql)
                                print('----------')

                            else:
                                raise NotImplementedError()

                            if any(s in e_str for s in ('You have an error in your SQL syntax',
                                                         'The target table users of the INSERT is not insertable-into')):
                                raise NotImplementedError()
            # [ END: Transfer data from Source table ]
        # [ END: Handle Database's tables ]

    except (Exception, KeyboardInterrupt) as e:
        print(f'\n\n!!! ERROR: {e}')
        traceback.print_exc()

        is_error = True

    finally:
        print('\n\n** FINISHED **')
        print('is_error:', is_error)

        # Close cursors
        cursor_source_sever.close()
        cursor_target_server.close()

        cursor_dict_source_sever.close()
        cursor_dict_target_server.close()

        # Commit changes
        if not is_error:
            connection_target_server.commit()

        # Disconnect DB and SSH
        ssh_connection_source_sever.close_connection()
        ssh_connection_target_server.close_connection()

if __name__ == "__main__":
    # Command Line section
    parser = argparse.ArgumentParser(description="Transfer data from Source server to Target server. Details read in main() description.")
    parser.add_argument('--do_insert', action='store_true',
                        help='Test (by default) or Production run (if --do_insert provided)')

    main(parser.parse_args())

代码可以将DBs从v0.9.0+传输到v1.2 (可能它可以使用0.9.0-，但我没有读过适当的“升级教程”S)。

随时可以问任何问题。