Not the answer you need?
Register and ask your own question!

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

cmcgrailcmcgrail ContributorCurrent User Role Patron
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
...

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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.

    [email protected]:/var/lib/mysql# cat /etc/mysql/my.cnf | grep "extra"
    extra_port = 3307
    [email protected]:/var/lib/mysql#

    [email protected]:/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

    [email protected]:/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)
  • cmcgrailcmcgrail Contributor Current User Role Patron
    Here's the entire option file. MySQL is definitely using it.

    [[email protected] ~]$ cat /etc/my.cnf

    [mysqld]

    thread_handling=pool-of-threads
    #extra_port = 3307
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    You currently have the line commented out with the # sign, which means the setting is getting ignored.
  • cmcgrailcmcgrail Contributor Current User Role Patron
    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.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • cmcgrailcmcgrail Contributor Current User Role Patron
    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

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

    Changed mode from enforcing to permissive. Rebooted.

    [[email protected] 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.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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).
  • woodygsdwoodygsd Entrant Inactive User Role Supporter
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.