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.

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

Solution 2:

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

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

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