Jayesh Bhoot's Ghost Town

Connecting to MySQL running on NixOS on local network

Posted on in ,

I have a Linux workstation, and a Macbook. While I prefer to work on Linux, I also like the mobility around my house. Once, I needed to access a database instance running on the Linux workstation from my MacBook. My first instinct was to sell it all and buy a Linux-operated ThinkPad! After I placated myself, I came up with the following setup..

  1. The big picture
  2. Configure MySQL to listen to remote connections
  3. Configure a remote-enabled MySQL user
  4. Configure NixOS to open up MySQL port

The big picture

This setup aims to allow connections to a MySQL database hosted on my NixOS workstation, only on local network.

I needed to configure MySQL to listen to connections on network interfaces. I also needed a MySQL user who is authorised to connect from a host on local network. Then, I needed to configure NixOS to open up the port used by MySQL.

Configure MySQL to listen to remote connections

NixOS features a service to configure MySQL, including the ability to configure MySQL's my.cnf. So it took only a few lines to get MySQL up and running. 

# in /etc/nixos/configuration.nix
services.mysql = {
  enable = true;

  package = pkgs.mysql84;

  configFile = pkgs.writeText "my.cnf" ''
  [mysqld]
  skip-networking=OFF

  # listen for connections on all network interfaces, though
  # only one interface at firewall level will be opened up.
  bind-address=0.0.0.0
  '';
};

Configure a remote-enabled MySQL user

I wanted to allow only connections from local network.

A default command CREATE USER 'bhoot'; would create a user which could connect from any remote host.

MySQL follows a username@hostname convention for user accounts, where hostname can use a host_ip/netmask syntax to allow connections only from the local network. 


# as root MySQL user
mysql> CREATE USER 'bhoot'@'192.168.100.0/255.255.255.0' IDENTIFIED BY 'my_password';
mysql> GRANT ALL ON my_schema.* to 'bhoot'@'192.168.100.0/255.255.255.0';

Configure NixOS to open up MySQL port

NixOS enables a firewall by default.

I was under the (borrowed) impression that NixOS automatically opens up the ports for enabled services. But that wasn't the case, at least for MySQL.

Opening up the MySQL port was a matter of a single line: 

# in /etc/nixos/configuration.nix
networking.firewall.enable = true;
# Open MySQL port, only on the wireless network interface.
networking.firewall.interfaces.wlp0s20f3.allowedTCPPorts = [ 3306 ];

Then, rebuild NixOS. 

$ sudo nixos-rebuild switch --flake .