For a beginner, the safest VPS setup is straightforward: install MySQL from Ubuntu’s package, verify and harden the service, create a separate least-privilege account for each application, keep port 3306 private unless remote access is genuinely required, and operate backups as a tested recovery system rather than as occasional dump files.
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
What MySQL does on a VPS
MySQL is a database server: it stores structured application data, answers SQL queries, enforces permissions, and keeps data available to software running on your VPS. A VPS gives you operating-system control, but it also makes you responsible for updates, account security, network exposure, storage capacity, and recovery. MySQL’s Linux root account and MySQL’s own root account are different identities. The former can administer the server through Ubuntu; the latter is a database account whose permissions and authentication are managed by MySQL.
As an Amazon Associate I earn from qualifying purchases.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
MySQL identifies an account by both username and client host. Thus 'appuser'@'localhost' is a different account from 'appuser'@'203.0.113.10'. That host component is an important security control: an account intended for a local application should not automatically be usable from every network address.
Prepare the VPS before installation
- Use a supported Ubuntu Server release and log in through SSH with an administrative account that can run
sudo. - Keep a second SSH session open while changing firewall or service settings, so a mistake does not lock you out.
- Confirm that the VPS has enough free storage for the operating system, MySQL data, binary logs if enabled, and backup files. Database growth, not a generic VPS size, should determine capacity.
- Apply pending Ubuntu security updates before exposing any new service.
- Decide whether the application and MySQL will run on the same VPS. Local-only operation is the safer default and avoids opening 3306 at all.
Install MySQL on Ubuntu
- Install the distribution package. Run
sudo apt install mysql-server. Ubuntu’s package normally starts the service after installation. - Check the service. Use
sudo service mysql statusorsudo systemctl status mysql. A running service should show an active state rather than a failed one. - Inspect startup failures. If the service is not active, read recent messages with
sudo journalctl -u mysql. Look for configuration syntax errors, unavailable storage, permission problems, or a port already in use before attempting random changes. - Complete the initial hardening workflow. Run the Ubuntu/MySQL hardening utility, commonly
sudo mysql_secure_installation, and review every prompt. Remove anonymous accounts, prevent remote use of the administrative account where offered, remove the test database if it is not needed, and reload privilege information. - Confirm automatic startup. Ubuntu packages generally enable MySQL at boot. Verify with
sudo systemctl is-enabled mysql; enable it withsudo systemctl enable mysqlif your installation is not configured to start automatically.
Do not put a database password directly in a shell command or a script visible to other users. Use the interactive password prompt, a protected option file, or MySQL’s login-path mechanism with permissions that prevent other accounts from reading the secret.
#1 Best Overall
- Easily store and access 2TB to content on the go with the Seagate Portable Drive, a USB external hard drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Understand the two root accounts
Ubuntu’s operating-system root can read files, control services, and change firewall rules. MySQL’s root can perform database administration. They may use different authentication methods; on Ubuntu, local administrative access may be tied to the operating-system account through socket authentication. Test local administration with sudo mysql rather than assuming that a password-based login will work. Do not use the MySQL root account in application configuration.
Create a database and an application user
Connect locally as an administrator, then create the schema and a dedicated account. Replace the names and secret-handling method for your deployment:
Recommended Free Tools
sudo mysqlCREATE DATABASE appdb;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'use-a-secret-manager-or-prompt';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'localhost';Exit with EXIT;. The application should connect as appuser, not as MySQL root. The example grants the four data-manipulation privileges commonly needed by a running application, but the correct set depends on the software. An application that creates or alters tables during migrations may temporarily require DDL privileges such as CREATE or ALTER. A safer pattern is to use a separate migration account or grant those rights only for the migration window, then remove them from the permanent runtime account.
Rank #2
- Easily store and access 5TB of content on the go with the Seagate portable drive, a USB external hard Drive
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop
- To get set up, connect the portable hard drive to a computer for automatic recognition software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Choose the narrowest privilege scope
MySQL privileges can apply globally, to one database, to individual tables or columns, or to stored routines. Granting on appdb.* confines the account to one schema; granting on *.* covers every database on the server and is rarely appropriate for an application.
| Scope | Example | Use and risk |
|---|---|---|
| Global | ON *.* |
Server-wide operation; reserve for carefully controlled administrators. |
| Database | ON appdb.* |
Normal application boundary when one account owns one schema. |
| Table | ON appdb.orders |
Useful when a service must see only selected tables. |
| Column | Specific columns in a table | Can hide sensitive fields, but requires more maintenance. |
| Routine | Stored procedure or function privileges | Useful for controlled database APIs. |
Be especially cautious with FILE, administrative privileges, and GRANT OPTION. FILE can expose or write files accessible to the server process; administrative privileges can change server operation; GRANT OPTION lets an account delegate its privileges. MySQL’s documented principle is to grant an account only the privileges it needs.
Review an account with:
SHOW GRANTS FOR 'appuser'@'localhost';When an employee, service, or deployment is retired, remove its account or privileges deliberately rather than leaving credentials active indefinitely.
Rank #3
- Easily store and access 1TB to content on the go with the Seagate Portable Drive, a USB external hard drive.Specific uses: Personal
- Designed to work with Windows or Mac computers, this external hard drive makes backup a snap just drag and drop. Reformatting may be required for Mac
- To get set up, connect the portable hard drive to a computer for automatic recognition no software required
- This USB drive provides plug and play simplicity with the included 18 inch USB 3.0 cable
- The available storage capacity may vary.
Keep MySQL private unless remote access is required
Local application on the same VPS
If your web application runs on the same machine, configure MySQL to listen only on the local interface. A local account such as 'appuser'@'localhost' and a local connection avoid exposing 3306 to the internet. Do not open the port merely because a client tool cannot connect from your laptop; use an SSH tunnel for administration instead.
Remote administration or a separate application host
When a real remote client is required, change MySQL’s bind-address in the package-supported configuration under /etc/mysql/. Bind to a private interface or a specific address rather than all interfaces, then restart with sudo systemctl restart mysql.service. Create an account whose host restriction matches the known client address or private subnet; avoid a wildcard host unless you fully understand the exposure.
Ubuntu identifies ufw as its default firewall tool. Preserve SSH before enabling or changing rules:
Rank #4
- High capacity in a small enclosure – The small, lightweight design offers up to 6TB* capacity, making WD Elements portable hard drives the ideal companion for consumers on the go.
- Plug-and-play expandability
- Vast capacities up to 6TB[1] to store your photos, videos, music, important documents and more
- SuperSpeed USB 3.2 Gen 1 (5Gbps)
sudo ufw allow OpenSSH
sudo ufw status verboseA source-restricted rule can permit only a known administration host:
sudo ufw allow from 203.0.113.10 to any port 3306 proto tcpUse your actual fixed address or private network, not the documentation address above. If addresses change frequently, prefer an SSH tunnel or private network instead of broadening the firewall. Remote connections should use secure authentication and TLS appropriate to the environment. Limiting network reachability is not a substitute for least-privilege accounts; both controls are needed.
Back up MySQL as a recovery system
Backups address system crashes, hardware failures, accidental deletion, upgrades, migrations, and replica setup. Oracle’s MySQL documentation stresses that the purpose is to recover and be running again, not merely to possess a file. Plan what is backed up, how often it runs, where copies are stored, how long they are retained, and how restoration will be performed.
Best Value
- Plug-and-play expandability
- SuperSpeed USB 3.2 Gen 1 (5Gbps)
Choose a backup method
| Method | Best fit | Characteristics |
|---|---|---|
mysqldump |
Small databases and simple VPS deployments | Built-in logical export; produces editable SQL and is easy to inspect and restore, but can take longer on large datasets. |
| MySQL Shell Dump Utility | Filtered or programmatic logical backups | Supports more advanced logical-dump workflows than a basic single-command export. |
| Percona XtraBackup | Larger production databases where backup performance matters | Physical backup approach; operationally more involved and should be matched to your MySQL version and recovery design. |
Logical and physical backups solve different problems. A logical dump is portable and convenient for moving selected schemas. A physical backup can reduce restore time for a large instance but needs compatible storage, tooling, and procedures. VPS-provider snapshots can speed up whole-server recovery, but they should complement database-aware backups rather than replace them; verify what is included, whether snapshots are crash-consistent, and how long they are retained.
Make a basic logical backup and restore
- Choose an account with the dump privileges required by your schema and protect the destination directory.
- Export the database using an interactive password prompt:
mysqldump -u root -p appdb > appdb.sql. - Restrict the file so other local users cannot read it, and encrypt it before copying it to storage outside the VPS.
- Create the destination database if necessary, then restore with
mysql -u root -p appdb < appdb.sql. - Check that tables, row counts, users, application connections, and recent writes behave as expected.
Do not treat a successful dump command as proof of recoverability. A file can be incomplete, unreadable, encrypted with a lost key, or impossible to restore within your outage window.
Use separate copies and rehearse recovery
A practical VPS arrangement keeps a short-term local or attached snapshot for fast rollback, copies encrypted logical backups to separate storage, and periodically restores one into a disposable database or staging VPS. Retain more than one generation and keep at least one copy outside the VPS provider’s failure domain; removable media can help, but a single external drive is not an off-site strategy by itself. Choose a schedule and retention period from your recovery-point and recovery-time requirements rather than from a universal rule. Record the restore steps, credentials or key locations, required MySQL version, and the person responsible for declaring a recovery complete.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Manage configuration and routine maintenance
Keep custom settings in the package-supported MySQL configuration locations instead of editing generated files. Before an invasive change, back up /etc/mysql and the configured MySQL data directory, and note the current service status. After changing a setting that requires a restart, run sudo systemctl restart mysql.service, then immediately check sudo systemctl status mysql and sudo journalctl -u mysql.
Avoid copying tuning values from an unrelated VPS. Memory size, storage latency, query patterns, connection concurrency, indexes, and workload mix determine whether a setting helps or harms performance. Change one variable at a time, measure application behavior, and keep a rollback copy of the previous configuration. Monitor free disk space and database growth because a full volume can stop writes and prevent MySQL from starting cleanly.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Quick Recap
Common problems and fixes
| Symptom | Likely cause | First action |
|---|---|---|
| Service is inactive or failed | Configuration error, permissions, storage, or port conflict | Run sudo systemctl status mysql, then inspect sudo journalctl -u mysql. |
Access denied locally |
Wrong account host, authentication method, or password | Test with sudo mysql; run SHOW GRANTS for the exact user/host pair. |
| Remote client times out | MySQL bound locally, firewall blocked, or wrong route | Confirm the bind address, source-restricted firewall rule, private-network route, and service restart. |
| Remote client is refused | Nothing is listening on the requested address or port | Check service status and listener configuration; do not respond by opening 3306 to everyone. |
| Restore fails | Missing database, privileges, incompatible options, or damaged dump | Restore into a disposable environment, read the first SQL error, verify the dump file and required account privileges, and repeat from a known-good backup. |
| Disk fills unexpectedly | Database growth, logs, dumps, or snapshots consuming space | Measure each directory, move encrypted backups off-server, and expand or archive storage before MySQL reaches zero free space. |
A safe beginner operating checklist
- Install from Ubuntu’s package and verify that MySQL starts at boot.
- Complete initial account hardening and keep administrative access separate from application credentials.
- Create one account per application or service, scoped to the required database and privileges.
- Keep MySQL local to the VPS whenever possible; use an SSH tunnel for occasional remote administration.
- If remote access is unavoidable, constrain the bind address, MySQL host pattern, firewall source, and authentication security.
- Automate encrypted backups, store copies separately, protect keys, and retain multiple generations.
- Restore periodically into an isolated environment and measure whether the result meets your recovery target.
- Back up
/etc/mysqland the configured data directory before major configuration or version changes. - Use service status and journal logs as the first diagnostic tools instead of repeatedly changing settings.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
