#!/bin/bash
for f in *schema*.gz; do
  DBName=$(echo $f | cut -d - -f 5)
  echo "Create "${DBName}
  echo "create database if not exists ${DBName}" | /usr/bin/mysql -u root -pXXXXXX
  zcat ${f} | /usr/bin/mysql -u root -pXXXXXX ${DBName}
  echo "Created"
done

#!/bin/bash
for f in *data*.gz; do
  DBName=$(echo $f | cut -d - -f 5)
  echo "Import "${DBName}
  zcat ${f} | /usr/bin/mysql -u root -pXXXXXX ${DBName}
  echo "Imported"
done

/nfs/dbs/31/mysql-slave-31.lololololol.com-schema-user_cluster_1-11-27-16.sql.gz
/nfs/dbs/31/mysql-slave-31.lololololol.com-schema-user_cluster_2-11-27-16.sql.gz
...
 
/nfs/dbs/31/mysql-slave-31.lololololol.com-data-user_cluster_1-11-27-16.sql.gz
/nfs/dbs/31/mysql-slave-31.lololololol.com-data-user_cluster_2-11-27-16.sql.gz
...

MYSQL_PORT=$1
FILE_NAME=$2
MYSQL_IP=x.x.x.x
MYSQL_USER=uid
MYSQL_PASS=pwd
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_IP} -P${MYSQL_PORT}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
 
DBLISTFILE=/tmp/DatabasesToDump-${FILE_NAME}.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
 
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
 
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} | pv | gzip > db-${FILE_NAME}.sql.gz

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb && dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb && apt-get update && apt-get -y upgrade && apt-get -y install lvm2 xfsprogs percona-server-server-5.7
 
# add 2 x 128 ssd and 1 x 256 hdd
# ssd n enter enter enter enter t enter 8e enter w enter
fdisk /dev/sdc
fdisk /dev/sdd
 
# hdd
fdisk /dev/sde
 
pvcreate /dev/sdc1 /dev/sdd1 /dev/sde1 && vgcreate mysql /dev/sdc1 /dev/sdd1 && vgcreate data /dev/sde1 && lvcreate --name data --size 240G mysql && lvcreate --name backups --size 240G data
 
mkfs.xfs /dev/mapper/mysql-data && mkfs.xfs /dev/mapper/data-backups
 
edit /etc/fstab
/dev/mapper/mysql-data /var/lib/mysql auto  defaults,nobarrier 0 2
/dev/mapper/data-backups /data auto  defaults,nobarrier 0 2
 
service mysql stop && cd /var/lib && mv mysql mysql.old && mkdir mysql && mkdir /data && mount -a && mv mysql.old/* mysql/  && chown mysql:mysql mysql
 
vi /etc/security/limits.d/91-mysql.conf
mysql   soft    nofile 400000
mysql   hard    nofile 400000 
 
/etc/sysctl.conf
fs.file-max = 20000000
net.ipv4.tcp_fin_timeout = 10
kernel.pid_max = 65535
kernel.randomize_va_space = 1
net.core.netdev_max_backlog=32768
net.core.rmem_max = 8388608
net.core.somaxconn = 16384
net.core.wmem_max = 8388608
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.all.log_martians = 1
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.conf.default.log_martians = 1
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.secure_redirects = 0
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.ip_forward=0
net.ipv4.ip_local_port_range = 2000 65000
net.ipv4.tcp_max_syn_backlog=8192
net.ipv4.tcp_rmem = 4096 87380 8388608
net.ipv4.tcp_syncookies=0
net.ipv4.tcp_syncookies = 0
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 0
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_wmem = 4096 87380 8388608
vm.overcommit_memory = 1
vm.swappiness = 0
fs.aio-max-nr = 500000
 
sysctl -p
 
echo session required pam_limits.so >> /etc/pam.d/common-session
 
vi /etc/mysql/percona-server.conf.d/mysqld.cnf 
[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp
innodb_use_native_aio=1
max_connections=4096
wait_timeout=5
interactive_timeout=120
myisam_sort_buffer_size=1024M
sort_buffer_size=1024M
innodb_file_per_table=ON
skip-name-resolve
default-storage-engine=InnoDB
max_allowed_packet=64M
# 48 hours
expire_logs_days = 3
server-id              = 108
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_buffer_pool_size = 28000M
innodb_flush_method = O_DIRECT
innodb_file_per_table
bind-address = 0.0.0.0
log-error    = /var/log/mysql/error.log
#log-erorr = /dev/null
log_error_verbosity=3
# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES
sql_mode=""
symbolic-links=0
 
# Forum requires this
lower_case_table_names  = 1
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate-do-table              = account.identity_data
replicate-do-table              = account.signature_data
replicate-do-table              = account.account_usertypes
replicate-do-table              = account.banned_words
replicate-wild-do-table         = forum\_skeleton.%
replicate-wild-do-table = domain\_%.%
replicate-wild-do-table = user\_cluster\_%.%
slave-skip-errors               = 1062
open-files-limit=400000
myisam-recover-options=FORCE,BACKUP