Adding MySQL Users Without Having to Type Long Commands Every Time

I was excited to launch my new website, selfhostit.com, but I was struggling to come up with ideas for my first article. I wanted to make a good first impression and share something valuable with my readers, but I couldn't seem to find the right topic. Then it hit me - why not write about something that's been a real timesaver for me: a simple bash command to add a new user in MySQL?

It might not be the most exciting topic, but I knew it was something that could help others who are managing a MySQL server like myself. But if you're managing a MySQL server, you might find yourself frequently creating new users with specific passwords and privileges. This can quickly become tedious and time-consuming, especially if you're manually executing the same commands over and over again.

With just a few lines of code, you can automate the user creation process and avoid errors that might arise from manual repetition. This can be especially useful if you have a large number of users to add, or if you need to regularly add and remove users based on certain conditions.

1function add_mysql_user {
2    local username=$1
3    local password=$2
4    local host=$3
5    mysql -u root -p -e "CREATE USER '$username'@'$host' IDENTIFIED BY '$password'; GRANT ALL PRIVILEGES ON *.* TO '$username'@'$host' WITH GRANT OPTION;"
6}

You could put different grant options in there as well:

GRANT SELECT ON *.* TO '$username'@'$host to grant only the SELECT query permission.

You could make the GRANT OPTION a function argument as well:

1function add_mysql_user {
2    local username=$1
3    local password=$2
4    local host=$3
5    local $permission=$4
6    mysql -u root -p -e "CREATE USER '$username'@'$host' IDENTIFIED BY '$password'; GRANT $permission ON *.* TO '$username'@'$host';"
7}

You can easily call the bash function like:

$ add_mysql_user user password host

or

$ add_mysql_user user password host SELECT

Think of anything you can put into a bash function so you can reduce your typing a bit. Thanks for reading.