Suddenly Can't connect to database

[sandipcd]

I am using Kloxo-mr 7 and install Drupal CMS. But sometimes it is happening that suddenly website can't connect to database. After updating the same password, it is connecting. The error is given below -

PDOException: SQLSTATE[HY000] [1045] Access denied for user 'hosting123ultrxx_'@'localhost' (using password: YES) in lock_may_be_available() (line 167 of /home/hosffsgsg/doctorsourav/includes/lock.inc).

Can you please tell me how to fix this problem ?

[MRatWork]

Inform here 'sh /script/sysinfo'.

[sandipcd]

A. Control Panel:
   - Kloxo-MR: 7.0.0.b-2016040705
   - Web: hiawatha-10.1.0-f.6.mr.el6.x86_64
   - PHP: php54s-5.4.45-1.ius.el6 (fpm mode)
B. Plateform:
   - OS: CentOS release 6.7 (Final) x86_64
   - Hostname: server1.worldservermarket.com
C. Services:
   1. MySQL: MariaDB-server-10.0.24-1.el6.x86_64
   2. PHP:
      - Branch: php54-cli-5.4.45-1.ius.el6.x86_64
      - Used: --Use PHP Branch--
   3. Web Used: apache
     - Hiawatha: --unused--
     - Lighttpd: --uninstalled--
     - Nginx: --uninstalled--
     - Httpd: httpd-2.2.31-1.mr.el6.x86_64
       - PHP Type: php-fpm_event
   4. WebCache: none
     - ATS: --uninstalled--
     - Squid: --uninstalled--
     - Varnish: --uninstalled--
   5. Dns: nsd
     - Bind: --uninstalled--
     - DJBDns: --uninstalled--
     - NSD: nsd-4.1.9-1.mr.el6.x86_64
     - PowerDNS: --uninstalled--
     - Yadifa: --uninstalled--
   6. Mail: qmail-toaster-1.03-1.3.55.mr.el6.x86_64
      - pop3/imap4: courier-imap-toaster-4.1.2-1.3.18.mr.el6.x86_64
      - spam: bogofilter
D. Memory:
                total       used       free     shared    buffers     cached
   Mem:          1844       1748         95         31        198        886
   -/+ buffers/cache:        663       1181
   Swap:         1023          1       1022
E. Disk Space:
   Filesystem      Size  Used Avail Use% Mounted on
   /dev/sda2       146G  6.8G  132G   5% /


*** Process Time: 00:00:00:04.4854 (dd:hh:mm:ss:xxxxxx) ***

* Note: run 'sh /script/sysinfo -y' if you want run 'fix-service-list' also
        (importance after Kloxo-MR update)

[MRatWork]

Try select 'high' or 'medium' for 'apache optimize' in 'webserver config'.

[sandipcd]

Done 'high' for 'apache optimize' in 'webserver config'. But it is still happening.

Is this happening because server has only 2 GB RAM ?

[MRatWork]

Done 'high' for 'apache optimize' in 'webserver config'. But it is still happening.

Is this happening because server has only 2 GB RAM ?

Memory is enough but need optimize mysql. Usually using mysqltuner.

[sandipcd]

Regarding mysqltuner, found two helpful topics. Which one I should follow -

https://www.linode.com/docs/databases/mysql/tuning-your-mysql-database
https://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner

[fossxplorer]

@sandipcd
1: Is there any thing interesting in the DB log?  /var/log/mysql.log  (IIRC)
2: Paste your MariaDB's config file (e.g in pastebin.org or similar).

I'll try to help you.

[sandipcd]

Now when giving command 'service httpd restart'. Stopping httpd failed. Any suggestion ?

Code: [Select]

[root@server1 ~]# service httpd restart
Stopping httpd:                                            [FAILED]
Starting httpd:                                            [  OK  ]
[root@server1 ~]#

Also phpmyadmin login link is not showing in database list page. See the attached file.

[MRatWork]

Click 1 of database and you will see 'sql manager' in 'tab menu'.

[sandipcd]

Done 'high' for 'apache optimize' in 'webserver config'. But it is still happening.

Is this happening because server has only 2 GB RAM ?

Memory is enough but need optimize mysql. Usually using mysqltuner.

Install mysqltuner and run the command - 'perl mysqltuner.pl'. Below is the output -

Code: [Select]

[root@server1 ~]# perl mysqltuner.pl
 >>  MySQLTuner 1.6.13 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: hoxxinxxxxx---
Please enter your MySQL administrative password:
[--] Skipped version check for MySQLTuner script
[!!] failed to execute: SHOW SLAVE STATUS\G
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW SLAVE HOSTS
[!!] FAIL Execute SQL / return code: 256
[OK] Currently running supported MySQL version 10.0.25-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE                   _SCHEMA
[--] Data in InnoDB tables: 5G (Tables: 1790)
[!!] Total fragmented tables: 1

-------- Security Recommendations ------------------------------------------------------------------
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE TRIM(USER) = '' OR USER IS NULL
[!!] FAIL Execute SQL / return code: 256
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS N              ULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE CAST(password as Binary) = PASS              WORD(user) OR CAST(password as Binary) = PASSWORD(UPPER(user)) OR CAST(password as Binary) = PASSWORD(UPPER(              LEFT(User, 1)) + SUBSTRING(User, 2, LENGTH(User)))
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE HOST='%'
[!!] FAIL Execute SQL / return code: 256
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 16h 46m 22s (2M q [35.049 qps], 18K conn, TX: 10G, RX: 2G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 990.6M
[--] Max MySQL memory    : 836.0M
[--] Other process memory: 562.2M
[--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 454.9M (45.92% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (84.39% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (25/2M)
[OK] Highest usage of available connections: 9% (14/151)
[OK] Aborted connections: 1.87%  (338/18047)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 6% (531 on disk / 8K total)
[!!] Table cache hit rate: 2% (399 open / 19K opened)
[OK] Open file limit used: 0% (5/20K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enough for your version (10.0.25-MariaDB)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.7% (77K cached / 244 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 128.0M/5.2G
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[OK] InnoDB Used buffer: 87.50% (7167 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 93.07% (263036560 hits/ 282631949 total)
[OK] InnoDB Write log efficiency: 99.96% (599060211 hits/ 599304638 total)
[!!] InnoDB log waits: 0.01% (19 waits / 244427 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE hostingultraso1_hostingultraso.cache_form; -- can free 178 MB
    Total freed space after theses OPTIMIZE TABLE : 178 Mb
    MySQL started within last 24 hours - recommendations may be inaccurate
    Dedicated this server to your database for highest performance.
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (20480) variable
    should be greater than table_open_cache ( 400)
Variables to adjust:
    query_cache_type (=0)
    table_open_cache (> 400)
    innodb_buffer_pool_size (>= 5G) if possible.
    innodb_buffer_pool_instances (=1)
    innodb_log_buffer_size (>= 8M)
[root@server1 ~]#


[MRatWork]

Modified /etc/my.cnf.d/server.cnf related 'mysqltuner' recommendation.