MySQL Replication Tuned for Laravel

.dbs

simmfins
simmfins_trail

00_create_my_cnf.sh

#!/bin/bash

echo "Enter user (homestead): "
read mysql_user_temp

echo "Enter password (secret): "
read -s mysql_password_temp

mysql_user=${mysql_user_temp:=homestead}
mysql_password=${mysql_password_temp:=secret}

my_cnf_path="$HOME/.my.cnf";

if [ -f $my_cnf_path ]; then
	rm $my_cnf_path
fi

echo "[client]" >> $my_cnf_path
echo "user=$mysql_user" >> $my_cnf_path
echo "password=$mysql_password" >> $my_cnf_path

chmod 600 $my_cnf_path

01_master_replication.sh

#!/bin/bash

# This should be run in the master server.
# Require 00_create_my_cnf.sh to be run first.

# https://serversforhackers.com/c/configuring-mysql-replication

# Edit /etc/mysql/mysql.conf.d/mysqld.cnf, this path is Laravel Forge specific.
# Uncomment server-id and log_bin

# NOTE: Please create a firewall rule to open mysql port (3306) exclusive for slave ip address
#       If it behind a load balancer use slave private ip.

script_path="$( cd "$(dirname "$0")" ; pwd -P )"

echo "+-------------------------------------------------------+"
echo "| TASKS (Edit /etc/mysql/mysql.conf.d/mysqld.cnf)       |"
echo "+-------------------------------------------------------+"
echo "| 1. Uncomment server-id option and value must be 2.    |"
echo "| 2. Uncomment log_bin option.                          |"
echo "+-------------------------------------------------------+"
echo "(Press enter to open the file)"
read
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf;

# Restart MySQL Service
sudo service mysql restart

echo "Enter Replication User (replication): "
read replication_user_temp

replication_user=${replication_user_temp:=replication}

echo "Enter Replication User Password (replication): "
read -s replication_user_pass_temp

replication_user_pass=${replication_user_pass_temp:=replication}

echo "Enter Replication Slave Ip (127.0.0.1): "
read replication_host_temp

replication_host=${replication_host_temp:='127.0.0.1'}

# Grant Replication on given user credetials
mysql -e "GRANT REPLICATION SLAVE ON *.* TO '${replication_user}'@'${replication_host}' IDENTIFIED BY '${replication_user_pass}';"
mysql -e "FLUSH PRIVILEGES;"

# Lock Table for writing
mysql -e "FLUSH TABLES WITH READ LOCK;"

mysql -e "SHOW MASTER STATUS;" > $script_path/master_status.txt
mysql -e "SHOW MASTER STATUS;"

# Define your databases at .dbs, one database per line.
mkdir -p $script_path/databases
readarray databases < $script_path/.dbs

for database in "${databases[@]}"
do
   :
   mysqldump $database > "$script_path/databases/`echo $database | sed 's/ //g'`.sql"
done

mysql -e "UNLOCK TABLES;"

02_slave_replication.sh

#!/bin/bash

# This should be run in the slave server
# Require 00_create_my_cnf.sh to be run first.

# https://serversforhackers.com/c/configuring-mysql-replication

script_path="$( cd "$(dirname "$0")" ; pwd -P )"

echo "Please ensure the exported databases from master server have been copied in `databases` folder."
echo

# Edit /etc/mysql/mysql.conf.d/mysqld.cnf, this path is Laravel Forge specific.
echo "+-------------------------------------------------------+"
echo "| TASKS (Edit /etc/mysql/mysql.conf.d/mysqld.cnf)       |"
echo "+-------------------------------------------------------+"
echo "| 1. Uncomment server-id option and value must be 2.    |"
echo "| 2. Uncomment log_bin option.                          |"
echo "| 3. Ensure there's a relay log option.                 |"
echo "|      relay-log = /var/log/mysql/mysql-relay-bin.log   |" 
echo "+-------------------------------------------------------+"
echo "(Press enter to open the file)"
read
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf;

# Restart MySQL Service
sudo service mysql restart

echo "Enter Replication User In Master (replication): "
read replication_user_in_master_temp

replication_user_in_master=${replication_user_in_master_temp:=replication}

echo "Enter Replication User Password (replication): "
read -s replication_user_in_master_pass_temp

replication_user_in_master_pass=${replication_user_pass_temp:=replication}

echo "Enter Master Host (127.0.0.1): "
read master_host

master_host=${master_host_temp:='127.0.0.1'}

echo "Enter Master Log File: "
read master_log_file

echo "Enter Master Log Position: "
read master_log_pos

# Define your databases at .dbs, one database per line.
readarray databases < $script_path/.dbs

for database in "${databases[@]}"
do
   :
   database=`echo $database | sed 's/ //g'`
   mysql -e "CREATE DATABASE IF NOT EXISTS $database;"
   mysql $database < "$script_path/databases/$database.sql"
done

mysql -e "CHANGE MASTER TO MASTER_HOST='${master_host}',MASTER_USER='${replication_user_in_master}', MASTER_PASSWORD='${replication_user_in_master_pass}', MASTER_LOG_FILE='${master_log_file}', MASTER_LOG_POS=${master_log_pos};';"

mysql -e "START SLAVE;"
mysql -e "SHOW SLAVE STATUS;"

download_mysql.sh

mkdir -p /tmp/mysql

cd /tmp/mysql

curl -LO https://gist.githubusercontent.com/raymadrona/249b590846c45db6d3b11c71a810d822/raw/44174b8890f4037b5a60c1d03354c1c1f3aaee97/.dbs
curl -LO https://gist.githubusercontent.com/raymadrona/249b590846c45db6d3b11c71a810d822/raw/44174b8890f4037b5a60c1d03354c1c1f3aaee97/00_create_my_cnf.sh
curl -LO https://gist.githubusercontent.com/raymadrona/249b590846c45db6d3b11c71a810d822/raw/44174b8890f4037b5a60c1d03354c1c1f3aaee97/01_master_replication.sh
curl -LO https://gist.githubusercontent.com/raymadrona/249b590846c45db6d3b11c71a810d822/raw/44174b8890f4037b5a60c1d03354c1c1f3aaee97/02_slave_replication.sh