1

I frequently need to do the following things in MySQL:

  1. Create a non-root user
  2. Set that user's password
  3. Grant that user all privileges
  4. Make a database with the same name as the new user
  5. Allow usage only for users from the localhost.

Previously I did this using PHPmyadmin, but I would prefer doing it directly from BASH. Is there a CLI way to execute these steps?

|improve this question
  • 1
    I'm voting to close this question because it appears off topic, and is likely a better fit at serverfault.com or maybe at dba.stackexchange.com – Stephen Rauch Oct 29 '17 at 21:13
  • What exactly are you trying to do? It’s not obvious from my parsing of your question which behavior is desired versus avoided. – Jeff Schaller♦ Oct 29 '17 at 23:00
  • I edited the question. I want to do the same thing I always did with PMA, but from the CLI --- Setting up a new DB user, a DB with the same name as of the user, the users password, and making sure the new user has all privileges. These are the 4 actions I want to do from the CLI. – Arcticooling Oct 29 '17 at 23:29
  • I tried to explain better what I aim to do from the CLI. I hope the question is clear and valid in it's current form. – Arcticooling Oct 31 '17 at 16:25
1

This works for me:

echo 'create database testdb; create user "testdb"@"%" identified by "mypassword"; grant all privileges on testdb.* to testdb;' | mysql -u root -p

The percentage sign indicates that connections to this database may be made from other systems. Replace the % with localhost if you only need an account that needs access to and from the same system.

|improve this answer
  • Thank you dearly. Why did you use echo? – Arcticooling Nov 6 '17 at 16:38
  • Very good question. Depending on how software is written, commands can receive input from files, the keyboard, or something called stdin (standard-in). Echo outputs by default to stdout (standard-out). You can connect the two together with the pipe command (vertical bar). Rather than first creating a text file and then running the text file against the mysql command I opted to do it all in one line. – captcha Nov 6 '17 at 21:52
  • I now understand that we echo what is written before the pipe, and then use the piped output as content for mysql. – Arcticooling Nov 6 '17 at 23:06
  • Correct. It only works for certain programs, and sometimes you may need to provide an additional argument like a dash ( - ) or so, but check the command's man page and it will tell you if and how it can read inputs. – captcha Nov 7 '17 at 1:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.