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?

4
  • 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 Oct 29, 2017 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, 2017 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. Oct 29, 2017 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. Oct 31, 2017 at 16:25

1 Answer 1

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.

4
  • Thank you dearly. Why did you use echo? Nov 6, 2017 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, 2017 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. Nov 6, 2017 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, 2017 at 1:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .