Drn@ecs.soton.ac.uk: Added proper code highlighting for bash

2019-08-05T15:57:04Z

Added proper code highlighting for bash

← Older revision		Revision as of 15:57, 5 August 2019
Line 4:		Line 4:
	# Run the script below direct from the console or in screen, in case you lose your network connection.		# Run the script below direct from the console or in screen, in case you lose your network connection.

	#!/bin/bash		<source lang="bash">
			#!/bin/bash

			if [ -z $1 ]; then
			echo "Usage: $0 <DATABASE_NAME>"
			exit 1
			fi

			database=$1
			read -p "Please enter the hostname of the server hosting this database [localhost]: " hostname
			read -p "Please enter a username that has permission to alter tables [root]: " username
			read -sp "Please enter the password for this user: " password

	~~if [ -z $1 ]; then~~		if [ -z "$hostname" ]; then
	~~echo "Usage: $0 <DATABASE_NAME>"~~		hostname="localhost"
	~~exit 1~~		fi
	fi		if [ -z "$username" ]; then
			username="root"
	~~database=$1~~		fi
	~~read -p "Please enter the hostname of the server hosting this database [localhost]: " hostname~~		mysql_password=""
	~~read -p "Please enter a username that has permission to alter tables [root]: " username~~		if [ ! -z $password ]; then
	~~read -sp "Please enter the password for this user: " password~~		mysql_password="-p$password"
			fi
	if [ -z "$hostname" ]; then		mysql_start="mysql -h $hostname -u $username $mysql_password $database"
	hostname="localhost"
	fi		for t in $(echo "show tables" \| $mysql_start --batch --skip-column-names); do
	if [ -z "$username" ]; then		sql="ALTER TABLE \`$t\` ENGINE = InnoDB;"
	username="root"		echo $sql
	fi		time $mysql_start -e "$sql";
	mysql_password=""		done
	if [ ! -z $password ]; then		</source>
	mysql_password="-p$password"
	fi
	mysql_start="mysql -h $hostname -u $username $mysql_password $database"

	for t in $(echo "show tables" \| $mysql_start --batch --skip-column-names); do
	sql="ALTER TABLE \`$t\` ENGINE = InnoDB;"
	echo $sql
	time $mysql_start -e "$sql";
	done

Drn@ecs.soton.ac.uk: Added script that can be used to convert database table engines

2019-08-05T15:54:53Z

Added script that can be used to convert database table engines

New page

Below is Bash script that will convert all tables to InnoDB if they are currently MyISAM. If tables are already InnoDB nothing will change and the script should complete quite quickly. If not you may find it takes several hours to run. It is advised that if you are running this on a live repository you do the following:
# Take the repository offline (i.e. either completely stop Apache and the EPrints indexer or get the former to load a splash screen saying the repository is down for maintenance).
# Take a backup of the database or if you are running the server as a virtual machine, a snapshot of the whole server.
# Run the script below direct from the console or in screen, in case you lose your network connection.

#!/bin/bash

if [ -z $1 ]; then
echo "Usage: $0 <DATABASE_NAME>"
exit 1
fi

database=$1
read -p "Please enter the hostname of the server hosting this database [localhost]: " hostname
read -p "Please enter a username that has permission to alter tables [root]: " username
read -sp "Please enter the password for this user: " password

if [ -z "$hostname" ]; then
hostname="localhost"
fi
if [ -z "$username" ]; then
username="root"
fi
mysql_password=""
if [ ! -z $password ]; then
mysql_password="-p$password"
fi
mysql_start="mysql -h $hostname -u $username $mysql_password $database"

for t in $(echo "show tables" | $mysql_start --batch --skip-column-names); do
sql="ALTER TABLE \`$t\` ENGINE = InnoDB;"
echo $sql
time $mysql_start -e "$sql";
done

Converting MyISAM tables to InnoDB - Revision history

Drn@ecs.soton.ac.uk: Added proper code highlighting for bash

Drn@ecs.soton.ac.uk: Added script that can be used to convert database table engines