Best Practice for SQL Users Backup
While the quick user backup as this one-line command
mysqldump -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 scriptStep 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 yourrootpassword or the password of the user that you use for the backup process. If you don't userootthen changerooton line 8 to your userpath: use your specified path to the output fileoe_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 old
rootuser 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
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
Last modified 3yr ago