How to Create a Read-Only MySQL User

Simple enough problem, but I couldn’t find a quick solution that worked out of the box for my configuration. Here’s a quick guide to setting up a read-only user on your MySQL server (useful for safely inspecting production databases):

# login to your mysql server as root mysql -u root -p # execute the following SQL CREATE USER production_read_only; SET PASSWORD FOR production_read_only = PASSWORD('REDACTED'); GRANT SELECT ON database.* TO production_read_only; UPDATE mysql.user SET Host = 'localhost' WHERE User = 'production_read_only'; FLUSH PRIVILEGES;

Note that you may need to change Host = 'localhost' depending on your MySQL server configuration.

Continue Reading