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