In a Client-Server architecture, two or more computers are connected together over a network to send and receive requests between one another.

A client-server DBMS comes in two parts. In the first part, the server usually resides on the machine as the database files and is responsible for all interactions with the database. The second part is the client; the client sends all database requests to the server which in turn processes the request and returns the results of the request back to the client.

MySQL client/server communication is not limited to environments where all computers run the same operating system.

Now, we let's set up a typical client-server architecture.

Requirements:

• 2 Ubuntu instances (One as a server and the other as the client)

For this tutorial, We'll be using an AWS EC2 instances.

STEPS:

1. Create and configure two Ubuntu 20.04 virtual servers. Name them accordingly:

Server 1 name - mysql server

Server 2 name - mysql client

1. On MySQL Ubuntu Server install MySQL Server software.

2. On linux server instance, install mysql server software

   sudo apt update
   

   sudo apt install mysql-server -y
   

3. configure mysql by running a security script

   sudo mysql_secure_installation
   

   follow the on screen prompts to set up password and login into mysql

   sudo mysql
   

4. Create a dedicated user project_user and grant privileges.

   CREATE USER 'project_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
   

5. Create a database named testDB

   CREATE DATABASE testDB;
   

6. Grant pivileges to user on the database we created.

   GRANT ALL PRIVILEGES ON testDB.* TO 'project_user'@'%';
   

   

Install MySQL client software on client server

1. On client linux instance, install mysql client software

   sudo apt update
   

   sudo apt install mysql-client -y
   

2. We need to update our inbound security rules on the server instance to accept only connections from our client server. Under the connection type we select MySQL/Aurora and for IP Address we use the client server's private IP addresss. This can be gotten by running ifconfig command on the client server.

Configure MySQL server to accept connections

1. Run the following command to edit mysql-server connection settings.

   sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
   

2. Search for bind-address and replace 127.0.0.1 with 0.0.0.0 save and exit
3. Restart mysql-daemon using

   sudo systemctl restart mysql
   

   Connect to MySQL server from MySQL client

4. We can connect by running

   sudo mysql -u project_user -p -h 172.31.4.13
   

5. Enter mysql server password when prompted
6. Run

   SHOW DATABASES;
   

   

If you get an output similar to the above image, you have successfully deployed a fully functional MySQL Client-Server set up.