Full back up, update and reinstall of MySQL Ubuntu 16

Back up MySQL manually or by script

Manual back up

If you want to back up only one MySQL schema them you can do:

#all tables in schema
mysqldump -u [uname] -p[pass] db_name > db_backup.sql
#specific tables in schema
mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql
#samples
mysqldump -u root --all-databases > all_databases.sql
mysqldump --all-databases > all_databases.sql

and then to reimport the database by:

#all tables in schema
mysqldump -u [uname] -p[pass] --all-databases > db_backup.sql
#specific tables in schema
mysqldump -u [uname] -p[pass] db_name > table_backup.sql

Note: For windows you will need to do:

cd C:\user\mysql\bin
mysqldump -u[username] -p[password] --all-databases > C:\user\localhost.sql

Back up with script

If you want to back all databases with a single script then you can use this script:

#!/bin/bash

# Parent backup directory
backup_parent_dir="/home/backups/mysql"
# MySQL settings
mysql_user="root"
mysql_password="rootpass"

# Read MySQL password from stdin if empty
if [ -z "${mysql_password}" ]; then
 echo -n "Enter MySQL ${mysql_user} password: "
 read -s mysql_password
 echo
fi

# Verify MySQL password
echo exit | mysql --user=${mysql_user} --password=${mysql_password} -B 2>/dev/null
if [ "$?" -gt 0 ]; then
 echo "MySQL ${mysql_user} password incorrect"
 exit 1
else
 echo "MySQL ${mysql_user} password correct."
fi

# Make backup directory
backup_date=`date +%Y_%m_%d_%H_%M`
backup_dir="${backup_parent_dir}/${backup_date}"
echo "Backup directory: ${backup_dir}"
mkdir -p "${backup_dir}"
chmod 700 "${backup_dir}"

# Get all MySQL schemas
mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} --password=${mysql_password} -B | sed /^Database$/d`

# Backup and compress each database
for database in $mysql_databases
do
 if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
       additional_mysqldump_params="--skip-lock-tables"
 else
       additional_mysqldump_params=""
 fi
 echo "Creating backup of \"${database}\" database"
 mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.gz"
 chmod 600 "${backup_dir}/${database}.gz"
done

Reinstall

Note: If you need more information and examples on MySQL installation and uninstallation you can find them here:
Install, Reinstall, Uninstall MySQL on Ubuntu 16

Then you can reinstall MySQL by first removing the old installation:

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean

Then install the version that you want:

sudo apt-get update
sudo apt-get install mysql-server mysql-client mysql-common
sudo mysql_secure_installation 

Upgrade MySQL on Ubuntu

If you want to upgrade your MySQL installation only one version you can try to do it by:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server-5.7

For upgrade more than one version. For example: MySQL 5.5 Server to MySQL 5.7 you can do it by

wget http://dev.mysql.com/get/mysql-apt-config_0.8.0-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.0-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

Related Article