Get 50% Discount Offer 26 Days

Recommended Services
Supported Scripts
WordPress
Hubspot
Joomla
Drupal
Wix
Shopify
Magento
Typeo3
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 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 is a powerful relational database management system (RDBMS) widely used for its performance and scalability. When set up on a Voxfor VPS, MySQL offers flexibility and customization options that are often not available on shared hosting.

Why Use VPS for Database Management?

Using MySQL on a VPS provides an isolated environment with dedicated resources and more control over security settings and performance. This makes VPS hosting suitable for applications that rely heavily on databases. 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 a 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
Connecting to Your VPS via SSH
  1. Enter your password when prompted.

Verifying MySQL Installation

Once connected, check if MySQL is installed:

mysql --version
Verifying MySQL Installation

Installing MySQL on Different Operating Systems

MySQL Installation on Ubuntu/Debian

Update the Package List:

sudo apt update
Installing MySQL on Different Operating Systems
  1. Install MySQL Server:
sudo apt install mysql-server
Install MySQL on VPS
  1. Start MySQL Service:
sudo systemctl start mysql
Start MySQL Service
  1. Enable MySQL to Start at Boot:
sudo systemctl enable mysql
Enable MySQL to Start at Boot
  1. Run the Security Script (recommended):
sudo mysql_secure_installation
Run the Security Script
Security Script Complete
  1. Verify Installation:
mysql --version
Verify Installation
  1. Check for the status by running:
sudo systemctl status mysql
Check for the status by running

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
  1. Enable MySQL to Start at Boot:
sudo systemctl enable mysqld
  1. Run the Security Script:
sudo mysql_secure_installation
  1. 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 is by using the SHOW DATABASES command.

Step-by-Step Guide to SHOW DATABASES

Log in to MySQL from your VPS terminal:

mysql -u root -p

Enter your MySQL root password.

Log in to MySQL from your VPS terminal

Then, Run:

SHOW DATABASES;
This command outputs a list of all databases on the MySQL server

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;
Advanced Techniques for Listing Databases

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_%';
Using Filters in Database Queries

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 graphical tool that can simplify MySQL management. If you prefer a GUI, MySQL Workbench offers a friendly interface.

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. Once connected, you can view all databases in the schema viewer.

Viewing Databases in MySQL Workbench

In Workbench, a sidebar displays available databases. This makes navigating and managing databases simple.

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

Log in to MySQL as the root user.

  1. Create a new user with restricted access:
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'password';
Creating a Limited Access User
  1. Grant limited permissions:
GRANT SELECT ON database_name.* TO 'limited_user'@'%';
Grant limited permissions
  1. Flush privileges:
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
Creating a Basic Script to List Databases
  1. Add the following code to the file:
#!/bin/bash
mysql -u root -p -e "SHOW DATABASES;"
Run this command to show databases
  1. Save and close the file.
  2. Make the script executable:
chmod +x list_databases.sh
Make the script executable
  1. Run the script:
./list_databases.sh

Scheduling Scripts with Cron Jobs

To automate the script with cron:

Open the cron tab editor:

crontab -e
Scheduling Scripts with Cron Jobs

Add a new job to run the script daily:

0 2 * * * /path_to_script/list_databases.sh
Add a new job to run the script daily

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 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
Run this command if MySQL Server Unavailability

Check server status and logs for further troubleshooting:

sudo systemctl status mysql
Check server status and logs for further troubleshooting

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 databases in MySQL 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. With regular practice, these techniques can help you manage your databases confidently and improve your overall MySQL experience on a VPS.

About the writer

Vinayak Baranwal Article Author

This article was written by Vinayak Baranwal, For more insightful content or collaboration opportunities, feel free to connect with Vinayak on LinkedIn through the provided link.

Leave a Reply

Your email address will not be published. Required fields are marked *

Lifetime Solutions:

VPS SSD

Lifetime Hosting

Lifetime Dedicated Servers