Activities

June 2013
M T W T F S S
« May   Jul »
 12
3456789
10111213141516
17181920212223
24252627282930

WHM/Cpanel : Php application does not connecting MySQL database

Recently I’ve to work with a WHM/Cpanel server to optimize the MySQL server to get better application performance. The default MySQL server config (/etc/my.cnf) was not been applying any server level optimization.

What my plan are,

a. Take a MySQl full database dump and stop the MySQL,Cpanel and Apache web-server.
b. Rename MySQL data directory and create a new one
c. setting proper permissions to that data directory
#chown mysql.mysql /var/lib/mysql && chmod -R 700 /var/lib/mysql
d. Replace the “/etc/my.cnf with optimized MySQL variables ( I prefer innoDB as default storage engine and enabled query cache, innodb buffer pool size.
e. Start mysql server with an empty database. ( in WHM/Cpanel, you may need to copy the folder ‘mysql” from the previous backup in mysql data directory.
f. Restore full database backup and restart MySQL service.

Expected problem

a. MySQl did not started with an empty data directory. You may need to copy the “mysql” database separately and set proper permission shown above.

b. MySQL-PHP connectivity : Once you started the MySQL server with optimized variables, you will able to access the server from MySQL console. BUT your php application does not able to connect MySQL if you’were using the database host name “localhost

Unfortunately my application exclusively used “localhost” to identify the DB server and application failed after this optimization process.

What is the cause and solution ?

Causes : The SQL connection string “localhost” is using socket type MySQL connectivity which requires read permission to access the MySQL socket file (/var/lib/mysql/mysql.sock). It will not connect to server since I’ve permit only MySQL user to access that file by executing this command ( chmod -R 700 /var/lib/mysql)

Solution

a. Set MySQL data folder permission where MySQL socket file will create by default to 755 ie # chmod -R 755 /var/lib/mysql and restart mySQL service.

 [root@rc-040 ~]#chmod -R 755 /var/lib/mysql
 [root@rc-040 ~]#service mysqld restart

b. Switching to TCP/IP connection : You may need to switch the MySQL connection type over TCP/IP connectivity. So this work with my earlier permission ie chmod -R 700 /var/lib/mysql. This require you may need to change the all the area where “localhost” used with loopback IP ( 127.0.0.1) or server IP

Fix is simple !! But it lead me to waste few hours to try different possibilities badly 🙁 :-(.

So clearly understand the difference of socket type and TCP/IP type connectivity before touching MySQL server.

MySQL(/etc/my.cnf)optimized values are shown below,

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#skip-innodb
#default-storage-engine=InnoDB
innodb_file_per_table
innodb_buffer_pool_size=1048M
innodb_additional_mem_pool_size=10M
default-storage-engine=Innodb
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=20M
innodb_log_file_size=30M
innodb_thread_concurrency=10
innodb_flush_method=O_DIRECT
transaction-isolation=READ-COMMITTED
query_cache_size=40M
query_cache_limit=2M
query_cache_type=1
table_cache=1024
max_heap_table_size=5M
sort_buffer_size =4M
join_buffer_size=4M
thread_cache_size=8
thread_cache=16
thread_concurrency =8
tmp_table_size=3M
key_buffer_size=1M
read_buffer_size=2M
read_rnd_buffer_size=6M
bulk_insert_buffer_size=4M

[client]
socket=/var/lib/mysql/mysql.sock

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>