Ultimate Guide to MySQL on VPS: Installation Steps, Database Control, and Advanced Tips

Managing MySQL on VPS is crucial for users ranging from web developers to system administrators. Knowing how to view a complete list of databases in MySQL on VPS helps you organize, audit, and manage your databases effectively. This guide walks through multiple ways to list all databases, covering both basic and advanced techniques tailored for VPS environments.

1. Introduction to MySQL on VPS

MySQL on VPS is a versatile relational database management system (RDBMS) known for its excellent performance and scalability. When hosted on a Voxfor VPS, it provides greater flexibility and customization compared to shared hosting solutions.

Why Use VPS for Database Management?

Using MySQL on VPS provides an isolated environment with dedicated resources and more control over security settings and performance. This characteristic makes VPS hosting ideal for database-intensive applications. This guide explores several ways to list all databases in MySQL on a VPS, including command-line operations, advanced SQL queries, and graphical tools.

2. Getting Started: Accessing Your VPS

To work with MySQL on VPS, you’ll first need to access your server and verify MySQL installation.

Connecting to Your VPS via SSH

1. Open a terminal (macOS, Linux) or Command Prompt/PuTTY (Windows) on your local machine.

Enter the SSH command with your VPS’s IP address:

ssh user@your_vps_ip

2. Enter your password when prompted.

Verifying MySQL on VPS Installation

Once connected, check if MySQL is installed:

mysql --version

Installing MySQL on Different Operating Systems

MySQL Installation on Ubuntu/Debian

Update the Package List:

sudo apt update

1. Install MySQL Server:

sudo apt install mysql-server

2. Start MySQL Service:

sudo systemctl start mysql

3. Enable MySQL to Start at Boot:

sudo systemctl enable mysql

4. Run the Security Script (recommended):

sudo mysql_secure_installation

5. Verify Installation:

mysql --version

6. Check for the status by running:

sudo systemctl status mysql

Note: This guide assumes MySQL is installed on Ubuntu. If using other operating systems, see the sections below.

MySQL Installation on CentOS/RHEL

Update the Package Repository:

sudo yum update       # For CentOS/RHEL 7 and earlier
sudo dnf update       # For CentOS/RHEL 8 and later

1. Install MySQL Server:
   • For CentOS 7, install MySQL directly:

sudo yum install mysql-server

• For CentOS/RHEL 8, install using the MySQL community repository:

sudo dnf install @mysql

• Start MySQL Service:

sudo systemctl start mysqld

2. Enable MySQL to Start at Boot:

sudo systemctl enable mysqld

3. Run the Security Script:

sudo mysql_secure_installation

4. Verify Installation:

mysql --version

You’re now ready to access MySQL and start listing databases.

3. Listing All Databases: The Basic Commands

The simplest way to list databases in MySQL on VPS is by using the SHOW DATABASES command.

Step-by-Step Guide to SHOW DATABASES

Log in to MySQL on VPS from your terminal:

mysql -u root -p

Enter your MySQL root password.

Then, Run:

SHOW DATABASES;

This command outputs a list of all databases on the MySQL server.

Troubleshooting SHOW DATABASES

If permission errors occur, it may be due to limited user access. Logging in as the root user or an admin with full permissions should resolve this.

4. Advanced Techniques for Listing Databases

For a deeper look into your databases, you can query specific tables within MySQL’s information_schema.

Using INFORMATION_SCHEMA for Database Insights

The INFORMATION_SCHEMA database contains metadata about all databases on the server:

SELECT schema_name AS Database
FROM information_schema.schemata;

This query provides a list similar to SHOW DATABASES, with options for customization.

Using Filters in Database Queries

You can filter results, for example, to list databases with a specific prefix:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'test_%';

This query shows only databases starting with test_, allowing you to focus on specific databases.

5. Using MySQL Workbench for Database Management

MySQL Workbench is a user-friendly graphical tool designed to make managing MySQL databases easier. For those who prefer a GUI, it provides an intuitive interface for efficient database management.

Setting Up MySQL Workbench on a VPS

1. Download MySQL Workbench on your local machine.
2. Configure an SSH connection to your VPS:
   • Go to Database > Connect to Database.
   • Select Standard TCP/IP over SSH.
   • Enter your SSH details and MySQL credentials.
3. Now, you can view all databases in the schema viewer.

Viewing Databases in MySQL Workbench

A sidebar in Workbench displays the available database. Such a feature simplifies database navigation and management.

6. Securing Database Access on a VPS

Securing MySQL databases on a VPS is critical for data safety. Since the root user has extensive privileges, it’s wise to create restricted users for specific tasks.

Creating a Limited Access User

Sign in to MySQL using the root user account.

1. Create a new user with restricted access:

CREATE USER 'limited_user'@'%' IDENTIFIED BY 'password';

2. Grant limited permissions:

GRANT SELECT ON database_name.* TO 'limited_user'@'%';

3. Flush privileges:

FLUSH PRIVILEGES;

Using restricted users helps reduce accidental changes and increase security.

7. Automating Database Listing and Management

For regular tasks, such as listing databases, automation can simplify management.

Creating a Basic Script to List Databases

Open a new file, for example, list_databases.sh:

nano list_databases.sh

1. Add the following code to the file:

#!/bin/bash
mysql -u root -p -e "SHOW DATABASES;"

2. Save and close the file.
3. Make the script executable:

chmod +x list_databases.sh

4. Run the script:

./list_databases.sh

Scheduling Scripts with Cron Jobs

To automate the script with cron:

Open the crontab editor:

crontab -e

Add a new job to run the script daily:

0 2 * * * /path_to_script/list_databases.sh

This setup will run the database listing script daily at 2 AM.

8. Troubleshooting Common Issues

When managing MySQL databases on a VPS, you may face some common issues.

Permission and Access Errors

For errors like Access denied for the user, verify:

• You’re using the correct username and password.

The user has proper permissions:

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

Network and Connection Issues

If remote connection issues arise:

• Confirm MySQL is listening on the correct IP in my.cnf.
• Check if your firewall allows MySQL’s default port, 3306.

MySQL Server Unavailability

If MySQL is down, start it with:

sudo systemctl start mysql

Check server status and logs for further troubleshooting:

sudo systemctl status mysql

9. Best Practices for VPS Database Management

To keep databases safe and efficient, follow these best practices:

Regular Backups

Backups prevent data loss. Automate backups with mysqldump:

mysqldump -u root -p --all-databases > /path_to_backup/all_databases.sql

Updating MySQL and Server Security

Regularly update MySQL and your server for the latest security patches. Use strong passwords and review access controls.

Monitoring Database Activity

Monitoring tools can track MySQL performance and resource usage, helping you identify issues before they impact applications.

Conclusion

Listing all MySQL databases on a VPS is a fundamental skill, beneficial for both database administration and development. From the basic SHOW DATABASES command to advanced queries and automation, this guide has provided several methods suited for a VPS environment. These techniques can be used with regular practice and should help you manage your databases confidently and improve your overall MySQL experience on a VPS.

About the writer

Vinayak Baranwal wrote this article. Use the provided link to connect with Vinayak on LinkedIn for more insightful content or collaboration opportunities.