MySQL Basics for Beginners Managing Databases on Your VPS

This guide explains the core MySQL commands and concepts you need to manage databases on your own VPS, covering installation, user permissions, backups, and common troubleshooting. You will learn the essential SQL statements and practical tips to keep your data safe and your server running smoothly.

Why Learn MySQL for Your VPS

If you run a website, application, or any service on a virtual private server (VPS), you almost certainly need a database. MySQL is one of the most popular open-source relational database management systems in the world. It stores your data in structured tables, lets you query it with SQL (Structured Query Language), and is the backbone of countless content management systems, e-commerce platforms, and custom applications. Learning the basics of MySQL administration is a fundamental skill for anyone managing their own server, because it gives you direct control over your data—without relying on a shared hosting provider’s control panel.

Installing MySQL on Your VPS

The installation process depends on your operating system. On Ubuntu or Debian, the package manager makes it straightforward:

1. Update your package index: sudo apt update
2. Install the MySQL server package: sudo apt install mysql-server
3. Run the security script to set a root password and remove insecure defaults: sudo mysql_secure_installation

On CentOS or AlmaLinux, you would use sudo yum install mysql-server or sudo dnf install mysql-server, then start the service with sudo systemctl start mysqld. After installation, check that the service is running with sudo systemctl status mysql (or mysqld on some distributions). The default root account often uses a Unix socket authentication on Debian-based systems, so you may need to log in with sudo mysql instead of a password.

Connecting to the MySQL Shell

Once installed, you interact with MySQL through its command-line client. To connect as root, use:

sudo mysql -u root -p

You will be prompted for the root password (or you might connect without one if using socket auth). The -u flag specifies the username, and -p tells the client to ask for a password. Once connected, your prompt changes to mysql>, and you can type SQL commands. To exit, type EXIT; or press Ctrl+D.

Creating and Managing Databases and Tables

The first thing you’ll do is create a database. The basic command is:

CREATE DATABASE myapp;

You can then switch to that database with USE myapp;. Inside a database, you create tables that define the structure of your data. For example, to store users:

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

This creates a table with four columns: an auto-incrementing integer primary key, a name string, a unique email, and a timestamp that defaults to the current time. To see the structure of a table, use DESCRIBE users;. To add, modify, or remove columns later, use ALTER TABLE commands, like ALTER TABLE users ADD phone VARCHAR(20);.

Working with Data: CRUD Operations

The four essential operations are Create, Read, Update, and Delete (CRUD). Here they are in SQL:

• Insert (Create): INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
• Select (Read): SELECT * FROM users WHERE name = 'Alice';
• Update: UPDATE users SET email = 'alice@newdomain.com' WHERE id = 1;
• Delete: DELETE FROM users WHERE id = 1;

Always use a WHERE clause in UPDATE and DELETE statements to avoid affecting all rows. For example, forgetting the WHERE in DELETE FROM users; will wipe the entire table.

Managing Users and Permissions

MySQL has its own user system separate from your Linux users. You should never use the root account for your application. Instead, create a dedicated user with only the privileges it needs. To create a user and grant access to a specific database:

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassword!123';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

The @'localhost' part restricts the user to connecting from the local machine. If your application needs to connect from another server, you would use that server’s IP address or '%' for any host (not recommended for security). To see current users, query the mysql.user table: SELECT User, Host FROM mysql.user;. To revoke privileges, use REVOKE ALL PRIVILEGES ON myapp.* FROM 'appuser'@'localhost';.

Backing Up and Restoring Databases

Regular backups are non-negotiable. The mysqldump utility creates a SQL file that can recreate your database. To back up a single database:

mysqldump -u root -p myapp > myapp_backup.sql

To restore it, you can pipe the file back into MySQL:

mysql -u root -p myapp < myapp_backup.sql

For large databases, consider using --single-transaction to avoid locking tables during the dump. You can also automate backups with a cron job that runs mysqldump and compresses the output, like mysqldump myapp | gzip > /backups/myapp_$(date +%F).sql.gz. Keep backups off the same machine to protect against disk failure.

Common Problems and How to Fix Them

New administrators often run into a few recurring issues:

• Access denied for user: Usually means the username, host, or password is wrong. Check the user exists and is allowed to connect from your host.
• Can’t connect via TCP/IP: MySQL may be listening only on localhost. Check the bind-address in the configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf on Debian) and ensure it’s set to 0.0.0.0 if you need remote access.
• Table doesn’t exist: You might be in the wrong database. Use SELECT DATABASE(); to check, and USE to switch.
• Lost root password: You can reset it by starting MySQL with --skip-grant-tables, but this is a security risk and should be done carefully.

Frequently Asked Questions

What is the difference between MySQL and MariaDB?

MariaDB is a fork of MySQL, created when MySQL was acquired by Oracle. It is fully compatible in most respects, uses the same commands, and is often the default on many Linux distributions. If you follow MySQL tutorials, they will work on MariaDB with very few exceptions.

How do I change a user’s password?

Use the ALTER USER command: ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewPassword!123';. Then run FLUSH PRIVILEGES; to apply the change immediately.

Why can’t I connect to MySQL from my remote application?

First, ensure MySQL is listening on all interfaces by setting bind-address = 0.0.0.0 in the config file. Then, create a user with a host matching your application server’s IP, and make sure your firewall (like UFW or firewalld) allows traffic on port 3306. Also verify that your hosting provider’s network firewall isn’t blocking the port.

How do I see all databases on my server?

Run SHOW DATABASES; in the MySQL shell. This lists all databases you have permission to view. To see all tables in the current database, use SHOW TABLES;.

What is the safest way to delete a database?

First, back it up with mysqldump. Then, use DROP DATABASE myapp; to remove it permanently. This command cannot be undone, so double-check the name before running it.

Related

• 750 kW & Megawatt EV Charging: Can Today’s Cars Use It?
• Relationship Vocabulary Words: 50+ Terms Explained
• How to Navigate a Workbook in Excel