How to Setup MySQL as the backend

Setting up MySQL

On Ubuntu 20.04

Install

This will allow you to login to the root with sudo mysql

sudo apt-get install mysql-server
sudo service mysql start
mysql_secure_installation
sudo mysql

To stop the service use

sudo service mysql stop

Create a database for GoDBLedger

While logged in as root

CREATE DATABASE ledger;

Create User for GoDBLedger

Login to the mysql prompt

CREATE USER 'godbledger'@'localhost' IDENTIFIED BY 'password';

This will create the following user: User: godbledger Pass: password

If you get a low password strength warning this can be disabled with

mysql> SET GLOBAL validate_password.policy = 0;

Obviously its preferable to have a stronger password than 'password' but this is what i have been doing for testing purposes

Grant the GoDBLedger user rights to databases & logins etc

GRANT ALL PRIVILEGES ON * . * TO 'godbledger'@'localhost';
FLUSH PRIVILEGES;

Please note that in this example we are granting godbledger full root access to everything in our database. While this is helpful for explaining some concepts, it may be impractical for most use cases and could put your database’s security at high risk.

Login to the ledger database with your GoDBLedger user

mysql -u godbledger -ppassword ledger

Delete the User (Optional)

From within MySQL prompt

DROP USER ‘godbledger’@‘localhost’;

Allowing External Connections to MySQL

Setup the user to allow external IP addresses to access

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP';
FLUSH PRIVILEGES;

So for our godbledger user it becomes

GRANT ALL PRIVILEGES ON *.* TO 'godbledger'@'localhost';

Then you need to allow external connections to MySQL as a whole. Find the configuration file then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).

Can also be found using

sudo find /etc -iname 'mysql*.cnf'

Change line

bind-address = 127.0.0.1

to

#bind-address = 127.0.0.1

Connecting to a remote mysql server

mysql -u godbledger -ppassword -hplay.godbledger.com ledger

Will connect to a running mysql server at play.godbledger.com

Configuring the database in GoDBLedger

The config file for godbledger defaults to using sqlite3 as the backend database. This needs to be modified to mysql and the mysql credentials need to be added to database location so the server knows how to communicate to mysql.

The config file is by default written to ~/.ledger/config.toml

And the following params should be updated:

DatabaseType = "mysql"
DatabaseLocation = "godbledger:password@tcp(127.0.0.1:3306)/ledger"

Where the mysql username is godbledger and password is password