How to change mysql port number in ubuntu

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

*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 ...