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

Add utf8mb4 support

If it's a large server + large database you probably need to increase the default size of the buffer pool.

MySQL > SELECT @@innodb_buffer_pool_instances, @@innodb_buffer_pool_size;
+--------------------------------+---------------------------+
| @@innodb_buffer_pool_instances | @@innodb_buffer_pool_size |
+--------------------------------+---------------------------+
|                              8 |                 268435456 |
+--------------------------------+---------------------------+

Edit /etc/mysql/mysql.conf.d/memory.cnf (or whatever the correct file is) and increase the size from 256MB to whatever you want.

[mysqld]
innodb_buffer_pool_size=1G

Then restart the server: sudo service mysql restart

When making changes to indexes and columns remember to consider full table locks to prevent FK errors if the table is live and being updated/added to.

ALTER TABLE email_list CHANGE opens_updated_on opens_updated_on DATE NULL, LOCK=EXCLUSIVE;

and in-place updates so the table isn't locked for a long time:

If you need a temp database for development it's easy to use docker to pull down an image:

docker run -p 3306:3306 --name dev-mariadb -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -e MYSQL_DATABASE=local -d mariadb:latest --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

At this point you can connect with:

username: root
password: root
database: local
host: 127.0.0.1
port: 3306

You can see the image with docker ps and stop it with docker stop dev-mariadb and then remove it docker rm dev-mariadb. https://mariadb.com/kb/en/library/installing-and-using-mariadb-via-docker/

You can also login with docker exec -t -i dev-mariadb /bin/bash, but you shouldn't make a habit of manually making changes with disposable containers.

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;"