How to Set Up a MySQL Database on Your VPS

How to Set Up a MySQL Database on Your VPS

MySQL is a widely used database management system that allows you to store and manage data effectively. Follow these steps to set up MySQL on your VPS.

Step 1: Update Your VPS Packages

• Log in to your VPS using SSH with your root or sudo user credentials:

  ssh username@your-vps-ip

• Update your package lists to ensure you have the latest software:

  sudo apt update sudo apt upgrade -y

Step 2: Install MySQL Server

• Install the MySQL server package by running:

  sudo apt install mysql-server -y

• Once the installation is complete, ensure that the MySQL service is running:

  sudo systemctl start mysql sudo systemctl enable mysql

Step 3: Secure Your MySQL Installation

• Run the security script to enhance MySQL security:

  sudo mysql_secure_installation

• Follow the prompts to:

  • Set a strong root password.
  • Remove anonymous users.
  • Disallow remote root login (optional for security).
  • Remove test databases.
  • Reload privilege tables.

Step 4: Access the MySQL Shell

• Log in to the MySQL shell using the root user:

  sudo mysql -u root -p

• Enter the root password you created during the secure installation step.

Step 5: Create a New MySQL Database

• Inside the MySQL shell, create a new database with this command:

  CREATE DATABASE database_name;

  Replace database_name with your desired database name.

• Verify that the database was created:

  SHOW DATABASES;

Step 6: Create a New MySQL User

• Create a new user and set a password:

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

  Replace username with your desired username and password with a strong password.

• Grant the new user privileges to the database:

  GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

• Apply the changes:

  FLUSH PRIVILEGES;

Step 7: Test Your MySQL Database Connection

• Exit the MySQL shell:

  EXIT;

• Test the new user’s access by logging in with the new credentials:

  mysql -u username -p

• Once logged in, select the database:

  USE database_name;

• Verify access by running a simple query:

  SHOW TABLES;

Step 8: Set Up Remote Access (Optional)

• If you need to allow remote access to the database, edit the MySQL configuration file:

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

• Locate the line:

  bind-address = 127.0.0.1

  Change it to:

  bind-address = 0.0.0.0

• Restart MySQL to apply the changes:

  sudo systemctl restart mysql

• Add a user with remote access privileges:

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

Step 9: Regular Maintenance

• Keep MySQL updated by running regular system updates.
• Monitor database performance and clean up unused data to optimize storage and efficiency.