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: {}
Yes, that should be the command that does what you want: run it when SQL storage is still in use. Once you have the contents in XML files, configure ejabberd to use Mnesia and import those files.
It crashed? Or it simply exported zero contents?
Ok, there's a feature incompatibility: you are asking ejabberd the details about the registered accounts, but ejabberd doesn't know what accounts exist.
You have ejabberd configured to use extauth, which means ejabberd uses that script to check if an account exists or not... But that script never reports the list of registered accounts.
This is a problem when you want to know the list of registered accounts:
This was reported in https://github.com/processone/ejabberd/issues/3705
One solution would be to enable the option auth_use_cache so that each login attempt gives a hint that ejabberd stores. Wait a few days or weeks, and later when you request the list of "registered accounts", ejabberd reads that auth cache and uses it.
The problem is that you would need to wait for all the registered accounts to login and leave that cached information in ejabberd...
The good news: there's a way you can fill the auth cache with the accounts:
Get the list of registered accounts yourself, manually, in some way
Enable auth_use_cache in ejabberd and restart
Run this command for every account you want to export. This will ensure extauth is run for that account, and the auth cache stores this hint:
ejabberdctl check_account user123 localhost
Just for curiosity, you can view the list of registered accounts (in fact it's only the list of auth cached accounts...)
ejabberdctl registered_users localhost admin user123
Now you can finally run export_piefxis
Remember: that auth cache information is deleted when ejabberd stops.
Once it's exported, you can revert configuration