KV.2020-02-01
User Guides
Advanced Settings
Change Logs
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 scriptBackup Users
Step 1: On your server, in terminal, prepare the following bash file using vi (or vim or any text-editor of choice)
1
vim mariadb_user_backup.sh
Copied!
​
Step 2: Paste in the content of the file as follow:
mariadb_user_backup.sh
1
#!/bin/sh
2
​
3
# This bash script is used to save users and permission from mysql database.
4
# Ref: https://stackoverflow.com/a/57878326
5
# explanation: https://wisdmlabs.com/blog/migrate-mysql-users-one-server-another/
6
​
7
HOSTNAME="localhost"
8
logininfo="-u root -pPASSWORD"
9
outfile="/path/oe_backup-user-$(date +\%Y\%m\%d).sql"
10
​
11
# The line below will save all users except 'root', empty name and specified ones
12
mysql ${logininfo} -B -N -e "SELECT CONCAT('\'',user,'\'@\'',host,'\'') from user where user != 'root' AND user != '' AND user not like 'auto%'" mysql | \
13
while read uh
14
do
15
echo "SHOW GRANTS FOR ${uh};"
16
done | mysql ${logininfo} -B -N | sed -e 's/$/;/' > ${outfile}
Copied!
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:1
chmod u+x mariadb_user_backup.sh
Copied!
​
Step 4: Execute the script file to obtain the user backup file
1
./mariadb_user_backup.sh
Copied!
​
Restore Users
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:
1
mysql_upgrade # Upgrade MySQL data
2
systemctl restart mariadb # Restart mariadb
Copied!
Last modified 2yr ago
Export as PDF
Copy link