6

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.

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.

  • It should probably be noted that the default communication method for mysql on localhost is a socket file, /var/lib/mysql/mysql.sock, not TCP/IP. – vidarlo Feb 3 '18 at 11:30
3

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.

3

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

  • I was getting Lost connection to MySQL server at 'reading initial communication packet', system error: 0 with HeidiSql after changing the default port in my.cnf. It turned out I hadn't edited the mysqld port (only the client one). Using netstat -tln really helped in the debugging process as it showed the port was still running on the default. – TryHarder Apr 29 '16 at 4:30
2

ALLAH There maybe 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.