Port for mysql linux

MySQL Port: List of MySQL Server Port Number

In this post we will discuss about MySQL Port numbers which is used by MySQL Server on Linux and windows. As you know that MySQL default port is 3306 but apart from this, it uses several other ports as well for different purpose.

List of MySQL Port Number

MySQL Default Port :

3306/TCP is the MySQL default port which uses TCP protocol for MySQL client-server communication.

33060/TCP : Used for MySQL client to server connection for X Protocol. It is required unless you are only using port 3306.

Query to Check Which Port MySQL is running on:

mysql> SHOW VARIABLES LIKE 'port'; mysql> SHOW GLOBAL VARIABLES LIKE 'port'; $ cat /etc/mysql/my.cnf | grep -i port

MySQL Administrative connection Port:

33062/TCP – This port is configured mainly for administrative connections. This port is not required but it provides alternative to the single administrative connection that is permitted on the network interface for ordinary connections. You can verify admin port value using below command.

mysql> SHOW VARIABLES LIKE 'admin_port';

MySQL High Availability Ports:

33061/TCP – This is the default MySQL group replication and internal communication port which is required for group replication communication between group members like InnoDb, cluster instances. It is also used by MySQL shell to check a server during InnoDB cluster configuration.

3306/TCP – This is the default port used for MySQL replication.

MySQL Backup Ports:

3306/TCP – This port is used for InnoDB cluster backup.

443/TCP – This port is used for Amazon s3 and Oracle object store purpose.

MySQL External Authentication Port:

389/TCP – It is used for MySQL Enterprise Authentication to LDAP and Active Directory.

Conclusion: MySQL uses 3306 as a default port for client server communication. It uses other ports as well for different tasks. You can configure MySQL ports other than 3306 as default port in my.cnf file and restart the server to change the configuration.

Must Read:

Share this:

Источник

How to Change Default MySQL/MariaDB Port in Linux

In this guide we’ll learn how to change the default port that MySQL/MariaDB database binds in CentOS 7 and Debian-based Linux distributions. The default port that MySQL database server is running under Linux and Unix is 3306/TCP.

In order to change the default MySQL/MariaDB database port in Linux, open MySQL server configuration file for editing by issuing the below command.

# vi /etc/my.cnf.d/server.cnf [On CentOS/RHEL] # vi /etc/mysql/mariadb.conf.d/50-server.cnf [On Debian/Ubuntu]

Search for the line stat starts with [mysqld] and place the following port directive under [mysqld] statement, as shown in the below file excerpts. Replace the port variable accordingly.

After you’ve added the new MySQL/MariaDB port, save and close the configuration file and install the following package under CentOS 7 in order to apply the required SELinux rules to allow the database to bind on the new port.

# yum install policycoreutils-python

Next, add the below SELinux rule to bind MySQL socket on the new port and restart the database daemon to apply changes, by issuing the following commands. Again, replace MySQL port variable to match your own port number.

--------------- On CentOS/RHEL --------------- # semanage port -a -t mysqld_port_t -p tcp 12345 # systemctl restart mariadb --------------- On Debian/Ubuntu --------------- # systemctl restart mysql [On Debian/Ubuntu]

In order to verify if the port configuration for MySQL/MariaDB database server has been successfully applied, issue netstat or ss command and filter the results via grep command in order to easily identify the new MySQL port.

# ss -tlpn | grep mysql # netstat -tlpn | grep mysql

You can also display the new MySQL port by logging in to MySQL database with root account and issue the below command. However, be aware that all connections to MySQL on localhost are made via MySQL unix domain socket, not via the TCP socket. But the TCP port number must be explicitly specified in case of command line remote connections to MySQL database using the -P flag.

# mysql -h localhost -u root -p -P 12345 MariaDB [(none)]> show variables like 'port';

In case of remote connection to MySQL database, the root user must be explicitly configured to allow incoming connections form all networks or just an IP address, by issuing the below command in MySQL console:

# mysql -u root -p MariaDB [(none)]> grant all privileges on *.* to 'root'@'192.168.1.159' identified by 'strongpass'; MariaDB [(none)]> flush privileges; MariaDB [(none)]> exit

Remotely log in to MySQL server via a command line client on the new port by issuing the below command.

# mysql -h 192.168.1.159 -P 12345 -u root -p

Finally, once you’ve changed MySQL/MariaDB database server port, you need to update your distribution Firewall rules to allow incoming connections to the new TCP port so that remote clients can successfully connect to the database.

Источник

How to test which port MySQL is running on and whether it can be connected to?

Neither works. Not sure if both are supposed to work, but at least one of them should 🙂 How can I make sure that the port is indeed 3306? Is there a linux command to see it somehow? Also, is there a more correct way to try it via a url?

14 Answers 14

To find a listener on a port, do this:

You should see a line that looks like this if mysql is indeed listening on that port.

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 

Port 3306 is MySql’s default port.

To connect, you just have to use whatever client you require, such as the basic mysql client.

mysql -h localhost -u user database

Or a url that is interpreted by your library code.

@mbmast the 127. means listen on local host only (not externally accessible). The 0.0.0.0 means «all interfaces», and therefore is (usually) externally visible.

I thought to be externally visible, it had to be the machine’s own IP address and that 0.0.0.0 means the service is not available from anywhere. Do I have that wrong? I have a box running MySQL, the firewall has 3306 open from any IP address but MySQL is refusing the connection, I thought because currently MySQL is listening on 0.0.0.0.

Should merge this with @bortunac’s answer, which references the -p parameter. Adding the process(es) really helps make this information more useful.

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT'; 

grep port /etc/mysql/my.cnf ( at least in debian/ubuntu works )

mysql -u user_name -puser_pass -e "SHOW variables LIKE 'port';" 

in /etc/mysql/my.cnf to see possible restrictions

It will show the list something like below:

Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1393/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1859/master tcp 0 0 123.189.192.64:7654 0.0.0.0:* LISTEN 2463/monit tcp 0 0 127.0.0.1:24135 0.0.0.0:* LISTEN 21450/memcached tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 16781/mysqld 

Use as root for all details. The -t option limits the output to TCP connections, -l for listening ports, -p lists the program name and -n shows the numeric version of the port instead of a named version.

In this way you can see the process name and the port.

Try only using -e ( —execute ) option:

$ mysql -u root -proot -e "SHOW GLOBAL VARIABLES LIKE 'PORT';" (8s 26ms) +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 

Replace root by your «username» and «password»

A simpler approach for some : If you just want to check if MySQL is on a certain port, you can use the following command in terminal. Tested on mac. 3306 is the default port.

mysql —host=127.0.0.1 —port=3306

If you successfully log in to the MySQL shell terminal, you’re good! This is the output that I get on a successful login.

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9559 Server version: 5.6.21 Homebrew Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 

Источник

How to change mysql port number in ubuntu

How can I can change port number of mysql from 3306 to my choice of number (1023) in Ubuntu 13.10? I tried by editing the port number in file: /etc/mysql/my.cnf . But after this change mysql doesn’t start. Please guide me so I can fix this.

5 Answers 5

There may be multiple files containing mysql configuration. Their full path may exists in file /etc/mysql/my.cnf by lines starting !includedir . For a sample, mine is:

!includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ 

So, after listing the second dir, I found a file named:

/etc/mysql/mysql.conf.d/mysqld.cnf 

So You have to change the port number in this file.

The best way for you is viewing the file:

and then changing port number in a file inside one of directories included my.cnf file.

For Ubuntu Desktop about version 18.04 , it was enough to edit the /etc/mysql/mysql.conf.d/mysqld.cnf file:

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log 

[mysqld] port = 3308 (or other number)  

+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | admin_port | 33062 | | large_files_support | ON | | mysqlx_port | 33060 | | mysqlx_port_open_timeout | 0 | | port | 3308 | | report_host | | | report_password | | | report_port | 3308 | | report_user | | | require_secure_transport | OFF | +--------------------------+-------+ 10 rows in set (0.02 sec) 

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock port=5123 old_passwords=1 bind = 10.10.29.66 key_buffer = 500M table_cache = 4000 sort_buffer_size = 3M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M max_connections = 400 query_cache_type = 1 query_cache_limit = 1M query_cache_size = 100M max_allowed_packet = 1M thread_cache_size = 8 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 local-infile=0 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick max_allowed_packet = 16M