These tips came out of a real engagement with a customer who was building their MySQL environment from scratch. The question wasn't just "how do I create a user?" — it was "how do I do this in a way I won't regret six months from now?" The following seven practices are what I shared with them, and they apply to most production MySQL setups.
A shared account like dba or admin is an audit nightmare. When something goes wrong (and it will), you won't be able to tell who did what. Instead, create individual named accounts for each DBA. This gives you accountability and makes it easy to revoke access when someone leaves.
CREATE USER 'john_dba'@'10.8.0.%' IDENTIFIED BY 'SecurePassword'; GRANT ALL PRIVILEGES ON *.* TO 'john_dba'@'10.8.0.%' WITH GRANT OPTION;
Notice the IP range 10.8.0.% — this ties the account to your VPN or internal network. More on that next.
MySQL lets you bind a user account to a specific IP or subnet. Use this. Your application server has a known IP — lock the app user to it. This single change blocks a huge class of attacks where compromised credentials are used from unexpected locations.
CREATE USER 'appuser'@'192.168.1.10' IDENTIFIED BY 'AppSecurePass'; GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'appuser'@'192.168.1.10';
For DBAs connecting over VPN, use the VPN subnet range (10.8.0.%) rather than % (which means "anywhere").
The general query log records every SQL statement MySQL receives. It's invaluable during incidents. The catch: by default it's off, and enabling it at runtime doesn't survive a restart. Put it in your config file.
[mysqld] general_log = 1 general_log_file = /var/log/mysql/general.log
Fair warning — this log grows fast on a busy server. Enable it with that in mind, and set up rotation (see next tip).
Unrotated logs will eventually fill your disk. On a production server, that means an outage. MySQL ships with a logrotate config — make sure it's active and test it manually at least once.
logrotate -f /etc/logrotate.d/mysql
Check that the rotation is set to a sane frequency (daily or weekly depending on your query volume) and that old logs are compressed.
Grant only what a user actually needs — nothing more. A reporting user should never have INSERT or DELETE. A read-only replica user should only be able to run replication commands. Overly permissive accounts are one of the most common root causes of data loss in incidents I've seen.
GRANT SELECT ON app_db.* TO 'readonly_user'@'192.168.1.%';
Review your existing user grants periodically with SHOW GRANTS FOR 'username'@'host'; and trim anything that isn't needed.
If a user connects over any network that isn't localhost, their credentials and query data should be encrypted. MySQL supports requiring SSL at the user level — use it for any account that connects remotely.
ALTER USER 'secure_user'@'%' REQUIRE SSL;
After applying this, any connection attempt without SSL will be rejected. Pair this with a valid certificate setup on the server side (ssl_cert, ssl_key, ssl_ca in your MySQL config).
User accounts accumulate. Developers get added, contractors wrap up, services get decommissioned — but the MySQL accounts often outlive all of them. Make it a habit to review active accounts and current log settings.
SELECT user, host FROM mysql.user; SHOW VARIABLES LIKE 'general_log';
Add this to your regular security review or onboarding checklist. It takes two minutes and catches a lot of drift.
None of these tips are exotic — they're the basics that separate a production-grade MySQL setup from one that's an incident waiting to happen. Named users, IP restrictions, least privilege, logging, SSL, and regular audits. If you're setting up MySQL fresh or inheriting someone else's setup, running through this checklist is a good first hour of work.
Hungry for more hands‑on guides on coding, security, and open‑source? Join our newsletter community—new insights delivered every week. Sign up below 👇