Automysqlbackup Fails Due to Issues in Information_schema
In a machine 'rh', tested automysqlbackup cron job if it would work and got the issue below:
1[root@rh icasimpan]# /usr/local/bin/automysqlbackup /etc/automysqlbackup/rh.conf
2Parsed config file "/etc/automysqlbackup/automysqlbackup.conf"
3
4# Checking for permissions to write to folders:
5base folder /var/lib ... exists ... ok.
6backup folder /var/lib/automysqlbackup ... exists ... writable? yes. Proceeding.
7checking directory "/var/lib/automysqlbackup/daily" ... exists.
8checking directory "/var/lib/automysqlbackup/weekly" ... exists.
9checking directory "/var/lib/automysqlbackup/monthly" ... exists.
10checking directory "/var/lib/automysqlbackup/latest" ... exists.
11checking directory "/var/lib/automysqlbackup/tmp" ... exists.
12checking directory "/var/lib/automysqlbackup/fullschema" ... exists.
13checking directory "/var/lib/automysqlbackup/status" ... exists.
14
15# Testing for installed programs
16mysql ... found.
17mysqldump ... found.
18gzip ... found.
19
20# Parsing databases ... done.
21======================================================================
22AutoMySQLBackup version 3.0
23http://sourceforge.net/projects/automysqlbackup/
24
25Backup of Database Server - localhost
26Databases - information_schema,mysql,performance_schema,rhdevDB,rhqaDB,rhstgDB,rhupdDB
27Databases (monthly) - mysql
28======================================================================
29======================================================================
30Dump full schema.
31
32Rotating 4 month backups for
33
34======================================================================
35
36======================================================================
37Dump status.
38
39Rotating 4 month backups for
40
41======================================================================
42
43Backup Start Time Thu May 10 21:54:35 UTC 2018
44======================================================================
45Daily Backup ...
46
47Daily Backup of Database ( information_schema )
48Rotating 6 day backups for information_schema
49----------------------------------------------------------------------
50Daily Backup of Database ( mysql )
51Rotating 6 day backups for mysql
52----------------------------------------------------------------------
53Daily Backup of Database ( performance_schema )
54Rotating 6 day backups for performance_schema
55----------------------------------------------------------------------
56Daily Backup of Database ( rhdevDB )
57Rotating 6 day backups for rhdevDB
58----------------------------------------------------------------------
59Daily Backup of Database ( rhqaDB )
60Rotating 6 day backups for rhqaDB
61----------------------------------------------------------------------
62Daily Backup of Database ( rhstgDB )
63Rotating 6 day backups for rhstgDB
64----------------------------------------------------------------------
65Daily Backup of Database ( rhupdDB )
66Rotating 6 day backups for rhupdDB
67----------------------------------------------------------------------
68
69Backup End Time Thu May 10 21:54:47 UTC 2018
70======================================================================
71Total disk space used for backup storage...
72Size - Location
7311M /var/lib/automysqlbackup
74
75======================================================================
76
77###### WARNING ######
78Errors reported during AutoMySQLBackup execution.. Backup failed
79Error log below..
80mysqldump: Got error: 1044: "Access denied for user 'root'@'localhost' to database 'information_schema'" when using LOCK TABLES
81mysqldump: Got error: 1142: "SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances'" when using LOCK TABLES
Solution: Since I don't need to backup the 'information_schema', I usually just list down the needed database in corresponding conf file (in this case /etc/automysqlbackup/rh.conf). Searched for the string
1CONFIG_db_names=()
and added only the needed databases as follows:
1CONFIG_db_names=('rhdevDB' 'rhqaDB' 'rhstgDB' 'rhupdDB')
Rerun. Most of the time (in all cases like this actually), this worked for me.