The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

cant connect to mysql database issue

Discussion in 'Database Discussions' started by melkham, Oct 22, 2015.

  1. melkham

    melkham Registered

    Joined:
    Sep 2, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    i have a dedicated server Centos 6.6 with 32RAM and 8 CPU .
    i have 2 wordpress sites running on it . the websites are going constantly offline with error ( cant connect to mysql database )

    upload_2015-10-22_16-4-8.png


    Please advice
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. melkham

    melkham Registered

    Joined:
    Sep 2, 2014
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    My sql /etc/my.cnf settings are

    [client]
    #password = your_password
    port = 3306
    socket = /var/lib/mysql/mysql.sock

    # Here follows entries for some specific programs

    # The MariaDB server
    [mysqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-external-locking
    key_buffer_size = 256M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size= 16M
    max_connections= 500
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8

    # Point the following paths to different dedicated disks
    #tmpdir = /tmp/
    #skip-networking
    log-bin=mysql-bin
    binlog_format=mixed

    server-id = 1

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [myisamchk]
    key_buffer_size = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout

    mysqltunner output =


    root@ns369446 [~]# perl mysqltuner.pl
    >> MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at MySQLTuner-perl by major
    >> Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 10.0.21-MariaDB-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
    [--] Data in MyISAM tables: 1G (Tables: 184)
    [--] Data in InnoDB tables: 15M (Tables: 65)
    [--] Data in MEMORY tables: 0B (Tables: 2)
    [!!] Total fragmented tables: 17

    -------- Security Recommendations -------------------------------------------
    [OK] There is no anonymous account in all database users
    [OK] All database users have passwords assigned
    [!!] User 'munin@localhost' has user name as password.
    [!!] User 'tecseek_1@localhost' has user name as password.
    [!!] User 'unixmenc@%' hasn't specific host restriction.
    [!!] User 'unixmenc_admin@%' hasn't specific host restriction.
    [!!] User 'unixmenc_anblik@%' hasn't specific host restriction.
    [!!] User 'unixmenc_chat@%' hasn't specific host restriction.
    [!!] User 'unixmenc_forum@%' hasn't specific host restriction.
    [!!] User 'unixmenc_miko@%' hasn't specific host restriction.
    [!!] User 'unixmenc_phpuser@%' hasn't specific host restriction.
    [!!] User 'unixmenc_staging@%' hasn't specific host restriction.
    [!!] User 'unixmenc_xx@%' hasn't specific host restriction.
    [!!] There is no basic password file list !

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4m 11s (35K q [139.558 qps], 614 conn, TX: 1B, RX: 15M)
    [--] Reads / Writes: 97% / 3%
    [--] Binary logging is enabled (GTID MODE: OFF)
    [--] Total buffers: 560.0M global + 6.4M per thread (500 max threads)
    [OK] Maximum reached memory usage: 822.7M (2.56% of installed RAM)
    [OK] Maximum possible memory usage: 3.7G (11.72% of installed RAM)
    [OK] Slow queries: 0% (0/35K)
    [OK] Highest usage of available connections: 8% (41/500)
    [OK] Aborted connections: 0.00% (0/614)
    [OK] Query cache efficiency: 37.2% (18K cached / 49K selects)
    [!!] Query cache prunes per day: 160063
    [OK] Sorts requiring temporary tables: 0% (5 temp sorts / 4K sorts)
    [!!] Temporary tables created on disk: 34% (616 on disk / 1K total)
    [OK] Thread cache hit rate: 79% (126 created / 614 connections)
    [OK] Table cache hit rate: 164% (161 open / 98 opened)
    [OK] Open file limit used: 5% (128/2K)
    [!!] Table locks acquired immediately: 86%
    [OK] Binlog cache memory access: 100.00% ( 7 Memory / 7 Total)

    -------- MyISAM Metrics -----------------------------------------------------
    [!!] Key buffer used: 19.8% (53M used / 268M cache)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/45.1M
    [OK] Read Key buffer hit rate: 99.9% (4M cached / 4K reads)
    [!!] Write Key buffer hit rate: 4.8% (1K cached / 1K writes)

    -------- InnoDB Metrics -----------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 128.0M/15.6M
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [!!] InnoDB Used buffer: 6.87% (563 used/ 8191 total)
    [OK] InnoDB Read buffer efficiency: 95.79% (12779 hits/ 13341 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 10 writes)

    -------- AriaDB Metrics -----------------------------------------------------
    [--] AriaDB is disabled.

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

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Optimize queries and/or use InnoDB to reduce lock wait
    Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_instances (=1)




    Please advice
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    MySQL was not up very long, but you can try adjusting the values advised under the "Recommendations" section of the tuner output.

    Thank you.
     
Loading...

Share This Page