When setting up a server or group of servers for the first time you may find yourself wanting to create MySQL users without having to login to MySQL. It would make sense for the mysqladmin command to offer this functionality but it does not. Instead you can create the MySQL user with the mysql command as shown in the below example performed on a server running Ubuntu Linux 12.04 also known as Precise Pangolin.
Create MySQL User From Linux CLI:
- /usr/bin/mysql -uroot -pPASSWORD -e "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON snort.* TO 'snort'@'localhost' IDENTIFIED BY 'PASSWORD HERE'"
In the above example I was creating the snort user that should be setup when installing Snort. When the above command is run you will not get any output on the screen but immediately following you should be able to login with the user created by running the command. When using the above example make sure to change the permissions you want to grant the user which include ALL, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, GRANT OPTION, INDEX, INSERT, LOCK TABLES, PROCESS, PROXY, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE, and USAGE. If you wanted to use provide the user you are creating every permission the syntax below would be appropriate.
Create MySQL User With All Grant Permissions On A Single Database:
- /usr/bin/mysql -uroot -pPASSWORD -e "GRANT ALL PERMISSIONS ON DATABASENAME.* TO 'USERNAME HERE'@'localhost' IDENTIFIED BY 'PASSWORD HERE'"
You could do the same as above but provide the user all permissions on all databases using the below syntax.
Create MySQL User With All Grant Permissions On A All Databases:
- /usr/bin/mysql -uroot -pPASSWORD -e "GRANT ALL PERMISSIONS ON *.* TO 'USERNAME HERE'@'localhost' IDENTIFIED BY 'PASSWORD HERE'"
Notice the only difference between the above two commands is either DATABASENAME.* or *.*. There are obviously many different variable but the above examples should provide a basic understanding of creating mysql users from the Linux command line without having to login to MySQL. For a more detailed explanation of the possible grant permissions click here.
