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:

mariadb_user_backup.sh

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

• PASSWORD : change to your root password or the password of the user that you use for the backup process. If you don't use root then change root on line 8 to your user

• path : use your specified path to the output file

• oe_backup-user-$(date +\%Y\%m\%d).sql : the output file will have the current date concatenated at the end of the file

• This script above also not save oldroot user info or empty users. You can change it by modify the code on line 12

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