ss-install-mysql-config.txt

#!/bin/bash

####################################################################################################
#### author: SlickStack ############################################################################
#### link: https://slickstack.io ###################################################################
#### mirror: littlebizzy/slickstack/blob/master/bash/ss-install-mysql-config.txt ###################
#### path: /var/www/ss-install-mysql-config ########################################################
#### destination: n/a (not a boilerplate) ##########################################################
#### purpose: Reinstalls MySQL module config files based on ss-config (idempotent) #################
#### module version: MySQL 8.0.x ###################################################################
#### sourced by: ss-install-mysql ##################################################################
#### bash aliases: ss install mysql config #########################################################
####################################################################################################

## SS-CONFIG MUST BE PROPERLY CONFIGURED AND ON CURRENT BUILD BEFORE RUNNING SS-INSTALL ##
## ENSURE YOUR SS-CONFIG BUILD REMAINS CURRENT BY RUNNING SS-UPDATE OCCASIONALLY ##

####################################################################################################
#### TABLE OF CONTENTS (SS-Install-MySQL-Config) ###################################################
####################################################################################################

## this is a brief summary of the different code snippets you will find in this script ##
## each section should be commented so you understand what is being accomplished ##

## A. Source SS-Config + SS-Functions
## B. Touch Timestamp File
## C. Message (Begin Script)
## D. Install My.conf
## E. Remove Version-Specific Settings
## F. Restart Modules (MySQL)
## G. Reset Permissions (MySQL Config)

####################################################################################################
#### A. SS-Install-MySQL-Config: Source SS-Config + SS-Functions ###################################
####################################################################################################

## before anything else we must source the critical variables that power this script ##
## ss-config is setup during ss-install wizard but ss-functions is hardcoded ##

## source ss-config ##
source /var/www/ss-config

## source ss-functions ##
source /var/www/ss-functions

## BELOW THIS RELIES ON SS-CONFIG AND SS-FUNCTIONS

####################################################################################################
#### B. SS-Install-MySQL-Config: Touch Timestamp File ##############################################
####################################################################################################

## this is a dummy timestamp file that will remember the last time this script was run ##
## it can be useful for developer reference and is sometimes used by SlickStack ##

ss_touch "${TIMESTAMP_SS_INSTALL_MYSQL_CONFIG}"

####################################################################################################
#### C. SS-Install-MySQL-Config: Message (Begin Script) ############################################
####################################################################################################

## this is a simple message that announces to the shell the purpose of this bash script ##
## it will only be seen by sudo users who manually run this script in the shell ##

ss_echo "${COLOR_INFO}Running ss-install-mysql-config... ${COLOR_RESET}"


## delete 
if [[ "${DB_REMOTE}" != "true" ]]; then

####################################################################################################
#### D. SS-Install-MySQL-Config: Install My.conf ###################################################
####################################################################################################

## here we overwrite the default my.cnf with an optimized version (sub-config disabled) ##
## all MySQL logs should be found under /var/www/logs/ after install complete ##

## stop MySQL ##
/etc/init.d/mysql* stop > /dev/null 2>&1
sleep 15s
# killall mysql* > /dev/null 2>&1
# sleep 15s

if [[ "${SYSTEM_UBUNTU_VERSION}" == "24.04" ]]; then 
    ss_wget "${TMP_MY_CNF}" "${GITHUB_MY_CNF_80}"
fi

if [[ "${SYSTEM_UBUNTU_VERSION}" == "22.04" ]]; then 
    ss_wget "${TMP_MY_CNF}" "${GITHUB_MY_CNF_80}"
fi

if [[ "${SYSTEM_UBUNTU_VERSION}" == "20.04" ]]; then 
    ss_wget "${TMP_MY_CNF}" "${GITHUB_MY_CNF_80}"
fi

if [[ "${SYSTEM_UBUNTU_VERSION}" == "18.04" ]]; then 
    ss_wget "${TMP_MY_CNF}" "${GITHUB_MY_CNF_57}"
fi

#### MySQL Settings ####
## SQL mode ##
if [[ -z "${SQL_MODE}" ]]; then 
    ss_sed "s/@SQL_MODE/STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@SQL_MODE/${SQL_MODE}/g" "${TMP_MY_CNF}"
fi

## max allowed packet ##
if [[ -z "$SQL_MAX_ALLOWED_PACKET" ]]; then 
    ss_sed "s/@SQL_MAX_ALLOWED_PACKET/256M/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@SQL_MAX_ALLOWED_PACKET/${SQL_MAX_ALLOWED_PACKET}/g" "${TMP_MY_CNF}"
fi

## connect timeout ##
if [[ -z "$SQL_CONNECT_TIMEOUT" ]]; then 
    ss_sed "s/@SQL_CONNECT_TIMEOUT/30/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@SQL_CONNECT_TIMEOUT/${SQL_CONNECT_TIMEOUT}/g" "${TMP_MY_CNF}"
fi

## wait timeout ##
if [[ -z "$SQL_WAIT_TIMEOUT" ]]; then 
    ss_sed "s/@SQL_WAIT_TIMEOUT/3600/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@SQL_WAIT_TIMEOUT/${SQL_WAIT_TIMEOUT}/g" "${TMP_MY_CNF}"
fi

## interactive timeout ##
if [[ -z "$SQL_INTERACTIVE_TIMEOUT" ]]; then 
    ss_sed "s/@SQL_INTERACTIVE_TIMEOUT/43200/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@SQL_INTERACTIVE_TIMEOUT/${SQL_INTERACTIVE_TIMEOUT}/g" "${TMP_MY_CNF}"
fi

## net read timeout ##
if [[ -z "$SQL_NET_READ_TIMEOUT" ]]; then 
    ss_sed "s/@SQL_NET_READ_TIMEOUT/30/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@SQL_NET_READ_TIMEOUT/${SQL_NET_READ_TIMEOUT}/g" "${TMP_MY_CNF}"
fi

## net write timeout ##
if [[ -z "$SQL_NET_WRITE_TIMEOUT" ]]; then 
    ss_sed "s/@SQL_NET_WRITE_TIMEOUT/60/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@SQL_NET_WRITE_TIMEOUT/${SQL_NET_WRITE_TIMEOUT}/g" "${TMP_MY_CNF}"
fi


## set InnoDB buffer pool size (supports "auto" or manual via ss-config) ##
PERCENT_RAM_MB=$(free -m | awk 'NR==2{printf "%d", $2*0.50 }')

if [[ "$INNODB_BUFFER_POOL_SIZE" == "auto" ]]; then 
    ss_sed "s/@INNODB_BUFFER_POOL_SIZE/${PERCENT_RAM_MB}M/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@INNODB_BUFFER_POOL_SIZE/${INNODB_BUFFER_POOL_SIZE}/g" "${TMP_MY_CNF}"
fi

## set InnoDB flush method ##
if [[ -z "$INNODB_FLUSH_METHOD" ]]; then 
    ss_sed "s/@INNODB_FLUSH_METHOD/O_DIRECT/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@INNODB_FLUSH_METHOD/${INNODB_FLUSH_METHOD}/g" "${TMP_MY_CNF}"
fi

## set InnoDB log file size ##
if [[ -z "$INNODB_LOG_FILE_SIZE" ]]; then 
    ss_sed "s/@INNODB_LOG_FILE_SIZE/256M/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@INNODB_LOG_FILE_SIZE/${INNODB_LOG_FILE_SIZE}/g" "${TMP_MY_CNF}"
fi

## set InnoDB log files in group ##
if [[ -z "$INNODB_LOG_FILES_IN_GROUP" ]]; then 
    ss_sed "s/@INNODB_LOG_FILES_IN_GROUP/1/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@INNODB_LOG_FILES_IN_GROUP/${INNODB_LOG_FILES_IN_GROUP}/g" "${TMP_MY_CNF}"
fi

## set InnoDB flush_log_at_trx_commit ##
if [[ "$INNODB_FLUSH_LOG_AT_TRX_COMMIT" == "false" ]]; then
    ss_sed "s/@INNODB_FLUSH_LOG_AT_TRX_COMMIT/0/g" "${TMP_MY_CNF}"
else 
    ss_sed "s/@INNODB_FLUSH_LOG_AT_TRX_COMMIT/1/g" "${TMP_MY_CNF}"
fi

####################################################################################################
#### E. SS-Install-MySQL-Config: Remove Version-Specific Settings ##################################
####################################################################################################

## delete incompatible settings if running mysql 8.0 ##
if [[ "${SYSTEM_UBUNTU_VERSION}" == "20.04" ]]; then
    ss_sed '/## MySQL 5.7/d' "${TMP_MY_CNF}"
fi

## delete incompatible settings if running mysql 5.7 ##
if [[ "${SYSTEM_UBUNTU_VERSION}" == "18.04" ]]; then
    ss_sed '/## MySQL 8.0/d' "${TMP_MY_CNF}"
fi

## copy files to their destinations ##
ss_cp "${TMP_MY_CNF}" "${PATH_MY_CNF}"

####################################################################################################
#### F. SS-Install-MySQL-Config: Restart Modules (MySQL) ###########################################
####################################################################################################

source "${PATH_SS_RESTART_MYSQL}"

####################################################################################################
#### G. SS-Install-MySQL-Config: Reset Permissions (MySQL Config) ##################################
####################################################################################################

source "${PATH_SS_PERMS_MYSQL_CONFIG}"

fi

####################################################################################################
#### SlickStack: Reset Permissions (SlickStack Scripts) ############################################
####################################################################################################

## we include this permissions reset in all cron jobs and bash scripts for redundancy ##
## chmod 0700 means only the root/sudo users can execute any SlickStack scripts ##

## THIS SNIPPET DOES NOT RELY ON SS-CONFIG OR SS-FUNCTIONS
## SNIPPET: ss bash scripts, ss cron jobs
## UPDATED: 02JUL2022

chown root:root /var/www/ss* ## must be root:root
chown root:root /var/www/crons/*cron* ## must be root:root
chown root:root /var/www/crons/custom/*cron* ## must be root:root
chmod 0700 /var/www/ss* ## 0700 means only root/sudo can execute
chmod 0700 /var/www/crons/*cron* ## 0700 means only root/sudo can execute
chmod 0700 /var/www/crons/custom/*cron* ## 0700 means only root/sudo can execute

####################################################################################################
#### SlickStack: External References Used To Improve This Script (Thanks, Interwebz) ###############
####################################################################################################

## Ref: https://github.com/littlebizzy/slickstack/issues/23
## Ref: https://stackoverflow.com/questions/36301100/how-do-i-turn-off-the-mysql-password-validation
## Ref: https://dba.stackexchange.com/questions/4614/cannot-drop-anonymous-user-from-mysql-user
## Ref: https://www.networkinghowtos.com/howto/disable-remote-root-logins-into-mysql/
## Ref: https://www.oreilly.com/library/view/mysql-in-a/9780596514334/re39.html
## Ref: https://stackoverflow.com/questions/36463966/when-is-flush-privileges-in-mysql-really-needed
## Ref: https://askubuntu.com/questions/210976/apt-get-remove-with-wildcard-removed-way-more-than-expected-why
## Ref: https://dev.mysql.com/doc/refman/8.0/en/grant.html
## Ref: https://geert.vanderkelen.org/2018/mysql8-unattended-dpkg/
## Ref: https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0
## Ref: https://www.techiediaries.com/ubuntu/install-mysql-8-ubuntu-20-04/
## Ref: https://kifarunix.com/install-mysql-8-on-ubuntu-20-04/
## Ref: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04
## Ref: https://mysqlserverteam.com/upgrading-to-mysql-8-0-default-authentication-plugin-considerations/
## Ref: https://dba.stackexchange.com/questions/209514/what-is-mysql-native-password
## Ref: https://linuxconfig.org/how-to-reset-root-mysql-password-on-ubuntu-18-04-bionic-beaver-linux
## Ref: https://askubuntu.com/questions/1242142/mysql-install-error-database-files-are-locked-daemon-already-running-warning
## Ref: https://stackoverflow.com/questions/11657829/error-2002-hy000-cant-connect-to-local-mysql-server-through-socket-var-run
## Ref: https://askubuntu.com/questions/1242142/mysql-install-error-database-files-are-locked-daemon-already-running-warning
## Ref: https://www.digitalocean.com/community/questions/connecting-to-mysql-without-pw-can-t-connect-to-local-mysql-server-through-socket-var-run-mysqld-mysqld-sock-2
## Ref: https://serverfault.com/questions/582866/mysql-socket-configuration-issue-in-my-cnf
## Ref: https://stackoverflow.com/questions/11990708/error-cant-connect-to-local-mysql-server-through-socket-var-run-mysqld-mysq
## Ref: https://medium.com/@aungzanbaw/how-to-reset-root-user-password-in-mysql-8-0-a5c328d098a8
## Ref: https://stackoverflow.com/questions/49931541/mysql-changing-authentication-type-from-standard-to-caching-sha2-password
## Ref: https://stackoverflow.com/questions/35978228/how-to-solve-innodb-unable-to-lock-ibdata1-mysql-error
## Ref: https://bobcares.com/blog/innodb-unable-to-lock-ibdata1-error-11/
## Ref: https://stackoverflow.com/questions/34954455/mysql-daemon-lock-issue/46779157
## Ref: https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html
## Ref: https://dev.mysql.com/doc/refman/8.0/en/server-options.html
## Ref: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_init_file
## Ref: https://www.pixelstech.net/article/1545701135-How-to-reset-root-password-in-MySQL-8
## Ref: https://stackoverflow.com/questions/61430362/ubuntu-20-04-set-mysql-phpmyadmin-root-password
## Ref: https://www.cloudbooklet.com/how-to-install-mysql-on-ubuntu-20-04/
## Ref: https://www.tecmint.com/reset-root-password-in-mysql-8/
## Ref: https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html
## Ref: https://serverfault.com/questions/500739/reinstall-mysql-and-keep-existing-database-tables-and-data
## Ref: https://askubuntu.com/questions/643251/having-trouble-installing-and-removing-mysql-in-ubuntu
## Ref: https://stackoverflow.com/questions/21620406/how-do-i-pause-my-shell-script-for-a-second-before-continuing
## Ref: https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781788395809/1/ch01lvl1sec15/starting-or-stopping-mysql-8
## Ref: http://dev.cs.ovgu.de/db/mysql/Common-errors.html
## Ref: https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server
## Ref: https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded
## Ref: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/upgrading-from-previous-series.html
## Ref: https://dev.mysql.com/doc/mysql-secure-deployment-guide/8.0/en/secure-deployment-configure-authentication.html
## Ref: https://phoenixnap.com/kb/access-denied-for-user-root-localhost
## Ref: https://www.sqlshack.com/how-to-install-mysql-on-ubuntu-18-04/
## Ref: https://mysql.tutorials24x7.com/blog/reset-root-password-of-mysql
## Ref: https://phoenixnap.com/kb/how-to-reset-mysql-root-password-windows-linux
## Ref: https://www.oreilly.com/library/view/mysql-8-cookbook/9781788395809/7ea345a8-d753-48cd-a11b-8e6b6a06cacc.xhtml
## Ref: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
## Ref: https://devanswers.co/install-secure-mysql-server-ubuntu-20-04/
## Ref: https://mariadb.com/kb/en/pluggable-authentication-overview/
## Ref: https://dev.mysql.com/doc/refman/8.0/en/connection-options.html
## Ref: https://stackoverflow.com/questions/41846000/mariadb-password-and-unix-socket-authentication-for-root
## Ref: https://www.percona.com/live/e18/sites/default/files/slides/Upgrading%20to%20MySQL%208.0%20and%20a%20More%20Automated%20Experience%20-%20FileId%20-%20159893.pdf
## Ref: https://superuser.com/questions/603026/mysql-how-to-fix-access-denied-for-user-rootlocalhost
## Ref: https://github.com/GoogleCloudPlatform/click-to-deploy/blob/master/vm/chef/cookbooks/mysql8-book/recipes/configure-apt-repo-version-8.0.rb
## Ref: https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/
## Ref: https://unix.stackexchange.com/questions/457388/how-to-find-out-the-variable-names-for-debconf-set-selections
## Ref: https://www.codementor.io/@slaith/setting-a-root-password-on-mysql-in-silent-mode-in-ubuntu-y6do6ug2u
## Ref: https://stackoverflow.com/questions/23358918/preconfigure-an-empty-password-for-mysql-via-debconf-set-selections
## Ref: https://forums.mysql.com/read.php?11,666369,666533#msg-666533
## Ref: https://gist.github.com/ziadoz/dc935a0167c68fc23b4f35ee8593125f
## Ref: https://www.reddit.com/r/mysql/comments/3vch8p/uninstall_mysql_via_shell_without_prompt/
## Ref: https://ubuntu.pkgs.org/19.10/ubuntu-main-amd64/mysql-server-8.0_8.0.17-0ubuntu2_amd64.deb.html
## Ref: https://serverfault.com/questions/813210/does-debconf-set-selections-automatically-deletes-values-from-debconf-database-o
## Ref: https://askubuntu.com/questions/399903/unattended-phpmyadmin-install-end-up-throwing-errors
## Ref: https://severalnines.com/database-blog/moving-mysql-57-mysql-80-what-you-should-know
## Ref: https://medium.com/@benmorel/remove-the-mysql-root-password-ba3fcbe29870
## Ref: https://dev.mysql.com/doc/refman/8.0/en/validate-password-installation.html
## Ref: https://dev.mysql.com/doc/refman/8.0/en/default-privileges.html
## Ref: https://vander.host/knowledgebase/databases/cant-login-as-root-to-phpmyadmin-on-new-server-install/
## Ref: https://devanswers.co/phpmyadmin-access-denied-for-user-root-localhost/
## Ref: https://serverfault.com/questions/906490/mysql-rejects-connections-with-auth-socket
## Ref: https://bugs.mysql.com/bug.php?id=79269
## Ref: https://php.watch/articles/PHP-7.4-MySQL-8-server-gone-away-fix
## Ref: https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost
## Ref: https://stackoverflow.com/questions/52364415/php-with-mysql-8-0-error-the-server-requested-authentication-method-unknown-to
## Ref: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04-quickstart
## Ref: https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
## Ref: https://www.percona.com/blog/2019/11/01/use-mysql-without-a-password/
## Ref: https://gist.github.com/jessuppi/ab5517aa50b3bc83ac37abb8267e005b
## Ref: https://dev.mysql.com/doc/refman/8.0/en/option-file-options.html
## Ref: https://docs.oracle.com/cd/E17952_01/mysql-5.6-en/mysqld-safe.html
## Ref: http://doc.mawan.de/dictionaries-common/README.problems
## Ref: https://askubuntu.com/questions/429512/dpkg-or-something-related-is-corrupted
## Ref: https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-18-04
## Ref: https://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html
## Ref: https://unix.stackexchange.com/questions/47584/in-a-bash-script-using-the-conditional-or-in-an-if-statement
## Ref: https://www.baeldung.com/linux/delete-lines-containing-string-from-file

## SS_EOF