Restoring MySQL Backups From Command Line

[Pandora]

Hi,

I have the following script to restore a backup from a dump. Syntax for executing this script might be:

./restore-db.sh 07-24-13 july24
where 07-24-13 is part of the backup.sql.gz file name and july24 is the MySQL DB to be created to hold the data

Here's the script:

#!/bin/sh

# Database name
FILE=/home/sitename/backups/site.$1.sql.gz
DBNAME=$2

# Does the backup source even exist?
if [ -f $FILE ];
then
   echo "Backup source found."
else
   echo "Backup file not found.  Please try again."
   exit 1
fi

# Find the backup file based on the args
echo "Creating database ${DBNAME}..."
mysql -u root -pPASS -e "create database if not exists ${DBNAME};"

# Do the restoration
echo "Restoring database..."
echo "Please wait..."
gunzip < $FILE | mysql -u root -pPASS ${DBNAME}

echo Done!

What I am having problems with is GRANT permissions for the database, to get this manually-created database to show up in cPanel and/or phpMyAdmin. I don't really want to create a global MySQL root user if I don't have to for security purposes.

What are the series of GRANT queries that I need to run to make this database accessible to a user? Let me know what the best solution is.

Thanks much.

 

[cPanelMichael]

Hello

You can utilize the database mapping utility to map the database to an account. EX:

[email protected] [~]# /usr/local/cpanel/bin/dbmaptool
/usr/local/cpanel/bin/dbmaptool cpuser --type mysql|pg --dbusers 'user1, user2' --dbs 'db1, db2'

           These flags are treated individually. This tool will not map a virtual user to a database.
           /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbs 'db1' --dbusers 'virt1'
           /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbs 'db1' && /usr/local/cpanel/bin/dbmaptool user1 --type mysql --dbusers 'virt1'
           The above two commands are analogous; result in cpuser 'user1' having privileges for 'db1' and having a virtual user named 'virt1.

Thank you.