how to make connection between ejabberd and mysql?

[MetalZoa0]

What are the steps to make connection between ejabberd and external mysql database?

Thank you very much in advance

[zinid]

Load schema from https://github.com/processone/ejabberd/blob/master/sql/mysql.sql:

$ mysql -u someuser -p somedatabase < mysql.sql

then add this to your ejabberd.yml:

default_db: odbc
odbc_type: mysql
odbc_server: "127.0.0.1"
odbc_database: "somedatabase"
odbc_username: "someuser"
odbc_password: "somepassword"

[MetalZoa0]

Right zinid, but this setting is valid for which version of ejabberd?

[MetalZoa0]

Thank you for your attention zinid. I'm just having trouble ha some time in trying to connect the ejabberd to an external database mysql type. And the company where I work has required such flexibility

[zinid]

@MetalZoa0 starting from 15.x it should be working.

[MetalZoa0]

@zinid , are only those settings for operating the ejabberd with MySQL?

[zinid]

I think yes. Just check it and, if something goes wrong, report.

[MetalZoa0]

@zinid I could not make the connection, you have any other advice? I am using linux ubuntu 15.10 and this is my configuration file:

[MetalZoa0]

@zinid this is my ejabberd.yml file:

    ###
###               ejabberd configuration file
###
###

### The parameters used in this configuration file are explained in more detail
### in the ejabberd Installation and Operation Guide.
### Please consult the Guide in case of doubts, it is included with
### your copy of ejabberd, and is also available online at
### http://www.process-one.net/en/ejabberd/docs/

### The configuration file is written in YAML.
### Refer to http://en.wikipedia.org/wiki/YAML for the brief description.
### However, ejabberd treats different literals as different types:
###
### - unquoted or single-quoted strings. They are called "atoms".
###   Example: dog, 'Jupiter', '3.14159', YELLOW
###
### - numeric literals. Example: 3, -45.0, .0
###
### - quoted or folded strings.
###   Examples of quoted string: "Lizzard", "orange".
###   Example of folded string:
###   > Art thou not Romeo,
###     and a Montague?

###   =======
###   LOGGING

##
## loglevel: Verbosity of log files generated by ejabberd.
## 0: No ejabberd log at all (not recommended)
## 1: Critical
## 2: Error
## 3: Warning
## 4: Info
## 5: Debug
##
loglevel: 4

##
## rotation: Disable ejabberd's internal log rotation, as the Debian package
## uses logrotate(8).
log_rotate_size: 0
log_rotate_date: ""

##
## overload protection: If you want to limit the number of messages per second
## allowed from error_logger, which is a good idea if you want to avoid a flood
## of messages when system is overloaded, you can set a limit.
## 100 is ejabberd's default.
log_rate_limit: 100

##
## watchdog_admins: Only useful for developers: if an ejabberd process
## consumes a lot of memory, send live notifications to these XMPP
## accounts.
##
## watchdog_admins:
##   - "bob@example.com"

###   ================
###   SERVED HOSTNAMES

##
## hosts: Domains served by ejabberd.
## You can define one or several, for example:
## hosts: 
##   - "example.net"
##   - "example.com"
##   - "example.org"
##
hosts:
  - "localhost"

##
## route_subdomains: Delegate subdomains to other XMPP servers.
## For example, if this ejabberd serves example.org and you want
## to allow communication with an XMPP server called im.example.org.
##
## route_subdomains: s2s

###   ===============
###   LISTENING PORTS

##
## listen: The ports ejabberd will listen on, which service each is handled
## by and what options to start it with.
##
listen: 
  - 
    port: 5222
    ip: "::"
    module: ejabberd_c2s
    ##
    ## If TLS is compiled in and you installed a SSL
    ## certificate, specify the full path to the
    ## file and uncomment this line:
    ##
    certfile: "/etc/ejabberd/ejabberd.pem"
    starttls: true
    ##
    ## Custom OpenSSL options
    ##
    protocol_options:
      - "no_sslv3"
    ##   - "no_tlsv1"
    max_stanza_size: 65536
    shaper: c2s_shaper
    access: c2s
  - 
    port: 5269
    ip: "::"
    module: ejabberd_s2s_in
  ##
  ## ejabberd_service: Interact with external components (transports, ...)
  ##
  ## - 
  ##   port: 8888
  ##   module: ejabberd_service
  ##   access: all
  ##   shaper_rule: fast
  ##   ip: "127.0.0.1"
  ##   hosts:
  ##     "icq.example.org":
  ##       password: "secret"
  ##     "sms.example.org":
  ##       password: "secret"

  ##
  ## ejabberd_stun: Handles STUN Binding requests
  ##
  ## - 
  ##   port: 3478
  ##   transport: udp
  ##   module: ejabberd_stun

  ##
  ## To handle XML-RPC requests that provide admin credentials:
  ##
  ## - 
  ##   port: 4560
  ##   module: ejabberd_xmlrpc
  - 
    port: 5280
    ip: "::"
    module: ejabberd_http
    ## request_handlers:
    ##   "/pub/archive": mod_http_fileserver
    web_admin: true
    http_poll: true
    http_bind: true
    ## register: true
    captcha: true

##
## s2s_use_starttls: Enable STARTTLS + Dialback for S2S connections.
## Allowed values are: false optional required required_trusted
## You must specify a certificate file.
##
s2s_use_starttls: optional

##
## s2s_certfile: Specify a certificate file.
##
s2s_certfile: "/etc/ejabberd/ejabberd.pem"

## Custom OpenSSL options
##
s2s_protocol_options:
  - "no_sslv3"
##   - "no_tlsv1"

##
## domain_certfile: Specify a different certificate for each served hostname.
##
## host_config:
##   "example.org":
##     domain_certfile: "/path/to/example_org.pem"
##   "example.com":
##     domain_certfile: "/path/to/example_com.pem"

##
## S2S whitelist or blacklist
##
## Default s2s policy for undefined hosts.
##
## s2s_access: s2s

##
## Outgoing S2S options
##
## Preferred address families (which to try first) and connect timeout
## in milliseconds.
##
## outgoing_s2s_families:
##   - ipv4
##   - ipv6
## outgoing_s2s_timeout: 10000

###   ==============
###   AUTHENTICATION

##
## auth_method: Method used to authenticate the users.
## The default method is the internal.
## If you want to use a different method,
## comment this line and enable the correct ones.
##
## auth_method: internal

##
## Store the plain passwords or hashed for SCRAM:
## auth_password_format: plain
## auth_password_format: scram
##
## Define the FQDN if ejabberd doesn't detect it:
## fqdn: "server3.example.com"

##
## Authentication using external script
## Make sure the script is executable by ejabberd.
##
## auth_method: external
## extauth_program: "/path/to/authentication/script"

##
## Authentication using ODBC
## Remember to setup a database in the next section.
##
## auth_method: odbc

##
## Authentication using PAM
##
## auth_method: pam
## pam_service: "pamservicename"

##
## Authentication using LDAP
##
## auth_method: ldap
##
## List of LDAP servers:
## ldap_servers:
##   - "localhost"
##
## Encryption of connection to LDAP servers:
## ldap_encrypt: none
## ldap_encrypt: tls
##
## Port to connect to on LDAP servers:
## ldap_port: 389
## ldap_port: 636
##
## LDAP manager:
## ldap_rootdn: "dc=example,dc=com"
##
## Password of LDAP manager:
## ldap_password: "******"
##
## Search base of LDAP directory:
## ldap_base: "dc=example,dc=com"
##
## LDAP attribute that holds user ID:
## ldap_uids:
##   - "mail": "%u@mail.example.org"
##
## LDAP filter:
## ldap_filter: "(objectClass=shadowAccount)"

##
## Anonymous login support:
##   auth_method: anonymous
##   anonymous_protocol: sasl_anon | login_anon | both
##   allow_multiple_connections: true | false
##
## host_config:
##   "public.example.org":
##     auth_method: anonymous
##     allow_multiple_connections: false
##     anonymous_protocol: sasl_anon
##
## To use both anonymous and internal authentication:
##
## host_config:
##   "public.example.org":
##     auth_method:
##       - internal
##       - anonymous

###   ==============
###   DATABASE SETUP

## ejabberd by default uses the internal Mnesia database,
## so you do not necessarily need this section.
## This section provides configuration examples in case
## you want to use other database backends.
## Please consult the ejabberd Guide for details on database creation.

##
## MySQL server:
##
 default_db: odbc
 odbc_type: mysql
 odbc_server: "127.0.0.1"
 odbc_database: "ejabberd"
 odbc_username: "root"
 odbc_password: "test123"
##
## If you want to specify the port:
## odbc_port: 1234

##
## PostgreSQL server:
##
## odbc_type: pgsql
## odbc_server: "server"
## odbc_database: "database"
## odbc_username: "username"
## odbc_password: "password"
##
## If you want to specify the port:
## odbc_port: 1234
##
## If you use PostgreSQL, have a large database, and need a
## faster but inexact replacement for "select count(*) from users"
##
## pgsql_users_number_estimate: true

##
## ODBC compatible or MSSQL server:
##
## odbc_type: odbc
## odbc_server: "DSN=ejabberd;UID=ejabberd;PWD=ejabberd"

##
## Number of connections to open to the database for each virtual host
##
## odbc_pool_size: 10

##
## Interval to make a dummy SQL request to keep the connections to the
## database alive. Specify in seconds: for example 28800 means 8 hours
##
## odbc_keepalive_interval: undefined

###   ===============
###   TRAFFIC SHAPERS

shaper:
  ##
  ## The "normal" shaper limits traffic speed to 1000 B/s
  ##
  normal: 1000

  ##
  ## The "fast" shaper limits traffic speed to 50000 B/s
  ##
  fast: 50000

##
## This option specifies the maximum number of elements in the queue
## of the FSM. Refer to the documentation for details.
##
max_fsm_queue: 1000

###.   ====================
###'   ACCESS CONTROL LISTS
acl:
  ##
  ## The 'admin' ACL grants administrative privileges to XMPP accounts.
  ## You can put here as many accounts as you want.
  ##
  admin:
     user:
         - "admin": "localhost"

  ##
  ## Blocked users
  ##
  ## blocked:
  ##   user:
  ##     - "baduser": "example.org"
  ##     - "test"

  ## Local users: don't modify this.
  ##
  local: 
    user_regexp: ""

  ##
  ## More examples of ACLs
  ##
  ## jabberorg:
  ##   server:
  ##     - "jabber.org"
  ## aleksey:
  ##   user:
  ##     - "aleksey": "jabber.ru"
  ## test:
  ##   user_regexp: "^test"
  ##   user_glob: "test*"

  ##
  ## Loopback network
  ##
  loopback:
    ip:
      - "127.0.0.0/8"

  ##
  ## Bad XMPP servers
  ##
  ## bad_servers:
  ##   server:
  ##     - "xmpp.zombie.org"
  ##     - "xmpp.spam.com"

##
## Define specific ACLs in a virtual host.
##
## host_config:
##   "localhost":
##     acl:
##       admin:
##         user:
##           - "bob-local": "localhost"

###   ============
###   ACCESS RULES
access:
  ## Maximum number of simultaneous sessions allowed for a single user:
  max_user_sessions: 
    all: 10
  ## Maximum number of offline messages that users can have:
  max_user_offline_messages: 
    admin: 5000
    all: 100
  ## This rule allows access only for local users:
  local: 
    local: allow
  ## Only non-blocked users can use c2s connections:
  c2s: 
    blocked: deny
    all: allow
  ## For C2S connections, all users except admins use the "normal" shaper
  c2s_shaper: 
    admin: none
    all: normal
  ## All S2S connections use the "fast" shaper
  s2s_shaper: 
    all: fast
  ## Only admins can send announcement messages:
  announce: 
    admin: allow
  ## Only admins can use the configuration interface:
  configure: 
    admin: allow
  ## Admins of this server are also admins of the MUC service:
  muc_admin: 
    admin: allow
  ## Only accounts of the local ejabberd server can create rooms:
  muc_create: 
    local: allow
  ## All users are allowed to use the MUC service:
  muc: 
    all: allow
  ## Only accounts on the local ejabberd server can create Pubsub nodes:
  pubsub_createnode: 
    all: allow
  ## In-band registration allows registration of any possible username.
  ## To disable in-band registration, replace 'allow' with 'deny'.
  register: 
    all: allow
  ## Only allow to register from localhost
  trusted_network: 
    loopback: allow
  ## Do not establish S2S connections with bad servers
  ## s2s: 
  ##   bad_servers: deny
  ##   all: allow

## By default the frequency of account registrations from the same IP
## is limited to 1 account every 10 minutes. To disable, specify: infinity
## registration_timeout: 600

##
## Define specific Access Rules in a virtual host.
##
## host_config:
##   "localhost":
##     access:
##       c2s:
##         admin: allow
##         all: deny
##       register:
##         all: deny

###   ================
###   DEFAULT LANGUAGE

##
## language: Default language used for server messages.
##
language: "pt"

##
## Set a different default language in a virtual host.
##
## host_config:
##   "localhost":
##     language: "ru"

###   =======
###   CAPTCHA

##
## Full path to a script that generates the image.
##
## captcha_cmd: "/lib/ejabberd/priv/bin/captcha.sh"

##
## Host for the URL and port where ejabberd listens for CAPTCHA requests.
##
## captcha_host: "example.org:5280"

##
## Limit CAPTCHA calls per minute for JID/IP to avoid DoS.
##
## captcha_limit: 5

###   =======
###   MODULES

##
## Modules enabled in all ejabberd virtual hosts.
##
modules: 
  mod_adhoc: {}
  mod_announce: # recommends mod_adhoc
    access: announce
  mod_blocking: {} # requires mod_privacy
  mod_caps: {}
  mod_carboncopy: {}
  mod_configure: {} # requires mod_adhoc
  mod_disco: {}
  ## mod_echo: {}
  mod_irc: {}
  mod_http_bind: {}
  ## mod_http_fileserver:
  ##   docroot: "/var/www"
  ##   accesslog: "/var/log/ejabberd/access.log"
  mod_last: {}
  mod_muc: 
    ## host: "conference.@HOST@"
    access: muc
    access_create: muc
    access_persistent: muc
    access_admin: muc_admin 
    max_users: 150
  ## mod_muc_log: {}
  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: {}
  mod_pubsub: 
    access_createnode: pubsub_createnode
    ## reduces resource comsumption, but XEP incompliant
    ignore_pep_from_offline: false
    max_items_node: 1000000
    ## XEP compliant, but increases resource comsumption
    ## ignore_pep_from_offline: false
    last_item_cache: false
    plugins: 
      - "flat"
      - "hometree"
      - "pep" # pep requires mod_caps
  mod_register: 
    ##
    ## Protect In-Band account registrations with CAPTCHA.
    ##
    ## captcha_protected: true

    ##
    ## Set the minimum informational entropy for passwords.
    ##
    ## password_strength: 32

    ##
    ## After successful registration, the user receives
    ## a message with this subject and body.
    ##
    welcome_message: 
      subject: "Bem vindo!"
      body: |-
        Olá.
        Seja bem vindo ao Jappix.

    ##
    ## When a user registers, send a notification to
    ## these XMPP accounts.
    ##
    ## registration_watchers:
    ##   - "admin1@example.org"

    ##
    ## Only clients in the server machine can register accounts
    ##
    ip_access: trusted_network

    ##
    ## Local c2s or remote s2s users cannot register accounts
    ##
    ## access_from: deny

    access: register
  mod_roster: {}
  mod_shared_roster: {}
  mod_stats: {}
  mod_time: {}
  mod_vcard: {}
  mod_version: {}

##
## Enable modules with custom options in a specific virtual host
##
## append_host_config:
##   "localhost":
##     modules:
##       mod_echo:
##         host: "mirror.localhost"

### Local Variables:
### mode: yaml
### End:
### vim: set filetype=yaml tabstop=

[zinid]

What is in the ejabberd logs? Also, please double check indentation of your ejabberd.yml, particularly section with odbc settings - looks like it's indented at one space, which is wrong because these are top-level options

[MetalZoa0]

@zinid right, I could make the connection to the mysql database, but we need some parameter for the ejabberd save your data in mysql database?

[MetalZoa0]

@zinid, It needs to add db_type: odbc in the modules in which I want to save the data in mysql database?

[mremond]

Yes, you need to configure the module you want to store in configured relational database with db_type: odbc. This is on a module per module basis.

[ismailmohammed]

Hi ,

Can anyone help me on connecting to cloud my SQL database configuration.

Ismail.

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.