Mysql – Help configuring MySQL install for remote connections / root accounts understanding

MySQLSecurity

I installed mysql/phpmyadmin/apache on a linux box for internal network use only. I don't have the strongest understanding of MySQL. 

Hostname: server.domain.lan
IP: 192.168.0.54
  • After I installed MySQL I ran mysqladmin -u root password STRONGPASSWORD
  • I edited /etc/my.conf and set the bind-address to 192.168.0.54

I am trying to set up a system to run on my LAN that I can upload websites to that need to create databases.

Screenshot from phpMyAdmin of the Privileges menu:

settings

So my questions are:

  • Why are there 3 root accounts?
  • What do I need to do to configure MySQL to allow remote connections from the root account so I can create databases, etc?

(Sorry I'm kind of new to MySQL so I'm not certain that I'm even asking the correct questions…)

Best Answer

root@localhost allows a root user to connect to mysql locally from the DB server using the mysql socket file.

root@127.0.0.1 allows a root user to connect to mysql locally from the DB server using the TCP/IP protocol. (Trust me, you will be needing this one. MySQL has a nasty bug in it. The bug sometimes causes the mysql socket file to disappear rendering root@localhost useless. You will need to connect using root@127.0.0.1 if this ever happens to you)

root@server.domain.lan allows a root user to connect to mysql from server.domain.lan using DNS (Dynamic Naming Services)

You could create root@'%' but I highly recommend you do not.

I would also recommend using a different password for each root user.

Related Question