我在主机上直接安装了 IredMail 0.9.0（2014 年 12 月 31 日发布），我想将其迁移到新的服务器docker 化的 IredMail+ LetsEncrypt SSL 证书 + 所有内容均由 docker-compose 包装。
我发现了三种方法可以实现这一点：
按照“升级教程”的步骤从一个版本升级到另一个版本这里然后将数据库转移到 dockerized IredMail - 这太长了，可能会遗漏一些东西，因为我有 12 个版本的差距。
根据“升级教程”仅进行 DB 更改 - 同样，可能会遗漏一些东西。
运行 dockerized IredMail 并将数据从旧数据库传输到新数据库，因为 dockerized IredMail 已经配置好了一切（因为完全限定域名 (FQDN) 是相同的）并且更新了数据库方案。可以手动或通过脚本完成（可以在下面的答案中找到）。
答案1
我阅读了所有“升级教程”以找到将 0.9.0 更新到 1.1 所需的所有 DB 更改，发现第三个选项对我来说最简单，所以我决定编写一个 python 脚本。
对于不太熟悉 Python 的人，以下是在 Ubuntu 上需要做的事情：
- $
sudo apt-get install -y python3-dev； - $
python3 -m venv ./migration； - $
cd ./migration； - $
source ./bin/activate； - $
pip install wheel mysql-connector-python paramiko； - 在里面创建
migration.py文件./migration并复制粘贴下面的代码； - 根据您的需要修改代码；
- $
python3 ./migration.py（用于试运行，即不进行任何转移）或python3 ./migration --do_insert用于实际转移。 - 迁移后，通过 SQL 编辑器修复数据库/表中
maildir字段的值。将其更改为旧服务器的值（您也可以在目录中手动找到正确的路径）。postmaster@your_domain.comvmail/mailbox.../vmail/vmail1/... - 用旧服务器上的文件夹替换
vmail新服务器上的文件vmail夹。
就我而言，我建立了与 2 个数据库的连接：
- 使用密码和主机上的 SQL 服务器进行 ssh；
- 在 IredMail Docker 中使用私钥和 SQL 服务器进行 ssh 操作（不要忘记在 docker 中打开端口以允许从 ssh-host 到 docker 的连接）。
此外，一切都得到了很好的评论。
我猜想仅传输vmailDB 就足够了，但是我不想尝试传输除 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())
该代码可以将 DB 从 v0.9.0+ 转移到 v1.2（可能它可以与 0.9.0- 一起工作，但我没有阅读适当的“升级教程”）。
随意问任何问题。