ساخت کاربر و تنظیم دسترسی در MySQL (شامل کاربر بکاپ)

sudo mysql

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON appdb.* TO 'app'@'localhost';
FLUSH PRIVILEGES;

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON appdb.* TO 'backup'@'localhost';
-- برای بکاپ همهٔ دیتابیس‌ها یا استفاده بدون --no-tablespaces:
GRANT PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

CREATE USER 'app'@'203.0.113.10' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'203.0.113.10';
FLUSH PRIVILEGES;

sudo ufw allow from 203.0.113.10 to any port 3306 proto tcp
sudo ufw status numbered

SHOW GRANTS FOR 'app'@'localhost';
SELECT user, host FROM mysql.user;

ALTER USER 'app'@'localhost' IDENTIFIED BY 'NEW_STRONG_PASSWORD';
DROP USER 'old_user'@'localhost';