Enable Connections to MySQL/MariaDB from Remote Machine

MySQL & MariaDB considers connections from the same local machine different than connections from a remote machine, even if using the same root user and password.  

When installing and configuring MySQL or MariaDB, the root account and password being setup is by default, only for connections from the same server..  

If you’re unable to establish a connection to the SQL server from a remote server (e.g. not the same server as SQL is installed on) and seeing Error Code 1130, this means SQL isn’t configured to allow remote connections via the specified user account. 

Step 1) Login via SSH to Variphy OVA Server

Step 2) Navigate to location of SQL Config file (.cnf file). The config file may be in one of several location:

•  cd /etc/mysql/
• cd /etc/mysql/mysql.conf.d/
• cd /etc/mysql/mariadb.conf.d/

Step 3) Open the .cnf config file with vi editor:  sudo vi FILENAME.cnf

Step 4) For MYSQL – Scroll to [mysqld] section and make sure ‘skip-networking’ is commented (or removed), comment out bind-address = 127.0.0.1 (Change to #bind-address = 127.0.0.1)

Step 4) For MariaDB – Scroll to [mysqld] section and Uncomment the following entries by removing the #.

• #skip-networking=0 (Change to skip-networking = 0)
• #skip-bind-address (Change to skip-bind-address)

Save and close file. 

***FOR SECURITY CONSIDER REVERTING THIS SETTING BACK ONCE YOUR REMOTE WORK IS COMPLETE*** Un-commment ‘bind-address = 127.0.0.1’ and re-add skip-networking (only if you deleted it in Step 4.)

Step 5) Restart the sql ervice:  sudo service mysql restart

Step 6) Connect to mysql server:  mysql -u root -p 

Step 7) Switch to ‘mysql’ database:  use mysql

Step 8) Create new user for remote server with the following SQL query: 

CREATE USER ‘newuser’@‘FQDN.local’ IDENTIFIED BY ‘password’;  

FQDN.local is the fully qualified domain name or IP address of remote/source server.

Step 9) Grant privileges to new user with the following SQL query :

GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@‘FQDN.local’ IDENTIFIED BY ‘PASSWORD’ WITH GRANT OPTION;

If you have issues connecting from remote server change ‘FQDN.local’ to ‘%’ (wildcard, all hosts)

Step 10) Reload all privileges with the following SQL query:  

FLUSH PRIVILEGES;

***FOR SECURITY CONSIDER REVERTING THIS SETTING BACK ONCE YOUR REMOTE WORK IS COMPLETE**** DROP USER ‘newuser’@‘FQDN.local’;