MySQL

sql_drop_tables.sh

Submitted by Andreas Fleckl on 
#!/bin/bash
# Script Version: 02
# Description: Drops all tables in a specified MySQL database. If only one argument is given, the user and database name will be the same.

# Check arguments
if [ $# -lt 1 ]; then
    echo "Usage: $0 <DB_USER> [DB_NAME]"
    exit 1
fi

# Assign arguments
DB_USER=$1
DB_NAME=${2:-$1} # If no second argument is provided, use the first as the database name.

# Prompt for password
read -sp "Enter MySQL password: " DB_PASS
echo

# Confirmation prompt
read -p "Are you sure you want to drop all tables in $DB_NAME?
Tags
MySQL
script

mysql_create_user_db.sh

Submitted by Andreas Fleckl on 
#!/bin/zsh
# Script Version: 1.1
# Description: Create MySQL Database and User (same name) with generated password

# Set variables
DBNAME=$1
PASSWORD=""
LOG_FILE="/var/log/mysql_db_user_creation.log"

# Functions
ask_for_input() {
    if [ -z "$DBNAME" ]; then
        read "DBNAME?Enter the database and username: "
    fi
}

generate_password() {
    PASSWORD=$(openssl rand -base64 12)
}

create_db_and_user() {
    echo "Creating MySQL Database and User..."
    mysql -u root -p <<EOF
CREATE DATABASE IF NOT EXISTS \`$DBNAME\`;
CREATE USER IF NOT EXIS
Tags
script
MySQL

nginx phpMyAdmin

Submitted by Andreas Fleckl on 
server {
       server_name phpmyadmin.bubuit.net;
       add_header Strict-Transport-Security "max-age=7200";
       location / {
               proxy_pass http://10.0.3.IP;
               include proxy_params;
               proxy_ignore_client_abort on;
               
               auth_basic "Restricted";
               auth_basic_user_file /etc/nginx/htpasswd;
       }
       include /etc/nginx/snippets/letsencrypt-acme-challenge.conf;
   listen 80; # managed by Certbot
}
  1. htpasswd -c /etc/nginx/htpasswd USERNAME

&nbsp

Tags
nginx
MySQL

MySQL Server

Submitted by Andreas Fleckl on
  1. ai default-mysql-client default-mysql-server
  2. mysql_secure_installation
    • mysqladmin -u root password 'PW'
  3. files
    •  /etc/mysql/my.cnf
    • /etc/mysql/debian.cnf
    • /etc/mysql/conf.d/mysqldump.cnf
    • /etc/mysql/mariadb.conf.d/50-client.cnf
Tags
MySQL

automysqlbackup

Submitted by Andreas Fleckl on
  1. ai automysqlbackup
  2. vim /etc/default/automysqlbackup
    1. BACKUPDIR="/var/backups/automysqlbackup"
    2. mkdir /var/backups/automysqlbackup
    3. /etc/cron.daily/automysqlbackup
    4. l /var/backups/automysqlbackup/daily/*/
  3. omz plugin info extract

restore

  1. unpack /var/backups/automysqlbackup/INTERVAL/DB-NAME/DB_INTERVAL_DATE.sql.COMPRESSION
    • bzip2 -dk .sql.bz2
    • gunzip .sql.gz
    • extract FILE
  2. mysql -u root -p DB-NAME < /var/backups/automysqlbackup/INTERVAL/DB
Tags
MySQL
backup

owncloud mysql

Submitted by Andreas Fleckl on
  1. mysql_secure_installation
  2. mysql -u root -p
  3. create database ocdb;
  4. grant all on ocdb.* to ocadmin@localhost identified by "PASSWD";
  5. flush privileges;
  6. quit
Tags
owncloud
MySQL

apache phpMyAdmin

Submitted by Andreas Fleckl on

<VirtualHost *:80>
DocumentRoot    /usr/share/phpmyadmin
ServerName    phpmyadmin.bubuit.net

<Directory /usr/share/phpmyadmin>
   Options SymLinksIfOwnerMatch
   DirectoryIndex index.php

Tags
apache
MySQL