Welcome to Centmin Mod Community
Become a Member

[Guide] Save time creating Nginx vhost & MySQL users and databases

Discussion in 'Centmin Mod Insights' started by eva2000, Aug 9, 2016.

Thread Status:
Not open for further replies.
  1. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    8:38 AM
    Nginx 1.13.x
    MariaDB 5.5
    Centmin Mod LEMP stack was developed to save system admins' and server owners' time - especially when it comes to mundane and repetitive tasks. For a LEMP stack, creating Nginx vhosts and MySQL users and databases are probably one of the most common tasks you'd be doing. So saving time is essential. Below is a quick outline of how Centmin Mod LEMP stack users can save time in these 2 tasks.

    Creating Nginx Site Domain Vhost Accounts



    Adding new Nginx site domain vhost accounts on Centmin Mod has always been easy via shell based menu, centmin.sh menu option 2. There is however a command line version in Centmin Mod 123.08stable/123.09beta01 and higher at /usr/bin/nv (alias /bin/nv).

    /usr/bin/nv or /bin/nv options available including an -s flag with 3 values for self-signed SSL cert creating, yes, no or yd = yes and make HTTPS default so HTTP redirects to HTTPS. There will be a 4th option = le later on once addons/acmetools.sh integration is added for free Letsencrypt SSL certificates.
    Code (Text):
    nv
    
    Usage: /bin/nv [-d yourdomain.com] [-s y|n|yd] [-u ftpusername]
    
      -d  yourdomain.com or subdomain.yourdomain.com
      -s  ssl self-signed create = y or n or https only vhost = yd
      -u  your FTP username
    
      example:
    
      /bin/nv -d yourdomain.com -s y -u ftpusername
      /bin/nv -d yourdomain.com -s n -u ftpusername
      /bin/nv -d yourdomain.com -s yd -u ftpusername
    

    So to create Nginx vhost site for newdomain1.com with self-signed SSL cert and pure-ftpd user ftp username = ftp1. At the end of output you get your site's login details, paths to public web root, log file path and also commands to remove the created newdomain1.com files if you want to revert. Also a full log is saved to /root/centminlogs for each run you make if you need to reference the full output and login info again.
    Code (Text):
    nv -d newdomain.com -s y -u ftp1
    

    Now you can also utilise this command line method to automatically create many Nginx vhost domain sites all at once :)

    Create a file i.e. /home/vhostlist.txt and in it add the domain name (without www.), whether y, n or yd for self-signed SSL cert and pure-ftpd ftp username desired - one line for each domain and ftp pairing.
    Code (Text):
    newdomain1.com n ftp1
    newdomain2.com n ftp2
    

    Then running this below single command line in SSH session as root user will use a basic while read loop to use /usr/bin/nv to generate Nginx vhost accounts for all site domains listed in /home/vhostlist.txt
    Code (Text):
    while read -r d s f; do echo "nv -d $d -s $s -u $f"; nv -d $d -s $s -u $f; done < /home/vhostlist.txt
    

    Example output with saved logs at
    • /root/centminlogs/centminmod_080816-161014_nginx_addvhost_nv.log
    • /root/centminlogs/centminmod_080816-161039_nginx_addvhost_nv.log
    Code (Text):
    while read -r d s f; do echo "nv -d $d -s $s -u $f"; nv -d $d -s $s -u $f; done < /home/vhostlist.txt
    nv -d newdomain1.com -s n -u ftp1
    ---------------------------------------------------------------
    Nginx Vhost Setup...
    ---------------------------------------------------------------
    
    FTP password auto generated: ****
    
    Password:
    Enter it again:
    
    -------------------------------------------------------------
    /usr/local/src/centminmod/tools/autoprotect.sh
    generated nginx include file: /usr/local/nginx/conf/autoprotect/demodomain.com/autoprotect-demodomain.com.conf
    generated nginx include file: /usr/local/nginx/conf/autoprotect/newdomain1.com/autoprotect-newdomain1.com.conf
    
    autoprotect.sh run completed...
    
    Restarting nginx (via systemctl):  [  OK  ]
    Restarting nginx (via systemctl):  [  OK  ]
    systemctl restart pure-ftpd.service
    
    -------------------------------------------------------------
    FTP hostname : IPADDR
    FTP port : 21
    FTP mode : FTP (explicit SSL)
    FTP Passive (PASV) : ensure is checked/enabled
    FTP username created for newdomain1.com : ftp1
    FTP password created for newdomain1.com : ****
    -------------------------------------------------------------
    vhost for newdomain1.com created successfully
    
    domain: http://newdomain1.com
    vhost conf file for newdomain1.com created: /usr/local/nginx/conf/conf.d/newdomain1.com.conf
    
    upload files to /home/nginx/domains/newdomain1.com/public
    vhost log files directory is /home/nginx/domains/newdomain1.com/log
    
    -------------------------------------------------------------
    Current vhost listing at: /usr/local/nginx/conf/conf.d/
    
                      
    Jul 29  08:31   1.1K   demodomain.com.conf
    Jul 29  08:31   845    ssl.conf
    Jul 29  08:39   1.6K   virtual.conf
    Aug 8   16:10   2.1K   newdomain1.com.conf
    
    -------------------------------------------------------------
    Commands to remove newdomain1.com
    
    pure-pw userdel ftp1
    rm -rf /usr/local/nginx/conf/conf.d/newdomain1.com.conf
    rm -rf /usr/local/nginx/conf/ssl/newdomain1.com/newdomain1.com.crt
    rm -rf /usr/local/nginx/conf/ssl/newdomain1.com/newdomain1.com.key
    rm -rf /usr/local/nginx/conf/ssl/newdomain1.com/newdomain1.com.csr
    rm -rf /usr/local/nginx/conf/ssl/newdomain1.com
    rm -rf /home/nginx/domains/newdomain1.com
    service nginx restart
    
    -------------------------------------------------------------
    vhost for newdomain1.com setup successfully
    newdomain1.com setup info log saved at:
    /root/centminlogs/centminmod_080816-161014_nginx_addvhost_nv.log
    -------------------------------------------------------------
    
    nv -d newdomain2.com -s n -u ftp2
    ---------------------------------------------------------------
    Nginx Vhost Setup...
    ---------------------------------------------------------------
    
    FTP password auto generated: ****
    
    Password:
    Enter it again:
    
    -------------------------------------------------------------
    /usr/local/src/centminmod/tools/autoprotect.sh
    generated nginx include file: /usr/local/nginx/conf/autoprotect/demodomain.com/autoprotect-demodomain.com.conf
    generated nginx include file: /usr/local/nginx/conf/autoprotect/newdomain1.com/autoprotect-newdomain1.com.conf
    generated nginx include file: /usr/local/nginx/conf/autoprotect/newdomain2.com/autoprotect-newdomain2.com.conf
    
    autoprotect.sh run completed...
    
    Restarting nginx (via systemctl):  [  OK  ]
    Restarting nginx (via systemctl):  Job for nginx.service failed because start of the service was attempted too often. See "systemctl status nginx.service" and "journalctl -xe" for details.
    To force a start use "systemctl reset-failed nginx.service" followed by "systemctl start nginx.service" again.
    [FAILED]
    systemctl restart pure-ftpd.service
    
    -------------------------------------------------------------
    FTP hostname : IPADDR
    FTP port : 21
    FTP mode : FTP (explicit SSL)
    FTP Passive (PASV) : ensure is checked/enabled
    FTP username created for newdomain2.com : ftp2
    FTP password created for newdomain2.com : ****
    -------------------------------------------------------------
    vhost for newdomain2.com created successfully
    
    domain: http://newdomain2.com
    vhost conf file for newdomain2.com created: /usr/local/nginx/conf/conf.d/newdomain2.com.conf
    
    upload files to /home/nginx/domains/newdomain2.com/public
    vhost log files directory is /home/nginx/domains/newdomain2.com/log
    
    -------------------------------------------------------------
    Current vhost listing at: /usr/local/nginx/conf/conf.d/
    
                      
    Jul 29  08:31   1.1K   demodomain.com.conf
    Jul 29  08:31   845    ssl.conf
    Jul 29  08:39   1.6K   virtual.conf
    Aug 8   16:10   2.1K   newdomain1.com.conf
    Aug 8   16:10   2.1K   newdomain2.com.conf
    
    -------------------------------------------------------------
    Commands to remove newdomain2.com
    
    pure-pw userdel ftp2
    rm -rf /usr/local/nginx/conf/conf.d/newdomain2.com.conf
    rm -rf /usr/local/nginx/conf/ssl/newdomain2.com/newdomain2.com.crt
    rm -rf /usr/local/nginx/conf/ssl/newdomain2.com/newdomain2.com.key
    rm -rf /usr/local/nginx/conf/ssl/newdomain2.com/newdomain2.com.csr
    rm -rf /usr/local/nginx/conf/ssl/newdomain2.com
    rm -rf /home/nginx/domains/newdomain2.com
    service nginx restart
    
    -------------------------------------------------------------
    vhost for newdomain2.com setup successfully
    newdomain2.com setup info log saved at:
    /root/centminlogs/centminmod_080816-161039_nginx_addvhost_nv.log
    -------------------------------------------------------------
    
     
    Last edited: Aug 9, 2016
    • Informative Informative x 2
  2. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    8:38 AM
    Nginx 1.13.x
    MariaDB 5.5

    Creating MySQL Users & Databases



    Centmin Mod LEMP stack from 123.08stable/123.09beta01 and higher come bundled with an official addons/mysqladmin_shell.sh script which makes creating MySQL users and databases fairly easy. Below are 3 examples including how to create multiple MySQL user/pass and database names all at once :)

    Available options
    Code (Text):
    ./mysqladmin_shell.sh
    
    ./mysqladmin_shell.sh {multidb|setuserdb|setpass|deluser|showgrants}
    

    Example of creating a single MySQL username = muser1 with password = mpass1 and MySQL database named = 1db
    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: muser1
    Enter new MySQL username's password: mpass1
    ---------------------------------
    Create MySQL database:
    ---------------------------------
    Enter new MySQL database name: 1db
    
    +---------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for muser1@localhost                                                                                                                 |
    +---------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'muser1'@'localhost' IDENTIFIED BY PASSWORD '*8F84870DDBA8AA31684F7D844ABEF73FB7631BD9'                               |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `1db`.* TO 'muser1'@'localhost' |
    +---------------------------------------------------------------------------------------------------------------------------------------------+
    
    Ok: MySQL user: muser1 MySQL database: 1db created successfully
    

    Adding a new MySQL database to existing MySQL username = muser1
    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: n
    -------------------------------------------------------------------------
    Add new database name to existing MySQL username:
    -------------------------------------------------------------------------
    Enter existing MySQL username you want to add new database name to: muser1
    ---------------------------------
    Create MySQL database:
    ---------------------------------
    Enter new MySQL database name: 2db
    
    +---------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for muser1@localhost                                                                                                                 |
    +---------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'muser1'@'localhost' IDENTIFIED BY PASSWORD '*8F84870DDBA8AA31684F7D844ABEF73FB7631BD9'                               |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `1db`.* TO 'muser1'@'localhost' |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `2db`.* TO 'muser1'@'localhost' |
    +---------------------------------------------------------------------------------------------------------------------------------------------+
    
    Ok: New MySQL database: 2db assigned to existing MySQL user: muser1
    

    Create multiple MySQL user/pass and database names all at once via multidb option. For example, contents of /home/dblist.txt - one entry per line in format order of
    Code (Text):
    dbname dbusername dbpassword
    db2name db2username db2password
    

    Then run multidb option and when prompted specify path to /home/dblist.txt
    Code (Text):
    ./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: dbusername 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: db2username MySQL database: db2name created successfully
    


    123.09beta01 improvements



    Centmin Mod 123.09beta01 as of August 9th, 2016 goes one step further to make multidb option available via non-interactive method just passing the path to the MySQL user/pass and db name file on command line. For example, contents of /home/dblist.txt - one entry per line in format order of
    Code (Text):
    dbn1 db1user db1pass
    db21 db2user db2pass
    

    Then pass /home/dblist.txt on command line
    Code (Text):
    ./mysqladmin_shell.sh multidb /home/dblist.txt
    

    Output is as follows
    Code (Text):
    ./mysqladmin_shell.sh multidb /home/dblist.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
    ----------------------------------------------------------------------------
    Grants for db1user@localhost
    GRANT USAGE ON *.* TO 'db1user'@'localhost' IDENTIFIED BY PASSWORD '*7C55059CBCD2275510DB535D59FC71505F21FB8D'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `dbn1`.* TO 'db1user'@'localhost'
    
    Ok: MySQL user: db1user MySQL database: dbn1 created successfully
    
    Grants for db2user@localhost
    GRANT USAGE ON *.* TO 'db2user'@'localhost' IDENTIFIED BY PASSWORD '*7545F6ACA37249F2F086A83BB636D7DDB3E99538'
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON `db21`.* TO 'db2user'@'localhost'
    
    Ok: MySQL user: db2user MySQL database: db21 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
    
     
    Last edited: May 8, 2017
    • Informative Informative x 2
    • Like Like x 1
  3. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,139
    Local Time:
    8:38 AM
    Nginx 1.13.x
    MariaDB 5.5
    • Like Like x 1
Thread Status:
Not open for further replies.