Skip to main content

Database Setup

This guide will walk you through setting up the MySQL database for the AIDBQuery on the Linux machine. MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. It is intended for mission-critical, heavy-load production systems and mass-deployed software.

Prerequisites​

  • Ubuntu 20.04 LTS

Guides​

caution

Expose port 3306 to allow remote connections to the MySQL server.

Step 1:

Update Package Index: First, update your package index to ensure you're getting the latest versions of packages.

sudo apt update    # For Debian/Ubuntu

Step 2:

Install MySQL Server: Use the package manager to install the MySQL server package.

sudo apt install mysql-server    # For Debian/Ubuntu

Step 3:

Start MySQL Service: After installation, start the MySQL service.

sudo systemctl start mysql    # For systems using systemd

You can check the status of the MySQL service using the following command:

sudo systemctl status mysql    # For systems using systemd

Output:

root@namespaceit:~/websites$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2024-02-22 05:00:25 CST; 1 months 25 days ago
   Main PID: 563 (mysqld)
     Status: "Server is operational"
      Tasks: 46 (limit: 19138)
     Memory: 257.8M
     CGroup: /system.slice/mysql.service
             └─563 /usr/sbin/mysqld

Warning: journal has been rotated since unit was started, output may be incomplete.

Secure MySQL Installation​

Run the MySQL security script to secure the installation.

sudo mysql_secure_installation

The screenshot below shows the different questions asked by MySQL. Fill in and adjust based on your needs:

MySQL Secure Installation

Create new user & database​

Drop database user if the user already exists. Please login with the root user to perform the following operation.

  • Login as root user
sudo mysql -u root -p
  • Drop user if exist(optional)
DROP USER '<username>'@'localhost';
  • Drop database if exist(optional)
DROP DATABASE <database_name>;
  • Create database user
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
  • Create database
CREATE DATABASE <database_name>;
  • Grant privileges to the user
GRANT ALL PRIVILEGES ON <database_name>.\* TO '<username>'@'localhost';
  • Flush privileges (required to apply the changes)
FLUSH PRIVILEGES;

Collect Database Credentials​

Exit MySQL: Collect the database name, username, and password for the database connection. You will need these details to connect to the database from the application. This step is not recommended, you can also use remote database for your project. It is totally up to you.