Currently#

An old MySQL server (5.5.40) on an old Debian installation (3.2.63 with Kernel 3.2).

I want to either upgrade Debian to a newer version (maybe reinstallation?) or use Ubuntu 18.04 (currently being discussed in our team).

TODO#

First of all, I’m going to dump all databases.

This is done by this script and on the machine directly but could also be run remotely.

#! /bin/bash

# naming timestamp 
TIMESTAMP=$(date +"%F")

# the output directory
BACKUP_DIR="~/db_dumps"

# username and password for mysql connection and mysqldump
MYSQL_USER="user"
MYSQL_PASSWORD="password"

# get the binaries
MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)

# create output dir ifnexists 
mkdir -p "$BACKUP_DIR"
 
# get all dbs
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 
# iteration with folder and file creation
for db in $databases; do
	echo "dumping $db"
	$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db > "$BACKUP_DIR/$db-$TIMESTAMP.sql"
done

# a spinner while waiting
spin()
{
  spinner="/|\\-/|\\-"
  while :
  do
    for i in `seq 0 7`
    do
      echo -n "${spinner:$i:1}"
      echo -en "\010"
      sleep 1
    done
  done
}

# if gzip parameter is given, gzip all files afterwards
while [[ $# -gt 0 ]] && [[ ."$1" = .--* ]] ;
do
	spin &
	SPIN_PID=$!
	trap "kill -9 $SPIN_PID" `seq 0 15`

    opt="$1";
    shift;
	case "$opt" in
			"--gzip")
				echo "gzipping"
				for file in $BACKUP_DIR/*.sql; do
					gzip $file
				done
				;;
			"--zip")
				echo "zipping"
				for file in $BACKUP_DIR/*.sql; do
					zip -q $file.zip $file
					rm $file
				done
				;;
			"--tar")
				echo "creating tarball"
				for file in $BACKUP_DIR/*.sql; do
					tar -cvf $file.tar $file
					rm $file
				done
				;;
			"--targz")
				echo "creating tarball and gzipping"
				for file in $BACKUP_DIR/*.sql; do
					tar -czvf $file.tar.gz $file
					rm $file
				done
				;;
			*)
				;;
		esac
done

echo "done"
exit 0

And then I’m importing all dumps to a new server, which might not be set up yet.

#! /bin/bash

# username and password for mysql connection and mysqldump
MYSQL_USER="user"
MYSQL_PASSWORD="password"
MYSQL_PORT=3306
MYSQL_HOST=127.0.0.1;

# get the binaries
MYSQL=$(which mysql)

for file in "${1}/*.sql"; do
	$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST --port=$MYSQL_PORT < "$file"
done