Setup MySQL Database for Remote Access

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' ;
    create user 'lacus'@'%' identified by 'lacus';
    grant all privileges on *.* to 'lacus'@'%' ;
    
  4. Open port: 3306 in the firewall:
    sudo ufw allow 3306/tcp
    sudo service ufw restart
    

Leave a Reply