How to configure MySQL on same machine with different port?

Question

How to configure two different port for MySQL on same machine? I know that default port is 3306. But I also want to configure 3307 and 3308. Is it possible?

One bonus question with current one ;)

Can we assign Different Ports to Different Databases and also can assign/create Credentials respectively?

score 10 · Answer 1 · answered Sep 30 '13 at 13:32

10

You can use the --port=port_num option. Have a look here for more information on how to configure multiple mysql instances.

answered Sep 30 '13 at 13:32

Ivaylo Strandjev

69,226
18
123
176

I appreciate your quick response. Can you provide sample config settings? Just for better understanding? – Smile Sep 30 '13 at 13:35
Not really - I don't have such at my disposal right now. I will have to look through the document but I encourage you to do so on your own, – Ivaylo Strandjev Sep 30 '13 at 13:36
I will look and if have any query will ask. – Smile Sep 30 '13 at 13:37

score 7 · Answer 2 · answered Sep 30 '13 at 13:37

7

You can launch several instance of mysql :

mysqld_safe --defaults-file=/path/to/my.cnf1
mysqld_safe --defaults-file=/path/to/my.cnf2
mysqld_safe --defaults-file=/path/to/my.cnf3

and change the port parameter in the [mysqld] section of each my.cnf.

If you want to have only one mysqld process/database you can use port redirection (with linux) :

iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 3307 -j REDIRECT --to-port 3306
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 3308 -j REDIRECT --to-port 3306

answered Sep 30 '13 at 13:37

Fabrice Jammes

2,275
1
26
39

Can I assign two different ports (3307 and 3308) to two different databases (dba and dbb)? – Smile Sep 30 '13 at 13:39
In order to do that, you need to run 2 different mysqld instances, create `dba` in the first one and `dbb` in the second one. – Fabrice Jammes Nov 11 '20 at 21:35

score 5 · Answer 3 · answered Sep 30 '13 at 13:42

You can copy /etc/mysql/my.cnf, change the port in it and change the pathes to data dirs as well, because i'm pretty sure You can't have more than 1 instance of mysql serving from the same directories.

Check http://dev.mysql.com/doc/refman/5.1/en/multiple-servers.html.

ex :

cp /etc/mysql/my.cnf /etc/mysql/my-3307.cnf
//edit my-3307.cnf, for example
port = 3307
basedir = /var/lib/mysql-3307
datadir = /var/lib/mysql-3307
//end-edit
mysql_upgrade --defaults-file=/etc/mysql/my-3307.cnf #checks the syntax and creates the dirs you need.
#start mysqld
mysqld --defaults-file=/etc/mysql/my-3307.cnf

Nicely provided info :) I appreciated it – Smile Sep 30 '13 at 13:48 — Smile, Sep 30 '13 at 13:48

the · Answer 4 · 2014-10-07T22:17:21.120

1

mysqld_multi is by far the best way to handle different instances of mysql.

Some more useful tips:

mysqld_multi --example
check if apparmor isn't keeping mysql from accessing /var/lib/mysql2 if you get weird permission errors.

edited Oct 07 '14 at 22:17

answered Jan 02 '14 at 16:33

the

21,007
11
68
101

score 0 · Answer 5 · answered Mar 05 '16 at 05:49

0

I'm using CentOS. Not is too simple,

Edit file /etc/my.cnf, Search and change or add line: port=port_number.
semanage port -a -t mysqld_port_t -p tcp port_number
Restart MySQL Server. service mysqld restart

answered Mar 05 '16 at 05:49

Rodrigo Prazim

788
7
14

How to configure MySQL on same machine with different port?

5 Answers5