Setting up Mysql server for remote connection on linux

Lets see how to install and setup Mysql server on a linux machine for remote access

Mysql remote connection

Pre requisites: A remote linux server.

Table of contents:
- Installing Mysql
- Setting up mysql for remote access
- Connecting from mysql workbench

Installing Mysql

sudo apt update && apt upgrade

Updates the system packages.

sudo apt install mysql-server 

Installs mysql on the system.

Setting up mysql for remote access

🟡 Go to /etc/mysql/mysql.conf.d/ and edit mysqld.conf file.

Change the binding address from 127.0.0.1 to 0.0.0.0 and restart the mysql service.

sudo systemctl restart mysql

🟡 Enter mysql command line to create user and grant access

sudo mysql
create user 'user'@'localhost' identifed by 'Password';
grant all on 'database' to 'user'@'Password';
flush privileges;

‘user’@’localhost’ can only access the mysql from localhost. You can add a specific IP from where you want to access. OR add ‘%’ in place of host to access mysql from anywhere.

create user 'admin'@'%' identified by 'P@$$w0rd';

The above line will create a user with name ‘admin’ and password ‘P@$$w0rd’ with access rights from anywhere.

You can specify access rights to a particular database or table or to all databases.

grant all on 'test_database' to 'admin'@'P@$$w0rd'; 

Grants access to user ‘admin’ with password ‘P@$$w0rd’ to database ‘test_database’.

grant all on 'test_database.test_table' to 'admin'@'P@$$w0rd'; 

Grants access to user ‘admin’ with password ‘P@$$w0rd’ to table ‘test_table’ in ‘test_database’

grant all on *.* to 'admin'@'P@$$w0rd';

Grants access to user ‘admin’ with password ‘P@$$w0rd’ to all databases.

Connecting from mysql workbench

connection popup

Fill the remote server IP, database username and password. You will get a success message on succesful connection.

Lets create a database in the remote server and check whether its being reflected in workbench or not.

sudo mysql
create database test_database;

Now refresh the schemas in workbench. You should see the newly created database.

Before and after creating database

Hope this content was of some use.

Thank yourself for learning something new

Stay Curious… Stay Creative…