setting-up-the-mysql-database-server

Now we will set up the MySQL database server on a separate Ubuntu server and configure it to allow remote connections from the PHP web application.

1 Installing MySQL Server

1. Install MySQL on the MySQL server:

   sudo apt update
   sudo apt install mysql-server -y

2. Verify MySQL installation:

   sudo systemctl status mysql

This will confirm if MySQL is running.

2 Configuring MySQL to Accept Remote Connections

By default, MySQL listens for connections on 127.0.0.1 (localhost), meaning it will only accept local connections. To allow connections from the web server, we need to configure MySQL to listen on the MySQL server’s IP address.

1. Edit the MySQL configuration file to allow remote connections by modifying the bind-address to the server’s IP address:

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

2. Find the line:

bind-address = 127.0.0.1

And change it to:

bind-address = 192.168.85.139

This allows MySQL to listen for connections from remote servers (in this case, the web server). Replace 192.168.85.139 with your MySQL server’s actual IP address.

3. Restart MySQL to apply the changes:

   sudo systemctl restart mysql

3 Creating the Database and Tables

Log in to MySQL as the root user and create the required database (todo_db) and tables (users and tasks):

1. Login to MySQL as root:

   mysql -u root -p

2. Create the todo_db database:

   CREATE DATABASE todo_db;

3. Create the users table:

   CREATE TABLE todo_db.users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(255) NOT NULL UNIQUE,
       password VARCHAR(255) NOT NULL
   );

4. Create the tasks table:

   CREATE TABLE todo_db.tasks (
       id INT AUTO_INCREMENT PRIMARY KEY,
       user_id INT NOT NULL,
       task TEXT NOT NULL,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       status ENUM('ongoing', 'done') DEFAULT 'ongoing',
       FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
   );

4 Creating a MySQL User and Granting Permissions

We will create a user (todo_user) and grant them access to the todo_db database:

CREATE USER 'todo_user'@'192.168.85.141' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON todo_db.* TO 'todo_user'@'192.168.85.141';
FLUSH PRIVILEGES;