MySQL: Can't start server: Bind on TCP/IP port: Address already in use resolved

In this article we will discuss the about the issues we faced after changing mysql data directory.

For one of the environment, there was a requirement to install mysql 5.6 server on CentOS Linux and after installation we had to change the default data directory to the new location.

Everything was fine but during mysql server startup it was taking lot of time. When we checked the log then we observed the below error.


18548 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
18548 [ERROR] Do you already have another mysqld server running on port: 3306 ?
18548 [ERROR] Aborting

Below are the error logs:

[oradb@orahowdb mysql]$  tail -100f /var/log/mysqld.log
tail: cannot open ‘/var/log/mysqld.log’ for reading: Permission denied
tail: no files remaining
[oradb@orahowdb mysql]$  sudo tail -100f /var/log/mysqld.log
2018-05-23 20:18:10 17743 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2018-05-23 20:18:10 17743 [Note] Shutting down plugin 'INNODB_CMP'
2018-05-23 20:18:10 17743 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2018-05-23 20:18:10 17743 [Note] Shutting down plugin 'INNODB_LOCKS'
2018-05-23 20:18:10 17743 [Note] Shutting down plugin 'INNODB_TRX'
2018-05-23 20:18:10 17743 [Note] Shutting down plugin 'InnoDB'
2018-05-23 20:18:10 17743 [Note] InnoDB: FTS optimize thread exiting.
2018-05-23 20:18:10 17743 [Note] InnoDB: Starting shutdown...
2018-05-23 20:18:12 17743 [Note] InnoDB: Shutdown completed; log sequence number 1602387
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'BLACKHOLE'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'ARCHIVE'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'MRG_MYISAM'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'MyISAM'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'MEMORY'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'CSV'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'sha256_password'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'mysql_old_password'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'mysql_native_password'
2018-05-23 20:18:12 17743 [Note] Shutting down plugin 'binlog'
2018-05-23 20:18:12 17743 [Note] /usr/sbin/mysqld: Shutdown complete

180523 20:18:12 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180523 20:21:33 mysqld_safe Logging to '/var/log/mysqld.log'.
180523 20:21:33 mysqld_safe Starting mysqld daemon with databases from /DBdata/mysql
2018-05-23 20:21:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-05-23 20:21:33 0 [Note] /usr/sbin/mysqld (mysqld 5.6.40) starting as process 18548 ...
2018-05-23 20:21:33 18548 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2018-05-23 20:21:33 18548 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2018-05-23 20:21:33 18548 [Note] Plugin 'FEDERATED' is disabled.
2018-05-23 20:21:33 18548 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-23 20:21:33 18548 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-23 20:21:33 18548 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-23 20:21:33 18548 [Note] InnoDB: Memory barrier is not used
2018-05-23 20:21:33 18548 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-23 20:21:33 18548 [Note] InnoDB: Using Linux native AIO
2018-05-23 20:21:33 18548 [Note] InnoDB: Using CPU crc32 instructions
2018-05-23 20:21:33 18548 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-05-23 20:21:33 18548 [Note] InnoDB: Completed initialization of buffer pool
2018-05-23 20:21:33 18548 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-23 20:21:33 18548 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-23 20:21:33 18548 [Note] InnoDB: Waiting for purge to start
2018-05-23 20:21:33 18548 [Note] InnoDB: 5.6.40 started; log sequence number 1602387
2018-05-23 20:21:33 18548 [Note] Server hostname (bind-address): '*'; port: 3306
2018-05-23 20:21:33 18548 [Note] IPv6 is available.
2018-05-23 20:21:33 18548 [Note]   - '::' resolves to '::';
2018-05-23 20:21:33 18548 [Note] Server socket created on IP: '::'.
2018-05-23 20:21:33 18548 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
2018-05-23 20:21:33 18548 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2018-05-23 20:21:33 18548 [ERROR] Aborting


Finally, we checked the mysql TCP/IP port[3306]:

[root@orahowdb ~]# lsof -i TCP:3306
COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  17915 mysql   10u  IPv6  55522      0t0  TCP *:mysql (LISTEN)

We can see that mysqld is already listining on port 3306 which was causing the problem. Finally we checked the pid of mysqld and killed the process.

[root@orahowdb ~]# ps -ef|grep mysql

root     17830     1  0 16:01 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/####

mysql    17915 17830  0 16:01 ?        00:00:04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log

root     18869 15338  0 20:23 pts/2    00:00:00 sudo tail -100f /var/log/mysqld.log

root     18870 18869  0 20:23 pts/2    00:00:00 tail -100f /var/log/mysqld.log

root     20223 19766  0 20:31 pts/2    00:00:00 grep --color=auto mysql

[root@orahowdb ~]# netstat -lp | grep 3306

[root@blrsubjiradb ~]# lsof -i TCP:3306

COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysqld  17915 mysql   10u  IPv6  55522      0t0  TCP *:mysql (LISTEN)

[root@orahowdb ~]# kill -9 17915

[root@orahowdb ~]# lsof -i TCP:3306

[root@orahowdb ~]# ps -ef|grep mysql

root     18869 15338  0 20:23 pts/2    00:00:00 sudo tail -100f /var/log/mysqld.log

root     18870 18869  0 20:23 pts/2    00:00:00 tail -100f /var/log/mysqld.log

root     20242 19766  0 20:32 pts/2    00:00:00 grep --color=auto mysql

[root@orahowdb ~]#

After killing the process, finally we restarted the database normally.