Setup MySQL / MariaDB

mysql_install.sh

# Install
sudo apt-get install mariadb-server

# Secure the server
sudo mysql_secure_installation

# vim /root/.my.cnf and place the following
[mysql]
user=root
password=...


# The root mysql account from uses https://mariadb.com/kb/en/authentication-plugin-unix-socket/ and an invalid password:
# Normally, you should create a new user and move on skipping altering root.
#mysql -u root -p
#> drop user 'root'@'localhost';
#> create user 'root'@'localhost' identified by 'your_password';
#> grant all privileges on *.* to 'root'@'localhost' with grant option;
#> flush privileges;

# Notice we are using `localhost` not `%` (all hosts)

# Once you have a working root user account you can 
# create a database and user for your app.
mysql
> CREATE DATABASE `mydb`;
> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
> GRANT USAGE ON `mydb`.* TO 'myuser'@localhost IDENTIFIED BY 'mypassword';
> GRANT ALL privileges ON `mydb`.* TO 'myuser'@localhost;
> flush privileges;

# Now you can use port forwarding to access the database from your computer
ssh deploy@IPGOESHERE -L 3306:127.0.0.1:3306'

# Develop locally and use the database

# Now lets add a CRON job

mkdir /var/backups/mysql

crontab -e
> # 2:15am backup mysql
> 15 2 * * * mysqldump -u root --all-databases | gzip > /var/backups/mysql/email_`date +'%m-%d-%Y'`.sql.gz

By default, https://mariadb.com/kb/en/authentication-plugin-unix-socket/ is used for authentication of the root user. You can disable this so that you can connect without using the system access.

The root@localhost user created by mysql_install_db is created with the ability to use two authentication plugins. First, it is configured to try to use the unix_socket authentication plugin. This allows the the root@localhost user to login without a password via the local Unix socket file defined by the socket system variable, as long as the login is attempted from a process owned by the operating system root user account. Second, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD. - https://mariadb.com/kb/en/authentication-plugins/

Consider jump-starting the creation:

mysql -p -u "root" -Bse "CREATE DATABASE \`${1}\`;
CREATE USER '$1'@'%' IDENTIFIED BY '$2';
GRANT ALL PRIVILEGES ON `${1}`.* TO '$1'@'%' WITH GRANT OPTION;"