Best Practice for SQL Users Backup

While the quick user backup as this one-line commandmysqldump -u [user] -p mysql > [Path]/[user_backup_filename].sql might work when you have same version for backup and restore SQL. In most cases, you want to implement something similar to the following bash script

Backup Users

Step 1: On your server, in terminal, prepare the following bash file using vi (or vim or any text-editor of choice)

vim mariadb_user_backup.sh

Step 2: Paste in the content of the file as follow:

#!/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}

Step 3: make sure you (or the user that run the mariadb_user_backup.sh file has the sufficient permission (i.e. chown) and make the script file executable:

chmod u+x mariadb_user_backup.sh

Step 4: Execute the script file to obtain the user backup file

./mariadb_user_backup.sh

Restore Users

Similar to the guide here.

If your new SQL server is newer than your old one, after importing both database(s) and users, you can run the following command in terminal to upgrade MySQL:

mysql_upgrade    # Upgrade MySQL data
systemctl restart mariadb   # Restart mariadb