Migration script for MySQL DBs for Xampp upgrade

Estimated reading time of this article: 2 minutes

How to migrate MySQL/MariaDB databases during a Xampp upgrade?

DBs and users grants are exported in the old DB and imported in the new DB.

Requierment: Bash, Linux

Assumption:

  • Old xampp is installed in /opt/lampp
  • New xampp is installed in /opt/lampp-new
#!/bin/bash

# Ref http://www.uptimemadeeasy.com/linux/mysql-migrate-users-server-server/

# mysqldump -u admin -p originaldb | mysql -u backup -p password duplicateddb;

dbs="YOUR DBs"

user=root
output=/tmp

# http://www.cyberciti.biz/tips/linux-unix-pause-command.html
function pause(){
   read -p "$*"
}

echo "Migrate DBs $dbs from /opt/lampp to /opt/lampp-new"
pause 'BE CAREFUL! Press [Enter]'

echo "Export"

sudo mv /usr/bin/mysql /usr/bin/mysql-old
sudo ln -s /opt/lampp/bin/mysql /usr/bin/mysql
sudo mv /usr/bin/mysqldump /usr/bin/mysqldump-old
sudo ln -s /opt/lampp/bin/mysqldump /usr/bin/mysqldump

sudo /opt/lampp/xampp start
sleep 5
echo
/opt/lampp/bin/php --version
echo
/opt/lampp/bin/mysql --version
echo

/opt/lampp/bin/mysqldump -u $user --dump-date --hex-blob --complete-insert --skip-lock-tables --single-transaction --routines --databases $dbs --add-drop-database > $output/migrate_db.sql
/opt/lampp/bin/mysql -u$user -s -N -B -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM mysql.user" > $output/show_grants.txt
/opt/lampp/bin/mysql -u$user -s -N -r < $output/show_grants.txt | perl -p -e's/$/;/' > $output/grants.sql
sudo /opt/lampp/xampp stop

pause 'Change. Press [Enter] key to continue...'

sudo mv /opt/lampp /opt/lampp-old
sudo mv /opt/lampp-new /opt/lampp

sudo mv /usr/bin/mysql /usr/bin/mysql-old
sudo ln -s /opt/lampp/bin/mysql /usr/bin/mysql
sudo mv /usr/bin/mysqldump /usr/bin/mysqldump-old
sudo ln -s /opt/lampp/bin/mysqldump /usr/bin/mysqldump

pause 'Import. Press [Enter] key to continue...'

sudo /opt/lampp/xampp start
sleep 5
echo
/opt/lampp/bin/php --version
echo
/opt/lampp/bin/mysql --version
echo

echo Import DB $output/migrate_db.sql
/opt/lampp/bin/mysql -u $user < $output/migrate_db.sql
echo Set grants $output/grants.sql
/opt/lampp/bin/mysql -u $user < $output/grants.sql

echo "Old XAMPP: /opt/lampp-old"
echo "New XAMPP: /opt/lampp"
(modified on ) –