Here are some useful guidelines in setting up a mysql server for remote access in Ubuntu.
- 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
- 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.
- 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'
; create user 'lacus'@'%' identified by 'lacus'; grant all privileges on *.* to 'lacus'@'%' ;
- Open port: 3306 in the firewall:
sudo ufw allow 3306/tcp sudo service ufw restart