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
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.cnfSo You have to change the port number in this file.
The best way for you is viewing the file:
/etc/my.cnfor
/etc/mysql/my.cnfand then changing port number in a file inside one of directories included my.cnf file.
For Ubuntu Desktop at 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.logTo:
[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.logFinally, 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.
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.cnfSet new port 5123:
port=5123Here 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 = 16MSave and close the file. Restart mysqld:
# service mysqld restartPlease note that once port changed, you need to update all your php, perl, python scripts including iptables scripts.
1In order to start MySQL or any other service on a port number below 1024, you need to start the service as the root user.
*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 ...
1
