Skip to content

Instantly share code, notes, and snippets.

@armenr
Last active November 3, 2016 07:21
Show Gist options
  • Save armenr/ebbc2a5a2aed83156cd222153b68210e to your computer and use it in GitHub Desktop.
Save armenr/ebbc2a5a2aed83156cd222153b68210e to your computer and use it in GitHub Desktop.
Restart MySQL with buffer pool save & reload
#!/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%';"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment