All the following commands assumes we saved MySQL root password into MROOTPASS variable:
export MROOTPASS=<mysql root password>
The most straight forward method to backup all the MySQL databases and all the website files (PHP scripts, images, etc..) stored in the /home directory is the following:
mysqldump --all-databases -u root -p$MROOTPASS | gzip > all-databases-$(date '+%Y-%m-%d_%H-%M-%S').sql.gz tar -cvzf home.tar.gz /home
If we backup some individual database (probably not as root) and change its user while restoring it, it might make a sense to remove DEFINER from the output script:
sed -e 's/*]*\*/\*/'
The following commands restore all the websites from the archives:
gunzip -c all-databases-2017-05-23_15-31-00.sql.gz | mysql -u root -p$MROOTPASS cd / sudo tar -xvzf home.tar.gz
After migration from MySQL version 14.14 Distrib 5.5.54 to 14.14 Distrib 5.7.18 (I do not know what is the difference between them) I got the following error: “ERROR 1805 (HY000) at line 1: Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted” while trying to drop some user, and fixed it by running:
mysql_upgrade -u root -p$MROOTPASS service mysql restart
