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