majiayu000 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