Ejabberd迁移：将Mnesia的passwd表迁移至MySQL的user表

Got it, let's walk through how to get those Mnesia passwd entries into your new MySQL database for ejabberd. Here's a step-by-step breakdown tailored to your scenario:

First, Understand Your Exported Data Structure #

Your exported Mnesia passwd entries are Erlang tuples, which look something like this (based on your example):

{passwd, {<<"flowie">>, <<"your-domain.com">>}, <<"encrypted-password-string">>, [{salt, <<"salt-value">>}]}

Each entry includes the username, domain, encrypted password, and optional salt (if your old ejabberd used salted hashes). We need to parse this into a format MySQL can ingest.

1. Parse the Erlang-Formatted Export #

You have two solid options here, depending on which language you're more comfortable with:

Option A: Use Erlang (Quickest if you know the language) #

Write a simple Erlang script to read your export file and spit out structured data (like CSV):

% Save this as parse_passwd.erl
-module(parse_passwd).
-export([run/0]).

run() ->
    {ok, Binary} = file:read_file("passwd_export.txt"), % Replace with your export file path
    Terms = binary_to_term(Binary),
    {ok, File} = file:open("passwd_converted.csv", [write]),
    io:format(File, "username,domain,password,salt~n", []),
    lists:foreach(fun({passwd, {User, Domain}, Pass, Opts}) ->
        Salt = proplists:get_value(salt, Opts, <<>>),
        io:format(File, "~s,~s,~s,~s~n", [User, Domain, Pass, Salt])
    end, Terms),
    file:close(File).

Compile and run it with:

erlc parse_passwd.erl && erl -noshell -s parse_passwd run -s init stop

This will generate a clean CSV file ready for import.

Option B: Use Python (More accessible for most) #

Use the erlastic library to parse the Erlang terms (install it first with pip install erlastic):

import erlastic

# Read the exported file
with open('passwd_export.txt', 'rb') as export_file:
    passwd_entries = erlastic.load(export_file)

# Generate CSV
with open('passwd_converted.csv', 'w') as csv_file:
    csv_file.write("username,domain,password,salt\n")
    for entry in passwd_entries:
        if entry[0] == 'passwd':
            # Extract fields from the Erlang tuple
            username, domain = entry[1]
            encrypted_pass = entry[2]
            # Grab salt if it exists (default to empty string if not)
            salt = next((opt[1] for opt in entry[3] if opt[0] == 'salt'), b'')
            
            # Decode binary values to strings and escape quotes for CSV
            csv_file.write(
                f'"{username.decode("utf-8").replace('"', '""")}",'
                f'"{domain.decode("utf-8").replace('"', '""")}",'
                f'"{encrypted_pass.decode("utf-8").replace('"', '""")}",'
                f'"{salt.decode("utf-8").replace('"', '""")}"\n'
            )

This handles special characters and ensures the CSV is properly formatted.

2. Prepare Your MySQL User Table #

Make sure your new ejabberd's MySQL table matches the data you're importing. A typical users table structure for ejabberd looks like this (adjust if your config uses a different table name/fields):

CREATE TABLE IF NOT EXISTS `users` (
    `username` VARCHAR(255) NOT NULL,
    `host` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `salt` VARCHAR(255) DEFAULT '',
    PRIMARY KEY (`username`, `host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Double-check that your ejabberd config points to this table for authentication (look for auth_method: sql and sql_type: mysql in ejabberd.yml).

3. Import the Data into MySQL #

Once you have your CSV, use one of these methods to import:

Method A: Use LOAD DATA INFILE (Fastest for large datasets) #

Run this MySQL command (replace paths and table name as needed):

LOAD DATA INFILE '/path/to/passwd_converted.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(username, host, password, salt);

If the CSV is on your local machine instead of the server, add the LOCAL keyword: LOAD DATA LOCAL INFILE....

Method B: Batch INSERT Statements (Good for small datasets) #

If you prefer, modify the Python script above to generate INSERT statements instead of CSV:

# Add this after extracting fields in the Python script
with open('insert_users.sql', 'w') as sql_file:
    for entry in passwd_entries:
        if entry[0] == 'passwd':
            username, domain = entry[1]
            encrypted_pass = entry[2]
            salt = next((opt[1] for opt in entry[3] if opt[0] == 'salt'), b'')
            
            # Escape single quotes for SQL
            safe_user = username.decode("utf-8").replace("'", "''")
            safe_domain = domain.decode("utf-8").replace("'", "''")
            safe_pass = encrypted_pass.decode("utf-8").replace("'", "''")
            safe_salt = salt.decode("utf-8").replace("'", "''")
            
            sql_file.write(
                f"INSERT INTO users (username, host, password, salt) "
                f"VALUES ('{safe_user}', '{safe_domain}', '{safe_pass}', '{safe_salt}');\n"
            )

Then run the SQL file with:

mysql -u your-mysql-user -p your-ejabberd-db-name < insert_users.sql

4. Verify the Migration Worked #

Test that users can authenticate with their old passwords using the ejabberd command line tool:

ejabberdctl check_password flowie your-domain.com their-old-password

If it returns true, the import worked! You can also test with an XMPP client to be sure.

Critical Notes to Avoid Headaches #

• Match Password Encryption Settings: Ensure your new ejabberd's auth_password_format in ejabberd.yml matches the old server. For example, if the old server used sha256 with salt, set auth_password_format: sha256 (or scram if that's what was used). Mismatched formats will break logins.
• Backup First: Always back up your new MySQL database before importing data—better safe than sorry.
• UTF-8 Encoding: Make sure all exported/imported data uses UTF-8 to avoid username/password garbling.

内容的提问来源于stack exchange，提问作者Flowie85