PDA

View Full Version : Restore Issues and mysql-zrm-reporter



trickykid
October 30th, 2006, 10:45 AM
Background:

Currently looking for solutions for MySQL database backups in a corporate environment. Have around 60 databases, all running MySQL. Want to move away from full mysqldump's and implement incremental backups, which brings me here.

Issue:

I've set mysql-zrm on a test machine. I've successfully created a full level 0 backup and performed a level 1 incremental backup. Both resolved or reported via email that they were successful, also displayed successful backup via CLI.

The level 0 backup directory created shows the backup.sql and all the data within it looks good. It was just a simple creation of a table on a test database.

The level 1 was successful after adding about 500kb of data to it and the directory displayed the index file and the current bin log for mysql (On a side note, the first one failed saying it failed to copy to my destination directory but then I realized it was a permissions issue with the user copying the bin log over, but it copied it anyways despite the error; I changed this destination locally to avoid any crazy errors).

When I now run the mysql-zrm-reporter like this:

mysql-zrm-reporter --where backup-set=test --show backup-status-info

The output is nothing on any other option I try as well. I created the backup-set as test.

I then proceeded to restore. I dropped the table I created with all of the data within it. And used a command like such below to attempt to restore from the level 0 backup to see if it would recreate the table:

mysql-zrm --action restore --backup-set test --source-directory /backup/hostname/test/20061030124039

Which gave me this output:

INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Restore done in 4 seconds.
MySQL server has been shutdown. Please restart after verification.

So I restarted MySQL and the database was empty.

Any suggestions. Am I missing something? Should mysql-zrm copy the mysql bin logs to the backup directory? The user I'm using to test with is the root mysql user for now.

tkr
October 30th, 2006, 10:59 AM
Background:

Currently looking for solutions for MySQL database backups in a corporate environment. Have around 60 databases, all running MySQL. Want to move away from full mysqldump's and implement incremental backups, which brings me here.

Issue:

I've set mysql-zrm on a test machine. I've successfully created a full level 0 backup and performed a level 1 incremental backup. Both resolved or reported via email that they were successful, also displayed successful backup via CLI.

The level 0 backup directory created shows the backup.sql and all the data within it looks good. It was just a simple creation of a table on a test database.

The level 1 was successful after adding about 500kb of data to it and the directory displayed the index file and the current bin log for mysql (On a side note, the first one failed saying it failed to copy to my destination directory but then I realized it was a permissions issue with the user copying the bin log over, but it copied it anyways despite the error; I changed this destination locally to avoid any crazy errors).

When I now run the mysql-zrm-reporter like this:

mysql-zrm-reporter --where backup-set=test --show backup-status-info

The output is nothing on any other option I try as well. I created the backup-set as test.

I then proceeded to restore. I dropped the table I created with all of the data within it. And used a command like such below to attempt to restore from the level 0 backup to see if it would recreate the table:

mysql-zrm --action restore --backup-set test --source-directory /backup/hostname/test/20061030124039

Which gave me this output:

INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Restore done in 4 seconds.
MySQL server has been shutdown. Please restart after verification.

So I restarted MySQL and the database was empty.

Any suggestions. Am I missing something? Should mysql-zrm copy the mysql bin logs to the backup directory? The user I'm using to test with is the root mysql user for now.


Hi
Can you post your log files please?
Its in /var/log/mysql-zrm by default.

Thanks
tk

trickykid
October 30th, 2006, 11:08 AM
Here is a snippet from the mysql-zrm.log file of the test database I was using:



Mon Oct 30 12:40:39 2006: WARNING: Could not open file /etc/mysql-zrm/test/last_backup. No such file or directory
Mon Oct 30 12:40:39 2006: INFO: backup-set=test
Mon Oct 30 12:40:39 2006: INFO: backup-date=20061030124039
Mon Oct 30 12:40:39 2006: INFO: host=localhost
Mon Oct 30 12:40:39 2006: INFO: backup-date-epoch=1162233639
Mon Oct 30 12:40:39 2006: INFO: retention-policy=2D
Mon Oct 30 12:40:39 2006: INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
Mon Oct 30 12:40:39 2006: INFO: mysql-version=4.1.12-standard-log
Mon Oct 30 12:40:39 2006: INFO: backup-directory=/backup/storm39.login.skylist.net/test/20061030124039
Mon Oct 30 12:40:39 2006: INFO: comment=storm39 Scimitar Backup
Mon Oct 30 12:40:39 2006: INFO: backup-level=0
Mon Oct 30 12:40:39 2006: INFO: logical-tables=Domains
logical_database=test
Mon Oct 30 12:40:39 2006: INFO: next-binlog=storm39-bin.000006
Mon Oct 30 12:40:39 2006: INFO: /backup/storm39.login.skylist.net/test/20061030124039/backup.sql=0ba6c8863b9eda4762c8452b14fb4c92
Mon Oct 30 12:40:39 2006: INFO: backup-size=0.00 MB
Mon Oct 30 12:40:39 2006: INFO: read-locks-time=00:00:00
Mon Oct 30 12:40:39 2006: INFO: flush-logs-time=00:00:00
Mon Oct 30 12:40:39 2006: INFO: backup-time=00:00:00
Mon Oct 30 12:40:39 2006: INFO: backup-status=Backup succeeded
Mon Oct 30 12:40:39 2006: INFO: Backup succeeded
Mon Oct 30 12:47:00 2006: INFO: backup-set=test
Mon Oct 30 12:47:00 2006: INFO: backup-date=20061030124700
Mon Oct 30 12:47:00 2006: INFO: host=localhost
Mon Oct 30 12:47:00 2006: INFO: backup-date-epoch=1162234020
Mon Oct 30 12:47:00 2006: INFO: retention-policy=2D
Mon Oct 30 12:47:00 2006: INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
Mon Oct 30 12:47:00 2006: INFO: mysql-version=4.1.12-standard-log
Mon Oct 30 12:47:00 2006: INFO: backup-directory=/backup/storm39.login.skylist.net/test/20061030124700
Mon Oct 30 12:47:00 2006: INFO: comment=storm39 Scimitar Backup
Mon Oct 30 12:47:00 2006: INFO: backup-level=1
Mon Oct 30 12:47:01 2006: ERROR: Copy from /var/lib/mysql/storm39-bin.000006 to /backup/storm39.login.skylist.net/test/20061030124700 failed
Mon Oct 30 12:47:01 2006: ERROR: Could not copy bin log file storm39-bin.000006
Mon Oct 30 12:47:01 2006: INFO: incremental=storm39-bin.[0-9]*
Mon Oct 30 12:47:02 2006: INFO: next-binlog=storm39-bin.000007
Mon Oct 30 12:47:02 2006: INFO: last-backup=/backup/storm39.login.skylist.net/test/20061030124039
Mon Oct 30 12:47:02 2006: INFO: /backup/storm39.login.skylist.net/test/20061030124700/storm39-bin.000006=e37fd24b3d811397d8917284c37b30be
Mon Oct 30 12:47:02 2006: INFO: backup-size=0.51 MB
Mon Oct 30 12:47:02 2006: INFO: read-locks-time=00:00:00
Mon Oct 30 12:47:02 2006: INFO: flush-logs-time=00:00:00
Mon Oct 30 12:47:02 2006: INFO: backup-time=00:00:02
Mon Oct 30 12:47:02 2006: INFO: backup-status=Backup done but with errors
Mon Oct 30 12:47:02 2006: INFO: Backup done but with errors
Mon Oct 30 12:53:40 2006: INFO: backup-set=test
Mon Oct 30 12:53:40 2006: INFO: backup-date=20061030125340
Mon Oct 30 12:53:40 2006: INFO: host=localhost
Mon Oct 30 12:53:40 2006: INFO: backup-date-epoch=1162234420
Mon Oct 30 12:53:40 2006: INFO: retention-policy=2D
Mon Oct 30 12:53:40 2006: INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
Mon Oct 30 12:53:40 2006: INFO: mysql-version=4.1.12-standard-log
Mon Oct 30 12:53:40 2006: INFO: backup-directory=/backup/storm39.login.skylist.net/test/20061030125340
Mon Oct 30 12:53:40 2006: INFO: comment=storm39 Scimitar Backup
Mon Oct 30 12:53:40 2006: INFO: backup-level=1
Mon Oct 30 12:53:40 2006: ERROR: Copy from /var/lib/mysql/storm39-bin.000007 to /backup/storm39.login.skylist.net/test/20061030125340 failed
Mon Oct 30 12:53:40 2006: ERROR: Could not copy bin log file storm39-bin.000007
Mon Oct 30 12:53:40 2006: INFO: incremental=storm39-bin.[0-9]*
Mon Oct 30 12:53:41 2006: INFO: next-binlog=storm39-bin.000008
Mon Oct 30 12:53:41 2006: INFO: last-backup=/backup/storm39.login.skylist.net/test/20061030124700
Mon Oct 30 12:53:41 2006: INFO: /backup/storm39.login.skylist.net/test/20061030125340/storm39-bin.000007=1f116faadea9c4ea837cd505996b1113
Mon Oct 30 12:53:41 2006: INFO: backup-size=0.04 MB
Mon Oct 30 12:53:41 2006: INFO: read-locks-time=00:00:00
Mon Oct 30 12:53:41 2006: INFO: flush-logs-time=00:00:00
Mon Oct 30 12:53:41 2006: INFO: backup-time=00:00:01
Mon Oct 30 12:53:41 2006: INFO: backup-status=Backup done but with errors
Mon Oct 30 12:53:41 2006: INFO: Backup done but with errors
Mon Oct 30 12:55:31 2006: INFO: backup-set=test
Mon Oct 30 12:55:31 2006: INFO: backup-date=20061030125531
Mon Oct 30 12:55:31 2006: INFO: host=localhost
Mon Oct 30 12:55:31 2006: INFO: backup-date-epoch=1162234531
Mon Oct 30 12:55:31 2006: INFO: retention-policy=2D
Mon Oct 30 12:55:31 2006: INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
Mon Oct 30 12:55:31 2006: INFO: mysql-version=4.1.12-standard-log
Mon Oct 30 12:55:31 2006: INFO: backup-directory=/var/tmp/backup/test/20061030125531
Mon Oct 30 12:55:31 2006: INFO: comment=storm39 Scimitar Backup
Mon Oct 30 12:55:31 2006: INFO: backup-level=1
Mon Oct 30 12:55:31 2006: INFO: incremental=storm39-bin.[0-9]*
Mon Oct 30 12:55:31 2006: INFO: next-binlog=storm39-bin.000009
Mon Oct 30 12:55:31 2006: INFO: last-backup=/backup/storm39.login.skylist.net/test/20061030125340
Mon Oct 30 12:55:31 2006: INFO: /var/tmp/backup/test/20061030125531/storm39-bin.000008=b3bb5a83b610e7f2b182845d3178e009
Mon Oct 30 12:55:31 2006: INFO: backup-size=0.01 MB
Mon Oct 30 12:55:31 2006: INFO: read-locks-time=00:00:00
Mon Oct 30 12:55:31 2006: INFO: flush-logs-time=00:00:00
Mon Oct 30 12:55:31 2006: INFO: backup-time=00:00:00
Mon Oct 30 12:55:31 2006: INFO: backup-status=Backup succeeded
Mon Oct 30 12:55:31 2006: INFO: Backup succeeded
Mon Oct 30 13:04:42 2006: INFO: Restore done in 4 seconds.


I found the problem with the reporter command, seems it's wanting the root backup directory specified, was using the default /var/lib/mysql-zrm

paddy
October 30th, 2006, 11:27 AM
Hi,

Thanks for trying MySQL ZRM.

1. Good that you figured out the MySQL ZRM reporter problem that your backup root
directory was different from the default "/var/lib/mysql-zrm". Did you find the error from
MySQL ZRM reporter log in /var/log/mysql-zrm/ directory? We will improve error reporting
from reporter tool in the next release.

2. It appears that you have done a full backup at 12:40:39 on Oct 30 for backup size 0 bytes!!! Restoring this backup will get you an empty database :-) We need to figure out
why the backup size was zero. I guess you had some data. In your email, you had mentioned about "backup.sql" file and contents were good. Was the file in
/backup/hostname/test/20061030124039 directory?

3. Are you backing up a remote MySQL database? If you are using remote database, you will have to configure "copy-plugin" (see mysql-zrm.conf file or http://mysqlbackup.zmanda.com). You can use ssh plugin that is provided as part of the
package.

Thanks,
Paddy

trickykid
October 30th, 2006, 11:29 AM
Ok, I wiped everything to start clean. I also noticed if I specify the destination directory in the mysql-zrm.conf file in my /etc/mysql-zrm/test/ directory, it doesn't output the backups there, but instead uses the /var/lib/mysql-zrm/test/20061030140225 directory.

If I specify the directory on the command line, it places it appropriately.

I scheduled a full weekly and then I ran:

mysql-zrm-scheduler --now --backup-set test --backup-level 0

Which also produced a report output now without errors:



backup_set backup_date backup_level backup_status comment
----------------------------------------------------------------------------------------------------------------------
test Mon 30 Oct 2006 02:02:25 PM CST 0 Backup succeeded Storm39 Test Backup

Which placed it in the directory I specified above.

Here is my current conf file for the backup-set "test":



comment=Test Backup
backup-level=0
backup-mode=logical
destination=/var/tmp/backup
retention-policy=1D
tables=Domains
database="test"
user="root"
password="mypassword"
mysql-binlog-path="/var/lib/mysql"
mailto="user@domain.com"
html-reports=backup-status-info

trickykid
October 30th, 2006, 11:36 AM
2. It appears that you have done a full backup at 12:40:39 on Oct 30 for backup size 0 bytes!!! Restoring this backup will get you an empty database :-) We need to figure out
why the backup size was zero. I guess you had some data. In your email, you had mentioned about "backup.sql" file and contents were good. Was the file in
/backup/hostname/test/20061030124039 directory?
I think that was a backup I did before I actually placed data into the table in the database. but yes, I did have a backup.sql file with the data contents within it, all looked good when I cat the file.


3. Are you backing up a remote MySQL database? If you are using remote database, you will have to configure "copy-plugin" (see mysql-zrm.conf file or http://mysqlbackup.zmanda.com). You can use ssh plugin that is provided as part of the
package.This is all being done locally for now while testing out.

Currently my previous post pointed out that it's not backing up to the dest dir I chose, but I'm going to change to the current /var/lib/mysql-zrm, add some data, do an incremental and test a restore from that directory.

paddy
October 30th, 2006, 11:38 AM
The "destination" parameter issue reported earlier is a bug. I have filed a bug
http://forums.zmanda.com/bugzilla/show_bug.cgi?id=12

Are you able to restore from the backups now?

Paddy

trickykid
October 30th, 2006, 11:45 AM
The "destination" parameter issue reported earlier is a bug. I have filed a bug
http://forums.zmanda.com/bugzilla/show_bug.cgi?id=12

Are you able to restore from the backups now?

Paddy
Both restores unsuccessful.

I attempted a restore first by dropping the table with data and attempting a recovery from the level 0 backup taken with just the table creation in the backup.sql file.

The incremental also gave errors, this is the log output starting first with the level 0 restore attempt that stated it was successful:


Mon Oct 30 14:23:01 2006: INFO: Restore done in 4 seconds.
Mon Oct 30 14:23:38 2006: ERROR: Command returned error
Mon Oct 30 14:23:38 2006: ERROR: Output of command: 'mysql --user=root --password=***** -e "source /tmp/hnYlF8UnbD;"' is {
ERROR 1062 (23000) at line 1629 in file: '/tmp/hnYlF8UnbD': Duplicate entry '6548' for key 1
}
Mon Oct 30 14:23:38 2006: ERROR: Incremental restore failed
Mon Oct 30 14:23:42 2006: ERROR: Restore failed

trickykid
October 30th, 2006, 11:48 AM
I did a normal:

mysql -u root -p test < /var/lib/mysql-zrm/test/20061030140225/backup.sql

And it reinserted the DB table just fine. The incremental failed after recreating the table from the backup.sql level 0 backup.

Sachin
October 31st, 2006, 01:33 AM
Hi,

I have tried the same scenario as the one in this thread and it worked. Please let me know if there are any other steps that can help to reproduce your issue.

sachin:~ # mysql-zrm --action backup --backup-set tmpdb --database TMPDATABASE
INFO: ZRM for MySQL Community Edition - version 1.1
WARNING: Could not open file /etc/mysql-zrm/tmpdb/last_backup. No such file or directory
INFO: backup-set=tmpdb
INFO: backup-date=20061031151418
INFO: host=localhost
INFO: backup-date-epoch=1162287858
INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
INFO: mysql-version=5.0.18-log
INFO: backup-directory=/local/backup/tmpdb/20061031151418
INFO: backup-level=0
WARNING: The database(s) TMPDATABASE will be backed up in logical mode since they contain tables that use a transactional engine.
INFO: logical-databases=TMPDATABASE
INFO: next-binlog=binlog.000359
INFO: /local/backup/tmpdb/20061031151418/backup.sql=6886a776684749a04047d06ab34fdff9
INFO: backup-size=0.00 MB
INFO: read-locks-time=00:00:00
INFO: flush-logs-time=00:00:00
INFO: backup-time=00:00:01
INFO: backup-status=Backup succeeded
INFO: Backup succeeded
sachin:~ #
sachin:~ # mysql -e "use TMPDATABASE;select * from employees"
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| id | firstname | lastname | address | city | state | zipcode | telephone | email |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| 1 | sachin | kale | lskdjf | lskdjflk | lksjdf | 12323 | 12324 | safd@lksd.com |
| 2 | sdf | sdf | df | df | df | sdf | df | dsf |
| 3 | ksdfh | kjdh | kdhf | kjdhf | khdfkj | sdkh | kdsjhf | kdjhf |
| 4 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 5 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 6 | ddfd | Dfdf | dfwqer | niuyhe | Dfd | NULL | NULL | NULL |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
sachin:~ #
sachin:~ # mysql -e "use TMPDATABASE;insert into employees(firstname,lastname) values (\"Tim\",\"Burton\")"
sachin:~ # echo $?
0
sachin:~ # mysql -e "use TMPDATABASE;select * from employees"
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| id | firstname | lastname | address | city | state | zipcode | telephone | email |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| 1 | sachin | kale | lskdjf | lskdjflk | lksjdf | 12323 | 12324 | safd@lksd.com |
| 2 | sdf | sdf | df | df | df | sdf | df | dsf |
| 3 | ksdfh | kjdh | kdhf | kjdhf | khdfkj | sdkh | kdsjhf | kdjhf |
| 4 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 5 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 6 | ddfd | Dfdf | dfwqer | niuyhe | Dfd | NULL | NULL | NULL |
| 7 | Tim | Burton | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
sachin:~ #
sachin:~ # mysql-zrm --action backup --backup-set tmpdb --database TMPDATABASE --backup-level 1
INFO: ZRM for MySQL Community Edition - version 1.1
INFO: backup-set=tmpdb
INFO: backup-date=20061031152125
INFO: host=localhost
INFO: backup-date-epoch=1162288285
INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
INFO: mysql-version=5.0.18-log
INFO: backup-directory=/local/backup/tmpdb/20061031152125
INFO: backup-level=1
INFO: incremental=binlog.[0-9]*
INFO: next-binlog=binlog.000360
INFO: last-backup=/local/backup/tmpdb/20061031151418
INFO: /local/backup/tmpdb/20061031152125/binlog.000359=ee69eb1ab00a4096d2d511ca4944ad61
INFO: backup-size=0.00 MB
INFO: read-locks-time=00:00:00
INFO: flush-logs-time=00:00:00
INFO: backup-time=00:00:00
INFO: backup-status=Backup succeeded
INFO: Backup succeeded
sachin:~ #
sachin:~ # mysql-zrm --action list --all-backups --backup-set tmpdb
INFO: ZRM for MySQL Community Edition - version 1.1
0 /local/backup/tmpdb/20061031151418
Index File
{ backup-status=Backup succeeded
mysql-version=5.0.18-log
backup-date=20061031151418
mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
logical-databases=TMPDATABASE
backup-level=0
backup-size=0.00 MB
backup-set=tmpdb
backup-time=00:00:01
backup-directory=/local/backup/tmpdb/20061031151418
host=localhost
/local/backup/tmpdb/20061031151418/backup.sql=6886a776684749a04047d06ab34fdff9
flush-logs-time=00:00:00
read-locks-time=00:00:00
next-binlog=binlog.000359
backup-date-epoch=1162287858
}
1 /local/backup/tmpdb/20061031152125
Index File
{ backup-status=Backup succeeded
/local/backup/tmpdb/20061031152125/binlog.000359=ee69eb1ab00a4096d2d511ca4944ad61
mysql-version=5.0.18-log
backup-date=20061031152125
mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
incremental=binlog.[0-9]*
last-backup=/local/backup/tmpdb/20061031151418
backup-level=1
backup-size=0.00 MB
backup-set=tmpdb
backup-time=00:00:00
backup-directory=/local/backup/tmpdb/20061031152125
host=localhost
flush-logs-time=00:00:00
read-locks-time=00:00:00
next-binlog=binlog.000360
backup-date-epoch=1162288285
}
sachin:~ # mysqladmin -f drop TMPDATABASE
Database "TMPDATABASE" dropped
sachin:~ #
sachin:~ # mysql < /local/backup/tmpdb/20061031151418/
backup.sql index
sachin:~ # mysql < /local/backup/tmpdb/20061031151418/backup.sql
sachin:~ # mysql -e "use TMPDATABASE;select * from employees"
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| id | firstname | lastname | address | city | state | zipcode | telephone | email |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| 1 | sachin | kale | lskdjf | lskdjflk | lksjdf | 12323 | 12324 | safd@lksd.com |
| 2 | sdf | sdf | df | df | df | sdf | df | dsf |
| 3 | ksdfh | kjdh | kdhf | kjdhf | khdfkj | sdkh | kdsjhf | kdjhf |
| 4 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 5 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 6 | ddfd | Dfdf | dfwqer | niuyhe | Dfd | NULL | NULL | NULL |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
sachin:~ #
sachin:~ # mysql-zrm --action restore --source-directory /local/backup/tmpdb/20061031152125
INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Incremental restore done
INFO: Restore done in 4 seconds.
MySQL server has been shutdown. Please restart after verification.
sachin:~ # /etc/init.d/mysql start
Starting service MySQL done
sachin:~ # mysql -e "use TMPDATABASE;select * from employees"
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| id | firstname | lastname | address | city | state | zipcode | telephone | email |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| 1 | sachin | kale | lskdjf | lskdjflk | lksjdf | 12323 | 12324 | safd@lksd.com |
| 2 | sdf | sdf | df | df | df | sdf | df | dsf |
| 3 | ksdfh | kjdh | kdhf | kjdhf | khdfkj | sdkh | kdsjhf | kdjhf |
| 4 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 5 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 6 | ddfd | Dfdf | dfwqer | niuyhe | Dfd | NULL | NULL | NULL |
| 7 | Tim | Burton | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
sachin:~ #

trickykid
October 31st, 2006, 06:39 AM
I noticed in your steps you didn't do an --action restore for the initial backup but rather inserted it manually, is this expected to perform restores? Shouldn't I be able to restore the initial level 0 backup of the table schema and proceed with the most recent incremental 1 backup I took?

Sachin
October 31st, 2006, 06:59 AM
Hi,

It also works with --action restore for the level 0 backups.
Here is the screenshot.

sachin:~ # mysql-zrm --action list --all-backups --backup-set tmpdb
INFO: ZRM for MySQL Community Edition - version 1.1
0 /local/backup/tmpdb/20061031151418
Index File
{ backup-status=Backup succeeded
mysql-version=5.0.18-log
backup-date=20061031151418
mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
logical-databases=TMPDATABASE
backup-level=0
backup-size=0.00 MB
backup-set=tmpdb
backup-time=00:00:01
backup-directory=/local/backup/tmpdb/20061031151418
host=localhost
/local/backup/tmpdb/20061031151418/backup.sql=6886a776684749a04047d06ab34fdff9
flush-logs-time=00:00:00
read-locks-time=00:00:00
next-binlog=binlog.000359
backup-date-epoch=1162287858
}
1 /local/backup/tmpdb/20061031152125
Index File
{ backup-status=Backup succeeded
/local/backup/tmpdb/20061031152125/binlog.000359=ee69eb1ab00a4096d2d511ca4944ad61
mysql-version=5.0.18-log
backup-date=20061031152125
mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
incremental=binlog.[0-9]*
last-backup=/local/backup/tmpdb/20061031151418
backup-level=1
backup-size=0.00 MB
backup-set=tmpdb
backup-time=00:00:00
backup-directory=/local/backup/tmpdb/20061031152125
host=localhost
flush-logs-time=00:00:00
read-locks-time=00:00:00
next-binlog=binlog.000360
backup-date-epoch=1162288285
}
sachin:~ # mysqladmin -f drop TMPDATABASE
Database "TMPDATABASE" dropped
sachin:~ # mysql-zrm --action restore --source-directory /local/backup/tmpdb/20061031151418
INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Restored database(s) from logical backup: TMPDATABASE
INFO: Restore done in 3 seconds.
MySQL server has been shutdown. Please restart after verification.
sachin:~ # /etc/init.d/mysql start
Starting service MySQL done
sachin:~ # mysql -e "use TMPDATABASE;select * from employees"
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| id | firstname | lastname | address | city | state | zipcode | telephone | email |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| 1 | sachin | kale | lskdjf | lskdjflk | lksjdf | 12323 | 12324 | safd@lksd.com |
| 2 | sdf | sdf | df | df | df | sdf | df | dsf |
| 3 | ksdfh | kjdh | kdhf | kjdhf | khdfkj | sdkh | kdsjhf | kdjhf |
| 4 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 5 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 6 | ddfd | Dfdf | dfwqer | niuyhe | Dfd | NULL | NULL | NULL |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
sachin:~ # mysql-zrm --action restore --source-directory /local/backup/tmpdb/20061031152125
INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Incremental restore done
INFO: Restore done in 3 seconds.
MySQL server has been shutdown. Please restart after verification.
sachin:~ # /etc/init.d/mysql start
Starting service MySQL done
sachin:~ # mysql -e "use TMPDATABASE;select * from employees"
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| id | firstname | lastname | address | city | state | zipcode | telephone | email |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
| 1 | sachin | kale | lskdjf | lskdjflk | lksjdf | 12323 | 12324 | safd@lksd.com |
| 2 | sdf | sdf | df | df | df | sdf | df | dsf |
| 3 | ksdfh | kjdh | kdhf | kjdhf | khdfkj | sdkh | kdsjhf | kdjhf |
| 4 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 5 | ksjd | kshfkj | kjdshfk | ksdhf | gfsdf | s | dfs | safd@lksd.com |
| 6 | ddfd | Dfdf | dfwqer | niuyhe | Dfd | NULL | NULL | NULL |
| 7 | Tim | Burton | NULL | NULL | NULL | NULL | NULL | NULL |
+----+-----------+----------+---------+----------+--------+---------+-----------+---------------+
sachin:~ #

trickykid
October 31st, 2006, 07:06 AM
What environment are you running on? Currently my test machine is Centos 4.0 with MySQL 4.1.12 with InnoDB.

The weird part is it reports a successful restore for my level 0 backup, but the table does not exist on the database after such restore.

Sachin
October 31st, 2006, 07:20 AM
I am using SLES10 with mysql 5.0

I have tested mysql 4.1.12 previously and it worked fine, but this was on SLES9.

I will check mysql documentation/bugzilla if there are issues specific to mysql 4.1.12 on CentOS though I think that it has nothing to with CentOS.

Will try your scenario on fc4 with mysql.4.1.12 ASAP.

trickykid
October 31st, 2006, 10:16 AM
Still no go and I got 0 errors on restore.

This time I created a table, inserted data and performed a level 0 backup of the database and it's one table, which was successful.

I then deleted around 10 rows from the table and attempted a restore. It claimed to be successful and took 3 seconds for the restore. The 10 records deleted are still not present.

tkr
October 31st, 2006, 10:29 AM
Can you post the logs again please?

Thanks
tk

trickykid
October 31st, 2006, 11:01 AM
Here is the snippet for the logs from today:



Tue Oct 31 12:49:54 2006: INFO: backup-set=test
Tue Oct 31 12:49:54 2006: INFO: backup-date=20061031124954
Tue Oct 31 12:49:54 2006: INFO: host=localhost
Tue Oct 31 12:49:54 2006: INFO: backup-date-epoch=1162320594
Tue Oct 31 12:49:54 2006: INFO: retention-policy=1D
Tue Oct 31 12:49:54 2006: INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
Tue Oct 31 12:49:54 2006: INFO: mysql-version=4.1.12-standard-log
Tue Oct 31 12:49:54 2006: INFO: backup-directory=/var/lib/mysql-zrm/test/20061031124954
Tue Oct 31 12:49:54 2006: INFO: comment=Storm39 Test Backup
Tue Oct 31 12:49:54 2006: INFO: backup-level=0
Tue Oct 31 12:49:54 2006: INFO: logical-tables=Domains
logical_database=test
Tue Oct 31 12:49:54 2006: INFO: next-binlog=storm39-bin.000016
Tue Oct 31 12:49:54 2006: INFO: last-backup=/var/lib/mysql-zrm/test/20061030141837
Tue Oct 31 12:49:54 2006: INFO: /var/lib/mysql-zrm/test/20061031124954/backup.sql=8d638b85492de0a22b1efa290c449875
Tue Oct 31 12:49:54 2006: INFO: backup-size=0.48 MB
Tue Oct 31 12:49:54 2006: INFO: read-locks-time=00:00:00
Tue Oct 31 12:49:54 2006: INFO: flush-logs-time=00:00:00
Tue Oct 31 12:49:54 2006: INFO: backup-time=00:00:00
Tue Oct 31 12:49:54 2006: INFO: backup-status=Backup succeeded
Tue Oct 31 12:49:54 2006: INFO: Backup succeeded
Tue Oct 31 12:54:14 2006: INFO: Restore done in 3 seconds.

paddy
October 31st, 2006, 11:36 AM
Hi,

I guess you are doing logical backups of MySQL 4.1.12 with InnoDB tables. Is my understanding correct?

1. Check if there are errors in /var/log/mysqld.log during backup
2. Are there any errors during restore in /var/log/mysql-zrm/mysql-zrm.log file? Can you please set "verbose=1" in mysql-zrm.conf if you have not done so?

3. There are MySQL bugs with this combination. Most relevant one is http://bugs.mysql.com/bug.php?id=10200. Please check mysqld logs to see if this is same problem.

I would be interested in seeing the backup.sql file from the backup directory assuming that you do not have any private or confidential information in the database.

Thanks for your help.
Paddy

trickykid
October 31st, 2006, 12:13 PM
Yes, Mysql 4.1.12 on Centos 4.0 using InnoDB tables. Most of our databases are anywhere from 20GB to 160GB in size.

1. No errors during backup.
2.
Output of verbose logging for restore attempt:


Tue Oct 31 14:39:22 2006: INFO: Mail address: user@domain.com is ok
Tue Oct 31 14:39:22 2006: INFO: Input Parameters Used {
Tue Oct 31 14:39:22 2006: INFO: verbose=1
Tue Oct 31 14:39:22 2006: INFO: retention-policy=1D
Tue Oct 31 14:39:22 2006: INFO: backup-level=0
Tue Oct 31 14:39:22 2006: INFO: mailto=user@domain.com
Tue Oct 31 14:39:22 2006: INFO: destination=/var/lib/mysql-zrm
Tue Oct 31 14:39:22 2006: INFO: source-directory=/var/lib/mysql-zrm/test/20061031124954
Tue Oct 31 14:39:22 2006: INFO: html-reports=backup-status-info
Tue Oct 31 14:39:22 2006: INFO: database=test
Tue Oct 31 14:39:22 2006: INFO: html-report-directory=/var/www/mysql-zrm/reports/
Tue Oct 31 14:39:22 2006: INFO: backup-mode=logical
Tue Oct 31 14:39:22 2006: INFO: tables=Domains
Tue Oct 31 14:39:22 2006: INFO: password=******
Tue Oct 31 14:39:22 2006: INFO: comment=Test Backup
Tue Oct 31 14:39:22 2006: INFO: user=root
Tue Oct 31 14:39:22 2006: INFO: mysql-binlog-path=/var/lib/mysql
Tue Oct 31 14:39:22 2006: INFO: }
Tue Oct 31 14:39:22 2006: INFO: Getting mysql variables
Tue Oct 31 14:39:22 2006: INFO: mysqladmin --user=root --password=***** variables
Tue Oct 31 14:39:23 2006: INFO: datadir is /var/lib/mysql/
Tue Oct 31 14:39:23 2006: INFO: mysql_version is 4.1.12-standard-log
Tue Oct 31 14:39:23 2006: INFO: log_bin=ON
Tue Oct 31 14:39:23 2006: INFO: Shutting down MySQL
Tue Oct 31 14:39:28 2006: INFO: Restore done in 6 seconds.


3. No errors related to Bug 10200. In fact, if I dump a using mysqldump, I get a perfect dump of the tables and data.

This is what we have in place now for backups, a homegrown script to perform mysqldumps daily, in which we've had no problems restoring data from. Just looking for a more manageable solution with possible incremental backups to avoid having to perform full dumps each day.

Unfortunately I'm running tests on our actual data from one of our other databases, so the data is confidential and I'm not allowed to share it. I'm testing against real data in order to get real test results.

paddy
October 31st, 2006, 12:29 PM
Thanks for your help so far. I understand you will not be able to provide sql file.

I'm trying to pinpoint the problem. Unfortunately, we have not been able to reproduce the
problem so far.

Is the backup image good? Does "mysql -u root -p test; source /var/lib/mysql-zrm/test/<date>/backup.sql" create a database with the 10 deleted rows?

Paddy

trickykid
October 31st, 2006, 12:33 PM
Yes it appears to be good. If I restore thru mysql-zrm --action restore it claims that it is successful, but yet does not insert into the database. If I do it manually:

mysqldump -u root -p test < /var/lib/mysql-zrm/test/<backup>/backup.sql

It reinserts everything without any problems and the data is there in the database.

paddy
October 31st, 2006, 01:00 PM
Can you please try (this is slightly different from shell redirection)

# mysql -u root -p test
mysql> source /var/lib/mysql-zrm/test/20061031124954/backup.sql

How big is your database?

thanks,
Paddy

kkg
October 31st, 2006, 07:51 PM
Hi,

Could you please send us the index file from the backup directory?
Looking at the log I have a feeling that the index file has a problem as I don't see any databases being restored.

--kkg

kkg
October 31st, 2006, 08:36 PM
Hi,

Found the issue and this is a bug in mysql-zrm restore code which gets triggered when a specific database and table is specified. In such a case, mysql-zrm was ignoring these entries in the index file.

I will be opening a bug for this and this will be fixed in the next release.

Thanks a lot to trickykid for your patience in helping us track this problem down.
--kkg

trickykid
November 1st, 2006, 09:06 AM
Awesome, I'll anticipate trying out the newer release when it's available.

trickykid
November 1st, 2006, 09:08 AM
Here is the output of the index from the latest backup I tested:

backup-set=test
backup-date=20061031124954
host=localhost
backup-date-epoch=1162320594
retention-policy=1D
mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
mysql-version=4.1.12-standard-log
backup-directory=/var/lib/mysql-zrm/test/20061031124954
comment=Storm39 Test Backup
backup-level=0
logical-tables=Domains
logical_database=test
next-binlog=storm39-bin.000016
last-backup=/var/lib/mysql-zrm/test/20061030141837
/var/lib/mysql-zrm/test/20061031124954/backup.sql=8d638b85492de0a22b1efa290c449875
backup-size=0.48 MB
read-locks-time=00:00:00
flush-logs-time=00:00:00
backup-time=00:00:00
backup-status=Backup succeeded

kkg
November 1st, 2006, 01:05 PM
Awesome, I'll anticipate trying out the newer release when it's available.

BTW there are a couple of ways by which you can avoid hitting this issue.

One is to do a backup after setting backup-mode to raw. Here you will not face the issue with restore.

If raw mode is specified, mysql-zrm will itself choose the mechanism to backup. In the raw mode, if the backed up database contains an InnoDB or BDB based table, mysql-zrm will use mysqldump to backup the database rather than attempting to do a raw backup. But if the database does not contain any tables based on transactional engines, then mysqlhotcopy is used.

The other way to avoid this bug is to only specify the databases option rather than the database and tables options. The drawback in this case is that you will not be able to selectively specify the tables you want to backup. The full database will be backed up.

--kkg

trickykid
November 1st, 2006, 01:42 PM
Yeah, unfortunately we need the option of specifying tables to backup. A lot of our databases have tmp type tables created that we don't need backed up, or it would waste a lot of space in doing so. We also have some tables that are rather large but also don't need to be backed up as the data changes too often, same reason for the tmp tables created.

I'll do some tests with raw as the option to test, let mysql-zrm decide which method to use to backup with.

paddy
November 2nd, 2006, 02:25 PM
Version 1.1.1 (available in Zmanda downloads (http://www.zmanda.com/downloads.html)page) has fixes for this problem.

Thanks for reporting the problem.

Paddy

lakshmi
September 14th, 2007, 06:32 AM
Thanks sachin,

Really it will be very useful for me. First I started to do full backup of one database and then I took incremental backup after some modification.

I dropped the database which I has taken backup. Then I restored full backup and checked the database, records are available (what was before taking backup).Afterwards I restored incremental backup of database, I checked database. It is also proper, records which are updated after full backups are available.

These both procedure was same as u did.

After restoring again I updated database and then I took another one incremental backup since I checked the incremental backups parse logs after modification but it didnt show new modification's query. Then I dropped database and checked by restoring full backup first and then restored remaining incremental backups by using selective binary log restoration.

whether it is needed to take another one incremental backup as I did or not though both full and incremental backup is scheduled daily? Also How can I identify full and incremental backup since you are using same backup set for both backups?