MySQL is straightforward to install, but the default installation leaves a few things in a state you wouldn't want in production. The root account has no password on some configurations, the anonymous user exists, and the test database is accessible to everyone.
Running mysql_secure_installation after installation fixes most of this, but there are a few additional steps — creating application-specific users with limited permissions, configuring the binary log for point-in-time recovery, and setting up the slow query log — that are worth doing while you're in there.
This guide covers the full setup: installation, security hardening, user and permission management, basic performance tuning, and preparing for remote access.
I run MySQL on Tencent Cloud Lighthouse with Ubuntu 22.04. The 4 GB RAM plan handles MySQL alongside your application comfortably. Two features that matter specifically for database hosting: Lighthouse's snapshot feature lets you take a full server backup (including all MySQL data) in seconds — useful before major schema changes — and CBS cloud disk expansion lets you grow database storage as your data grows without migrating servers. The console-level firewall also makes it easy to keep MySQL only accessible on localhost without complex firewall rules.
- Key Takeaways
A MySQL server that is:
sudo apt update
sudo apt install -y mysql-server
# Start MySQL and enable on boot
sudo systemctl start mysql
sudo systemctl enable mysql
# Verify it's running
sudo systemctl status mysql
Check the MySQL version:
mysql --version
# mysql Ver 8.0.x Distrib 8.0.x, for Linux (x86_64)
MySQL ships with a security hardening script that addresses the most common vulnerabilities:
sudo mysql_secure_installation
Work through each prompt:
| Prompt | Recommended answer |
|---|---|
| Set up VALIDATE PASSWORD component? | Y — enforces password strength |
| Password validation policy (0/1/2) | 1 (MEDIUM) for most setups; 2 (STRONG) for sensitive data |
| Change root password? | Y if you want a password for root (see gotcha below) |
| Remove anonymous users? | Y — anonymous access should never exist in production |
| Disallow root login remotely? | Y — root should only connect from localhost |
| Remove test database? | Y — no reason to keep it |
| Reload privilege tables? | Y — applies changes immediately |
On Ubuntu, MySQL root uses the auth_socket plugin by default — it authenticates by your OS user rather than a password:
# Connect as root using sudo
sudo mysql
# You should see the MySQL prompt:
# mysql>
# Check current users
SELECT user, host, plugin FROM mysql.user;
# Exit
EXIT;
Never use the root account for your applications. Create a dedicated database and user for each app:
sudo mysql
-- Create a database for your application
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Create a user with a strong password
-- 'localhost' means this user can only connect from the same server
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'use_a_strong_unique_password';
-- Grant only what's needed
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
ON myapp_db.* TO 'myapp_user'@'localhost';
-- Apply changes
FLUSH PRIVILEGES;
-- Verify
SHOW GRANTS FOR 'myapp_user'@'localhost';
EXIT;
Test the new user:
mysql -u myapp_user -p myapp_db
# Enter password
# Should connect to myapp_db
# EXIT; to quit
WordPress needs slightly different permissions:
CREATE DATABASE wordpress_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';
FLUSH PRIVILEGES;
Edit the MySQL configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add or update these settings in the [mysqld] section:
[mysqld]
# Bind to localhost only (don't listen on external interfaces)
# Remove this line if you need remote connections
bind-address = 127.0.0.1
# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# InnoDB settings (for typical web apps)
innodb_buffer_pool_size = 256M # Set to ~50-70% of available RAM
# For 4 GB server: 2G
# For 2 GB server: 512M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2 # Better performance (slight durability tradeoff)
# Connection settings
max_connections = 100
wait_timeout = 600
interactive_timeout = 600
# Query cache (disabled in MySQL 8 — it's been removed)
# query_cache_type = 0 # Not needed for MySQL 8
# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # Log queries taking more than 2 seconds
Apply changes:
sudo systemctl restart mysql
By default, MySQL only accepts local connections (bind-address = 127.0.0.1). If you need remote access (e.g., from a database management tool on your laptop), configure it securely.
Don't open MySQL's port at all. Instead, SSH tunnel through:
# On your local machine:
# Creates a local port 3307 that tunnels to MySQL on the server
ssh -L 3307:127.0.0.1:3306 ubuntu@YOUR_SERVER_IP -N
# Connect your MySQL client to localhost:3307
# It actually connects to the server's MySQL via the SSH tunnel
If SSH tunneling isn't practical:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Change: bind-address = 0.0.0.0 (or remove the bind-address line)
sudo systemctl restart mysql
# Open MySQL port in firewall — ONLY for your specific IP
sudo ufw allow from YOUR_IP to any port 3306
# Create a user that can connect remotely
sudo mysql
CREATE USER 'remote_user'@'YOUR_IP' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'remote_user'@'YOUR_IP';
FLUSH PRIVILEGES;
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add:
log_error = /var/log/mysql/error.log
sudo systemctl restart mysql
# Check the log
sudo tail -f /var/log/mysql/error.log
sudo mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 5 "BUFFER POOL"
# Queries taking longer than long_query_time
sudo tail -50 /var/log/mysql/slow.log
# Or use mysqldumpslow for analysis
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Shows the 10 slowest queries by total time
sudo mysql -e "SHOW STATUS LIKE 'Threads_connected';"
sudo mysql -e "SHOW STATUS LIKE 'Max_used_connections';"
If Max_used_connections is close to your max_connections setting, consider increasing it.
On Ubuntu, MySQL 8.0 root uses auth_socket plugin by default — it authenticates based on the Linux user, not a password. This means:
sudo mysql works (you're running as root)mysql -u root -p fails (no password set)This is intentional and more secure. The solution: don't connect as root from applications. Always create a dedicated application user, as shown in Part 4.
If you need to change root to password-based auth (not recommended):
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_root_password';
FLUSH PRIVILEGES;
Then mysql -u root -p will work. But again — don't use root for applications.
# Connect
sudo mysql # As root (socket auth)
mysql -u USER -p DATABASE # As specific user
# Service management
sudo systemctl start|stop|restart|status mysql
# Check MySQL status
mysqladmin -u root status
mysqladmin -u root processlist # Show running queries
# Database operations (in MySQL shell)
SHOW DATABASES;
USE myapp_db;
SHOW TABLES;
DESCRIBE table_name;
SHOW CREATE TABLE table_name\G
# User management
SELECT user, host, plugin FROM mysql.user;
SHOW GRANTS FOR 'user'@'localhost';
DROP USER 'user'@'localhost';
# Backup / restore
mysqldump -u USER -p DATABASE > backup.sql
mysqldump -u USER -p --all-databases > all_backup.sql
mysql -u USER -p DATABASE < backup.sql
# Check disk usage by database
SELECT table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;
| Issue | Likely Cause | Fix |
|---|---|---|
| Connection refused | Service not running or wrong port | Check systemctl status SERVICE and verify firewall rules |
| Permission denied | Wrong file ownership or permissions | Check file ownership with ls -la and use chown/chmod to fix |
| 502 Bad Gateway | Backend service not running | Restart the backend service; check logs with journalctl -u SERVICE |
| SSL certificate error | Certificate expired or domain mismatch | Run sudo certbot renew and verify domain DNS points to server IP |
| Service not starting | Config error or missing dependency | Check logs with journalctl -u SERVICE -n 50 for specific error |
| Out of disk space | Logs or data accumulation | Run df -h to identify usage; clean logs or attach CBS storage |
| High memory usage | Too many processes or memory leak | Check with htop; consider upgrading instance plan if consistently high |
| Firewall blocking traffic | Port not open in UFW or Lighthouse console | Open port in Lighthouse console firewall AND sudo ufw allow PORT |
Do I need a separate server for MySQL?
For most individual projects and small teams, running MySQL on the same server as your application is practical. The zero-latency between app and database (localhost communication) is a genuine advantage. Separate database servers are warranted at significant scale.
How do I back up a MySQL database?
Use the database's built-in dump tools for logical backups (recoverable to any host). Combine with Lighthouse snapshots for full-server point-in-time recovery. Set up automated daily cron jobs for consistent backups.
How do I connect to MySQL remotely for development?
Use SSH tunneling: ssh -L LOCAL_PORT:localhost:DB_PORT user@server. This routes your local database client through SSH without exposing the database port to the internet.
What user permissions should database application users have?
Application users should have only the permissions they need — typically SELECT, INSERT, UPDATE, DELETE on their specific database. Never use the root/admin database account in application connection strings.
htop or your database's status commands and upgrade if you see consistent memory pressure.Set up your database server today:
👉 Tencent Cloud Lighthouse — Ubuntu VPS for your database
👉 View current pricing and promotions
👉 Explore all active deals and offers