Aruba ClearPass SQL backends: MySQL setup

In this video: how to setup MySQL on Ubuntu Linux to use it as an Aruba ClearPass Authentication and Authorization backend.

Commands used/script:
sudo apt-get install mysql-server

To make SQL listen on the network, edit the file /etc/mysql/my.cnf, and search for the line with 'bind-address' and change that to look like:
bind-address = 0.0.0.0
Then restart mysql:
sudo service mysql restart
netstat -na | grep 3306

If you have the Ubuntu firewall installed, make sure it allows access from ClearPass to port 3306 on the MySQL server:
sudo ufw allow from 10.254.1.21 to any port 3306 proto tcp

Now create a database:
sudo mysql -u root -p

Enter the password that you picked during the MySQL installation. We will now continue in the MySQL prompt:
create database cpdemo;
connect cpdemo;
GRANT ALL PRIVILEGES ON cpdemo.* TO 'clearpass'@'10.254.1.21' IDENTIFIED BY 'aruba123';
show grants for clearpass@'10.254.1.21';

Now, we create and fill the cpuser table (still in the MySQL prompt, connected to the cpdemo database):
drop table cpuser;
create table cpuser ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), cpusername VARCHAR(50), cppassword VARCHAR(50), cppassword_md5 VARCHAR(50), cppassword_sha VARCHAR(50), cppassword_sha256 VARCHAR(128) );
insert into cpuser (cpusername,firstname,lastname,email,cppassword,cppassword_md5,cppassword_sha,cppassword_sha256) values ('testuser','Testfirst','Testlast','testmail','aruba123',MD5('aruba123'),SHA('aruba123'),SHA2('aruba123',256)) ;
drop table cpdevice;
create table cpdevice ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, macaddr VARCHAR(30) NOT NULL, name VARCHAR(30) NOT NULL, location VARCHAR(50), label VARCHAR(50), color VARCHAR(50), status VARCHAR(50) );
insert into cpdevice (macaddr,name,location,label,color,status) values ('00408ca2b2b5','AXIS IP-camera','2nd floor back side','security','white','production') ;

Check if that went okay:
select * from cpuser;
select * from cpdevice;

Related videos:
How to install PostgreSQL on Ubuntu Linux for ClearPass:    • Aruba ClearPass SQL backends: Postgre...  
How to install the MySQL driver on ClearPass:    • How to install the MySQL driver into ...  
How to configure Aruba ClearPass with MySQL backend:    • Configure HPE Aruba Networks ClearPas...  
How to configure Aruba ClearPass with a PostgreSQL backend:    • Configure HPE Aruba Networks ClearPas...