Step-by-step MySQL and MariaDB installation on Linux, mysql_secure_installation, creating databases and users, basic SQL commands, remote access configuration, and performance tuning.
MySQL and MariaDB are the most widely used relational database management systems worldwide. The vast majority of web applications, e-commerce sites, and enterprise systems use one of these databases. This guide covers installation, basic configuration, and management of both systems.
MariaDB is a community-driven fork created by the original MySQL developers. Key differences:
| Feature | MySQL | MariaDB |
|---|---|---|
| License | GPL + Commercial | GPL (fully open) |
| Developer | Oracle | MariaDB Foundation |
| Performance | Good | Generally faster |
| Compatibility | Reference | Compatible with MySQL |
| New features | Slower | Faster release cycle |
MariaDB is recommended for new projects. It is fully compatible with MySQL and generally offers better performance. Migrating from MySQL to MariaDB is straightforward.
# Update package list
apt update
# Install MySQL Server
apt install -y mysql-server
# Check service status
systemctl status mysql
# Enable auto-start
systemctl enable mysql
mysql --version
# Output: mysql Ver 8.0.xx Distrib 8.0.xx, for Linux (x86_64)
# Update package list
apt update
# Install MariaDB Server
apt install -y mariadb-server mariadb-client
# Check service status
systemctl status mariadb
# Enable auto-start
systemctl enable mariadb
# Check version
mysql --version
# Output: mysql Ver 15.1 Distrib 10.x.x-MariaDB
# Add MariaDB repository
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
# Install MariaDB
dnf install -y MariaDB-server MariaDB-client
# Start and enable service
systemctl start mariadb
systemctl enable mariadb
Always run the security wizard after installation:
mysql_secure_installation
The wizard asks the following questions:
Enter current password for root (enter for none): [Enter]
Switch to unix_socket authentication [Y/n]: n
Change the root password? [Y/n]: Y
New password: [Enter a strong password]
Re-enter new password: [Repeat the password]
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y
Disable remote root access. Create separate users for remote connections. Use strong passwords (at least 12 characters with uppercase, lowercase, numbers, and special characters).
# Connect as root
mysql -u root -p
# Connect to a specific database
mysql -u user -p database_name
# Connect to remote server
mysql -h 192.168.1.100 -u user -p database_name
# Connect with specific port
mysql -h server -P 3306 -u user -p
-- Create database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- List all databases
SHOW DATABASES;
-- Select database
USE myapp;
-- Drop database (careful!)
DROP DATABASE old_database;
-- Check database size
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;
-- Create user (localhost only)
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPass123!';
-- Create user (remote access)
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPass123!';
-- Access from specific IP
CREATE USER 'appuser'@'192.168.1.50' IDENTIFIED BY 'StrongPass123!';
-- Grant full privileges on database
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';
-- Grant read-only access
GRANT SELECT ON myapp.* TO 'readonly'@'localhost';
-- Grant privileges on specific table
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'appuser'@'localhost';
-- Apply privileges
FLUSH PRIVILEGES;
-- View user privileges
SHOW GRANTS FOR 'appuser'@'localhost';
-- Drop user
DROP USER 'old_user'@'localhost';
-- Create table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert data
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
-- Query data
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;
-- Update data
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- Delete data
DELETE FROM users WHERE id = 5;
-- List tables
SHOW TABLES;
-- View table structure
DESCRIBE users;
By default, MySQL/MariaDB only accepts connections from localhost. For remote access:
# For MySQL
nano /etc/mysql/mysql.conf.d/mysqld.cnf
# For MariaDB
nano /etc/mysql/mariadb.conf.d/50-server.cnf
# Change or comment out the bind-address line
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
systemctl restart mysql
# or
systemctl restart mariadb
# With UFW
ufw allow 3306/tcp
# With iptables
iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
Exposing MySQL port (3306) to the internet is a security risk. Use SSH tunneling or VPN when possible. If remote access is required, restrict to specific IP addresses.
nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# InnoDB buffer pool (70-80% of RAM)
innodb_buffer_pool_size = 1G
# Connection pool
max_connections = 150
# Temporary table size
tmp_table_size = 64M
max_heap_table_size = 64M
# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
# Binary log (for replication)
# log_bin = /var/log/mysql/mysql-bin.log
# expire_logs_days = 7
systemctl restart mysql
# Check variables
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# Start service
systemctl start mysql
# Stop service
systemctl stop mysql
# Restart service
systemctl restart mysql
# Reload configuration (no restart needed)
systemctl reload mysql
# Check service status
systemctl status mysql
# View logs
journalctl -u mysql -f
tail -f /var/log/mysql/error.log
MySQL and MariaDB are powerful and reliable database systems. After installation, don't neglect security configuration, perform regular backups, and optimize performance settings according to your server's resources. Both systems run smoothly on REXE servers.
MariaDB is recommended for new projects. It is fully compatible with MySQL, generally offers better performance, and is completely open source. Your existing MySQL applications will run on MariaDB without changes. However, if you need Oracle's MySQL-specific features (Group Replication, MySQL Shell, etc.), choose MySQL.
Start MySQL in safe mode: run systemctl stop mysql, then mysqld_safe --skip-grant-tables &. Connect with mysql -u root and run ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';. Finally run FLUSH PRIVILEGES; and restart the service normally.
Check three things: 1) Verify bind-address is set to 0.0.0.0, 2) Check that the user was created with '%' or the specific IP (SHOW GRANTS FOR 'user'@'%'), 3) Verify port 3306 is open in the firewall (ufw status or iptables -L).
Set it to 70-80% of your server's total RAM. For example, 2-3GB for 4GB RAM, 5-6GB for 8GB RAM. If the server is dedicated to the database, you can go up to 80%. Remember to restart the service after making changes.
While technically possible, it is not recommended. Both use port 3306 by default and will conflict. You can move one to a different port, but this creates complexity. Generally, choosing one and sticking with it is a better approach.
PostgreSQL installation on Ubuntu/Debian and RHEL/CentOS, creating users and databases, basic SQL commands, remote connection setup, and backup strategies. Step-by-step guide.
Redis and Memcached installation on Ubuntu/Debian, basic commands, persistence settings, TTL configuration, Redis vs Memcached comparison, and application integration.
Step-by-step phpMyAdmin installation on Apache and Nginx, SSL configuration, user management, IP restrictions, and security hardening best practices.