Issuing innobackupex command to backup replica slave works - all user and mysql databases dumped. However, running the exact same command via a cron job is missing a user database and mysql.
mysqladmin stop-slave
/usr/bin/innobackupex --user=“backup-user” --password=“########” --slave-info /opt/slave_backup
/usr/bin/innobackupex --apply-log /opt/slave_backup/2013-05-15_22-10-13
mysqladmin start-slave
backup-user granted:
mysql> grant select, reload, process, file, super, lock tables, replication client, replication slave on . to ‘backup-user’@‘localhost’ identified by ‘######’;
nkelly
May 15, 2013, 11:46pm
2
This issue started happening after testing incremental backups.
revin
May 16, 2013, 3:35am
3
When you test manually versus cron, are you running with different users? After the backup is finished, can you check the mysql database is included in the backup or not?
nkelly
May 16, 2013, 12:04pm
4
When testing I am on the server as root (sudo su -). The cron job runs under the root account but the innobackupex is done under the backup-user account. The files are created as root in both scenarios.
The cron job calls: sh /etc/backup-script.
The backup-script contains the following code:
mysqladmin stop-slave
/usr/bin/innobackupex --user=“backup-user” --password=“########” --slave-info /opt/slave_backup
mysqladmin start-slave
If run manually, the backup folder contains everything I would expect:
-rw-r--r--. 1 root root 264 May 16 04:55 backup-my.cnf
-rw-r-----. 1 root root 1820327936 May 16 04:56 ibdata1
-rw-r--r--. 1 root root 23 May 16 04:56 xtrabackup_binlog_info
-rw-r--r--. 1 root root 77 May 16 04:56 xtrabackup_slave_info
drwxr-xr-x. 2 root root 4096 May 16 04:56 mysql
drwxr-xr-x. 2 root root 1024 May 16 04:56 userdb1
drwxr-xr-x. 2 root root 1024 May 16 04:56 userdb2
drwxr-xr-x. 2 root root 4096 May 16 04:56 userdb3
drwxr-xr-x. 2 root root 2048 May 16 04:56 performance_schema
drwxr-xr-x. 2 root root 3072 May 16 04:56 userdb4
drwxr-xr-x. 2 root root 1024 May 16 04:56 userdb5
drwxr-xr-x. 2 root root 1024 May 16 04:56 userdb6
drwxr-xr-x. 2 root root 1024 May 16 04:56 userdb7
-rw-r-----. 1 root root 2560 May 16 04:56 xtrabackup_logfile
-rw-r-----. 1 root root 85 May 16 04:56 xtrabackup_checkpoints
-rw-r--r--. 1 root root 13 May 16 04:56 xtrabackup_binary
When run via the midnight cron job, databases are missing (interesting that in last night's run the mysql database was finally backed unlike in the past):
-rw-r--r--. 1 root root 264 May 16 12:00 backup-my.cnf
-rw-r-----. 1 root root 2048 May 16 12:00 xtrabackup_logfile
-rw-r-----. 1 root root 1828716544 May 16 12:01 ibdata1
-rw-r--r--. 1 root root 0 May 16 12:01 xtrabackup_suspended
-rw-r--r--. 1 root root 23 May 16 12:01 xtrabackup_binlog_info
-rw-r--r--. 1 root root 78 May 16 12:01 xtrabackup_slave_info
drwxr-xr-x. 2 root root 4096 May 16 12:01 mysql
drwxr-xr-x. 2 root root 1024 May 16 12:01 userdb1
drwxr-xr-x. 2 root root 1024 May 16 12:01 userdb2
After applying the --apply-log, the directory looks like this:
-rw-r--r--. 1 root root 264 May 16 12:00 backup-my.cnf
-rw-r--r--. 1 root root 0 May 16 12:01 xtrabackup_suspended
-rw-r--r--. 1 root root 23 May 16 12:01 xtrabackup_binlog_info
-rw-r--r--. 1 root root 78 May 16 12:01 xtrabackup_slave_info
drwxr-xr-x. 2 root root 4096 May 16 12:01 mysql
drwxr-xr-x. 2 root root 1024 May 16 12:01 userdb1
drwxr-xr-x. 2 root root 1024 May 16 12:01 userdb2
-rw-r-----. 1 root root 2097152 May 16 17:31 xtrabackup_logfile
-rw-r--r--. 1 root root 268435456 May 16 17:32 ib_logfile1
-rw-r--r--. 1 root root 268435456 May 16 17:32 ib_logfile2
-rw-r--r--. 1 root root 24 May 16 17:32 xtrabackup_binlog_pos_innodb
-rw-r--r--. 1 root root 268435456 May 16 17:32 ib_logfile0
-rw-r-----. 1 root root 1828716544 May 16 17:32 ibdata1
-rw-r--r--. 1 root root 65 May 16 17:32 xtrabackup_checkpoints