Want more timely Centmin Mod News Updates?
Become a Member

mysqladmin_shell.sh Shell based Addon

Discussion in 'Add Ons' started by eva2000, Jun 23, 2014.

  1. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
    We have phpmyadmin.sh Addon for Centmin Mod already.

    How about shell based script to do some simple tasks just for creating new MySQL users/passwords, creating new MySQL databases and assigning MySQL permissions, changing MySQL user passwords and deleting MySQL users ?

    This is what mysqladmin_shell.sh does and is early beta so you might want to test it on test VPS server before using it on live production servers :)

    Note: Added to Centmin Mod .08 beta 03



    mysqladmin_shell.sh has now been added to /addons/mysqladmin_shell.sh folder for Centmin Mod .08 beta03 and higher releases. So do not need to download via github.com

    Download via github.com
    Code (Text):
    mkdir -p /root/tools
    cd /root/tools/
    wget -O mysqladmin_shell.sh https://github.com/centminmod/centminmod/raw/123.09beta01/addons/mysqladmin_shell.sh
    chmod +x mysqladmin_shell.sh
    


    Basic usage
    Code (Text):
    /root/tools/mysqladmin_shell.sh {setuserdb|setpass|deluser|showgrants}

    Create MySQL user/pass and MySQL database and grant privileges to database
    Code (Text):
    /root/tools/mysqladmin_shell.sh setuserdb

    Change existing MySQL user's password
    Code (Text):
    /root/tools/mysqladmin_shell.sh setpass

    Delete MySQL user
    Code (Text):
    /root/tools/mysqladmin_shell.sh deluser

    Show existing MySQL user's privileges via Show Grants
    Code (Text):
    /root/tools/mysqladmin_shell.sh showgrants


    Screenshots below

    mysqladmin_shell_01.png mysqladmin_shell_02.png mysqladmin_shell_03.png


    Multiple DB Creation



    Updated addons/mysqladmin_shell.sh with multiple database creation support from a specified text file. Also fixed root prompt as it is not needed with .08 beta03+ and higher as /root/.my.cnf has a record of the mysql root password which is auto generated on initial Centmin Mod install

    For example, contents of /home/dblist.txt - one entry per line in format order of
    Code (Text):
    dbname dbusername dbpassword
    db2name db2username db2password
    


    run new multidb command option and enter path of dblist.txt file i.e. /home/dblist.txt
    Code (Text):
    ./addons/mysqladmin_shell.sh multidb
    --------------------------------------------------------------
    Basic MySQL Admin - create mysql user & databases
    --------------------------------------------------------------
    --------------------------------------------------------------
    
    ----------------------------------------------------------------------------
    Create Multiple MySQL Databases, User & Pass From specified filepath/name
    i.e. /home/nginx/domains/domain.com/dbfile.txt
    One entry per line in dbfile.txt in format of:
    databasename databaseuser databasepass
    ----------------------------------------------------------------------------
    
    Enter full path to db list file i.e. /home/nginx/domains/domain.com/dbfile.txt (to exit type = x): /home/dblist.txt
    
    Grants for dbusername@localhost
    GRANT USAGE ON *.* TO 'dbusername'@'localhost' IDENTIFIED BY PASSWORD '*6207EE3F049D8F87CC0B4BBB8814917B31FB8E4F'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `dbname`.* TO 'dbusername'@'localhost'
    
    Ok: MySQL user:  MySQL database: dbname created successfully
    
    Grants for db2username@localhost
    GRANT USAGE ON *.* TO 'db2username'@'localhost' IDENTIFIED BY PASSWORD '*59F34589CBECBF3AB16AA51EA7E7C1D8DF068A76'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `db2name`.* TO 'db2username'@'localhost'
    
    Ok: MySQL user:  MySQL database: db2name created successfully


    New Global MySQL User Creation Option



    September 20th, 2016: Updated Centmin Mod 123.09beta01's addons/mysqladmin_shell.sh with new menu option = setglobaluser to create a MySQL username with has access to all databases on server but without SUPER ADMIN (root) privileges.

    Centmin Mod 123.09beta01's addons/mysqladmin_shell.sh available options
    Code (Text):
    ./mysqladmin_shell.sh {multidb|setglobaluser|setuserdb|setpass|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
    deluser - delete a mysql usernames
    showgrants - show existing mysql username granted privileges

    create MySQL username = dbu1 global user with password dbp1
    Code (Text):
    ./mysqladmin_shell.sh setglobaluser
    --------------------------------------------------------------
    Basic MySQL Admin - create mysql user & databases
    --------------------------------------------------------------
    --------------------------------------------------------------
    
    
    Create a MySQL Username that has access to all Databases
    But without SUPER ADMIN privileges
    
     Enter new MySQL username you want to create: dbu1
     Enter new MySQL username's password: dbp1
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for dbu1@localhost                                                                                                                                                                                |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'dbu1'@'localhost' IDENTIFIED BY PASSWORD '*5D00BE915D6AB01172C807300ED23E31F251C24F' |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    
    Ok: MySQL global user: dbu1 created successfully
    
    


    mysqladmin_shell.sh createuserdb Added



    New mysqladmin_shell.sh option has been added for createuserdb option. This allows unattended creating of a mysql database + mysql username + mysql password without any question prompts. Suited for scripted setups where you want to create mysql database, user and password on the command line.

    Full addons/mysqladmin_shell.sh options

    Code (Text):
    ./mysqladmin_shell.sh
    
    ./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
    

    Example for createuserdb run where you pass on command line the database name, mysql username and mysql password - in this example db name = xdb1, db user = xdbuser1 and db pass = xdbpass1
    Code (Text):
    ./mysqladmin_shell.sh createuserdb xdb1 xdbuser1 xdbpass1
    --------------------------------------------------------------
    Basic MySQL Admin - create mysql user & databases
    --------------------------------------------------------------
    --------------------------------------------------------------
    
    Grants for xdb1@localhost
    GRANT USAGE ON *.* TO 'xdb1'@'localhost' IDENTIFIED BY PASSWORD '*6D7525873C093916CA45CF6A9F12142EFC4C5F38'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `createuserdb`.* TO 'xdb1'@'localhost'
    
    Ok: MySQL user: xdb1 MySQL database: createuserdb created successfully
    

    This is opposed to using the old setuserdb command which creates the mysql database name, db user and db pass with question prompts interactively below example created mysql dbname = ydb1, db user = ydbuser1 and db pass = ydbpass1
    Code (Text):
    ./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: y
    ---------------------------------
    Create MySQL username:
    ---------------------------------
     Enter new MySQL username you want to create: ydbuser1
     Enter new MySQL username's password: ydbpass1
    ---------------------------------
    Create MySQL database:
    ---------------------------------
     Enter new MySQL database name: ydb1
    
    Grants for ydbuser1@localhost
    GRANT USAGE ON *.* TO 'ydbuser1'@'localhost' IDENTIFIED BY PASSWORD '*34CE4FFBB62702263F1970A79FB0BA6CF651489B'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `ydb1`.* TO 'ydbuser1'@'localhost'
    
    Ok: MySQL user: ydbuser1 MySQL database: ydb1 created successfully
    


    Updates



    Notes



    Reminder when creating database names make sure you do not use hyphens on invalid identifier characters in the names. For database names and tables, hyphens aren't really a valid mysql identifiers allowed MySQL :: MySQL 5.6 Reference Manual :: 9.2 Schema Object Names. Underscores yes, hyphens no.
     
    Last edited: May 8, 2017
  2. GhoHan

    GhoHan Member

    33
    14
    8
    Jun 1, 2014
    Ratings:
    +14
    Local Time:
    6:34 PM
    Nginx 1.13.X
    MariaDB 10.1.X
    thanks so much george.
     
  3. pamamolf

    pamamolf Well-Known Member

    4,003
    420
    83
    May 31, 2014
    Ratings:
    +812
    Local Time:
    2:34 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Do i have to install already Phpmyadmin to get this work?

    And how can i download this from ssh as .sh file as i can't see a link related or even a zip?

    Thanks
     
  4. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
    it's a standalone file so no need for phpmyadmin.sh

    just download it - i like to put all my custom .sh files in /root/tools

    Code:
    mkdir -p /root/tools
    cd /root/tools/
    wget -O mysqladmin_shell.sh https://github.com/centminmod/centminmod/raw/master/addons/mysqladmin_shell.sh
    chmod +x mysqladmin_shell.sh
    
     
    Last edited: Dec 22, 2015
  5. pamamolf

    pamamolf Well-Known Member

    4,003
    420
    83
    May 31, 2014
    Ratings:
    +812
    Local Time:
    2:34 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Can you please explain why is asking always if i have set a root password?

    What exactly is this and why you select always no in the screenshots?
     
  6. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
    Granting privileges to user accounts and databases requires grant privileges and only mysql root user has those privileges by default. So the commands need mysql root user and if password is set for mysql root user it needs to pass the password when command is run.

    Set to n in screenshots, because .07+ and higher have mysql root password set with /root/.my.cnf file which has the mysql root password so no need to pass mysql root password on command line. But if you remove /root/.my.cnf and change your mysql root password, then you'd have to answer yes when prompted and enter the mysql root password
     
  7. pamamolf

    pamamolf Well-Known Member

    4,003
    420
    83
    May 31, 2014
    Ratings:
    +812
    Local Time:
    2:34 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    It is confusing a bit the "set" in the question as with my poor english i understand that is asking if i have set on the database in general or in the mycnf file the password and in both cases the answer is yes.
     
  8. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
  9. sepulchre

    sepulchre Member

    167
    22
    18
    Dec 22, 2014
    Ratings:
    +28
    Local Time:
    1:34 PM
    Can I use this for restoring my database? And how do I connect my Xenforo installation with this database? Or should I use the other add-on?
     
  10. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
    No this addon is for creating the initial mysql username and password and assigning it (connecting it) to a newly created mysql database only which is info you add to Xenforo config.php. For backup and restore use included inbuilt tools for MySQL mysqldump/mysql command line MySQL :: MySQL 5.5 Reference Manual :: 7 Backup and Recovery and various tutorials MySQL :: MySQL 5.5 Reference Manual :: 3 Tutorial

    mysqldump - MySQL :: MySQL 5.5 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
    You can use phpmyadmin to backup and restore but once your database size grows, reliability of phpmyadmin for complete backups diminishes and the chance of php time outs and incomplete backup sql files grows. You could end up with corrupted or incomplete backup sql files for MySQL datbaases with phpmyadmin and no avenue to restore a fully working backup. Safest is mysqldump for backup and mysql command for restores.
     
    Last edited: Dec 29, 2014
  11. sepulchre

    sepulchre Member

    167
    22
    18
    Dec 22, 2014
    Ratings:
    +28
    Local Time:
    1:34 PM
    Ah, ok.
    I am lost a bit so I have a question.

    What is the most basic and easy way to restore my current database for my Xenforo forum? Can you tell me the steps I have to do?

    edit: I think I will try phpmyadmin.
     
    Last edited: Dec 29, 2014
  12. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
  13. sepulchre

    sepulchre Member

    167
    22
    18
    Dec 22, 2014
    Ratings:
    +28
    Local Time:
    1:34 PM
    Yeah, I have seen the mysqldump method. Thank you. It seems most of the people are using this method.
    Just, I am not sure how you tell your forum (for example Xenforo), that it sees the restored database as its own database?
    How do you connect the database with your forum? Or is uploading enough? It will automatically connect both with each other?
    Don't I need to configure somewhere something like "Hey, Xenforo, this 1234_database is your database now."
    So, I went with phpmyadmin, but there is a problem, you see it in the other thread. But I while try out this method, too.
     
  14. Mask

    Mask Active Member

    108
    31
    28
    Nov 10, 2014
    Ratings:
    +37
    Local Time:
    4:34 PM
    Nginx 1.9.1
    MariaDB 10.0.19
    This tool is so awesome that it should be a part of standard CMM install. (My vote goes for it)
    May be just skip the root password check. I guess we all have /root/.my.cnf file set for our servers.
     
  15. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
    Last edited: Jun 13, 2015
  16. Mask

    Mask Active Member

    108
    31
    28
    Nov 10, 2014
    Ratings:
    +37
    Local Time:
    4:34 PM
    Nginx 1.9.1
    MariaDB 10.0.19
    You are awesome :)
     
  17. eva2000

    eva2000 Administrator Staff Member

    49,877
    11,487
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +17,839
    Local Time:
    9:34 PM
    Nginx 1.21.x
    MariaDB 10.x
    Updated addons/mysqladmin_shell.sh with multiple database creation support from a specified text file. Also fixed root prompt as it is not needed with .08 beta03+ and higher as /root/.my.cnf has a record of the mysql root password which is auto generated on initial Centmin Mod install

    For example, contents of /home/dblist.txt - one entry per line in format order of
    Code:
    dbname dbusername dbpassword
    db2name db2username db2password
    
    run new multidb command option and enter path of dblist.txt file i.e. /home/dblist.txt
    Code:
    ./addons/mysqladmin_shell.sh multidb
    --------------------------------------------------------------
    Basic MySQL Admin - create mysql user & databases
    --------------------------------------------------------------
    --------------------------------------------------------------
    
    ----------------------------------------------------------------------------
    Create Multiple MySQL Databases, User & Pass From specified filepath/name
    i.e. /home/nginx/domains/domain.com/dbfile.txt
    One entry per line in dbfile.txt in format of:
    databasename databaseuser databasepass
    ----------------------------------------------------------------------------
    
    Enter full path to db list file i.e. /home/nginx/domains/domain.com/dbfile.txt (to exit type = x): /home/dblist.txt
    
    Grants for dbusername@localhost
    GRANT USAGE ON *.* TO 'dbusername'@'localhost' IDENTIFIED BY PASSWORD '*6207EE3F049D8F87CC0B4BBB8814917B31FB8E4F'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `dbname`.* TO 'dbusername'@'localhost'
    
    Ok: MySQL user:  MySQL database: dbname created successfully
    
    Grants for db2username@localhost
    GRANT USAGE ON *.* TO 'db2username'@'localhost' IDENTIFIED BY PASSWORD '*59F34589CBECBF3AB16AA51EA7E7C1D8DF068A76'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `db2name`.* TO 'db2username'@'localhost'
    
    Ok: MySQL user:  MySQL database: db2name created successfully
     
  18. pamamolf

    pamamolf Well-Known Member

    4,003
    420
    83
    May 31, 2014
    Ratings:
    +812
    Local Time:
    2:34 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Can you please help me with the syntax to create a database and a user with a password and grant all privileges for this user on that db?

    e.x:
    Code:
    dbname: database1
    user: datauser1
    pass: 123456
     
  19. ModeltogTossen

    ModeltogTossen I wish I could??

    313
    97
    28
    Dec 20, 2015
    Denmark
    Ratings:
    +143
    Local Time:
    1:34 PM
    1.9.12
    10.0.23
    One way - at shell on server:

    mysql -u root -p <enter>
    enter password <enter>
    create database1;
    grant all privileges on database1.* to datauser1@'%' identified by '123456';
    flush privileges;
    quit;

    The '%' does that the datauser1 is allowed from everywhere.

    Hope this is what you are seeking..
     
  20. pamamolf

    pamamolf Well-Known Member

    4,003
    420
    83
    May 31, 2014
    Ratings:
    +812
    Local Time:
    2:34 PM
    Nginx-1.17.x
    MariaDB 10.3.x
    Thanks for your help but i am looking for syntax for mysqladmin_shell.sh script so i can get all of them in a single line :)