This guide covers how to set up and configure mysql on a Linux VPS. Whether you're running a production environment or a development setup, these instructions will help you get started quickly and securely.
Prerequisites
- Basic familiarity with the Linux command line
- A database client tool for testing connections
- A registered domain name (for public-facing services)
- A VPS running Ubuntu 22.04 or later (2GB+ RAM recommended)
- The target database server installed and running
Installation and Initial Setup
Regular maintenance is essential for keeping your mysql installation running smoothly. Schedule periodic reviews of log files, disk usage, and security updates to prevent issues before they occur.
-- Check current configuration
SHOW VARIABLES LIKE '%slow-query%';
SHOW STATUS LIKE '%slow-query%';
-- Optimize settings
SET GLOBAL slow-query_size = '256M';
Note that file paths may vary depending on your Linux distribution. The examples here are for Debian/Ubuntu; adjust paths accordingly for RHEL/CentOS-based systems.
Advanced Settings
For production deployments, consider implementing high availability by running multiple instances behind a load balancer. This approach provides both redundancy and improved performance under heavy load.
- Enable firewall and allow only necessary ports
- Keep all software components up to date
- Use strong, unique passwords for all services
Configuration Tuning
After applying these changes, monitor the server's resource usage for at least 24 hours to ensure stability. Tools like htop, iostat, and vmstat can provide real-time insights into system performance.
# Configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add these optimizations:
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
max_connections = 200
# Restart the service
sudo systemctl restart mysql
Note that file paths may vary depending on your Linux distribution. The examples here are for Debian/Ubuntu; adjust paths accordingly for RHEL/CentOS-based systems.
Performance Considerations
It's recommended to test this configuration in a staging environment before deploying to production. This helps identify potential compatibility issues and allows you to benchmark performance differences.
Next Steps
With mysql now set up and running, consider implementing monitoring to track performance metrics over time. Regularly review your configuration as your workload changes and scale resources accordingly.