How to check the running configuration file used by MySQL Database Server

By Posted on

Introduction

This is an article derived from the previous article before. That article exist in this link. It has an unique title which is ‘How to Solve Error Message ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0′. On the other hand, the content of this article has a connection with that article. It is mainly to show how to check the running configuration file used by MySQL Database Server. Actually, the content is part of the article mentioned above. So, in one of the step in that article, there is a need to check for the running configuration file used by MySQL Database Server. It happens because every change in the configuration file applied doesn’t affect the behaviour of the running MySQL Database Server’s process.

Checking the Running Configuration File of the currently running MySQL Database Server

The following is the step for checking the running configuration file used by MySQL Database Server :

1. Check the listening process of the MySQL Database Server. The representation of that listening process is available in the form of a listening port. Normally, MySQL Database Server will listen in port ‘3306’. So, execute the following command to check whether that port is currently listening or not as ‘root’ or ‘super admin’ account :

  

user@hostname:~$ netstat -tulpn | grep 3306
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp6       0      0 :::3306                 :::*                    LISTEN      -                   
user@hostname:~$

The above command execution will present an output without displaying the process ID of the listening port. It is because the command execution is using not a ‘root’ or a ‘superadmin’ level account. Below is the actual command execution using the ‘root’ account : 

user@hostname:~$ sudo su - 
[sudo] password for user: 
root@hostname ~# netstat -tulpn | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      21192/mysqld        
root@hostname ~#

2. Next, check the running process of the binary file of MySQL Database Server using the process ID retrieved from the above output command : 

root@hostname ~# ps -aux | grep 21192
mysql    21192  0.2  0.1 3031392 24664 ?       Sl   Sep16   3:50 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
root     32760  0.0  0.0  23960  1104 pts/17   S+   18:55   0:00 grep 21192
root@hostname ~#

3. After finding the actual file executing the MySQL Database Server process, start checking for the running configuration file used by that process. Just execute the following command : 

root@hostname ~# cd /usr/sbin
root@hostname sbin# strace ./mysqld
...
stat("/etc/my.cnf", 0x7ffc9dd0f010)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=839, ...}) = 0
openat(AT_FDCWD, "/etc/mysql/my.cnf", O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=839, ...}) = 0
brk(0x31a2000)                          = 0x31a2000
read(3, "#\n# The MySQL database server co"..., 4096) = 839
openat(AT_FDCWD, "/etc/mysql/conf.d/", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4
fstat(4, {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
getdents(4, /* 4 entries */, 32768)     = 120
getdents(4, /* 0 entries */, 32768)     = 0
close(4)                                = 0
stat("/etc/mysql/conf.d/mysql.cnf", {st_mode=S_IFREG|0644, st_size=629, ...}) = 0
openat(AT_FDCWD, "/etc/mysql/conf.d/mysql.cnf", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0644, st_size=629, ...}) = 0
read(4, "[mysqld]\n\n# Connection and Threa"..., 4096) = 629
read(4, "", 4096)                       = 0
close(4)                                = 0
stat("/etc/mysql/conf.d/mysqldump.cnf", {st_mode=S_IFREG|0644, st_size=55, ...}) = 0
openat(AT_FDCWD, "/etc/mysql/conf.d/mysqldump.cnf", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0644, st_size=55, ...}) = 0
read(4, "[mysqldump]\nquick\nquote-names\nma"..., 4096) = 55
read(4, "", 4096)                       = 0
close(4)                                = 0
read(3, "", 4096)                       = 0
close(3)                                = 0
stat("/root/.my.cnf", 0x7ffc9dd0f010)   = -1 ENOENT (No such file or directory)
...
root@hostname sbin#

As in the above output command appear, the file configuration is one of appear in it. But the correct one is the one exist in ‘/etc/mysql/conf.d/mysql.cnf’. It is the right one according to the above output message in one line. That line is in the following line : 

openat(AT_FDCWD, "/etc/mysql/conf.d/mysql.cnf", O_RDONLY) = 4

So, in order to change and give an impact to the behaviour of the running MySQL Database Server. Just edit the file mentioned above.

Leave a Reply