I have written a quick shell script to take each backup of MySql database & that works good.
I was using the same scripts for all mysql servers tool, I have installed it on ubuntu 10.04 server.
Now the problem is when run the shell script I am getting an error saying that as below
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
I have given the permission to the information_schema database but the above error still persists.
Can any one please help me to solve this issue. Let me know if any changes needs to be done in shell script.
Simple Shell Script to all my friends
#!/bin/bash
MyUser='root' # Username
d=`date +%b_%d_%Y_%H_%M`
m="/var/backups/bugzilla_backup/Bugzilla_Dump_$d"
mkdir $m
#Get List of Databases
/usr/bin/mysql -u $MyUser -Bse 'show databases' > /usr/local/bin/dbname
# Backup listed Databases
while read l; do /usr/bin/mysqldump $l -u $MyUser > $m/$l-$d; /bin/bzip2 $m/$l-$d; sleep 15; done < /usr/local/bin/dbname
Best Answer
The problem is that is is also trying to dump schema, this request was auto ignored until recently. You need to add a variable to the script to ignore certain tables.
Here is my script that ignores information_scheme, and also add the date and 24 hour time to the file names.