Hello there!
If you are familiar with RDS and especially with MySQL running on RDS you may already know but for me this was a new thing that I missed when setting up and importing an existing database to MySQL.
AWS documentation clearly states to achieve a robust backup solution – in terms of restore options and data integrity – with MySQL running MyISAM engine is a big no-no. However if you fail to convert the database and attached tables to InnoDB upon setup and enabled auto-backups of your DB then you will be presented with the following warning after each scheduled backup.
“DB instance ‘yourservername’ contains MyISAM tables that have not been migrated to InnoDB. These tables can impact your ability to perform point-in-time restores. Consider converting these tables to InnoDB”
Further information/explanation found under “Automated Backups with Unsupported MySQL Storage Engines” section
Let’s get our hands dirty and fix this shall we?!
First of all need to establish a connection to the MySQL server. Easiest would be to jump on it from another server within the same VPC that has access to the private subnet – strongly recommend to keep the DB server in a private subnet with no NAT or route out to the internet. AWS takes care of the patching and running of the underlying instance anyways so it has no need to connect out – your setup and mileage may vary though.
From bash type mysql -h 'rds-server-dns-name-without-port' -u 'username' -p and then press Enter. It will prompt for the password enter it and press Enter again.
Once logged in let’s check if AWS was right in thinking some DBs were running MyISAM engine in MySQL. There are multiple ways doing this I used the following script.
SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM informatENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
This query will search for all tables in all DBs except the three mentioned in brackets; mysql, information_schema, performance_schema.
Now you can go ahead and ALTER the tables manually one by one. Given there are 17 tables this should not be too bad but what happens if there are more than that and we don’t have the time or will to do it manually? Well let’s script it!
I found a very useful script online and tweaked it a little bit to suit my setup better.
#!/bin/bash
#
# Converts all MyISAM tables in all databases to InnoDB
#
DATABASES="ALL"
MYSQL_USER='username'
MYSQL_PASS='your password here'
MYSQL_HOST='dbname.servername.availability-zone.rds.amazonaws.com'
#
# Leave everything as is below
set -u
#
# MySQL command we will use
MYSQL_COMMAND="mysql -s -u "$MYSQL_USER" -h $MYSQL_HOST"
MYSQL_COMMAND="mysql -s -u "$MYSQL_USER" --password="$MYSQL_PASS" -h $MYSQL_HOST"
#
# get a list of databases if we want to convert all databases
if [ "$DATABASES" == "ALL" ] ; then
DATABASES=$(echo "SHOW DATABASES" | $MYSQL_COMMAND | egrep -v '(performance_schema|information_schema|mysql)')
fi
#
#
for DATABASE in $DATABASES ; do
echo Converting $DATABASE
# Check if the table is MyISAM (we don't want to convert InnoDB tables over and over again)
TABLES=$(echo "SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = '$DATABASE' and ENGINE = 'MyISAM'" | $MYSQL_COMMAND)
for TABLE in $TABLES ; do
echo Converting MyISAM $TABLE to InnoDB
echo "ALTER TABLE $TABLE ENGINE = INNODB" | $MYSQL_COMMAND $DATABASE
done
if [ "x$TABLES" = "x" ] ; then
echo No MyISAM tables found in $DATABASE database
fi
echo
done
Feel free to copy the whole lot and tweak it to suit you and save it as a shell file.
When done logon to a server that has access to the RDS instance and run the script bash scriptname.sh. You will get a few Warning messages as your password is hardcoded in the program but it will complete nonetheless.
The output will show in which DB MyISAM engine was found. With the list of affected DBs let’s connect back to MySQL with the first command mysql -h 'rds-server-dns-name-without-port' -u 'username' -p.
This time let’s query the databases directly SELECT TABLE_NAME, ENGINE FROM information_schema.tables WHERE TABLE_SCHEMA = 'database_name';
To confirm the conversion worked start a manual snapshot and check if it gives any warnings in the console.
Feel free to send me a message or leave a comment if you found this helpful. Thanks!