MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius's daughter, and “SQL”, the abbreviation for Structured Query Language. Brimble.com uses MySQL to store the tables for most all things running on the server.

I used the official docker mysql:8.4

• publish a new network port
  • 3306 → 3306 TCP
• volumes:
  • /var/lib/mysql → /mnt/nvme/appdata/mysql
• network:
  • Type: bridge
  • Fixed IP address (optional): blank
• Env:
  • Key 1 (MYSQL_ROOT_PASSWORD):
  • Key 2 (MYSQL_DATABASE): nextcloud (i used this to create the nextcloud structure… added more later)
  • Key 3 (MYSQL_USER):
  • Key 4 (MYSQL_PASSWORD):

• User standard SQL commands to import databases from back up, allow privileges, etc.
• See Install MySql section of installing ubuntu pages for most commands. write access is not needed as that feature was removed by changing the way the NCAA pool temp stores the data.

docker exec -it mysql bash

• Create csv file
• Cyberduck it over to tmp folder on unraid server
• copy it to tmp folder on docker

  docker cp /tmp/2021.csv mysql:/tmp/2021.csv

• go back to docker console, copy it to mysql-files directory

   cp /tmp/2021.csv /var/lib/mysql-files/2021.csv

• log into mysql
• check LOCAL INFILE var

   mysql> show global variables like 'local_infile';

• fix it if need be

   mysql> set global local_infile=true;

• log out of mysql and back in with proper option

   mysql --local_infile=1 -u root -ppassword DB_name

• Use normal commands

  LOAD DATA LOCAL INFILE "/var/lib/mysql/2021.csv" INTO TABLE games COLUMNS TERMINATED BY ',';
  UPDATE games SET vscore = NULL where year = 2021;
  UPDATE games SET hscore = NULL where year = 2021;

• show processlist;

• show variables like '%timeout';

• Either run scripts (see below), use vs-code setup to edit my.cnf from /etc folder OR copy my.cnf from /etc folder in the docker to the server for editing

  docker cp mysql:/etc/my.cnf .

• add following lines to my.cnf (put them just under the [mysqld] line… putting them under [client] is no bueno!

  [mysqld]
  
  wait_timeout = 600
  interactive_timeout = 600
  max_connections = 500

• copy my.cnf back to docker

  cp my.cnf mysql:/etc/my.cnf

• restart docker
• script: sqlconnections.sh

  #!/bin/bash
  declare -r dockern="mysql"
  echo "checking if my.cnf is already updated"
  docker exec $dockern grep -q "wait_timeout = 600" /etc/my.cnf && a=1 || a=0
  if [ $a == 1 ]
  then
      echo "it has already been updated... no changes made"
  else
      echo "it has not been updated... updating my.cnf to include new wait_timeout, interactive_timeout, and max_connection variables"
      docker exec $dockern sed -i '/user=mysql/a wait_timeout = 600\ninteractive_timeout = 600\nmax_connections = 500' /etc/my.cnf
      echo "done... restarting docker"
  sleep 5
  docker container restart $dockern
  fi
  echo "script complete"