Skip to main content

MySQL Database Server on Ubuntu

Trademark Notice

MySQL® is a registered trademark of Oracle Corporation and/or its affiliates. All trademarks are used for representation purposes only. This content is for educational purposes only.

Complete guide to installing and configuring MySQL on Ubuntu Server.

Installation​

MySQL 8.0 Installation​

# Update package list
sudo apt update

# Install MySQL server
sudo apt install -y mysql-server

# Start and enable MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

# Check status
sudo systemctl status mysql

# Check version
mysql --version

Secure Installation​

# Run security script
sudo mysql_secure_installation

Follow prompts:

  • Set root password
  • Remove anonymous users: Yes
  • Disallow root login remotely: Yes
  • Remove test database: Yes
  • Reload privilege tables: Yes

Initial Configuration​

Access MySQL​

# Login as root
sudo mysql

# Or with password
mysql -u root -p

Create Database and User​

-- Create database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create user
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'strong_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

-- Verify
SHOW DATABASES;
SELECT User, Host FROM mysql.user;

-- Exit
EXIT;

Configure Remote Access​

# Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Change bind-address
bind-address = 0.0.0.0

# Or specific IP
bind-address = 192.168.1.100
-- Create remote user
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'remote_user'@'%';

-- Or specific IP
CREATE USER 'remote_user'@'192.168.1.0/255.255.255.0' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'remote_user'@'192.168.1.%';

FLUSH PRIVILEGES;
# Open firewall
sudo ufw allow 3306/tcp

# Restart MySQL
sudo systemctl restart mysql

Configuration Files​

/etc/mysql/
├── my.cnf                          # Main config (includes others)
├── mysql.cnf                       # MySQL client config
└── mysql.conf.d/
    └── mysqld.cnf                  # MySQL server config

Main Configuration​

# Edit server configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Basic Settings
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp

# Character Set
character-set-server    = utf8mb4
collation-server        = utf8mb4_unicode_ci

# Network
bind-address            = 127.0.0.1
max_connections         = 200

# Query Cache (disabled in MySQL 8.0+)
# query_cache_type      = 1
# query_cache_size      = 16M

# InnoDB Settings
innodb_buffer_pool_size = 1G
innodb_log_file_size    = 256M
innodb_flush_method     = O_DIRECT
innodb_flush_log_at_trx_commit = 2

# Logging
log_error               = /var/log/mysql/error.log
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 2

# Binary Logging (for replication)
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 7
max_binlog_size         = 100M

# Security
local-infile            = 0
# Restart MySQL
sudo systemctl restart mysql

User Management​

-- Create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- Change password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

-- Grant specific privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'username'@'localhost';

-- Grant all privileges
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost';

-- Grant with grant option
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' WITH GRANT OPTION;

-- Revoke privileges
REVOKE INSERT, UPDATE ON database.* FROM 'username'@'localhost';

-- Drop user
DROP USER 'username'@'localhost';

-- Show grants
SHOW GRANTS FOR 'username'@'localhost';

-- List all users
SELECT User, Host, authentication_string FROM mysql.user;

Database Operations​

-- Create database
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- List databases
SHOW DATABASES;

-- Use database
USE dbname;

-- Drop database
DROP DATABASE dbname;

-- Show tables
SHOW TABLES;

-- Describe table
DESCRIBE tablename;
SHOW CREATE TABLE tablename;

-- Show table size
SELECT 
    table_schema AS 'Database',
    table_name AS 'Table',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'dbname'
ORDER BY (data_length + index_length) DESC;

Backup and Restore​

Full Database Backup​

# Backup single database
mysqldump -u root -p dbname > backup_dbname_$(date +%Y%m%d).sql

# Backup with compression
mysqldump -u root -p dbname | gzip > backup_dbname_$(date +%Y%m%d).sql.gz

# Backup all databases
mysqldump -u root -p --all-databases > backup_all_$(date +%Y%m%d).sql

# Backup specific tables
mysqldump -u root -p dbname table1 table2 > backup_tables_$(date +%Y%m%d).sql

# Backup structure only
mysqldump -u root -p --no-data dbname > structure_$(date +%Y%m%d).sql

# Backup data only
mysqldump -u root -p --no-create-info dbname > data_$(date +%Y%m%d).sql

Restore Database​

# Restore database
mysql -u root -p dbname < backup_dbname.sql

# Restore from compressed backup
gunzip < backup_dbname.sql.gz | mysql -u root -p dbname

# Restore all databases
mysql -u root -p < backup_all.sql

# Create database and restore
mysql -u root -p -e "CREATE DATABASE dbname"
mysql -u root -p dbname < backup_dbname.sql

Automated Backup Script​

# Create backup script
sudo nano /usr/local/bin/mysql-backup.sh
#!/bin/bash

# Configuration
BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASSWORD="your_password"
RETENTION_DAYS=7

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup filename
BACKUP_FILE="$BACKUP_DIR/mysql_backup_$(date +%Y%m%d_%H%M%S).sql.gz"

# Perform backup
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases | gzip > $BACKUP_FILE

# Remove old backups
find $BACKUP_DIR -name "mysql_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Log
echo "Backup completed: $BACKUP_FILE"
# Make executable
sudo chmod +x /usr/local/bin/mysql-backup.sh

# Add to cron (daily at 2 AM)
sudo crontab -e
0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Replication Setup​

Master Configuration​

# Edit master config
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp_db
bind-address = 0.0.0.0
-- Create replication user
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;
-- Note: File and Position values

Slave Configuration​

# Edit slave config
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp_db
-- Configure slave
CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

-- Verify
-- Look for: Slave_IO_Running: Yes
--           Slave_SQL_Running: Yes

Performance Tuning​

InnoDB Optimization​

# Calculate buffer pool size (50-70% of RAM)
# For 8GB RAM: 4-5GB buffer pool

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# InnoDB Buffer Pool
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4

# InnoDB Log
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# InnoDB File
innodb_file_per_table = 1
innodb_open_files = 400

# InnoDB Thread
innodb_thread_concurrency = 0
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# InnoDB Other
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

Connection and Cache Tuning​

[mysqld]
max_connections = 200
max_connect_errors = 100
max_allowed_packet = 64M
thread_cache_size = 50
table_open_cache = 4000
table_definition_cache = 400

# Temp Tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Sort Buffer
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 2M

Query Cache (MySQL 5.7 only)​

# Not available in MySQL 8.0+
[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

Monitoring​

Check MySQL Status​

-- Show status variables
SHOW STATUS;
SHOW GLOBAL STATUS;

-- Show variables
SHOW VARIABLES;
SHOW GLOBAL VARIABLES LIKE '%buffer%';

-- Show processes
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Table status
SHOW TABLE STATUS FROM dbname;

-- Check connections
SELECT user, host, db, command, time, state 
FROM information_schema.processlist;

Slow Query Log​

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- Check slow query log
SHOW VARIABLES LIKE 'slow_query%';
# Analyze slow queries
sudo mysqldumpslow /var/log/mysql/mysql-slow.log

# Top 10 slowest queries
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Performance Schema​

-- Enable performance schema
SET GLOBAL performance_schema = ON;

-- View top queries by execution time
SELECT * FROM sys.statement_analysis 
ORDER BY total_latency DESC 
LIMIT 10;

-- View table with most I/O
SELECT * FROM sys.io_global_by_file_by_bytes 
LIMIT 10;

Security​

Security Best Practices​

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Remove remote root
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Remove test database
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

FLUSH PRIVILEGES;

Password Policy (MySQL 8.0)​

-- Check password policy
SHOW VARIABLES LIKE 'validate_password%';

-- Set password policy
SET GLOBAL validate_password.policy = MEDIUM;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

SSL/TLS Encryption​

# Generate SSL certificates
sudo mysql_ssl_rsa_setup --uid=mysql

# Verify SSL
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"
-- Require SSL for user
ALTER USER 'username'@'%' REQUIRE SSL;

-- Check SSL status
SHOW STATUS LIKE 'Ssl_cipher';

Troubleshooting​

# Check MySQL error log
sudo tail -f /var/log/mysql/error.log

# Check if MySQL is running
sudo systemctl status mysql

# Check MySQL port
sudo netstat -tlnp | grep 3306

# Test connection
mysql -u root -p -h 127.0.0.1 -e "SELECT 1"

# Check disk space
df -h /var/lib/mysql

# Check MySQL process
ps aux | grep mysql

# Repair tables
sudo mysqlcheck -u root -p --auto-repair --all-databases

# Optimize tables
sudo mysqlcheck -u root -p --optimize --all-databases

Common Issues​

MySQL won't start:

# Check error log
sudo tail -100 /var/log/mysql/error.log

# Check permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Check disk space
df -h

# Try starting in safe mode
sudo mysqld_safe --skip-grant-tables &

Connection refused:

# Check if MySQL is running
sudo systemctl status mysql

# Check port
sudo netstat -tlnp | grep 3306

# Check firewall
sudo ufw status

Best Practices​

  1. Regular Backups: Automate daily backups
  2. Security: Use strong passwords, limit remote access
  3. Monitoring: Enable slow query log
  4. Updates: Keep MySQL updated
  5. Replication: Set up master-slave for high availability
  6. Optimization: Tune InnoDB buffer pool
  7. Indexing: Create proper indexes for queries
  8. Connection Pooling: Use connection pooling in applications
  9. SSL/TLS: Encrypt connections
  10. Documentation: Document schema changes

Resources​