MySQL / MariaDB

Administer MySQL and MariaDB databases.

Installation & Setup

# Install
apt install mysql-server

# Secure installation
mysql_secure_installation

# Access
mysql -u root -p

# Create database and user
CREATE DATABASE mydb;
CREATE USER 'myapp'@'%' IDENTIFIED BY 'secret';
GRANT ALL PRIVILEGES ON mydb.* TO 'myapp'@'%';
FLUSH PRIVILEGES;

Configuration

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 200
slow_query_log = 1
long_query_time = 2

Backup & Restore

# Backup
mysqldump -u root -p mydb > backup.sql
mysqldump -u root -p --all-databases > full_backup.sql

# Restore
mysql -u root -p mydb < backup.sql

Replication

# Primary
[mysqld]
server-id = 1
log_bin = mysql-bin

# Replica
CHANGE MASTER TO
  MASTER_HOST='primary',
  MASTER_USER='replicator',
  MASTER_PASSWORD='secret',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=0;
START SLAVE;

Best Practices

• Enable slow query logging
• Use InnoDB storage engine
• Regular backups with mysqldump
• Monitor with SHOW PROCESSLIST