brimble.com

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== MySQL ======	
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.

===== Version =====
I used the official docker mysql:8.4

===== Docker Setup =====
  * 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): ********

===== App Settings =====

  * User standard SQL commands to import databases from back up, allow privileges, etc.
  * See [[unraid:virtual:ubuntu20.04#install_mysql_if_not_using_a_docker|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.

===== Connect =====
<code>docker exec -it mysql bash</code>

===== Load Football pool =====
  * Create csv file
  * copy it over to the transfer folder on unraid backup share
  * copy it to datalink folder on mysql appdata folder<code>sudo cp /mnt/backup/xfer/2024.csv /mnt/nvme/appdata/mysql/datalink/</code>
  * log into mysql shell
  * check LOCAL INFILE var <code> mysql> show global variables like 'local_infile';</code>
  * fix it if need be <code> mysql> set global local_infile=true;</code>
  * log out of mysql and back in with proper option <code> mysql --local_infile=1 -u root -ppassword DB_name</code>
  * Use normal commands <code>LOAD DATA LOCAL INFILE "/var/lib/mysql/datalink/2024.csv" INTO TABLE games_nfl COLUMNS TERMINATED BY ',';
UPDATE games SET vscore = NULL where year = 2024;
UPDATE games SET hscore = NULL where year = 2024;</code>
  * DON'T FORGET TO ADD A NEW COLUMN FOR THE NEW YEAR IN "OVERALL_NFL"!!!

===== Adjust Max Connections =====
  * <code>show processlist;</code>
  * <code>show variables like '%timeout';</code>
  * 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 <code>docker cp mysql:/etc/my.cnf .</code>
  * add following lines to my.cnf (put them just under the [mysqld] line... putting them under [client] is no bueno!<code>[mysqld]

wait_timeout = 600
interactive_timeout = 600
max_connections = 500</code>
  * copy my.cnf back to docker <code>cp my.cnf mysql:/etc/my.cnf</code>
  * restart docker
  * script: sqlconnections.sh <code>#!/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"</code>
brimble.com

User Tools

Site Tools

Page Tools
