Learn about Centmin Mod LEMP Stack today
Register Now

mysqladmin_shell.sh Shell based Addon

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

  1. eva2000

    eva2000 Administrator Staff Member

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.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 ?

    mysqladmin_shell.sh is a shell script for managing MySQL users, databases, and permissions. This script provides an easy-to-use interface to perform common tasks such as creating, deleting, or modifying MySQL users and databases.


    Always run command cmupdate to ensure you're on latest Centmin Mod code to get the latest /usr/local/src/centminmod /addons/mysqladmin_shell.sh code.

    Usage Options
    Code (Text):
    ./mysqladmin_shell.sh
    Usage: ./mysqladmin_shell.sh {multidb|setglobaluser|setuserdb|setpass|createuserdb|deluser|showgrants}
    Options:
      multidb
        Multiple MySQL database/user creation mode. Pass a file name containing
        db, user, and pass as 3-column entries.
      setglobaluser
        Create a MySQL username with access to all databases on the server
        without SUPER ADMIN privileges (non-root).
      setuserdb
        Create individual MySQL usernames 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, and dbpass.
      deluser
        Delete MySQL usernames.
      showgrants
        Show existing MySQL username granted privileges.
    

    Available Options
    1. multidb: Create multiple MySQL databases and users by providing a file with the necessary details.
    2. setglobaluser: Create a MySQL user with global access to all databases on the server without SUPER ADMIN privileges.
    3. setuserdb: Create an individual MySQL user and database or assign a new database to an existing MySQL user.
    4. setpass: Change the password of an existing MySQL user.
    5. createuserdb: Create an individual MySQL user and database in an unattended mode.
    6. deluser: Delete an existing MySQL user.
    7. showgrants: Show the granted privileges of an existing MySQL user.
    Usage Examples
    1. multidb
      To create multiple MySQL databases and users using a file i.e. /home/nginx/domains/domain.com/dbfile.txt containing database, user, and password details in three columns, run:
      Code (Text):
      ./mysqladmin_shell.sh multidb /home/nginx/domains/domain.com/dbfile.txt
      

      The input file /home/nginx/domains/domain.com/dbfile.txt should be in the following format:
      Code (Text):
      dbname dbusername dbpassword
      db2name db2username db2password
      

      Example output
      Code (Text):
      ./mysqladmin_shell.sh multidb /home/nginx/domains/domain.com/dbfile.txt
      --------------------------------------------------------------
      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
      ----------------------------------------------------------------------------
      ---------------------------------
      Ok: MySQL user: db2username MySQL database: db2name created successfully
      ---------------------------------
      Ok: MySQL user: dbusername MySQL database: dbname created successfully
      
    2. setglobaluser
      To create a MySQL user with global access to all databases without SUPER ADMIN privileges, run:
      Code (Text):
      ./mysqladmin_shell.sh setglobaluser
      

      Follow the prompts to enter the desired username and password.
      Example output
      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: globalusername
       Enter new MySQL username's password: globalpassword
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Grants for globalusername@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 *.* TO `globalusername`@`localhost` IDENTIFIED BY PASSWORD '*83C5F6453A29A03C47435CEE47906380E12CE3EF' |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      Ok: MySQL global user: globalusername created successfully
      
    3. setuserdb
      To create an individual MySQL user and database or assign a new database to an existing MySQL user, run:
      Code (Text):
      ./mysqladmin_shell.sh setuserdb
      

      Follow the prompts to enter the necessary information.
      As to mysqladmin_shell.sh 0.1.2+, there are now 4 options to choose from with setuserdb option (3rd and 4th option are new in 0.1.2+).
      Code (Text):
      ./mysqladmin_shell.sh setuserdb
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
       1. Create a new MySQL username & new MySQL database 
       2. Add a new database name to existing MySQL username 
       3. Add an existing database name to existing MySQL username 
       4. Add an existing database name to new MySQL username 
       5. Exit 
       Enter option number 1-5: 
      

      Option 1
      Code (Text):
      ./mysqladmin_shell.sh setuserdb
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
       1. Create a new MySQL username & new MySQL database 
       2. Add a new database name to existing MySQL username 
       3. Add an existing database name to existing MySQL username 
       4. Add an existing database name to new MySQL username 
       5. Exit 
       Enter option number 1-5: 1
      ---------------------------------
      Create MySQL username:
      ---------------------------------
       Enter new MySQL username you want to create: newusername1
       Enter new MySQL username's password: newusernamepass1
      ---------------------------------
      Create MySQL database:
      ---------------------------------
       Enter new MySQL database name: newusernamedb1
      Grants for newusername1@localhost
      GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*9CEEE683880AB09CBBF704F6CAB657613DD366DD'
      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 `newusernamedb1`.* TO `newusername1`@`localhost`
      Ok: MySQL user: newusername1 MySQL database: newusernamedb1 created successfully
      

      Option 2
      Code (Text):
      ./mysqladmin_shell.sh setuserdb
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
       1. Create a new MySQL username & new MySQL database 
       2. Add a new database name to existing MySQL username 
       3. Add an existing database name to existing MySQL username 
       4. Add an existing database name to new MySQL username 
       5. Exit 
       Enter option number 1-5: 2
      -------------------------------------------------------------------------
      Add new database name to existing MySQL username:
      -------------------------------------------------------------------------
       Enter existing MySQL username you want to add new database name to: newusername1
      ---------------------------------
      Create MySQL database:
      ---------------------------------
       Enter new MySQL database name: newusernamedb2
      Grants for newusername1@localhost
      GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*9CEEE683880AB09CBBF704F6CAB657613DD366DD'
      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 `newusernamedb1`.* TO `newusername1`@`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 `newusernamedb2`.* TO `newusername1`@`localhost`
      Ok: New MySQL database: newusernamedb2 assigned to existing MySQL user: newusername1
      

      Option 3
      Code (Text):
      ./mysqladmin_shell.sh setuserdb
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
       1. Create a new MySQL username & new MySQL database 
       2. Add a new database name to existing MySQL username 
       3. Add an existing database name to existing MySQL username 
       4. Add an existing database name to new MySQL username 
       5. Exit 
       Enter option number 1-5: 3
      -------------------------------------------------------------------------
      Add existing database name to existing MySQL username:
      -------------------------------------------------------------------------
       Enter existing MySQL username you want to add existing database name to: newusername1
       Enter existing MySQL database name to attach to MySQL user newusername1: existingdb1
      Grants for newusername1@localhost
      GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*9CEEE683880AB09CBBF704F6CAB657613DD366DD'
      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 `newusernamedb1`.* TO `newusername1`@`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 `newusernamedb2`.* TO `newusername1`@`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 `existingdb1`.* TO `newusername1`@`localhost`
      Ok: existing MySQL database: existingdb1 assigned to existing MySQL user: newusername1
      

      Option 4
      Code (Text):
      ./mysqladmin_shell.sh setuserdb
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
       1. Create a new MySQL username & new MySQL database 
       2. Add a new database name to existing MySQL username 
       3. Add an existing database name to existing MySQL username 
       4. Add an existing database name to new MySQL username 
       5. Exit 
       Enter option number 1-5: 4
      -------------------------------------------------------------------------
      Add existing database name to new MySQL username:
      -------------------------------------------------------------------------
       Enter new MySQL username you want to create: newusername2
       Enter new MySQL username's password: newusername2pass
       Enter existing MySQL database name to attach to new MySQL user newusername2: existingdb1
      Grants for newusername2@localhost
      GRANT USAGE ON *.* TO `newusername2`@`localhost` IDENTIFIED BY PASSWORD '*DB5B4F64CF824616AB992AEB513699CBD6502899'
      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 `existingdb1`.* TO `newusername2`@`localhost`
      Ok: MySQL user: newusername2 MySQL database: existingdb1 created successfully
      
    4. setpass
      To change the password of an existing MySQL user, run:
      Code (Text):
      ./mysqladmin_shell.sh setpass
      

      Follow the prompts to enter the username and the new password.
      Example
      Code (Text):
      ./mysqladmin_shell.sh setpass
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
      --------------------------------------------------------------
      Change Existing MySQL username's password:
      --------------------------------------------------------------
       Enter MySQL username you want to change password for: newusername1
       Enter MySQL username's new password to change to: newusername1newpass
      Ok: Changing MySQL password for newusername1 was successful
      
    5. createuserdb
      To create an individual MySQL user and database in an unattended mode, run:
      Code (Text):
      ./mysqladmin_shell.sh createuserdb mydbname mydbuser mydbpass
      

      Replace mydbname, mydbuser, and mydbpass with the desired database name, user, and password, respectively.
      Example output
      Code (Text):
      ./mysqladmin_shell.sh createuserdb mydbname mydbuser mydbpass
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
      Grants for mydbuser@localhost
      GRANT USAGE ON *.* TO `mydbuser`@`localhost` IDENTIFIED BY PASSWORD '*656A1F1C6814E2C4D43C302508A1E82184AD5320'
      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 `mydbname`.* TO `mydbuser`@`localhost`
      Ok: MySQL user: mydbuser MySQL database: mydbname created successfully
      
    6. deluser
      To delete an existing MySQL user, run:
      Code (Text):
      ./mysqladmin_shell.sh deluser
      

      Follow the prompts to enter the username to be deleted.
      Example output
      Code (Text):
      ./mysqladmin_shell.sh deluser
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
      ---------------------------------
      Delete MySQL username:
      ---------------------------------
       Enter MySQL username you want to delete: mydbuser
      Ok: Deleting 'mydbuser'@'localhost' was successful
      
    7. showgrants
      To display the granted privileges of an existing MySQL user, run:
      Code (Text):
      ./mysqladmin_shell.sh showgrants
      

      Follow the prompts to enter the username to display their granted privileges.
      Example output
      Code (Text):
      ./mysqladmin_shell.sh showgrants
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
      ---------------------------------
      Show Grants for MySQL username:
      ---------------------------------
       Enter MySQL username to Show Grant permissions: newusername1
      Grants for newusername1@localhost
      GRANT USAGE ON *.* TO `newusername1`@`localhost` IDENTIFIED BY PASSWORD '*B60F2CF5DA878C3D9335B46D7B6E52B9D38B3F67'
      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 `newusernamedb1`.* TO `newusername1`@`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 `newusernamedb2`.* TO `newusername1`@`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 `existingdb1`.* TO `newusername1`@`localhost`
      Ok: SHOW GRANTS for 'newusername1'@'localhost' was successful
      
    Additional Information
    The mysqladmin_shell.sh script simplifies the process of managing MySQL users, databases, and permissions by providing a user-friendly interface. Here are some additional points to consider when using the script:
    1. Error Handling: The script includes error handling to inform the user if a task was unsuccessful. For instance, if the creation of a MySQL user fails, the script will display an error message to notify the user.
    2. Compatibility: The script is compatible with different MySQL server versions, including MariaDB. Ensure that the MySQL server is properly installed and configured on your system before using the script.
    3. Security: When using this script, be mindful of security best practices. For example, avoid using weak passwords or granting excessive privileges to users.
    4. Unattended Mode: The createuserdb option allows you to create individual MySQL users and databases in an unattended mode, which can be useful for automation purposes or when deploying multiple instances.
    5. File Cleanup: The script uses temporary files to store user input and automatically cleans them up upon completion or termination. This helps prevent sensitive information from being stored on the system.

    Updates


    • April 19th, 2023: Add additional options to setuserdb menu for adding an existing database name to existing MySQL username or adding an existing database to a new MySQL username
      Code (Text):
      ./mysqladmin_shell.sh setuserdb
      --------------------------------------------------------------
      Basic MySQL Admin - create mysql user & databases 
      --------------------------------------------------------------
      --------------------------------------------------------------
       1. Create a new MySQL username & new MySQL database 
       2. Add a new database name to existing MySQL username 
       3. Add an existing database name to existing MySQL username 
       4. Add an existing database name to new MySQL username 
       5. Exit 
       Enter option number 1-5: 
      
    • September 19th, 2016: Updated Centmin Mod 123.09beta01 branch's addons/mysqladmin_shell.sh multidb option to support detecting if the db list file has multiple mysql databases assigned to same mysql user/pass and add those mysql databases to the same mysql user/pass details here.
    • September 20th, 2016: example manual SSH commands to DROP mysql usernames and database names etc.
    • September 20th, 2016: Example of manually create mysql username with access to all databases.
    • September 20th, 2016: Updated Centmin Mod 123.09beta01's addons/mysqladmin_shell.sh's multidb mode to support creating databases without any specified accompanying mysql username/passwords.
    • May 8th, 2017: Updated to add new createuserdb option for unattended script usage with no interactive prompts

    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: Apr 19, 2023
  2. GhoHan

    GhoHan Member

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

    pamamolf Well-Known Member

    4,045
    427
    83
    May 31, 2014
    Ratings:
    +825
    Local Time:
    8:33 PM
    Nginx-1.25.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

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.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,045
    427
    83
    May 31, 2014
    Ratings:
    +825
    Local Time:
    8:33 PM
    Nginx-1.25.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

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.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,045
    427
    83
    May 31, 2014
    Ratings:
    +825
    Local Time:
    8:33 PM
    Nginx-1.25.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

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.x
    MariaDB 10.x
  9. sepulchre

    sepulchre Member

    167
    22
    18
    Dec 22, 2014
    Ratings:
    +28
    Local Time:
    7:33 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

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.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:
    7:33 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

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.x
    MariaDB 10.x
  13. sepulchre

    sepulchre Member

    167
    22
    18
    Dec 22, 2014
    Ratings:
    +28
    Local Time:
    7:33 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:
    10:33 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

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.x
    MariaDB 10.x
    Last edited: Jun 13, 2015
  16. Mask

    Mask Active Member

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

    eva2000 Administrator Staff Member

    51,690
    11,940
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,431
    Local Time:
    3:33 AM
    Nginx 1.25.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,045
    427
    83
    May 31, 2014
    Ratings:
    +825
    Local Time:
    8:33 PM
    Nginx-1.25.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:
    7:33 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,045
    427
    83
    May 31, 2014
    Ratings:
    +825
    Local Time:
    8:33 PM
    Nginx-1.25.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 :)