WP-CLI and database users



  • So WP-CLI is awesome. I love it. but I want to spin up multiple sites on a a single Fedora server.

    Putting everything in different sub-directories is simple enough for the wp core download.

    But what do I do about the database creation. The mariadb root password is known. but I don't want that as the application db user.
    https://developer.wordpress.org/cli/commands/db/cli/

    The wp cli instructions don't seem to show a way to use the root account to create the database and application account.



  • So, reading more, things just say the WP DB user.. Well I don't have only one DB user, and I don't want only one.

    I guess I need to make the DB and users without WP CLI first and then drop to WP CLI?



  • @JaredBusch said in WP-CLI and database users:

    The wp cli instructions don't seem to show a way to use the root account to create the database and application account.

    I don't believe that they do, I think you have to do that manually (or separately at least) and feed the resulting account info into WP-CLI.



  • @scottalanmiller said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    The wp cli instructions don't seem to show a way to use the root account to create the database and application account.

    I don't believe that they do, I think you have to do that manually (or separately at least) and feed the resulting account info into WP-CLI.

    Which is what I did last night to get the task done.

    I get that this is a fringe case. Anyone setting up like this already has full access to all of the instances.

    Mostly I want everything separate just in case I ever want to migrate to something else for one of the sites.



  • So here is a good question. What kind of permissions do I need to setup on a MySQL account to let it have access to create new databases.



  • @JaredBusch said in WP-CLI and database users:

    So here is a good question. What kind of permissions do I need to setup on a MySQL account to let it have access to create new databases.

    You can view permissions here:
    https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#privileges-provided-summary

    Usually those that can create new databases have full access, aka ALL PRIVILEGES.
    That means they can do everything except grant others the same access.

    If you just want to be able to create databases and tables, it's the CREATE privilege you need.



  • @JaredBusch said in WP-CLI and database users:

    Mostly I want everything separate just in case I ever want to migrate to something else for one of the sites.

    Why not skip the just in case part and start with them as separate sites to begin with?



  • @JaredBusch check out WordOps https://wordops.net/





  • ok a better example of my issue with WP-CLI and the database....

    in my new guide to standing up WP I have this step.
    4e9669da-90ce-4b44-85f9-66cae2ab5d9d-image.png

    I think this is a horrible solution. In order to let wp-cli create the dataabse, the WP DB user have to have the ability to actually CREATE a database.

    So unlike more normal guides where I tell you to create a DB user with GRANT ALL only on the database it needs (dbname.*), I had to create it with GRANT ALL on everything (*.*).

    I guess, I could refrain from setting the root password stuff until the very end and first revoke these permission and then re-add with only GRANT ALL on the specific database.

    But that just seems stupidly clunky.



  • @JaredBusch said in WP-CLI and database users:

    ok a better example of my issue with WP-CLI and the database....

    in my new guide to standing up WP I have this step.
    4e9669da-90ce-4b44-85f9-66cae2ab5d9d-image.png

    I think this is a horrible solution. In order to let wp-cli create the dataabse, the WP DB user have to have the ability to actually CREATE a database.

    So unlike more normal guides where I tell you to create a DB user with GRANT ALL only on the database it needs (dbname.*), I had to create it with GRANT ALL on everything (*.*).

    I guess, I could refrain from setting the root password stuff until the very end and first revoke these permission and then re-add with only GRANT ALL on the specific database.

    But that just seems stupidly clunky.

    I think you are mistaken. Just grant the privileges to the database you are about to create

     sudo mysql -e "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    

    then you can do the wp-cli stuff like wp config create, wp db create etc.



  • @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    ok a better example of my issue with WP-CLI and the database....

    in my new guide to standing up WP I have this step.
    4e9669da-90ce-4b44-85f9-66cae2ab5d9d-image.png

    I think this is a horrible solution. In order to let wp-cli create the dataabse, the WP DB user have to have the ability to actually CREATE a database.

    So unlike more normal guides where I tell you to create a DB user with GRANT ALL only on the database it needs (dbname.*), I had to create it with GRANT ALL on everything (*.*).

    I guess, I could refrain from setting the root password stuff until the very end and first revoke these permission and then re-add with only GRANT ALL on the specific database.

    But that just seems stupidly clunky.

    I think you are mistaken. Just grant the privileges to the database you are about to create

     sudo mysql -e "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    

    then you can do the wp-cli stuff like wp config create, wp db create etc.

    My results on a google search were unclear. So I went assuming no as that is illogical to me.

    But wouldn't be the first time something illogical to me was fact.



  • @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    ok a better example of my issue with WP-CLI and the database....

    in my new guide to standing up WP I have this step.
    4e9669da-90ce-4b44-85f9-66cae2ab5d9d-image.png

    I think this is a horrible solution. In order to let wp-cli create the dataabse, the WP DB user have to have the ability to actually CREATE a database.

    So unlike more normal guides where I tell you to create a DB user with GRANT ALL only on the database it needs (dbname.*), I had to create it with GRANT ALL on everything (*.*).

    I guess, I could refrain from setting the root password stuff until the very end and first revoke these permission and then re-add with only GRANT ALL on the specific database.

    But that just seems stupidly clunky.

    I think you are mistaken. Just grant the privileges to the database you are about to create

     sudo mysql -e "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    

    then you can do the wp-cli stuff like wp config create, wp db create etc.

    My results on a google search were unclear. So I went assuming no as that is illogical to me.

    But wouldn't be the first time something illogical to me was fact.

    Well, it's very logical to me. And I just confirmed on MariaDB 10.1.44 that it works the way I thought it did.

    Privileges are dependent on object names and not a particular object. Therefor the rights to something on a database, table or whatever can be created before that something exists.

    Or in this case, give the right to a user to create a database before that database exists.



  • @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    ok a better example of my issue with WP-CLI and the database....

    in my new guide to standing up WP I have this step.
    4e9669da-90ce-4b44-85f9-66cae2ab5d9d-image.png

    I think this is a horrible solution. In order to let wp-cli create the dataabse, the WP DB user have to have the ability to actually CREATE a database.

    So unlike more normal guides where I tell you to create a DB user with GRANT ALL only on the database it needs (dbname.*), I had to create it with GRANT ALL on everything (*.*).

    I guess, I could refrain from setting the root password stuff until the very end and first revoke these permission and then re-add with only GRANT ALL on the specific database.

    But that just seems stupidly clunky.

    I think you are mistaken. Just grant the privileges to the database you are about to create

     sudo mysql -e "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    

    then you can do the wp-cli stuff like wp config create, wp db create etc.

    My results on a google search were unclear. So I went assuming no as that is illogical to me.

    But wouldn't be the first time something illogical to me was fact.

    Well, it's very logical to me. And I just confirmed on MariaDB 10.1.44 that it works the way I thought it did.

    Privileges are dependent on object names and not a particular object. Therefor the rights to something on a database, table or whatever can be created before that something exists.

    Well that works for me. I'll update the guide in a bit and then run through a new deployment as a test.



  • @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    ok a better example of my issue with WP-CLI and the database....

    in my new guide to standing up WP I have this step.
    4e9669da-90ce-4b44-85f9-66cae2ab5d9d-image.png

    I think this is a horrible solution. In order to let wp-cli create the dataabse, the WP DB user have to have the ability to actually CREATE a database.

    So unlike more normal guides where I tell you to create a DB user with GRANT ALL only on the database it needs (dbname.*), I had to create it with GRANT ALL on everything (*.*).

    I guess, I could refrain from setting the root password stuff until the very end and first revoke these permission and then re-add with only GRANT ALL on the specific database.

    But that just seems stupidly clunky.

    I think you are mistaken. Just grant the privileges to the database you are about to create

     sudo mysql -e "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    

    then you can do the wp-cli stuff like wp config create, wp db create etc.

    My results on a google search were unclear. So I went assuming no as that is illogical to me.

    But wouldn't be the first time something illogical to me was fact.

    Well, it's very logical to me. And I just confirmed on MariaDB 10.1.44 that it works the way I thought it did.

    Privileges are dependent on object names and not a particular object. Therefor the rights to something on a database, table or whatever can be created before that something exists.

    Well that works for me. I'll update the guide in a bit and then run through a new deployment as a test.

    Great. I like the guides you post, they are very well thought out. 👍



  • @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    ok a better example of my issue with WP-CLI and the database....

    in my new guide to standing up WP I have this step.
    4e9669da-90ce-4b44-85f9-66cae2ab5d9d-image.png

    I think this is a horrible solution. In order to let wp-cli create the dataabse, the WP DB user have to have the ability to actually CREATE a database.

    So unlike more normal guides where I tell you to create a DB user with GRANT ALL only on the database it needs (dbname.*), I had to create it with GRANT ALL on everything (*.*).

    I guess, I could refrain from setting the root password stuff until the very end and first revoke these permission and then re-add with only GRANT ALL on the specific database.

    But that just seems stupidly clunky.

    I think you are mistaken. Just grant the privileges to the database you are about to create

     sudo mysql -e "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    

    then you can do the wp-cli stuff like wp config create, wp db create etc.

    My results on a google search were unclear. So I went assuming no as that is illogical to me.

    But wouldn't be the first time something illogical to me was fact.

    Well, it's very logical to me. And I just confirmed on MariaDB 10.1.44 that it works the way I thought it did.

    Privileges are dependent on object names and not a particular object. Therefor the rights to something on a database, table or whatever can be created before that something exists.

    Well that works for me. I'll update the guide in a bit and then run through a new deployment as a test.

    Great. I like the guides you post, they are very well thought out. 👍

    does not like it.

    [[email protected] ~]$ # sudo mysql -e "GRANT ALL ON *.* TO '$DB_USER'@'localhost';"
    [[email protected] ~]$ sudo mysql -e "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''ziiCh6geiqu6'.* TO 'eitaethie9cahX7u'@'localhost'' at line 1
    [[email protected] ~]$ sudo mysql -e "FLUSH PRIVILEGES;"
    

    But no issue with the wildcard.

    [[email protected] ~]$ sudo mysql -e "GRANT ALL ON *.* TO '$DB_USER'@'localhost';"
    [[email protected] ~]$ sudo mysql -e "FLUSH PRIVILEGES;"
    [[email protected] ~]$ 
    
    

    3087daf4-ceeb-4114-a32e-f49843820222-image.png



  • @Pete-S well WTF... Even after the DB exists..

    The DB_NAME is correct.....

    [[email protected] html]$ sudo mysql -e -uroot -p$DB_ROOT_PASS "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
    ERROR 1049 (42000): Unknown database 'GRANT ALL ON 'ziiCh6geiqu6'.* TO 'eitaethie9cahX7u'@'localhost';'
    [[email protected] html]$ sudo mysql -e -uroot -p$DB_ROOT_PASS "GRANT ALL ON $DB_NAME.* TO '$DB_USER'@'localhost';"
    ERROR 1049 (42000): Unknown database 'GRANT ALL ON ziiCh6geiqu6.* TO 'eitaethie9cahX7u'@'localhost';'
    [[email protected] html]$ cat wp-config.php | grep DB_NAME
    define( 'DB_NAME', 'ziiCh6geiqu6' );
    [[email protected] html]$ 
    


  • @JaredBusch said in WP-CLI and database users:

    @Pete-S well WTF... Even after the DB exists..

    The DB_NAME is correct.....

    I don't understand why. What happens if you just login and do it manually? Do you get the same result?

    "Unknown database". To me that is related to the default database set in mysql sessions. Do you have a USE command somewhere?

    Best action might be to test all the SQL commands manually instead of from the script.



  • @Pete-S said in WP-CLI and database users:

    Do you have a USE command somewhere?

    No, because when the script runs originally, there is no exisiting database to use.



  • Alright, after some troubleshooting. The line on your script should be:

    sudo mysql -e "GRANT ALL ON $DB_NAME.* TO '$DB_USER'@'localhost';"

    No ' around $DB_NAME. That gives you a syntax error.

    Also since DB_USER doesn't contain spaces and neither does localhost, you don't need any ' around those either (but it doesn't cause any syntax errors).

    And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

    sudo mysql -uroot -p$DB_ROOT_PASS -e "CREATE USER [email protected] IDENTIFIED by '$DB_PASS';"
    sudo mysql -uroot -p$DB_ROOT_PASS -e "GRANT ALL ON $DB_NAME.* TO [email protected];"
    sudo mysql -uroot -p$DB_ROOT_PASS -e "FLUSH PRIVILEGES;"
    

    I'm sure you know but you can also put more than one command in the execute string. ; is what separates the commands.
    Or put the SQL in a file.
    For instance:

    sudo mysql -uroot -p$DB_ROOT_PASS -e "GRANT ALL ON $DB_NAME.* TO [email protected]; FLUSH PRIVILEGES;"


  • @Pete-S said in WP-CLI and database users:

    And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

    That was a once off artifact of me doing it on this system after the root password has been set.

    That was also the reason it did not work for me as I tried without the '



  • @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

    That was a once off artifact of me doing it on this system after the root password has been set.

    OK, so maybe this then:

    sudo mysql -e "CREATE USER [email protected] IDENTIFIED by '$DB_PASS';"
    sudo mysql -e "GRANT ALL ON $DB_NAME.* TO [email protected];"
    sudo mysql -e "FLUSH PRIVILEGES;"


  • @Pete-S said in WP-CLI and database users:

    I'm sure you know but you can also put more than one command in the execute string. ; is what separates the commands.

    My guides are specifically wrote lik this to clearly separate the commands that are used for the people following my guides to see every thing they are doing.



  • @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

    That was a once off artifact of me doing it on this system after the root password has been set.

    OK, so maybe this then:

    sudo mysql -e "CREATE USER [email protected] IDENTIFIED by '$DB_PASS';"
    sudo mysql -e "GRANT ALL ON $DB_NAME.* TO [email protected];"
    sudo mysql -e "FLUSH PRIVILEGES;"
    

    right. Updating the guide. but half tempted to leave the single quotes everywhere it that causes no error in order to protect against spaces by others. Though I am using pwgen to do this.



  • Guide updated. I left the ' everywhere else.

    Of note, I had to drop the DB_NAME to 16 characters because it broke it longer. Even though current mariadb should accept much longer database names according to my quick google on the subject.



  • @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    @JaredBusch said in WP-CLI and database users:

    @Pete-S said in WP-CLI and database users:

    And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

    That was a once off artifact of me doing it on this system after the root password has been set.

    OK, so maybe this then:

    sudo mysql -e "CREATE USER [email protected] IDENTIFIED by '$DB_PASS';"
    sudo mysql -e "GRANT ALL ON $DB_NAME.* TO [email protected];"
    sudo mysql -e "FLUSH PRIVILEGES;"
    

    right. Updating the guide. but half tempted to leave the single quotes everywhere it that causes no error in order to protect against spaces by others. Though I am using pwgen to to this.

    It's kind of f*cked up to have spaces in user names and passwords. Personally I don't use something unless it's specifically needed but either way works.