cant connect to mysql database issue

melkham

Registered
Sep 2, 2014
4
0
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
 

melkham

Registered
Sep 2, 2014
4
0
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 =


[email protected] [~]# perl mysqltuner.pl
>> MySQLTuner 1.6.1 - Major Hayden <[email protected]>
>> 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 '[email protected]' has user name as password.
[!!] User '[email protected]' has user name as password.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' hasn't specific host restriction.
[!!] User '[email protected]%' 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 [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for [email protected]% to [email protected]
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
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,227
463
MySQL was not up very long, but you can try adjusting the values advised under the "Recommendations" section of the tuner output.

Thank you.