Local MySQL root user for development purposes

README.md

Sample of how to create a MySQL development user

Create a MySQL root-like user for development

Note: This is also a MySQL 8 workaround for the new caching_sha2_password authentication plugin. Example below uses the legacy mysql_native_password instead for compatibility reasons.

CREATE USER `my_root_user`@`%` IDENTIFIED WITH mysql_native_password BY 'my_root_pwd';

GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, 
      Create User, Create View, Delete, Drop, Event, Execute, File, Grant Option, 
      Index, Insert, Lock Tables, Process, References, Reload, Replication Client, 
      Replication Slave, Select, Show Databases, Show View, Shutdown, Trigger, Update
      ON *.* TO `my_root_user`@`%`;

Create a development user with specific database privileges

CREATE USER `my_user`@`%` IDENTIFIED WITH mysql_native_password BY 'my_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, EXECUTE
      ON `my_database`.* TO `my_user`@`%` WITH GRANT OPTION;

Update a development user password

ALTER USER 'my_root_user'@'localhost' IDENTIFIED BY 'MyNewPassword';