The user grant syntax in mysql is problematic if you want to use a variable to set the hostname when you are creating users in a mysql script. The @ notation means the start of a variable normally, but it’s also used in the mysql grants… so if you try to do this:

SET @hostname='localhost';
GRANT SELECT, SHOW VIEW ON *.* TO 'username'@'@hostname' IDENTIFIED BY 'pass';

You get the error:

Lookup Error - MySQL Database Error: Malformed hostname (illegal symbol: '@')

Or if you take the single quotes out, you just get a syntax error.

The solution is to use a placeholder when you do the create user grants, then later replace all instances of the placeholder with the variable.

/* uncomment to set appropriate environment */
SET @hostname='localhost'; /* development */
/* SET @hostname='testing.hostname.com'; */
/* SET @hostname='production.hostname.com'; */

GRANT SELECT ON db_name.table_name TO 'username'@'env' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT ON db_name.* TO 'username2'@'env' IDENTIFIED BY 'pass';
GRANT SELECT ON *.* TO 'username3'@'env' IDENTIFIED BY 'pass';
/* ... */

UPDATE mysql.user SET host = @hostname WHERE host = 'env';
UPDATE mysql.db SET host = @hostname WHERE host = 'env';
UPDATE mysql.tables_priv SET host = @hostname WHERE host = 'env';
FLUSH PRIVILEGES;