Install MySQL on Ubuntu
As part of setting up a server to be used for integration testing and staging of bankbog.dk I had to install MySQL (don't hate me postgres lovers). This is mostly just a reminder to myself later on, when I inevitably forget what I did.
Mostly I followed this digital ocean guide: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04
A recap:
sudo apt update # possibly upgrade after this.
sudo apt install mysql-server
sudo systemctl start mysql.service
After installation, it's a good idea to secure the installation with
mysql_secure_installation. However, due to to root not being allowed to
login with a password on the vm, I had to go into mysql first and change so
that root is ideentified with "auth_socket":
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
Then we can secure the installation:
sudo mysql_secure_installation
It will ask a bunch of questions, just say what makes sense.
Now make a new user to use for integration tests:
CREATE USER 'integration_test_user'@'%' IDENTIFIED BY 'CrazYPASSw0rd!!!';
CREATE USER 'integration_test_user'@'localhost' IDENTIFIED BY 'CrazYPASSw0rd!!!';
The user created with @'%' can log in from anywhere, which is something we
will need in order to reach the machine from our deployment pipeline, which will
be some github machine - we can't know the ip of the machine running our tests.
With a more "cloudy" setup, we would have a hosted database somewhere and we
would be able to allow-list the github machine running the integration tests as
part of the pipeline run (I have done this with linode in the past) but I like
money too much and this approach with a VM is way cheaper and we're still only
in the very beginning of bankbog's life.
Since we want the integration_test_user to be able to basically "whatever" (create new databases, run migrations, insert, update, delete, select, etc.), we grant it every privilege possible:
GRANT ALL ON *.* TO 'integration_test_user'@'%';
GRANT ALL ON *.* TO 'integration_test_user'@'localhost';
We have still not allowed for outside connections though. To do that, we must
make mysql listen to incoming connections from "whereever". Edit
/etc/mysql/mysql.conf.d/mysqld.cnf:
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
Then restart:
$ sudo systemcl restart mysql
Make sure that ufw is enabled btw. and allow connections on the mysql port 3306.
sudo ufw status # to check if ufw is even running - may not be.
Before enabling, make sure to allow port 22 since you're probably ssh'ing right now. If you're already running a site, also allow 80 and 443.
sudo ufw allow 22 # SSH
sudo ufw allow 80 # HTTP
sudo ufw allow 443 # HTTPS
sudo ufw allow 3306 # MySQL
sudo ufw enable
All done - check it with your local sql client.
This has been an installment in the note-to-self series, hope it was helpful.