How to run MySQL container in Linux
How to Run MySQL Container in Linux
Running MySQL in a Docker container has become the preferred method for database deployment in modern development and production environments. This comprehensive guide will walk you through everything you need to know about running MySQL containers in Linux, from basic setup to advanced configurations and best practices.
Introduction
MySQL containerization offers numerous advantages including consistent deployment environments, easy scaling, simplified backup procedures, and improved resource isolation. Whether you're a developer setting up a local development environment or a system administrator deploying production databases, understanding how to properly run MySQL containers is essential for modern database management.
In this guide, you'll learn how to install Docker, pull MySQL images, configure containers with proper security settings, manage data persistence, and troubleshoot common issues. We'll cover everything from basic single-container setups to advanced multi-container configurations with networking and volume management.
Prerequisites and Requirements
Before diving into MySQL container deployment, ensure your Linux system meets the following requirements:
System Requirements
- Linux distribution (Ubuntu 18.04+, CentOS 7+, RHEL 7+, or equivalent)
- Minimum 2GB RAM (4GB recommended for production)
- At least 10GB available disk space
- Root or sudo privileges
- Internet connection for downloading Docker images
Software Prerequisites
- Docker Engine (version 20.10 or later recommended)
- Docker Compose (optional but recommended)
- Basic command-line knowledge
- Understanding of MySQL fundamentals
Network Requirements
- Available ports for MySQL (default 3306)
- Firewall configuration if applicable
- Network connectivity for container communication
Installing Docker on Linux
Ubuntu/Debian Installation
First, update your package index and install prerequisite packages:
```bash
sudo apt update
sudo apt install apt-transport-https ca-certificates curl gnupg lsb-release
```
Add Docker's official GPG key and repository:
```bash
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
echo "deb [arch=amd64 signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
```
Install Docker Engine:
```bash
sudo apt update
sudo apt install docker-ce docker-ce-cli containerd.io docker-compose-plugin
```
CentOS/RHEL Installation
Install required packages and add Docker repository:
```bash
sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
```
Install Docker:
```bash
sudo yum install docker-ce docker-ce-cli containerd.io docker-compose-plugin
```
Post-Installation Setup
Start and enable Docker service:
```bash
sudo systemctl start docker
sudo systemctl enable docker
```
Add your user to the docker group to run Docker commands without sudo:
```bash
sudo usermod -aG docker $USER
```
Log out and back in for group changes to take effect, then verify installation:
```bash
docker --version
docker run hello-world
```
Running Your First MySQL Container
Basic MySQL Container Deployment
The simplest way to run a MySQL container is using the official MySQL image from Docker Hub:
```bash
docker run --name mysql-server -e MYSQL_ROOT_PASSWORD=your_secure_password -d mysql:8.0
```
This command creates a MySQL container with the following parameters:
- `--name mysql-server`: Assigns a name to the container
- `-e MYSQL_ROOT_PASSWORD`: Sets the root password environment variable
- `-d`: Runs the container in detached mode
- `mysql:8.0`: Specifies the MySQL image version
Container with Port Mapping
To access MySQL from outside the container, map the MySQL port:
```bash
docker run --name mysql-server \
-e MYSQL_ROOT_PASSWORD=your_secure_password \
-p 3306:3306 \
-d mysql:8.0
```
The `-p 3306:3306` flag maps port 3306 from the container to port 3306 on the host system.
Verifying Container Status
Check if your MySQL container is running:
```bash
docker ps
```
View container logs to ensure MySQL started successfully:
```bash
docker logs mysql-server
```
Advanced MySQL Container Configuration
Environment Variables Configuration
MySQL containers support numerous environment variables for configuration:
```bash
docker run --name mysql-advanced \
-e MYSQL_ROOT_PASSWORD=root_password \
-e MYSQL_DATABASE=myapp_db \
-e MYSQL_USER=appuser \
-e MYSQL_PASSWORD=app_password \
-e MYSQL_ROOT_HOST=% \
-p 3306:3306 \
-d mysql:8.0
```
Key environment variables include:
- `MYSQL_ROOT_PASSWORD`: Root user password
- `MYSQL_DATABASE`: Creates a database on startup
- `MYSQL_USER`: Creates a non-root user
- `MYSQL_PASSWORD`: Password for the non-root user
- `MYSQL_ROOT_HOST`: Allows root connections from specified hosts
Custom MySQL Configuration
Create a custom MySQL configuration file:
```bash
mkdir -p /opt/mysql/conf
cat > /opt/mysql/conf/my.cnf << EOF
[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 200
query_cache_size = 64M
query_cache_type = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
EOF
```
Run MySQL container with custom configuration:
```bash
docker run --name mysql-custom \
-e MYSQL_ROOT_PASSWORD=your_password \
-p 3306:3306 \
-v /opt/mysql/conf:/etc/mysql/conf.d \
-d mysql:8.0
```
Data Persistence and Volume Management
Using Named Volumes
Create a named volume for persistent data storage:
```bash
docker volume create mysql-data
```
Run MySQL container with the named volume:
```bash
docker run --name mysql-persistent \
-e MYSQL_ROOT_PASSWORD=your_password \
-p 3306:3306 \
-v mysql-data:/var/lib/mysql \
-d mysql:8.0
```
Using Bind Mounts
Create a directory on the host system:
```bash
sudo mkdir -p /opt/mysql/data
sudo chown -R 999:999 /opt/mysql/data
```
Run MySQL with bind mount:
```bash
docker run --name mysql-bindmount \
-e MYSQL_ROOT_PASSWORD=your_password \
-p 3306:3306 \
-v /opt/mysql/data:/var/lib/mysql \
-d mysql:8.0
```
Backup and Restore Strategies
Create a database backup:
```bash
docker exec mysql-server mysqldump -u root -p your_database > backup.sql
```
Restore from backup:
```bash
docker exec -i mysql-server mysql -u root -p your_database < backup.sql
```
Docker Compose for MySQL Deployment
Docker Compose simplifies multi-container applications and MySQL configuration management.
Basic Docker Compose Configuration
Create a `docker-compose.yml` file:
```yaml
version: '3.8'
services:
mysql:
image: mysql:8.0
container_name: mysql-compose
restart: always
environment:
MYSQL_ROOT_PASSWORD: root_password
MYSQL_DATABASE: myapp
MYSQL_USER: appuser
MYSQL_PASSWORD: app_password
ports:
- "3306:3306"
volumes:
- mysql_data:/var/lib/mysql
- ./mysql/conf:/etc/mysql/conf.d
networks:
- mysql_network
volumes:
mysql_data:
networks:
mysql_network:
driver: bridge
```
Deploy using Docker Compose:
```bash
docker-compose up -d
```
Advanced Docker Compose with Multiple Services
Create a comprehensive setup with MySQL, phpMyAdmin, and application services:
```yaml
version: '3.8'
services:
mysql:
image: mysql:8.0
container_name: mysql-server
restart: always
environment:
MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
MYSQL_DATABASE: ${MYSQL_DATABASE}
MYSQL_USER: ${MYSQL_USER}
MYSQL_PASSWORD: ${MYSQL_PASSWORD}
ports:
- "3306:3306"
volumes:
- mysql_data:/var/lib/mysql
- ./mysql/conf:/etc/mysql/conf.d
- ./mysql/init:/docker-entrypoint-initdb.d
networks:
- app_network
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
timeout: 20s
retries: 10
phpmyadmin:
image: phpmyadmin/phpmyadmin
container_name: phpmyadmin
restart: always
environment:
PMA_HOST: mysql
PMA_PORT: 3306
PMA_ARBITRARY: 1
ports:
- "8080:80"
depends_on:
- mysql
networks:
- app_network
volumes:
mysql_data:
networks:
app_network:
driver: bridge
```
Create an environment file `.env`:
```bash
MYSQL_ROOT_PASSWORD=secure_root_password
MYSQL_DATABASE=myapp_db
MYSQL_USER=appuser
MYSQL_PASSWORD=secure_app_password
```
Security Best Practices
Container Security Configuration
Run MySQL container with security enhancements:
```bash
docker run --name mysql-secure \
-e MYSQL_ROOT_PASSWORD=your_secure_password \
-p 127.0.0.1:3306:3306 \
--user 999:999 \
--read-only \
--tmpfs /tmp \
--tmpfs /var/run/mysqld \
-v mysql_data:/var/lib/mysql \
-v /opt/mysql/conf:/etc/mysql/conf.d:ro \
-d mysql:8.0
```
Security features implemented:
- Bind to localhost only (`127.0.0.1:3306:3306`)
- Run as non-root user (`--user 999:999`)
- Read-only filesystem (`--read-only`)
- Temporary filesystems for writable directories
Network Security
Create a custom network for MySQL containers:
```bash
docker network create --driver bridge mysql_secure_network
```
Run MySQL in the custom network:
```bash
docker run --name mysql-networked \
-e MYSQL_ROOT_PASSWORD=your_password \
--network mysql_secure_network \
-d mysql:8.0
```
Password and Authentication Security
Use Docker secrets for password management in production:
```bash
echo "your_secure_password" | docker secret create mysql_root_password -
echo "app_secure_password" | docker secret create mysql_app_password -
```
Connecting to MySQL Container
Command Line Connection
Connect using the MySQL client from within the container:
```bash
docker exec -it mysql-server mysql -u root -p
```
Connect from the host system (requires MySQL client):
```bash
mysql -h 127.0.0.1 -P 3306 -u root -p
```
Application Connection Examples
Python Connection
```python
import mysql.connector
config = {
'user': 'appuser',
'password': 'app_password',
'host': '127.0.0.1',
'port': 3306,
'database': 'myapp_db',
'raise_on_warnings': True
}
try:
cnx = mysql.connector.connect(config)
cursor = cnx.cursor()
cursor.execute("SELECT VERSION()")
version = cursor.fetchone()
print(f"MySQL Version: {version[0]}")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
cursor.close()
cnx.close()
```
Node.js Connection
```javascript
const mysql = require('mysql2');
const connection = mysql.createConnection({
host: '127.0.0.1',
port: 3306,
user: 'appuser',
password: 'app_password',
database: 'myapp_db'
});
connection.connect((err) => {
if (err) {
console.error('Error connecting: ' + err.stack);
return;
}
console.log('Connected as id ' + connection.threadId);
});
```
Monitoring and Logging
Container Resource Monitoring
Monitor MySQL container resource usage:
```bash
docker stats mysql-server
```
View detailed container information:
```bash
docker inspect mysql-server
```
Log Management
Configure log rotation and management:
```bash
docker run --name mysql-logging \
-e MYSQL_ROOT_PASSWORD=your_password \
--log-driver json-file \
--log-opt max-size=10m \
--log-opt max-file=3 \
-d mysql:8.0
```
Access MySQL error logs:
```bash
docker exec mysql-server tail -f /var/log/mysql/error.log
```
Common Issues and Troubleshooting
Container Startup Issues
Problem: Container exits immediately after starting
Solution: Check environment variables and logs:
```bash
docker logs mysql-server
docker run --rm -it mysql:8.0 /bin/bash
```
Problem: Permission denied errors
Solution: Fix volume permissions:
```bash
sudo chown -R 999:999 /opt/mysql/data
sudo chmod -R 755 /opt/mysql/data
```
Connection Issues
Problem: Cannot connect to MySQL from host
Solution: Verify port mapping and firewall settings:
```bash
docker port mysql-server
netstat -tulpn | grep 3306
sudo ufw allow 3306
```
Problem: Access denied for user
Solution: Reset MySQL root password:
```bash
docker exec -it mysql-server mysql_secure_installation
```
Performance Issues
Problem: Slow query performance
Solution: Optimize MySQL configuration:
```bash
Add to custom my.cnf
[mysqld]
innodb_buffer_pool_size = 75% of available RAM
query_cache_size = 64M
max_connections = adjust based on application needs
```
Problem: High memory usage
Solution: Limit container resources:
```bash
docker run --name mysql-limited \
-e MYSQL_ROOT_PASSWORD=your_password \
--memory=2g \
--cpus=2 \
-d mysql:8.0
```
Data Recovery Issues
Problem: Lost data after container removal
Solution: Always use persistent volumes:
```bash
docker run --name mysql-persistent \
-e MYSQL_ROOT_PASSWORD=your_password \
-v mysql_data:/var/lib/mysql \
-d mysql:8.0
```
Problem: Corrupted database files
Solution: Run MySQL repair utilities:
```bash
docker exec mysql-server mysqlcheck --all-databases --repair
```
Best Practices and Professional Tips
Production Deployment Guidelines
1. Always use specific image tags: Avoid `latest` tag in production
```bash
docker run --name mysql-prod -d mysql:8.0.32
```
2. Implement health checks: Monitor container health
```bash
docker run --name mysql-health \
--health-cmd="mysqladmin ping -h localhost" \
--health-interval=30s \
--health-timeout=10s \
--health-retries=3 \
-d mysql:8.0
```
3. Use resource limits: Prevent resource exhaustion
```bash
docker run --name mysql-limited \
--memory=4g \
--cpus=2 \
--restart=unless-stopped \
-d mysql:8.0
```
Security Hardening
1. Use non-root users: Run containers with minimal privileges
2. Implement network segmentation: Use custom Docker networks
3. Regular security updates: Keep MySQL images updated
4. Encrypt data at rest: Use encrypted storage volumes
5. Monitor access logs: Implement comprehensive logging
Backup and Disaster Recovery
1. Automated backups: Implement regular backup schedules
```bash
#!/bin/bash
BACKUP_DIR="/opt/mysql/backups"
DATE=$(date +%Y%m%d_%H%M%S)
docker exec mysql-server mysqldump --all-databases > $BACKUP_DIR/backup_$DATE.sql
```
2. Test restore procedures: Regular disaster recovery testing
3. Multiple backup locations: Store backups in different locations
4. Point-in-time recovery: Enable binary logging for recovery
Performance Optimization
1. Memory allocation: Optimize InnoDB buffer pool size
2. Connection pooling: Implement application-level connection pooling
3. Query optimization: Regular query performance analysis
4. Index management: Maintain proper database indexes
5. Monitoring: Implement comprehensive performance monitoring
Scaling and High Availability
MySQL Replication Setup
Master-slave replication with Docker containers:
```yaml
version: '3.8'
services:
mysql-master:
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: master_password
MYSQL_REPLICATION_USER: repl_user
MYSQL_REPLICATION_PASSWORD: repl_password
volumes:
- master_data:/var/lib/mysql
- ./master.cnf:/etc/mysql/conf.d/master.cnf
ports:
- "3307:3306"
mysql-slave:
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: slave_password
volumes:
- slave_data:/var/lib/mysql
- ./slave.cnf:/etc/mysql/conf.d/slave.cnf
ports:
- "3308:3306"
depends_on:
- mysql-master
volumes:
master_data:
slave_data:
```
Load Balancing
Implement MySQL load balancing with ProxySQL:
```yaml
version: '3.8'
services:
proxysql:
image: proxysql/proxysql
ports:
- "6032:6032"
- "6033:6033"
volumes:
- ./proxysql.cnf:/etc/proxysql.cnf
depends_on:
- mysql-master
- mysql-slave
```
Migration and Upgrades
Database Migration
Migrate existing MySQL data to containers:
```bash
Export from existing MySQL
mysqldump -u root -p --all-databases > migration_backup.sql
Import to container
docker exec -i mysql-server mysql -u root -p < migration_backup.sql
```
Version Upgrades
Upgrade MySQL container versions safely:
```bash
Backup current data
docker exec mysql-server mysqldump --all-databases > pre_upgrade_backup.sql
Stop current container
docker stop mysql-server
Start new version container with same volume
docker run --name mysql-upgraded \
-v mysql_data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=your_password \
-d mysql:8.0.33
```
Conclusion
Running MySQL containers in Linux provides a flexible, scalable, and maintainable approach to database deployment. This comprehensive guide has covered everything from basic container setup to advanced production configurations, security hardening, and troubleshooting procedures.
Key takeaways include the importance of data persistence through proper volume management, security best practices for production environments, and the benefits of using Docker Compose for complex deployments. Remember to always implement proper backup strategies, monitor container performance, and keep your MySQL images updated with the latest security patches.
As you continue working with MySQL containers, focus on automation, monitoring, and documentation of your deployment procedures. Consider implementing Infrastructure as Code practices using tools like Terraform or Ansible for consistent and repeatable deployments across different environments.
The containerization of MySQL databases represents a significant step toward modern, cloud-native application architectures. By following the practices outlined in this guide, you'll be well-equipped to deploy, manage, and scale MySQL containers effectively in any Linux environment.
For next steps, consider exploring Kubernetes for container orchestration, implementing comprehensive monitoring solutions like Prometheus and Grafana, and investigating advanced MySQL features like clustering and sharding for large-scale applications.