The Project | About Us | Contribute | Donations | License

HOME

In this section, we will cover the steps to configure a database server on a Linux system. We will use MySQL as our database server for this example, but the concepts can be applied to other database systems like PostgreSQL, MariaDB, etc.

Objectives

Install MySQL on a Linux system.
Configure MySQL for optimal performance and security.
Create and manage databases and users.
Perform basic database operations.

Prerequisites

Basic understanding of Linux command line.
A Linux system with sudo privileges.

Step 1: Installing MySQL

1.1 Update the Package Index

First, update the package index on your system to ensure you have the latest information about available packages.

sudo apt update

1.2 Install MySQL Server

Install the MySQL server package using the package manager.

sudo apt install mysql-server

1.3 Start MySQL Service

Ensure that the MySQL service is running.

sudo systemctl start mysql

1.4 Enable MySQL to Start on Boot

Enable the MySQL service to start automatically at boot time.

sudo systemctl enable mysql

Step 2: Securing MySQL

2.1 Run the Security Script

MySQL comes with a security script that can help you secure your installation. Run the following command:

sudo mysql_secure_installation

You will be prompted to configure various security options, such as setting the root password, removing anonymous users, disallowing remote root login, and removing test databases. Follow the prompts to complete the setup.

Step 3: Configuring MySQL

3.1 Edit MySQL Configuration File

The main configuration file for MySQL is located at /etc/mysql/mysql.conf.d/mysqld.cnf. Open this file in a text editor.

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

3.2 Adjust Configuration Settings

You can adjust various settings to optimize MySQL performance. For example, you can increase the maximum allowed packet size:

[mysqld]
max_allowed_packet = 64M

After making changes, save the file and exit the text editor.

3.3 Restart MySQL Service

Restart the MySQL service to apply the changes.

sudo systemctl restart mysql

Step 4: Creating and Managing Databases and Users

4.1 Access MySQL Shell

sudo mysql -u root -p

4.2 Create a New Database

Create a new database named example_db.

CREATE DATABASE example_db;

4.3 Create a New User

Create a new user named example_user with a password.

CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'password';

4.4 Grant Privileges

Grant all privileges on the example_db database to the example_user.

GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'localhost';

4.5 Flush Privileges

Flush the privileges to ensure that they are applied.

FLUSH PRIVILEGES;

4.6 Exit MySQL Shell

Exit the MySQL shell.

EXIT;

Step 5: Basic Database Operations

5.1 Connect to the Database

mysql -u example_user -p

5.2 Select the Database

Select the example_db database.

USE example_db;

5.3 Create a Table

Create a table named users.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

5.4 Insert Data

Insert a new record into the users table.

INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

5.5 Query Data

Query the data from the users table.

SELECT * FROM users;

5.6 Update Data

Update a record in the users table.

UPDATE users SET email = '[email protected]' WHERE username = 'john_doe';

5.7 Delete Data

Delete a record from the users table.

DELETE FROM users WHERE username = 'john_doe';

5.8 Exit MySQL Shell