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