vim mariadb_user_backup.shpath : use your specified path to the output file#!/bin/sh
# This bash script is used to save users and permission from mysql database.
# Ref: https://stackoverflow.com/a/57878326
# explanation: https://wisdmlabs.com/blog/migrate-mysql-users-one-server-another/
HOSTNAME="localhost"
logininfo="-u root -pPASSWORD"
outfile="/path/oe_backup-user-$(date +\%Y\%m\%d).sql"
# The line below will save all users except 'root', empty name and specified ones
mysql ${logininfo} -B -N -e "SELECT CONCAT('\'',user,'\'@\'',host,'\'') from user where user != 'root' AND user != '' AND user not like 'auto%'" mysql | \
while read uh
do
echo "SHOW GRANTS FOR ${uh};"
done | mysql ${logininfo} -B -N | sed -e 's/$/;/' > ${outfile}chmod u+x mariadb_user_backup.sh./mariadb_user_backup.shmysql_upgrade # Upgrade MySQL data
systemctl restart mariadb # Restart mariadbPassword
This is procedure to backup Open Enventory database and restore it later on the same server or migrating to a different server
.xz at the end of the file as above.-v : verbose, give more info[/path/backup-database] and [/path/backup-user]: any path and filename
mysqldump -u [user] -p [database] > [Path]/[backup_filename].sqlmysqldump -u [user] -p [database] | xz > [Path]/[backup_filename].sql.xzmysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sqlunxz -vk [/path/backup-database].sql.xzmysql -u root -p [database-name] < [/path/backup-database].sql #for database restore
mysql -u root -p mysql < [/path/backup-user].sql #for users restore, this might not work for mariabd 10.4+ /Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p [database] > [Path]/[backup_filename].sql/Applications/XAMPP/xamppfiles/bin/mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql/Applications/XAMPP/xamppfiles/bin/mysql -u root -p [database-name] < [/path/to/backup-database].sql #for database restore
/Applications/XAMPP/xamppfiles/bin/mysql -u root -p mysql < [/path/to/backup-user].sql #for users restore, this might not work for mariabd 10.4+c:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p [database] > [Path]/[backup_filename].sqlc:\Programs\XAMPP\mysql\bin\mysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sqlc:\Programs\XAMPP\mysql\bin\mysql -u root -p [database-name] < [/path/to/backup-database].sql #for database restore
c:\Programs\XAMPP\mysql\bin\mysql -u root -p mysql < [/path/to/backup-user].sql #for users restore, this might not work for mariabd 10.4+