There is pretty supported action to move from Mnesia to SQL, a special command ejabberdctl export2sql (in older versions it used to be called export2odbc). Some setups may need that because Mnesia is limited to 2GiB and blah blah blah.
In my case there is really no need to have an external database. There are around 10-15 active users and no more than 30 of total users, and the size of the export from SQL database is around 200 kB.
Somebody in the past did that thing, exported a database into SQL (specifically, MySQL). How can I revert that change?
Can export_piefxis help me somehow? What it does?
Update 1. After some exploration I found that only two modules have some data in the SQL database: mod_roster and mod_vcard. So only those need to be converted. Is there a way to convert their data externally and load into ejabberd? I mean, there is a ejabberdctl dump_table, but again no reverse command.
Also, export_piefxis doesn't seem to help. It just exports zero content, while there is data in the database and it is fetched when I connect with e.g. newly instantiated client.
Update 2. Software: Debian 11.2 LXC container running on Proxmox VE 6.4-1 host. Ejabberd 21.01-2 installed with apt from standard Debian repo.
The database was migrated from old Ejabberd of around version 2.1, that was running on Gentoo and last updated in 2015. I don't have that machine at my discretion anymore. Mnesia database migration was done as follows:
# on the old machine
ejabberdctl backup /tmp/[email protected]
ejabberdctl mnesia_change_nodename ejabberd@localhost ejabberd@jabber2 \
/tmp/[email protected] /tmp/[email protected]
# on the new machine (starting with empty database)
ejabberdctl install_fallback /tmp/[email protected]
ejabberdctl stop
ejabberdctl start
The configuration on the new machine just references the same MySQL database for mod_roster and mod_vcard.
ejabberd.yml:
loglevel: info
log_rotate_count: 0
hosts:
- "example.org"
host_config:
"rterm.ru":
sql_type: mysql
sql_server: "dbserver"
sql_database: "ejabberd"
sql_username: "ejabberd"
sql_password: "password"
auth_method: [external]
certfiles:
- "/etc/ejabberd/ejabberd.pem"
acme:
auto: false
define_macro:
'TLS_CIPHERS': "HIGH:!aNULL:!eNULL:!3DES:@STRENGTH"
'TLS_OPTIONS':
- "no_sslv3"
- "no_tlsv1"
- "no_tlsv1_1"
- "cipher_server_preference"
- "no_compression"
c2s_ciphers: 'TLS_CIPHERS'
s2s_ciphers: 'TLS_CIPHERS'
c2s_protocol_options: 'TLS_OPTIONS'
s2s_protocol_options: 'TLS_OPTIONS'
listen:
-
port: 5222
ip: "::"
module: ejabberd_c2s
max_stanza_size: 262144
shaper: c2s_shaper
access: c2s
starttls_required: true
protocol_options: 'TLS_OPTIONS'
-
port: 5223
ip: "::"
module: ejabberd_c2s
max_stanza_size: 262144
shaper: c2s_shaper
access: c2s
tls: true
protocol_options: 'TLS_OPTIONS'
-
port: 5269
ip: "::"
module: ejabberd_s2s_in
max_stanza_size: 524288
-
port: 5443
ip: "::"
module: ejabberd_http
tls: true
protocol_options: 'TLS_OPTIONS'
request_handlers:
/admin: ejabberd_web_admin
/api: mod_http_api
/bosh: mod_bosh
/upload: mod_http_upload
/ws: ejabberd_http_ws
-
port: 5280
ip: "::"
module: ejabberd_http
tls: false
request_handlers:
# This runs behind reverse HTTP proxy with Let's Encrypt, which is neighbour container on the same host, so this is safe
/admin: ejabberd_web_admin
/bosh: mod_bosh
/upload: mod_http_upload
-
port: 1883
ip: "::"
module: mod_mqtt
backlog: 1000
auth_method: [external]
extauth_program: "/etc/ejabberd/auth_mysql.py"
extauth_pool_size: 5
disable_sasl_mechanisms:
- "X-OAUTH2"
s2s_use_starttls: required
acl:
admin:
user:
- "[email protected]"
local:
user_regexp: ""
loopback:
ip:
- 127.0.0.0/8
- ::1/128
access_rules:
local:
allow: local
c2s:
deny: blocked
allow: all
announce:
allow: admin
configure:
allow: admin
muc_create:
allow: local
pubsub_createnode:
allow: local
trusted_network:
allow: loopback
api_permissions:
"console commands":
from:
- ejabberd_ctl
who: all
what: "*"
"admin access":
who:
access:
allow:
- acl: loopback
- acl: admin
oauth:
scope: "ejabberd:admin"
access:
allow:
- acl: loopback
- acl: admin
what:
- "*"
- "!stop"
- "!start"
"public commands":
who:
ip: 127.0.0.1/8
what:
- status
- connected_users_number
shaper:
normal:
rate: 3000
burst_size: 20000
fast: 200000
shaper_rules:
max_user_sessions: 10
max_user_offline_messages:
5000: admin
100: all
c2s_shaper:
none: admin
normal: all
s2s_shaper: fast
modules:
mod_adhoc: {}
mod_admin_extra: {}
mod_announce:
access: announce
mod_avatar: {}
mod_blocking: {}
mod_bosh: {}
mod_caps: {}
mod_carboncopy: {}
mod_client_state: {}
mod_configure: {}
mod_disco: {}
mod_fail2ban: {}
mod_http_api: {}
mod_http_upload:
put_url: https://@HOST@:5443/upload
custom_headers:
"Access-Control-Allow-Origin": "https://@HOST@"
"Access-Control-Allow-Methods": "GET,HEAD,PUT,OPTIONS"
"Access-Control-Allow-Headers": "Content-Type"
mod_last: {}
mod_mqtt: {}
mod_muc:
access:
- allow
access_admin:
- allow: admin
access_create: muc_create
access_persistent: muc_create
access_mam:
- allow
default_room_options:
mam: true
mod_muc_admin: {}
mod_offline:
access_max_user_messages: max_user_offline_messages
mod_ping: {}
mod_pres_counter:
count: 5
interval: 60
mod_privacy: {}
mod_private: {}
mod_proxy65:
access: local
max_connections: 5
mod_pubsub:
access_createnode: pubsub_createnode
plugins:
- flat
- pep
force_node_config:
"eu.siacs.conversations.axolotl.*":
access_model: open
storage:bookmarks:
access_model: whitelist
mod_push: {}
mod_push_keepalive: {}
mod_roster:
db_type: sql # This is what I want to get rid of
versioning: true
mod_s2s_dialback: {}
mod_shared_roster: {}
mod_sic: {}
mod_stream_mgmt:
resend_on_timeout: if_offline
mod_stun_disco: {}
mod_vcard:
db_type: sql # And this
search: false
mod_vcard_xupdate: {}
mod_version: {}
