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
- 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
- Enter your password when prompted.
Verifying MySQL 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
- Install MySQL Server:
sudo apt install mysql-server
- Start MySQL Service:
sudo systemctl start mysql
- Enable MySQL to Start at Boot:
sudo systemctl enable mysql
- Run the Security Script (recommended):
sudo mysql_secure_installation
- Verify Installation:
mysql --version
- 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
- 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
- Enable MySQL to Start at Boot:
sudo systemctl enable mysqld
- Run the Security Script:
sudo mysql_secure_installation
- 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.
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 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
- Download MySQL Workbench on your local machine.
- 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.
- 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.
- Create a new user with restricted access:
CREATE USER 'limited_user'@'%' IDENTIFIED BY 'password';
- Grant limited permissions:
GRANT SELECT ON database_name.* TO 'limited_user'@'%';
- 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
- Add the following code to the file:
#!/bin/bash
mysql -u root -p -e "SHOW DATABASES;"
- Save and close the file.
- Make the script executable:
chmod +x list_databases.sh
- Run the script:
./list_databases.sh
Scheduling Scripts with Cron Jobs
To automate the script with cron:
Open the cron tab 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 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 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
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.