How to Setup Mysql Database for Remote Access

Here are some useful guidelines in setting up a mysql server for remote access in Ubuntu.

1. Install and configure mysql server.

   sudo apt-get update
   sudo apt-get install mysql-server
   mysql_secure_installation
   

   *Note in MySQL - it will ask to set the password but not in MariaDB
2. Bind MySQL to the public IP where it is hosted by editing the file MySQL: /etc/conf/my.cnf or MariaDB: /etc/mysql/mariadb.conf.d/50-server.conf, the cnf file is sometimes pointing to another file - make sure to check that. Search for the line with "bind-address" string. Set the value to your IP address or comment the bind-address line.
3. Make sure that your user has enough privilege to access the database remotely:

   create user 'lacus'@'localhost' identified by 'lacus';
   grant all privileges on *.* to 'lacus'@'localhost' <with grant option>;
   create user 'lacus'@'%' identified by 'lacus';
   grant all privileges on *.* to 'lacus'@'%' <with grant option>;
   

4. Open port: 3306 in the firewall:

   sudo ufw allow 3306/tcp
   sudo service ufw restart