Bash script to backup/restore mysql databases/users


This note contains couple of bash script that will allow you to backup/restore mysql users and databases
Backup:

for db in $(echo "show databases;" |mysql |grep -v "Database\|^mysql$\|information_schema\|performance_schema\|^test"); 
do 
   mysqldump $db |gzip > "$db".sql.gz; 
   echo "done with $db"; 
done

Restore:

for db in $(ls |cut -d "." -f 1); 
do 
   mysql $db < "$db".sql; 
  echo "done with $db"; 
done

Run the following if you need to create databases before restoring data:

for db in $(ls |cut -d "." -f 1); 
do 
   echo "create database $db;" |mysql; 
   mysql $db < "$db".sql; 
   echo "done with $db"; 
done

All the stuff from the above can be pasted into the linux shell. I print the "done with $db" to keep the track of the processed databases.

You can use the following to get the list of mysql users with their grants:

for user in $(echo "select concat(user,'@',host) from mysql.user;" |mysql |grep -v concat); 
do 
   echo "show grants for $(echo $user|sed -e "s|@|'@'|g" -e "s|^|'|g" -e "s|$|'|g");" |mysql |grep -v "^Grants for\|root" |sed 's/$/;/g'; 
done

The result can be pasted into the mysql console on the new server. I exclude the root user otherwise the existing user on the new server might get overwritten.

Share Button

Leave a Reply

You must be logged in to post a comment.