Errors in MySQL backups

[Mitchell]

I see following error in daily backup of MySQL. Should it be concerning?

###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

[slansing]

I found a question regarding the same issue on this site:

http://www.cyberciti.biz/faq/linux-unix ... ss-denied/

There are a couple ways to work past this error, let us know if this helped!

[Mitchell]

Thank you. Tried using GRANT

Code: Select all

GRANT SELECT,LOCK TABLES ON information_schema.* TO 'root'@'localhost';

however since 'root'@'localhost' does not have access to these tables, it is giving access denied for 'root'@'localhost' . Which user should I use to connect to DB in order to be able to execute the GRANT?

Regards
-Ashish

[scottwilkerson]

In a default install of XI, root user has access to these.

It is my guess that you changed the root mysql password and that is why you are receiving this error.

The solution is to edit the file /root/scripts/automysqlbackup and change the password in the file to be your root mysql password

[Mitchell]

Yes. the password for root user was changed and I changed the password in the /root/scripts/automysqlbackup file as well.
The backup is running and it is taking backup of 'nagios' and 'nagiosql' db successfully. The warning message at bottom of the email is only for DB 'information_schema'. I am not sure if it is becasue of password change?

Regards
Ashish

======================================================================
AutoMySQLBackup VER 2.5
http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - pnagios01lxv.mitchell.com
======================================================================
Backup Start Time Tue Aug 28 07:00:01 PDT 2012
======================================================================
Daily Backup of Database ( information_schema )
Rotating last weeks Backup...
removed `/store/backups/mysql/daily/information_schema/information_schema_2012-08-21_07h00m.Tuesday.sql.gz'


Backup Information for /store/backups/mysql/daily/information_schema/information_schema_2012-08-28_07h00m.Tuesday.sql
compressed uncompressed ratio uncompressed_name
483 976 57.4% /store/backups/mysql/daily/information_schema/information_schema_2012-08-28_07h00m.Tuesday.sql
----------------------------------------------------------------------
Daily Backup of Database ( mysql )
Rotating last weeks Backup...
removed `/store/backups/mysql/daily/mysql/mysql_2012-08-21_07h00m.Tuesday.sql.gz'


Backup Information for /store/backups/mysql/daily/mysql/mysql_2012-08-28_07h00m.Tuesday.sql
compressed uncompressed ratio uncompressed_name
133611 491304 72.8% /store/backups/mysql/daily/mysql/mysql_2012-08-28_07h00m.Tuesday.sql
----------------------------------------------------------------------
Daily Backup of Database ( nagios )
Rotating last weeks Backup...
removed `/store/backups/mysql/daily/nagios/nagios_2012-08-21_07h00m.Tuesday.sql.gz'


Backup Information for /store/backups/mysql/daily/nagios/nagios_2012-08-28_07h00m.Tuesday.sql
compressed uncompressed ratio uncompressed_name
8975168 97235086 90.8% /store/backups/mysql/daily/nagios/nagios_2012-08-28_07h00m.Tuesday.sql
----------------------------------------------------------------------
Daily Backup of Database ( nagiosql )
Rotating last weeks Backup...
removed `/store/backups/mysql/daily/nagiosql/nagiosql_2012-08-21_07h00m.Tuesday.sql.gz'


Backup Information for /store/backups/mysql/daily/nagiosql/nagiosql_2012-08-28_07h00m.Tuesday.sql
compressed uncompressed ratio uncompressed_name
48422 379568 87.3% /store/backups/mysql/daily/nagiosql/nagiosql_2012-08-28_07h00m.Tuesday.sql
----------------------------------------------------------------------
Daily Backup of Database ( test )
Rotating last weeks Backup...
removed `/store/backups/mysql/daily/test/test_2012-08-21_07h00m.Tuesday.sql.gz'


Backup Information for /store/backups/mysql/daily/test/test_2012-08-28_07h00m.Tuesday.sql
compressed uncompressed ratio uncompressed_name
550 1393 64.3% /store/backups/mysql/daily/test/test_2012-08-28_07h00m.Tuesday.sql
----------------------------------------------------------------------
Backup End Tue Aug 28 07:00:07 PDT 2012
======================================================================
Total disk space used for backup storage..
Size - Location
275M /store/backups/mysql

======================================================================
If you find AutoMySQLBackup valuable please make a donation at
http://sourceforge.net/project/project_ ... _id=101066
======================================================================

###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

[scottwilkerson]

Can you run the following line exaxtly as is and show the output, I want to see if you still have the old login

Code: Select all

echo "show tables;"|mysql information_schema -pnagiosxi

[Mitchell]

it is throwing bad syntax error.

BTW, I see this error on my test machine as well where I have not changed the password. I am able to login to DB and run 'show tables' on my test machine.

Code: Select all

mysql information_schema -p'nagiosxi'
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
28 rows in set (0.00 sec)

[lmiltchev]

This has been reported as a bug:

http://bugs.mysql.com/bug.php?id=21527

Can you run the following command and show the output?

Code: Select all

mysql --version

[Mitchell]

[root@pnagios01lxv ~]# mysql --version
mysql Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (i386) using readline 5.1