Welcome to Centmin Mod Community
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 Member

    82
    19
    8
    Feb 22, 2015
    Ratings:
    +48
    Local Time:
    2:50 AM
    Nginx 1.21.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?

     
  2. eva2000

    eva2000 Administrator Staff Member

    54,527
    12,211
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,780
    Local Time:
    10:50 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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 dbuser1@localhost
    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
    
     
  3. JoeDer

    JoeDer Member

    82
    19
    8
    Feb 22, 2015
    Ratings:
    +48
    Local Time:
    2:50 AM
    Nginx 1.21.x
    MariaDB 10.3.x
    Thank you George for this quick fix!! it's ok now :)
     
  4. eva2000

    eva2000 Administrator Staff Member

    54,527
    12,211
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,780
    Local Time:
    10:50 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    Glad to hear and thanks for helping squashing another bug :D
     
  5. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    2:50 AM
    Nginx-1.25.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

    54,527
    12,211
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,780
    Local Time:
    10:50 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    2:50 AM
    Nginx-1.25.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

    54,527
    12,211
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,780
    Local Time:
    10:50 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
     
  9. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    2:50 AM
    Nginx-1.25.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

    54,527
    12,211
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,780
    Local Time:
    10:50 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
  11. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    2:50 AM
    Nginx-1.25.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
     
  12. pamamolf

    pamamolf Premium Member Premium Member

    4,084
    428
    83
    May 31, 2014
    Ratings:
    +834
    Local Time:
    2:50 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    Hello George :)

    Any ETA for this one?
     
  13. eva2000

    eva2000 Administrator Staff Member

    54,527
    12,211
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,780
    Local Time:
    10:50 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    No ETA whenever I have time :)
     
  14. Jon Snow

    Jon Snow Active Member

    835
    170
    43
    Jun 30, 2017
    Ratings:
    +256
    Local Time:
    8:50 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    Was looking for a way to do the same thing as @pamamolf and found this topic.

    Would be useful :singing:
     
  15. Jon Snow

    Jon Snow Active Member

    835
    170
    43
    Jun 30, 2017
    Ratings:
    +256
    Local Time:
    8:50 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    In case others find this topic like me, I ended up using this:
    Code (Text):
    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 MYSQLDATABASE.* TO 'MYSQLUSER'@'localhost';
     
    Last edited by a moderator: Apr 22, 2023
  16. eva2000

    eva2000 Administrator Staff Member

    54,527
    12,211
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,780
    Local Time:
    10:50 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    That works. Though just updated Centmin Mod 130.00beta01's addons/mysqladmin_shell.sh 0.1.2 setuserdb menu option for a 3rd and 4th option for adding an existing database name to existing MySQL username or adding an existing database to a new MySQL username. Full details and updated documentation at https://community.centminmod.com/threads/mysqladmin_shell-sh-shell-based-addon.543/ :)
     
    Last edited: Apr 22, 2023