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

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.

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

There is a 4th option = le via addons/acmetools.sh integration for free Letsencrypt SSL certificates. -s lelived will be one you want for letsencrypt live SSL cert with HTTPS default or -s lelive without d for non-HTTP + HTTPS access and not HTTPS being default. You can see an example with Letsencrypt SSL certificate HTTPS at Install - Quick Centmin Mod Install + Nginx Vhost Site + MySQL Database Setup

Code (Text):

nv
Usage: /usr/bin/nv [-d yourdomain.com] [-s y|n|yd|le|led|lelive|lelived]
  -d  yourdomain.com or subdomain.yourdomain.com
  -s  ssl self-signed create = y or n or https only vhost = yd
  -s  le - letsencrypt test cert or led test cert with https default
  -s  lelive - letsencrypt live cert or lelived live cert with https default
  example:
  /usr/bin/nv -d yourdomain.com -s y
  /usr/bin/nv -d yourdomain.com -s n
  /usr/bin/nv -d yourdomain.com -s yd
  /usr/bin/nv -d yourdomain.com -s le
  /usr/bin/nv -d yourdomain.com -s led
  /usr/bin/nv -d yourdomain.com -s lelive
  /usr/bin/nv -d yourdomain.com -s lelived

Make sure all intended domain names, subdomain names etc have DNS A records updated to point to the Centmin Mod server's IP address. For domain.com need that and www.domain.com with DNS A records.

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

if you have letsencrypt support enabled and what live HTTPS default

Code (Text):

nv -d newdomain.com -s lelived -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

if want letsencrypt SSL with HTTPS default change n to lelived

Code (Text):

newdomain1.com lelived ftp1
newdomain2.com lelived 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
-------------------------------------------------------------

 

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

 

Questions, suggestions and Feedback

• For Nginx vhost setup, post a new thread in Nginx, PHP-FPM & MariaDB MySQL | Centmin Mod Community
• For addons/mysqladmin_shell.sh, post a new thread in Add Ons | Centmin Mod Community

 

Updated 1st post guide to make sure nv command with Letsencrypt SSL and Nginx HTTPS is documented in above examples