Enabling extra_port in option file cause the database to refuse to open

Is this expected behavior?

Starting up the instance without extra_port set looks like this:

/etc/my.cnf

[mysqld]

thread_handling=pool-of-threads

excerpt from err.log

2014-08-25 13:06:57 31488 [Note] Server hostname (bind-address): ‘*’; port: 3306
2014-08-25 13:06:57 31488 [Note] IPv6 is available.
2014-08-25 13:06:57 31488 [Note] - ‘::’ resolves to ‘::’;
2014-08-25 13:06:57 31488 [Note] Server socket created on IP: ‘::’.
2014-08-25 13:06:57 31488 [Note] Event Scheduler: Loaded 0 events
2014-08-25 13:06:57 31488 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.6.19-67.0’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Percona Server (GPL), Release 67.0, Revision 618

If I add this to /etc/my.cnf after the thread_handling line:

extra_port = 3307

the excerpt from the log looks like this:

2014-08-25 13:06:05 31330 [Note] Server hostname (bind-address): ‘'; port: 3306
2014-08-25 13:06:05 31330 [Note] IPv6 is available.
2014-08-25 13:06:05 31330 [Note] - ‘::’ resolves to ‘::’;
2014-08-25 13:06:05 31330 [Note] Server socket created on IP: ‘::’.
2014-08-25 13:06:05 31330 [Note] Server hostname (bind-address): '
’; port: 3306
2014-08-25 13:06:05 31330 [Note] IPv6 is available.
2014-08-25 13:06:05 31330 [Note] - ‘::’ resolves to ‘::’;
2014-08-25 13:06:05 31330 [Note] Server socket created on IP: ‘::’.
2014-08-25 13:06:05 31330 [ERROR] Can’t start server: Bind on TCP/IP port: Permission denied
2014-08-25 13:06:05 31330 [ERROR] Do you already have another mysqld server running on port: 3306 ?
2014-08-25 13:06:05 31330 [ERROR] Aborting

However if I start the server manually with extra_port as a command line option, it appears to work.

mysqld_safe --extra_port=3307 &

ps -ef| grep my

root 31649 31002 0 13:20 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --extra_port=3307
mysql 31742 31649 3 13:20 pts/0 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --extra-port=3307 --log-error=/var/lib/mysql/cmcgrail2014vm.localdomain.err --pid-file=/var/lib/mysql/cmcgrail2014vm.localdomain.pid

Here’s the excerpt from the log, with successful use of the extra_port:

2014-08-25 13:20:46 31742 [Note] Server hostname (bind-address): ‘'; port: 3306
2014-08-25 13:20:46 31742 [Note] IPv6 is available.
2014-08-25 13:20:46 31742 [Note] - ‘::’ resolves to ‘::’;
2014-08-25 13:20:46 31742 [Note] Server socket created on IP: ‘::’.
2014-08-25 13:20:46 31742 [Note] Server hostname (bind-address): '
’; port: 3306
2014-08-25 13:20:46 31742 [Note] IPv6 is available.
2014-08-25 13:20:46 31742 [Note] - ‘::’ resolves to ‘::’;
2014-08-25 13:20:46 31742 [Note] Server socket created on IP: ‘::’.
2014-08-25 13:20:46 31742 [Note] Event Scheduler: Loaded 0 events
2014-08-25 13:20:46 31742 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.6.19-67.0’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Percona Server (GPL), Release 67.0, Revision 618

Logged in and checked that the runtime reflects the config.

mysql> show variables like ‘%extra%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| extra_max_connections | 1 |
| extra_port | 3307 |
±----------------------±------+
2 rows in set (0.00 sec)

Confirmed I can connect on alternate port

$ mysql --port=3307 -h127.0.0.1 --protocol=tcp
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.19-67.0 Percona Server (GPL), Release 67.0, Revision 618

Hi,

I have tested locally and its working for me. Can you check where exactly you are mentioned that variable in my.cnf? it should be under [mysqld], also check that mysql is using the same my.cnf.

root@nilnandan-Dell-XPS:/var/lib/mysql# cat /etc/mysql/my.cnf | grep “extra”
extra_port = 3307
root@nilnandan-Dell-XPS:/var/lib/mysql#

root@nilnandan-Dell-XPS:/var/lib/mysql# ps -ef | grep mysql
root 19800 17447 0 14:26 pts/2 00:00:00 /bin/sh /usr/bin/mysqld_safe
mysql 20234 19800 0 14:26 pts/2 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

root@nilnandan-Dell-XPS:/var/lib/mysql# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618

mysql> show global variables like ‘extra%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| extra_max_connections | 1 |
| extra_port | 3307 |
±----------------------±------+
2 rows in set (0.00 sec)

Here’s the entire option file. MySQL is definitely using it.

[cmcgrail@cmcgrail2014vm ~]$ cat /etc/my.cnf

[mysqld]

thread_handling=pool-of-threads
#extra_port = 3307

You currently have the line commented out with the # sign, which means the setting is getting ignored.

Right. The instance would not start with that line uncommented. Since I wanted the instance to start, I commented that out.

I’m trying to get some help figuring out why having that line in the option file makes the database crash on startup. The previous reply was helpful as it confirmed that you SHOULD be able to put it in the option file. I included the contents of the file to show that there really wasn’t anything else in the file that might be conflicting with that setting.

I’d try again and verify that no other MySQL instances are running when you try the extra_port option in the my.cnf. Your error message is complaining about port 3306, not 3307. So unless there is a bug or querk in the error message (possible), it seems there may be something else going on, as it should complain about 3307 instead if that is the issue.

If you are running SELinux, you also may want to shut that down temporarily or add an exception for the port to see if that helps. This may not be the case since you did get it to start with mysqld_safe, but you never know.

Something like: sudo /usr/sbin/semanage port -a -t mysqld_port_t -p tcp 3307

SELinux is definitely implicated. It is rather strange that it would interfere with one way of enabling extra_port, and not both, but that seems to be what was happening.

There had never been cause for me to even check if SELinux was running on the test VMs I set up to evaluate features like this. In this case though I checked and saw that it was indeed running. After adjusting the config to permissive mode and rebooting, the database started and opened just fine with extra_port enabled in the option file.

The OS still wrote something to the messages log when it processed the request to use the extra port, but the error message was slightly different (see below) and in permissive mode it did not prevent the port from being used.

Here are some command line excerpts that provide more detail.

Try to start percona server with extra_port in the option file. [ Host is default install of CentOS 6.2 in an Oracle VirtualBox. ]

This showed up in the messages log

Sep 1 14:11:37 cmcgrail2014vm kernel: type=1400 audit(1409605897.072:32493): avc: denied { name_bind } for pid=16614 comm=“mysqld” src=3307 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:port_t:s0 tclass=tcp_socket

Disabled firewall in GUI. This appeared to have no effect.

A check still showed selinux was running

[root@cmcgrail2014vm log]# sestatus
SELinux status: enabled
SELinuxfs mount: /selinux
Current mode: enforcing
Mode from config file: enforcing
Policy version: 24
Policy from config file: targeted
[root@cmcgrail2014vm log]# vi /etc/selinux/config

Changed mode from enforcing to permissive. Rebooted.

[root@cmcgrail2014vm cmcgrail]# sestatus
SELinux status: enabled
SELinuxfs mount: /selinuxa
Current mode: permissive
Mode from config file: permissive
Policy version: 24
Policy from config file: targeted

On reboot there was no problem automatically starting mysql with extra_port in the option file!

mysql> show variables like ‘extra%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| extra_max_connections | 1 |
| extra_port | 3307 |
±----------------------±------+
2 rows in set (0.01 sec)

Although another error was logged in the messages file…

Sep 1 14:20:04 cmcgrail2014vm kernel: type=1400 audit(1409606404.437:3): avc: denied { name_bind } for pid=2010 comm=“mysqld” src=3307 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:port_t:s0 tclass=tcp_socket

=====

Now that the issue has been isolated we can deal with this is a more tailored way if it is an issue when we try and make use of this feature outside of sandboxed test systems.

Thanks for the assistance.

Glad you got it going!

The error message makes sense because (at least based on what I see here) you put SELinux in permissive mode but did not add a rule for that port. That means SELinux will not prevent the action in permissive mode, but still logs the exception. So if you add a rule to SELinux for mysqld and port 3307, it should prevent the error message and allow you to run SELinux in enforcing mode (if desired).

Thanks people… saved the day. wouldn’t start as a service under centOS with a change in the datadir (as mentioned in the actual .service files… if I had bothered to look before). But would start as a command line.

Changed selinux.config … and bingo… off we go.