Skip to content

Introduction

Storing structured data is an integral part of how most applications work. The more users an application has, the more data it needs to store. To ensure that storage is efficient and reliable, you need to understand how databases work, how to build them, and what administrative tasks are associated with databases. These are the topics we will cover in this module.

We will study databases using MySQL as an example — an open-source database management system. MySQL is very commonly used when a relational database is needed, as it is free and relatively easy to use.

Database management systems have a client-server architecture: the databases themselves are stored on the server, while SQL queries and other commands are executed by clients.

Server Installation

We will run the server part, i.e., the database management system itself, on a virtual machine running Ubuntu. Make sure your virtual machine has sufficient resources allocated (1 CPU core, 1 gigabyte of RAM, and 20 gigabytes of disk space) and that you can connect to the virtual machine via SSH.

To install the MySQL server, you need to run two commands on the virtual machine. The first updates the local list of available packages in the repositories, and the second installs the MySQL server:

sudo apt update
sudo apt install mysql-server

The MySQL server on Linux runs as a service called mysql. After installation, you can check its status:

systemctl status mysql

Connecting to the Server

From the virtual machine, you can connect to the database management system using the client that was installed automatically along with the server:

sudo mysql

The running client is an interactive program where you can execute SQL queries and MySQL commands. To exit it, run the exit command:

exit

Creating a New User

Now let's understand why we used this particular command to connect from the virtual machine. To do this, run the following query in the running mysql client:

SELECT user,plugin FROM mysql.User;

Where:

  • mysql is the name of the database that stores system information required for the database server to operate;
  • User is the name of the table containing users.

MySQL supports various user authentication mechanisms. By default, it has a root user with the auth_socket authentication mechanism. With this mechanism, MySQL uses the user under which the client is running. Since we started the client with sudo, i.e., under the root user, we connected on behalf of this user. MySQL verified that the user table contains a root user and allowed our client to connect to this server.

We can only use the root user with this authentication mechanism when connecting from the same virtual machine where the server is installed. To be able to connect from your computer, you need to create a separate user with a different authentication method:

​​CREATE USER 'mysqluser'@'ip' IDENTIFIED BY 'P@ssw0rd';

Where:

  • mysqluser is the name of the new user;
  • ip is the IP address from which the new user can connect. It can be a specific IP address, localhost (then the user can only connect from the virtual machine where the MySQL server is installed), or %, which means the user can connect to the server from any IP address.

Configuring Access Rights

Next, you need to configure rights for the new user. To do this, run the following command:

GRANT ALL PRIVILEGES ON *.* TO 'mysqluser'@'%';

These rights allow the user to perform all operations on all databases (like the root user).

Connecting to the Server with the New User

Close the client and try to connect with the user we just created:

exit
mysql -u mysqluser -p
show databases;

The client will ask you for a password — with this authentication mechanism, we will be able to connect to this database server from other computers.

bind-address

Before moving on to configuring clients, there is one more thing that needs to be fixed — bind-address. MySQL, like a web server, has a listener configuration that specifies the IP address on which the server will accept requests. This configuration is located in the file /etc/mysql/mysql.conf.d/mysqld.cnf.

Before making changes to the file, create a backup copy so that in case of any problem you can easily restore the database server, and only then edit the file:

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

By default, bind-address is set to 127.0.0.1. This means the server will only accept requests from clients on the same virtual machine. To allow connections to the server from any IP address, set bind-address 0.0.0.0. To apply these changes, you need to restart the mysql service:

sudo systemctl restart mysql

If the configuration contains no errors, the service will be running after the restart:

systemctl status mysql

If the service does not start after making changes to the file, check whether the changes were made correctly. If you cannot find the error, the backup copy will come in handy.

Client Installation

We will run the MySQL client part, which we will use to execute SQL queries, directly on your computer. To connect to the database, we will use MySQL Workbench — an integrated database development environment that allows you to execute SQL queries, create and manage databases, and perform other administrative tasks.

On macOS

First, download the installer. To choose the correct version, open the system information window (About This Mac). Here we are interested in the Chip: if you have Apple M1 or Apple M2, you need the ARM version, and if Intel — x86.

Registration is not required to download the installer.

Run the installer, follow the instructions, and after installation — launch the program and create a new connection:

  1. Choose a name for the connection, for example, mate-database-server.
  2. In the hostname field, enter the IP address of your virtual machine — the one used for SSH connection to the virtual machine.
  3. In the username field, enter the MySQL user name and password.
  4. Click the Test connection button. If all data was entered correctly, you should see a corresponding message.

To connect to the server, double-click the created connection. Then you will be able to perform the following operations:

  • write and execute SQL queries to the database server;
  • save query files to your computer, and edit existing SQL query files;
  • perform administrative tasks, such as user management.

On Windows

First, download the installer.

Registration is not required to download the installer.

Run the installer, follow the instructions, and after installation — launch the program and create a new connection:

  1. Choose a name for the connection, for example, mate-database-server.
  2. In the hostname field, enter the IP address of your virtual machine — the one used for SSH connection to the virtual machine.
  3. In the username field, enter the MySQL user name and password.
  4. Click the Test connection button. If all data was entered correctly, you should see a corresponding message.

To connect to the server, double-click the created connection. Then you will be able to perform the following operations:

  • write and execute SQL queries to the database server;
  • save query files to your computer, and edit existing SQL query files;
  • perform administrative tasks, such as user management.