#!/bin/bash
#
# Perform MySQL 5.6+ restart with buffer pool save and reload

# MySQL credentials
user=
pass=

# Exit on any command failure
set -e

# Show me my pool stats
mysql -u $user -p$pass -e "SHOW STATUS LIKE '%buff%';"

# Dump the buffer pool
mysql -u $user -p$pass -e 'SET GLOBAL innodb_buffer_pool_dump_now=ON;'

# Wait for buffer dump to complete
echo "Waiting for dump to complete"

while ! mysql  -u $user -p$pass -e "SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';" | grep 'dump completed' | cut -d ' ' -f3- ; do
  sleep 2
done

# Restart MySQL
service mysql restart

# Load the saved buffer pool
mysql -u $user -p$pass -e 'SET GLOBAL innodb_buffer_pool_load_now=ON;'

echo "Waiting for load to complete"

# Wait for buffer pool load to complete
while mysql -u $user -p$pass -e "SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';" | grep '/'; do
  sleep 2
done

echo "Load succeeded"

# Show me my pool stats again
mysql -u $user -p$pass -e "SHOW STATUS LIKE '%buff%';"