1 of 1

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)

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

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

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:

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

Restore Users

Similar to the guide .

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:

Best Practice for SQL Users Backup

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:

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

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:

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

Restore Users

Similar to the guide .

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:

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}

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}

Best Practice for SQL Users Backup

hashtagBackup Users

hashtagRestore Users

Best Practice for SQL Users Backup

hashtagBackup Users

hashtagRestore Users

Backup Users

Restore Users

Backup Users

Restore Users