Differences

This shows you the differences between two versions of the page.

--- servers:mysql [2019/02/13 22:04] – Sean Rhone
+++ servers:mysql [2019/02/13 22:07] (current) – Sean Rhone
@@ Line 1: / Line 1: @@
+====== Information ======
+  * MySQL Community Edition ((https://www.mysql.com/products/community/))
+  * [[Information:Realm of Espionage]]
+===== Prerequisites =====
+  * [[distros:fedora_server | Fedora Server]]
+====== Repository ======
+  * https://dev.mysql.com/downloads/repo/yum/
+  sudo dnf install 'https://dev.mysql.com/get/mysql80-community-release-fc29-2.noarch.rpm'
+====== 5.7 ======
+  * Disables the latest GA repo and enables the 5.7 repo
+  * This is necessary for TrinityCore
+  sudo dnf repolist all | grep 'mysql'
+  sudo dnf config-manager --disable 'mysql80-community'
+  sudo dnf config-manager --enable 'mysql57-community' && sudo dnf repolist enabled | grep 'mysql'
+====== Dependencies ======
+****
+  sudo dnf install 'mysql-community-server'
+====== Settings ======
+===== Initial =====
+****
+  echo '!includedir /etc/my.cnf.d' | sudo tee --append '/etc/my.cnf' > '/dev/null'
+===== Settings =====
+  * :!: The empty ''sql-mode'' setting is intentional ((this disables strict mode))
+  sudo mkdir -p '/etc/my.cnf.d' && sudo -e '/etc/my.cnf.d/custom.cnf'
+<code>
+[mysqld]
+character-set-client-handshake = FALSE
+character-set-server = utf8mb4
+collation-server = utf8mb4_unicode_ci
+expire_logs_days = 7
+max_binlog_size = 100M
+skip-networking = On
+sql-mode = </code>
+===== Verify =====
+****
+  mysqladmin variables -u 'root' -p | egrep 'utf8|networking|socket'
+====== Initial Setup ======
+===== Service =====
+****
+  sudo systemctl enable 'mysqld' --now
+===== Get Temporary Password =====
+****
+  cat '/var/log/mysqld.log' | grep 'temporary password'
+===== Setup =====
+****
+  mysql_secure_installation
+====== Database Repair and Optimization ======
+===== Create MySQL User =====
+  mysql -u 'root' -p
+  GRANT SELECT, INSERT ON *.* to 'maintenance'@'localhost' IDENTIFIED BY 'x';
+  FLUSH PRIVILEGES;
+===== Authentication File =====
+  sudo -u 'mysql' mkdir -p '/var/lib/mysql/auth' && sudo -u 'mysql' -e '/var/lib/mysql/auth/maintenance' && sudo chmod '600' '/var/lib/mysql/auth/maintenance'
+<code>
+[mysqlcheck]
+user=maintenance
+password=x</code>
+===== Service =====
+  sudo -e '/etc/systemd/system/db-m.service'
+<code>
+[Service]
+User=mysql
+Group=mysql
+Type=oneshot
+ExecStart='/usr/bin/mysqlcheck' --defaults-extra-file='/var/lib/mysql/auth/maintenance' --auto-repair --optimize --all-databases --force
+ExecStartPost='/usr/bin/sync'</code>
+===== Timer =====
+  sudo -e '/etc/systemd/system/db-m.timer' && sudo systemctl daemon-reload && sudo systemctl enable 'db-m.timer' --now && sudo systemctl start 'db-m' && sudo systemctl status 'db-m' -l
+<code>
+[Unit]
+Description=MySQL Database Check, Repair, and Optimization
+After=mysqld.service
+[Timer]
+OnCalendar=weekly
+Persistent=true
+[Install]
+WantedBy=timers.target</code>
+====== Notable Commands ======
+===== Show Users on Server =====
+****
+  select User,Host from mysql.user;
+===== Show Data Folder Location =====
+****
+  select @@datadir;
+===== Socket File =====
+****
+  /var/lib/mysql/mysql.sock
+===== Log =====
+****
+  /var/log/mysqld.log
+===== Reset =====
+  sudo systemctl stop 'mysqld'
+  sudo rm -Rf '/etc/my.cnf'* '/var/lib/mysql'* '/var/log/mysqld.log' && sync
+  sudo dnf reinstall 'mysql-community-server'
+===== Password Validation =====
+==== cnf ====
+****
+  validate_password_policy=LOW
+==== Query ====
+  mysql -u 'root' -p
+  SHOW VARIABLES LIKE 'validate_password%';
+==== Configure ====
+  * Default policy is ''MEDIUM''
+  mysql -u 'root' -p
+  SET GLOBAL validate_password_policy=LOW;
+==== Disable ====
+  mysql -u 'root' -p
+  UNINSTALL PLUGIN validate_password;
+==== Enable ====
+  mysql -u 'root' -p
+  INSTALL PLUGIN validate_password SONAME 'validate_password.so';