Apache BasicAuth with MySQL (authn_dbd)

.appendTo_apache2.conf

# Append to apache2.conf
# Dont forget to repalce [user] [password] [dbname] with you own info
# ref: https://httpd.apache.org/docs/2.4/mod/mod_dbd.html
# ref: https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html

<IfModule mod_authn_dbd.c>
DBDriver mysql
DBDParams host=localhost,port=3306,user=apache,pass=apache,dbname=auth
DBDMin  2
DBDKeep 8
DBDMax  20
DBDExptime 300
</IfModule>
# END of apache2.conf

.htaccess

# In Vhost conf file or .htaccess where needed:
# Dont forget to repalce [apache] to you own table name
# When inside <VirtualHost>, use inside one of Related Directives[https://httpd.apache.org/docs/2.4/sections.html]
# ref: https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html

AuthName "Login Required"
AuthType Basic
AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT password AS password FROM apache WHERE username = %s"
Require valid-user
# END of conf file / .htaccess

apt.info

# Run each line in shell(ssh) 
# Ignore if you compiled Apache from source code rather than using a package management tool

# Enable apache database auth module
sudo a2enmod authn_dbd

# Enable, to cache auth info (for high traffic website,  lower db pressure), this is actually not implemented in our config files above. 
sudo a2enmod authn_socache

# Check for software update
sudo apt-get update

# Install mysql module for authn_dbd
sudo apt-get install apache2-utils libaprutil1-dbd-mysql

# Restart Apache
sudo service apache2 restart

sql_structure.sql

/*
Same thing, replace `apache` with your own table name

for the password, you may use one of the format here: https://httpd.apache.org/docs/2.4/misc/password_encryptions.html

Apache will automatically identify the format of the password, and verify password accordingly.

And finally, don't forget to create a new user and grant the select priv to this database (called 'auth' in our example)

*/
DROP TABLE IF EXISTS `apache`;
CREATE TABLE `apache` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET ascii NOT NULL,
  `password` varchar(255) CHARACTER SET ascii NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;