• 注册日期: 2012-04-12
  • 文章数: 206

主题: UNKNOWN_MYSQL_US'@'localhost

==== ==== 必填信息。没有填写将不予回复 ====
- iRedMail 版本号：iredmail 0.9.5-1 + PRO-2.6.1
- 使用哪个数据库存储用户帐号（OpenLDAP，MySQL，PostgreSQL）：OpenLDAP
- 使用的 Linux/BSD 发行版名称及版本号：CentOS7
- 与您的问题相关的日志信息：
====
版主:

請問這是否正常的log

588-/usr/libexec/mysqld, Version: 5.5.50-MariaDB (MariaDB Server). started with:
589-Tcp port: 3306  Unix socket: (null)
590-Time                 Id Command    Argument
591:161013 16:11:34         1 Connect   Access denied for user 'UNKNOWN_MYSQL_US'@'localhost' (using password: NO)
592:                1 Connect   UNKNOWN_MYSQL_US@localhost as anonymous on
593:161013 16:11:47         2 Connect   iredadmin@localhost as anonymous on iredadmin
594-                2 Query     SELECT bytes FROM used_quota WHERE username = 'test@l.domain.com'
595-                2 Query     SELECT messages FROM used_quota WHERE username = 'test@l.domain.com'

/etc/my.cnf

 [mariadb]
bind-address            = 127.0.0.1
port                    = 3306
general_log = 1
general_log_file = /var/log/mariadb/mariadb.log
log-error=/var/log/mariadb/mariadb_error.log
slow_query_log
slow_query_log_file = /var/log/mariadb/mariadb_slow.log
skip-external-locking
query-cache-type    = 1
query-cache-size    = 8M
query_cache_limit   = 1M
thread_cache_size =4k
innodb_file_per_table
ssl-cert = /etc/pki/tls/certs/iRedMail.crt
ssl-key = /etc/pki/tls/private/iRedMail.key
ssl-cipher = ALL
[client]
default-character-set=utf8
[mariadb_safe]
log-error   = /var/log/mariadb/mariadb_error.log
pid-file    = /var/run/mariadb/mariadb.pid
  • 注册日期: 2009-12-18
  • 文章数: 2,864

回复: UNKNOWN_MYSQL_US'@'localhost

不正常。

iRedMail 里用到的 SQL 用户名都在 iRedMail.tips 里可以找到，无非也就是：

- root：备份脚本里用到
- vmail, vmailadmin：postfix, dovecot, iredadmin 等组件用到
- amavisd, roundcube, iredadmin, iredapd：看名字应该知道是哪个组件用的

其它就没了（以 iRedMail.tips 里的为准）。

你的 log 里出现 unknown_sql_user，而且没有用密码来连接（using password: NO），这个绝对不正常。

log 里显示 'UNKNOWN_MYSQL_US'@'localhost'，说明 sql 请求是服务器 localhost 上发起的，可能要留意一下是否一些 web 程序被利用了。

  • 注册日期: 2012-04-12
  • 文章数: 206

回复: UNKNOWN_MYSQL_US'@'localhost

/usr/sbin/mysqld, Version: 10.0.27-MariaDB (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
161014 10:43:38     2 Connect   root@localhost as anonymous on
                    2 Quit
161014 10:44:00     3 Connect   iredadmin@localhost as anonymous on iredadmin
                    3 Query     SELECT bytes FROM used_quota WHERE username = 'domainspam@domain.com'

上面, 是另一台 CentOS6 的 log , 差異為下面這一行
[161014 10:43:38     2 Connect   root@localhost as anonymous on
                    2 Quit
161014 10:44:00     3 Connect   iredadmin@localhost as anonymous on iredadmin


而先前PO的文 , CentOS7 那一台是
591:161013 16:11:34         1 Connect   Access denied for user 'UNKNOWN_MYSQL_US'@'localhost' (using password: NO)
592:                1 Connect   UNKNOWN_MYSQL_US@localhost as anonymous on
593:161013 16:11:47         2 Connect   iredadmin@localhost as anonymous on iredadmin

其無 root@localhost ,這一行 .
ps: CentOs7 異常這一台 ,我有自己安裝 Let's Encrypt , OpenDmarc, Dspam ,python-policyd-spf
在這幾個新增的模組 ,有可能造成的錯誤機率, 有可能是哪個?

  • 注册日期: 2009-12-18
  • 文章数: 2,864

回复: UNKNOWN_MYSQL_US'@'localhost

看哪个需要 mysql access，然后看它们的 config file。

5 rain6966回复 最后由 rain6966 (2016-10-19 12:13:43) 编辑

  • 注册日期: 2012-04-12
  • 文章数: 206

回复: UNKNOWN_MYSQL_US'@'localhost

版主:

我重新fresh 安裝 iRedMail
CentOS7,iRedMail 0.9.5-1 ,iRedAdmin0.6.3 ( Pro版未裝上)
和先前那台差異,多裝 Sogo

在 mariadb 啟動 general_log = 1
重新restart mariadb 後
仍然出現:

34-161019 11:38:00  InnoDB: Starting shutdown...
35-161019 11:38:01  InnoDB: Shutdown completed; log sequence number 2225190
36-161019 11:38:01 [Note] /usr/libexec/mysqld: Shutdown complete
37-
38-161019 11:38:01 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
39-/usr/libexec/mysqld, Version: 5.5.50-MariaDB (MariaDB Server). started with:
40-Tcp port: 3306  Unix socket: (null)
41-Time                 Id Command    Argument
42:161019 11:48:39          1 Connect   Access denied for user 'UNKNOWN_MYSQL_US'@'localhost' (using passwor      d: NO)
43:                 1 Connect   UNKNOWN_MYSQL_US@localhost as anonymous on
44:161019 11:49:01          2 Connect   sogo@localhost as anonymous on sogo
45-                 2 Query     SET CHARACTER SET utf8
46-                 2 Query     SELECT count(*) FROM sogo_sessions_folder WHERE c_lastseen <= 1476847141
47:                 3 Connect   sogo@localhost as anonymous on sogo
48-                 3 Query     SET CHARACTER SET utf8
49-                 3 Query     BEGIN
50-                 3 Query     SELECT t1.c_alarm_number, t1.c_name, t1.c_path, t1.c_recurrence_id, t1.c_uid       FROM  sogo_alarms_folder t1 WHERE t1.c_alarm_date >= 1476848640 AND t1.c_alarm_date <= 1476848940
51-                 3 Query     ROLLBACK
52-                 3 Query     BEGIN
53-                 3 Query     DELETE FROM sogo_alarms_folder WHERE c_alarm_date <= 1476848940
54-                 3 Query     COMMIT

請問這還是正常?


PS:安裝時中間出現
warning: /var/cache/yum/x86_64/7/epel/packages/amavisd-new-2.10.1-5.el7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Public key for amavisd-new-2.10.1-5.el7.noarch.rpm is not installed
--------------------------------------------------------------------------------
Total                                              2.1 MB/s | 218 MB  01:45
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Importing GPG key 0x352C64E5:
Userid     : "Fedora EPEL (7) <epel@fedoraproject.org>"
Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5
Package    : epel-release-7-6.noarch (@extras)
From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Running transaction check


等很久時間後,繼續自動安裝 ,這有影響?

  • 注册日期: 2009-12-18
  • 文章数: 2,864

回复: UNKNOWN_MYSQL_US'@'localhost

rain6966 写道:

等很久時間後,繼續自動安裝 ,這有影響?

等的时间长应该是由于 sogo yum repo 访问速度太慢导致的，能完成安装就没有影响。

rain6966 写道:

42:161019 11:48:39          1 Connect   Access denied for user 'UNKNOWN_MYSQL_US'@'localhost' (using passwor      d: NO)
43:                 1 Connect   UNKNOWN_MYSQL_US@localhost as anonymous on
44:161019 11:49:01          2 Connect   sogo@localhost as anonymous on sogo
45-                 2 Query     SET CHARACTER SET utf8
46-                 2 Query     SELECT count(*) FROM sogo_sessions_folder WHERE c_lastseen <= 1476847141
47:                 3 Connect   sogo@localhost as anonymous on sogo

似乎是 /etc/sogo/sogo.conf 里没有设置正确的 sql username／password？检查一下。

7 rain6966回复 最后由 rain6966 (2016-10-20 09:17:46) 编辑

  • 注册日期: 2012-04-12
  • 文章数: 206

回复: UNKNOWN_MYSQL_US'@'localhost

/etc/sogo/sogo.conf 此檔內容如下 ;
我只更改設定 >  WOLogFile = /var/log/sogo/sogo.log;

# grep -v '//' sogo.conf|sed '/^$/d'

{
    WOPort = 127.0.0.1:20000;
    WOLogFile = /var/log/sogo/sogo.log;
    WOWorkersCount = 10;
    SOGoMaximumPingInterval = 3540;
    SOGoMaximumSyncInterval = 3540;
    SOGoInternalSyncInterval = 30;
    WOWatchDogRequestTimeout = 60;
    SOGoMaximumSyncWindowSize = 100;
    SOGoMaximumSyncResponseSize = 2048;
    SOGoLanguage = English;
    SOGoLoginModule = Mail;
    SOGoForceExternalLoginWithEmail = YES;
    SOGoMailCustomFromEnabled = YES;
    SOGoEnableEMailAlarms = YES;
    SOGoSMTPServer = 127.0.0.1;
    SOGoMailingMechanism = smtp;
    SOGoSieveFolderEncoding = UTF-8;
    SOGoMemcachedHost = 127.0.0.1;
    SOGoTimeZone = "America/New_York";
    SOGoFirstDayOfWeek = 1;
    SOGoRefreshViewCheck = every_5_minutes;
    SOGoMailReplyPlacement = below;
    SOGoAppointmentSendEMailNotifications = YES;
    SOGoFoldersSendEMailNotifications = YES;
    SOGoACLsSendEMailNotifications = YES;
    SOGoPasswordChangeEnabled = YES;
    /* SQL backend
    SOGoUserSources = (
        {
            type = sql;
            id = users;
            canAuthenticate = YES;
            userPasswordAlgorithm = ssha;
            prependPasswordScheme = YES;
            isAddressBook = YES;
            displayName = "Domain Address Book";
            SOGoEnableDomainBasedUID = YES;
            DomainFieldName = "domain";
        },
    );
    SQL backend */

    SOGoUserSources = (
        {
            type = ldap;
            baseDN = "o=domains,dc=l,dc=domain1,dc=com";
            bindDN = "cn=vmailadmin,dc=l,dc=domain1,dc=com";
            bindPassword = "1fhITkS26z8bkSYBFcb0uFVQlt7EG8";
            filter = "objectClass=mailUser AND accountStatus=active AND enabledService=mail AND enabledService=sogo";
            scope = SUB;
            userPasswordAlgorithm = ssha;
            IDFieldName = mail;
            bindFields = (mail);
            CNFieldName = cn;
            UIDFieldName = mail;
            IMAPLoginFieldName = mail;
            SearchFieldNames = (cn, sn, displayName, telephoneNumber, mail, shadowAddress);
            canAuthenticate = YES;
            displayName = "Global Address Book";
            id = ldap_auth;
            isAddressBook = YES;
        }
    );

}

Sogo 不熟 , 此次是第二次安裝 , 第一次未仔細研究即砍掉重裝 .

  • 注册日期: 2009-12-18
  • 文章数: 2,864

回复: UNKNOWN_MYSQL_US'@'localhost

为何你的 sogo.conf 漏掉了许多参数。例如 line 129-137 的 7 个用于指定 SQL 账号密码的参数：
https://bitbucket.org/zhb/iredmail/src/ … o.conf-129

  • 注册日期: 2012-04-12
  • 文章数: 206

回复: UNKNOWN_MYSQL_US'@'localhost

因我使用grep -v '//' sogo.conf|sed '/^$/d'
我也不知會這樣 .
查看 sogo.conf  原始檔
129-132 是有產生

    //NGImap4DisableIMAP4Pooling = NO
    SOGoProfileURL = "mysql://sogo:GD0V7w1mFHdgy8WosMMTIQAFFT1Qb3@127.0.0.1:3306/sogo/sogo_user_profile";
    OCSFolderInfoURL = "mysql://sogo:GD0V7w1mFHdgy8WosMMTIQAFFT1Qb3@127.0.0.1:3306/sogo/sogo_folder_info";
    OCSSessionsFolderURL = "mysql://sogo:GD0V7w1mFHdgy8WosMMTIQAFFT1Qb3@127.0.0.1:3306/sogo/sogo_sessions_folder";
    OCSEMailAlarmsFolderURL = "mysql://sogo:GD0V7w1mFHdgy8WosMMTIQAFFT1Qb3@127.0.0.1:3306/sogo/sogo_alarms_folder";

    // Default language in the web interface
    SOGoLanguage = English;

135~ 137  未產生

    OCSCacheFolderURL = "PH_SOGO_DB_TYPE://PH_SOGO_DB_USER:PH_SOGO_DB_PASSWD@PH_SQL_SERVER_ADDRESS:PH_SQL_SERVER_PORT/PH_SOGO_DB_NAME/PH_SOGO_DB_TABLE_CACHE_FOLDER";
    OCSStoreURL = "PH_SOGO_DB_TYPE://PH_SOGO_DB_USER:PH_SOGO_DB_PASSWD@PH_SQL_SERVER_ADDRESS:PH_SQL_SERVER_PORT/PH_SOGO_DB_NAME/PH_SOGO_DB_TABLE_STORE";
    OCSAclURL = "PH_SOGO_DB_TYPE://PH_SOGO_DB_USER:PH_SOGO_DB_PASSWD@PH_SQL_SERVER_ADDRESS:PH_SQL_SERVER_PORT/PH_SOGO_DB_NAME/PH_SOGO_DB_TABLE_ACL";
  • 注册日期: 2012-04-12
  • 文章数: 206

回复: UNKNOWN_MYSQL_US'@'localhost

版主:
我又重新再fresh 安裝iRedMail
還是會出現 " UNKNOWN_MYSQL_US'@'localhost" 現象.

MariaDB [sogo]> show tables;
+----------------------+
| Tables_in_sogo       |
+----------------------+
| sogo_alarms_folder   |
| sogo_folder_info     |
| sogo_sessions_folder |
| sogo_user_profile    |
+----------------------+
4 rows in set (0.00 sec)

正常 第135~ 137  行 是需要產生的嗎?

這次安裝除了啟動 Mariadb的 debug log外 ,未安裝其他套件或更改設定 .

  • 注册日期: 2009-12-18
  • 文章数: 2,864

回复: UNKNOWN_MYSQL_US'@'localhost

rain6966 写道:

正常 第135~ 137  行 是需要產生的嗎?

135-137 在下一版本里是默认生成的，但是 iRedMail-0.9.5-1 没有这几行。抱歉，带来误解。

rain6966 写道:

MariaDB [sogo]> show tables;

必须有一个 'users' 表，它是一个 SQL VIEW，从 "vmail.mailbox" 里获取账号信息的。参考 line 42：
https://bitbucket.org/zhb/iredmail/src/ … sogo.sh-42