How to Install a Database Server (MySQL/PostgreSQL)

How to Install a Database Server (MySQL/PostgreSQL)

Database servers such as MySQL and PostgreSQL are essential for storing and managing data for your applications. This guide covers the installation and basic configuration of both MySQL and PostgreSQL on your VPS.

Step 1: Access Your VPS via SSH

• Open an SSH client (e.g., Terminal for macOS/Linux or PuTTY for Windows).
• Connect to your VPS by entering:
  ssh root@your-server-ip
• Press Enter, then enter your password when prompted.

Installing MySQL

Step 2A: Update Your Package Manager

To ensure you’re installing the latest version of MySQL, first update the package list:

Step 3A: Install MySQL

• Install MySQL by running:
  sudo apt install mysql-server -y
• Once installed, start the MySQL service and enable it to start on boot:
  sudo systemctl start mysql sudo systemctl enable mysql

Step 4A: Secure MySQL Installation

To improve MySQL’s security, run the secure installation script:

• You’ll be prompted to set a root password and make security adjustments.
• Answer each prompt with your preferred security options.

Step 5A: Access the MySQL Command Line

• Log in to MySQL as root:
  sudo mysql -u root -p
• Enter the password you set during the secure installation process.

Step 6A: Create a Database and User (Optional)

• To create a new database, enter:

  CREATE DATABASE your_database_name;

• Create a new user and grant them privileges on the database:

  CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON your_database_name.* TO 'username'@'localhost'; FLUSH PRIVILEGES;

• Exit the MySQL shell:

  EXIT;

Installing PostgreSQL

Step 2B: Update Your Package Manager

As with MySQL, first update your package list:

Step 3B: Install PostgreSQL

• Install PostgreSQL by running:
  sudo apt install postgresql postgresql-contrib -y
• Start the PostgreSQL service and enable it to start at boot:
  sudo systemctl start postgresql sudo systemctl enable postgresql

Step 4B: Access the PostgreSQL Command Line

• Switch to the postgres user:
  sudo -i -u postgres
• Start the PostgreSQL interactive terminal:
  psql

Step 5B: Create a Database and User

• To create a new database, enter:

  CREATE DATABASE your_database_name;

• Create a user and grant them privileges on the new database:

  CREATE USER username WITH ENCRYPTED PASSWORD 'password'; GRANT ALL PRIVILEGES ON DATABASE your_database_name TO username;

• Exit the PostgreSQL interactive terminal:

  \q

• Switch back to the root user:

  exit

Step 6: Verify Installation

• MySQL: To confirm MySQL is running, you can check its status:
  sudo systemctl status mysql
• PostgreSQL: Similarly, to confirm PostgreSQL is running:
  sudo systemctl status postgresql

If either displays as “active (running),” your database server is up and ready for use.

Additional Tips

• Remote Connections: By default, MySQL and PostgreSQL only accept local connections. You may need to adjust configuration settings to allow remote access if required.
• Backups: It’s important to regularly back up your database to prevent data loss. Each database system provides built-in tools for exporting data.

Your VPS is now equipped with a database server! You can use either MySQL or PostgreSQL to support applications, websites, and more.