Learn about Centmin Mod LEMP Stack today
Become a Member

MariaDB Adding database to an existing user

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by JoeDer, Jan 27, 2019.

  1. JoeDer

    JoeDer Premium Member Premium Member

    49
    11
    8
    Feb 22, 2015
    Ratings:
    +34
    Local Time:
    8:27 AM
    Nginx 1.17.x
    MariaDB 10.3.x
    • CentOS Version: CentOS 7 64bit ?
    • Centmin Mod Version Installed: 123.09beta01
    • Nginx Version Installed: 1.15.8
    • PHP Version Installed: 7.2.14
    • MariaDB MySQL Version Installed: 10.1.37
    • When was last time updated Centmin Mod code base ? : 26 Jan 2019
    • Persistent Config:
    Code:
    LETSENCRYPT_DETECT='y'
    NGINX_WEBDAV='y'
    NGINX_IPV='y'
    NGINX_MP4='y'
    PHPFINFO='y'
    PHP_PGO='y
    
    Well, I created a new user and database via mysqladmin_shell.sh script and I want to add another database to this user, I run again mysqladmin_shell.sh script and I choose not to create a new user with n (No) but to add a new database to the existing user that I created before, after that I get the following:

    Code:
    ERROR 1133 (28000) at line 1: Can't find any matching row in the user table
    
    Error: command was unsuccessful
    
    Untitled.png

    Is it something that I'm doing wrong?
     
    • Informative Informative x 1
  2. eva2000

    eva2000 Administrator Staff Member

    42,269
    9,550
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,708
    Local Time:
    4:27 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    ah you found a bug in addons/mysqladmin_shell.sh which i just updated Centmin Mod 123.09beta01 with a fix for. Just run via SSH the command = cmupdate to update your code and retry, you'll need to delete the previously tried mysql database name which failed as that was created just without assigning to the mysql user.

    Code (Text):
    cmupdate
    Saved working directory and index state WIP on 123.09beta01: cd9fab2 update PHP-FPM systemd integration support for CentOS 7 in 123.09beta01
    HEAD is now at cd9fab2 update PHP-FPM systemd integration support for CentOS 7 in 123.09beta01
    remote: Enumerating objects: 7, done.
    remote: Counting objects: 100% (7/7), done.
    remote: Compressing objects: 100% (1/1), done.
    remote: Total 4 (delta 3), reused 4 (delta 3), pack-reused 0
    Unpacking objects: 100% (4/4), done.
    From https://github.com/centminmod/centminmod
       cd9fab2..8f3c562  123.09beta01 -> origin/123.09beta01
    Updating cd9fab2..8f3c562
    Fast-forward
     addons/mysqladmin_shell.sh | 6 +++---
     1 file changed, 3 insertions(+), 3 deletions(-)
    

    example fixed run
    Code (Text):
    addons/mysqladmin_shell.sh setuserdb       
    --------------------------------------------------------------
    Basic MySQL Admin - create mysql user & databases 
    --------------------------------------------------------------
    --------------------------------------------------------------
    
     Do you want to create a new MySQL username (type = y) or 
     Add a new database name to existing MySQL username (type = n) ? 
     Enter y or n: n
    -------------------------------------------------------------------------
    Add new database name to existing MySQL username:
    -------------------------------------------------------------------------
     Enter existing MySQL username you want to add new database name to: dbuser1
    ---------------------------------
    Create MySQL database:
    ---------------------------------
     Enter new MySQL database name: dba3
    
    Grants for [email protected]
    GRANT USAGE ON *.* TO 'dbuser1'@'localhost' IDENTIFIED BY PASSWORD '*7AE9613C8ACDAB89E8D213238B2AF1BBF251FBAA'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `db1`.* TO 'dbuser1'@'localhost'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `dba3`.* TO 'dbuser1'@'localhost'
    
    Ok: New MySQL database: dba3 assigned to existing MySQL user: dbuser1
    
     
    • Like Like x 1
    • Winner Winner x 1
  3. JoeDer

    JoeDer Premium Member Premium Member

    49
    11
    8
    Feb 22, 2015
    Ratings:
    +34
    Local Time:
    8:27 AM
    Nginx 1.17.x
    MariaDB 10.3.x
    Thank you George for this quick fix!! it's ok now :)
     
    • Like Like x 1
  4. eva2000

    eva2000 Administrator Staff Member

    42,269
    9,550
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,708
    Local Time:
    4:27 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    Glad to hear and thanks for helping squashing another bug :D
     
    • Like Like x 1
  5. pamamolf

    pamamolf Premium Member Premium Member

    3,551
    343
    83
    May 31, 2014
    Ratings:
    +658
    Local Time:
    8:27 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    What about to have an option to add a new user to an existing database?
     
  6. eva2000

    eva2000 Administrator Staff Member

    42,269
    9,550
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,708
    Local Time:
    4:27 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    what usage case do you have that requires that ? in 19 yrs of using web servers, I haven't once had to add more than one mysql user to the same database :)
     
  7. pamamolf

    pamamolf Premium Member Premium Member

    3,551
    343
    83
    May 31, 2014
    Ratings:
    +658
    Local Time:
    8:27 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    A friend is using Centminmod and he is using the mysql root user for his platform to connect to the database.

    I just found that when i was trying to help him with something simple...

    So creating a non root user and attach it to the platform config will be better :)

    I can do it manually or by using Phpmyadmin but i think it will be useful to have it on that tool as we can avoid any mistakes trying to do that and we will avoid the Phpmyadmin installation ...

    Thanks
     
  8. eva2000

    eva2000 Administrator Staff Member

    42,269
    9,550
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,708
    Local Time:
    4:27 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    I see. Yeah in that case probably could add such to existing addons/mysqladmin_shell.sh at https://community.centminmod.com/threads/mysqladmin_shell-sh-shell-based-addon.543/

    could be a new variation of setuserdb option that exists already.
    Code (Text):
    /usr/local/src/centminmod/addons/mysqladmin_shell.sh
    
    /usr/local/src/centminmod/addons/mysqladmin_shell.sh {multidb|setglobaluser|setuserdb|setpass|createuserdb|deluser|showgrants}
    
    multidb - multiple mysql databse/user creation mode passing a file name containing db, user, pass 3 column entries
    setglobaluser - create a mysql username with access to all databases on server without SUPER ADMIN privileges (non-root)
    setuserdb - create individual mysql username and databases or assign a new database to an existing mysql username
    setpass - change mysql username password
    createuserdb - unattended create individual mysql username & databases fields required are dbname dbuser dbpass
    deluser - delete a mysql usernames
    showgrants - show existing mysql username granted privileges
     
    • Like Like x 1
  9. pamamolf

    pamamolf Premium Member Premium Member

    3,551
    343
    83
    May 31, 2014
    Ratings:
    +658
    Local Time:
    8:27 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    Yes that will be great !

    As we have deluser then maybe something like adduser :)

    Code:
    adduser - add a mysql user to an existing database
    Thank you !
     
  10. eva2000

    eva2000 Administrator Staff Member

    42,269
    9,550
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +14,708
    Local Time:
    4:27 PM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    thinking of changing mysqladmin_shell.sh's setuserdb option to change

    from
    Code (Text):
     Do you want to create a new MySQL username (type = y) or
     Add a new database name to existing MySQL username (type = n) ?
     Enter y or n: 

    to
    Code (Text):
     1. Create a new MySQL username & new MySQL database
     2. Add a new database name to existing MySQL username
     3. Add a existing database name to existing MySQL username
     Enter option number 1-3: 

    or
    Code (Text):
     1. Create a new MySQL username & new MySQL database 
     2. Add a new database name to existing MySQL username 
     3. Add a existing database name to existing MySQL username 
     4. Add a existing database name to new MySQL username 
     Enter option number 1-4: 
    
     
    Last edited: Dec 4, 2019 at 3:17 PM
  11. pamamolf

    pamamolf Premium Member Premium Member

    3,551
    343
    83
    May 31, 2014
    Ratings:
    +658
    Local Time:
    8:27 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    Yes it will be much better to change the setuserdb option !

    I prefer this one:
    Code:
     1. Create a new MySQL username & new MySQL database
     2. Add a new database name to existing MySQL username
     3. Add a existing database name to existing MySQL username
     4. Add a existing database name to new MySQL username
     Enter option number 1-4: 
    Thanks