mysql - RADIUS 服务器冗余 MySQL

Question

我目前正在为结合 MySQL 运行 FreeRADIUS V.3.xx 的 RADIUS 应用程序构建测试设置。整个设置是多余的。所有服务器都运行在运行 openSUSE 13.1 的虚拟机上

我的问题是我似乎无法找到冗余 MySQL 服务器的正确配置。有人有这方面的经验吗？

到目前为止我所做的基本上是遵循官方freeradius wiki的相关部分...尝试使用组、细节、rlm_sql_mysql套接字的直接操作来提出变体...我能想到的任何东西..但没有成功。目前已回到“wiki状态”

即:

我从.../mods-enabled/中删除了到sql的链接，因为这导致冗余服务器甚至实例化都失败...

文件:.../radius.conf

部分:模块{...}添加的条目:

sql sql1{ ...#config...}
sql sql2{ ...#config...}

部分:实例化 {...} 添加条目:

redundant redundant_sql {
      sql1
      sql2
      handled
    }

然后在文件中添加redundant_sql:.../sites-available/default(链接到已启用站点)

部分:授权{...}和后验证{...}(我不需要记账)

以下是一些调试输出:

实例化(sql1 和 sql2 都必须可访问才能启动 RADIUS 服务器。这很糟糕，但不是当前的问题):

    radiusd: #### Instantiating modules ####
 instantiate {
 }
 modules {
  # Loaded module rlm_sql
  # Instantiating module "sql1" from file /etc/raddb/radiusd.conf
  sql sql1 {
    driver = "rlm_sql_mysql"
    server = "**IP sql1**"
    port = "3306"
    login = "radius"
    password = <<< secret >>>
    radius_db = "radius"
    read_groups = yes
    read_clients = no
    delete_stale_sessions = yes
    sql_user_name = "%{User-Name}"
    default_user_profile = ""
    client_query = "SELECT id, nasname, shortname, type, secret, server FROM nas"
    authorize_check_query = "SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id"
    authorize_reply_query = "SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id"
    authorize_group_check_query = "SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id"
    authorize_group_reply_query = "SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id"
    group_membership_query = "SELECT groupname FROM radusergroup WHERE username = BINARY '%{SQL-User-Name}' ORDER BY priority"
    simul_count_query = ""
    simul_verify_query = "SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL"
    safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
  }
rlm_sql (sql1): Creating new attribute sql1-SQL-Group
rlm_sql (sql1): Registering sql_groupcmp for sql1-SQL-Group
   accounting {
    reference = "%{tolower:type.%{Acct-Status-Type}.query}"
   }
   post-auth {
    reference = ".query"
   }
   mysql {
    tls {
    }
   }
rlm_sql (sql1): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
rlm_sql (sql1): Attempting to connect to database "radius"
rlm_sql (sql1): Initialising connection pool
   pool {
    start = 5
    min = 4
    max = 32
    spare = 3
    uses = 0
    lifetime = 0
    cleanup_interval = 30
    idle_timeout = 60
    retry_delay = 1
    spread = no
   }
rlm_sql (sql1): Opening additional connection (0)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql1): Opening additional connection (1)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql1): Opening additional connection (2)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql1): Opening additional connection (3)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql1): Opening additional connection (4)
rlm_sql_mysql: Starting connect to MySQL server
  # Instantiating module "sql2" from file /etc/raddb/radiusd.conf
  sql sql2 {
    driver = "rlm_sql_mysql"
    server = "**IP sql2**"
    port = "3306"
    login = "radius"
    password = <<< secret >>>
    radius_db = "radius"
    read_groups = yes
    read_clients = no
    delete_stale_sessions = yes
    sql_user_name = "%{User-Name}"
    default_user_profile = ""
    client_query = "SELECT id, nasname, shortname, type, secret, server FROM nas"
    authorize_check_query = "SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id"
    authorize_reply_query = "SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id"
    authorize_group_check_query = "SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id"
    authorize_group_reply_query = "SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id"
    group_membership_query = "SELECT groupname FROM radusergroup WHERE username = BINARY '%{SQL-User-Name}' ORDER BY priority"
    simul_count_query = ""
    simul_verify_query = "SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL"
    safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
  }
rlm_sql (sql2): Creating new attribute sql2-SQL-Group
rlm_sql (sql2): Registering sql_groupcmp for sql2-SQL-Group
   accounting {
    reference = "%{tolower:type.%{Acct-Status-Type}.query}"
   }
   post-auth {
    reference = ".query"
   }
   mysql {
    tls {
    }
   }
rlm_sql (sql2): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
rlm_sql (sql2): Attempting to connect to database "radius"
rlm_sql (sql2): Initialising connection pool
   pool {
    start = 5
    min = 4
    max = 32
    spare = 3
    uses = 0
    lifetime = 0
    cleanup_interval = 30
    idle_timeout = 60
    retry_delay = 1
    spread = no
   }
rlm_sql (sql2): Opening additional connection (0)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql2): Opening additional connection (1)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql2): Opening additional connection (2)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql2): Opening additional connection (3)
rlm_sql_mysql: Starting connect to MySQL server
rlm_sql (sql2): Opening additional connection (4)
rlm_sql_mysql: Starting connect to MySQL server

停止 sql1 并发送请求后，会发生这种情况:

...#irrelevant stuff
Received Access-Request Id 36 from **IP switch**:8345 to **IP RADIUS**:1812 length 128
        User-Name = '**mac-address**'
        User-Password = '**mac-address**'
        NAS-IP-Address = **IP switch**
        NAS-Port = 3
        NAS-Port-Id = 'Port 3'
        NAS-Port-Type = Ethernet
        NAS-Identifier = '**nas name**'
        Service-Type = Call-Check
        Framed-MTU = 1500
        Called-Station-Id = '**mac-address**'
        Calling-Station-Id = '**mac-address**'
    (1) # Executing section authorize from file /etc/raddb/sites-enabled/default
    (1)   authorize {
    (1)   [preprocess] = ok
    (1)     update request {
    (1) EXPAND %{tolower:%{1}%{2}%{3}%{4}%{5}%{6}}
    (1)    --> **mac-address**
    (1)     Calling-Station-Id := '"**mac-address**"'
    (1)     User-Name := '&Calling-Station-Id -> '**mac-address**''
    (1)     User-Password := '&Calling-Station-Id -> '**mac-address**''
    (1)     } # update request = noop
    (1)     [updated] = updated
    (1)    } # if (Calling-Station-Id =~ **Syntax check**  = updated
    (1)     ... skipping else for request 1: Preceding "if" was taken
    (1)   } # rewrite_calling_station_id rewrite_calling_station_id = updated
    (1) detail : EXPAND /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
    (1) detail :    --> /var/log/radius/radacct/**IP switch**/detail-20151019
    (1) detail : /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/**IP switch**/detail-20151019
    (1) detail : EXPAND %t
    (1) detail :    --> Mon Oct 19 15:03:18 2015
    (1)   [detail] = ok
    (1)   redundant redundant_sql {
    (1) sql1 : EXPAND %{User-Name}
    (1) sql1 :    --> **mac-address**
    (1) sql1 : SQL-User-Name set to '**mac-address**'
    rlm_sql (sql1): Reserved connection (4)
    (1) sql1 : EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
    (1) sql1 :    --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = '**mac-address**' ORDER BY id
    rlm_sql (sql1): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck WHERE username = '**mac-address**' ORDER BY id'
    rlm_sql_mysql: MYSQL check_error: 2006, returning RLM_SQL_RECONNECT
    rlm_sql (sql1): Reconnecting (4)
    rlm_sql_mysql: Starting connect to MySQL server
    rlm_sql_mysql: Couldn't connect socket to MySQL server radius@**IP sql1**:radius
    rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on '**IP sql1**' (111 "Connection refused")'
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql (sql1): Reserved connection (3)
    rlm_sql (sql1): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck WHERE username = '**mac-address**' ORDER BY id'
    rlm_sql_mysql: MYSQL check_error: 2006, returning RLM_SQL_RECONNECT
    rlm_sql (sql1): Reconnecting (3)
    rlm_sql_mysql: Starting connect to MySQL server
    rlm_sql_mysql: Couldn't connect socket to MySQL server radius@**IP sql1**:radius
    rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on '**IP sql1**' (111 "Connection refused")'
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql (sql1): Reserved connection (2)
    rlm_sql (sql1): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck WHERE username = '**mac-address**' ORDER BY id'
    rlm_sql_mysql: MYSQL check_error: 2006, returning RLM_SQL_RECONNECT
    rlm_sql (sql1): Reconnecting (2)
    rlm_sql_mysql: Starting connect to MySQL server
    rlm_sql_mysql: Couldn't connect socket to MySQL server radius@**IP sql1**:radius
    rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on '**IP sql1**' (111 "Connection refused")'
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql (sql1): Reserved connection (1)
    rlm_sql (sql1): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck WHERE username = '**mac-address**' ORDER BY id'
    rlm_sql_mysql: MYSQL check_error: 2006, returning RLM_SQL_RECONNECT
    rlm_sql (sql1): Reconnecting (1)
    rlm_sql_mysql: Starting connect to MySQL server
    rlm_sql_mysql: Couldn't connect socket to MySQL server radius@**IP sql1**:radius
    rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on '**IP sql1**' (111 "Connection refused")'
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql_mysql: Socket destructor called, closing socket
    rlm_sql (sql1): Failed to reconnect (1), no free connections are available
    rlm_sql (sql1): Executing query: 'SELECT id, username, attribute, value, op FROM radcheck WHERE username = '**mac-address**' ORDER BY id'
    Segmentation fault

在破坏与 sql1 的最后一个连接后，我希望“冗余”启动并联系 sql2...但 RADIUS 反而中止。

我知道我只 ** IP 和 Mac，但如果该项目通过“测试设置阶段”，其他一切都会正确重置。

我真的很感激任何帮助，因为我已经束手无策了。

非常感谢您阅读到这里!

Answer 1

这是 FreeRADIUS 中的缺陷，您应该在 FreeRADIUS issue tracker 上打开问题单。使用您上面发布的信息，如果可以的话，在调试器下运行它的回溯。

对于 gdb，你应该能够执行以下操作:

gdb --args <path to radiusd> -X

run
bt

回溯将显示问题所在。我快速尝试在 v3.1.x 分支上重现它，但失败了，但这可能是因为该错误需要事先打开连接才能触发缺陷。