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.
amc
6,9627 gold badges38 silver badges51 bronze badges
asked Jan 19, 2014 at 17:34
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:
/etc/my.cnf
or
/etc/mysql/my.cnf
and then changing port number in a file inside one of directories included my.cnf file.
Eliah Kagan
115k53 gold badges311 silver badges484 bronze badges
answered Feb 3, 2018 at 11:14
For Ubuntu Desktop about version 18.04, it was enough to edit the /etc/mysql/mysql.conf.d/mysqld.cnf file:
From:
[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
To:
[mysqld]
port = 3308 (or other number) <-------------------
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
Finally, it’s mandatory to restart the server by running: sudo systemctl restart mysql in the terminal.
You can confirm the new settings by running the command: SHOW VARIABLES LIKE '%port%'; in the MySQL Console (mysql>) which generates output something like:
+--------------------------+-------+
| 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)
As you can see, I now have the port value: 3308.
answered Jul 9, 2019 at 1:01
Manuel JordanManuel Jordan
1,1433 gold badges16 silver badges35 bronze badges
1
MySQL server and client uses a file called my.cnf. You need to open /etc/my.cnf (Global mysqld configuration file) to specify new port.
MySQL Change Default Port
Open /etc/my.cnf file:
# vi /etc/my.cnf
Set new port 5123:
port=5123
Here is is my sample /etc/my.cnf file:
[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
Save and close the file. Restart mysqld:
# service mysqld restart
Please note that once port changed, you need to update all your php, perl, python scripts including iptables scripts.
answered Jan 19, 2014 at 17:53
MaythuxMaythux
81.3k54 gold badges235 silver badges268 bronze badges
1
In order to start MySQL or any other service on a port number below 1024, you need to start the service as the root user.
answered Jan 19, 2014 at 19:00
BertBert
3,0851 gold badge13 silver badges12 bronze badges
*UPDATE» — See Bert’s answer below .. yes have done my.cnf three changes 2 for port and change user to «root» and yes it starts as port 1023 … netstat -tln shows 1023 as the listener .. kudos Bert …
JUST tried 1023 … and noted «failed to start» message … checked and 1023 is RESERVED
1023 TCP UDP Reserved[1] Official
You’ll have to use another number … soz …
Remember there are «two» places to change the mysql port number in the /etc/mysql/my.cnf
The First:
[Client]
port = 1234
The Second:
[mysqld]
port = 1234
then restart the service … hope this also helps … worked for me just now to test it …
answered Jan 19, 2014 at 17:57
A_nobodyA_nobody
1711 silver badge4 bronze badges
1
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.
[mysqld] port = 12345
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.
If You Appreciate What We Do Here On TecMint, You Should Consider:
TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.
If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.
We are thankful for your never ending support.
Following are the steps to change mysql server port :
All these task are to performed on specified server and client side.
Server Side
• Install MySQL server :
sudo aptget install mysqlserver5.6
• Configure MySQL server :
Password for MySQL during installation was set to “root” // you can use you own mysql password or change it
• Change port for MySQL server :
Go to config file of mysql server :
sudo nano /etc/mysql/my.cnf
#change socket port for client :
port = 3306 to port = 6606
#change mysqld port for server :
port =3306 to port =6606
#change bind address :
bindaddress = 127.0.0.1 to bindaddress =0.0.0.0
• Create database :
mysql> create database esa_project;
mysql> use esa_project;
mysql> create table SICSR (sid int(10) PRIMARY KEY, sfname varchar(200), slname varchar(200),city varchar(50),fees varchar(10));
• Insert into SICSR :
mysql>insert into SICSR values ('1','bhargav','amin','vadodara','500000');
• Grant privileges to client:
mysql>GRANT ALL ON esa_project.* TO sicsradmin@'10.10.24.32' IDENTIFIED BY 'mysql';
Client side
• Install MySQL client :
$sudo aptget install mysqlclient
• Try to access MySQL server at 10.10.24.36 :
$mysql u sicsradmin p h 10.10.24.36
Incase you get error such as :
1. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘10.10.24.36’ (111)
Change bindaddress in /etc/mysql/my.cnf file :
bindaddress = 127.0.0.1 to bindaddress =0.0.0.0
Open port 6606 in iptables if not opened already:
sudo iptables A INPUT i eth0 p tcp m tcp dport 6606 j ACCEPT
OR
You can also allow access to specific client by provide rules such as :
2. Write a rule to allow all the client from any port connect to MySQL server ip and port
for input if not given already:
sudo iptables A INPUT p tcp s 0/0 sport 1024:65535 d 10.10.24.36 dport 6606 m state state NEW,ESTABLISHED j ACCEPT
Write another rule to allow output from MySQL server to any client if not given already
sudo iptables A OUTPUT p tcp s 10.10.24.36 sport 6606 d 0/0 dport 1024:65535 m state state ESTABLISHED j ACCEPT
3. ERROR 1130 (HY000): Host '10.10.24.32‘ is not allowed to connect to this MySQL server
Check for privileges of client on MySQL server:
show grants for 'sicsradmin'@'10.10.24.32';
GRANT ALL ON esa_project.* TO sicsradmin@'10.10.24.32' IDENTIFIED BY 'mysql';
• Try to access MySQL server at 10.10.24.36 :
$mysql u sicsradmin p h 10.10.24.36
Note : If connection is successful you will be able to login into mysql