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.