PDA

View Full Version : MySQL Replication Server Failing to Backup



trickykid
December 20th, 2007, 11:33 AM
I'm getting the following error in the verbose output when attempting to test a full backup of a MySQL server that is a slave to our master server. I've tried changing the backup-mode in the conf file as raw, logical and commenting it out and yet it still fails:


dbrep-full:backup:ERROR: Binary logging is off. Logical backup cannot be done

And of course I don't have Binary logging turned on, why should I when it's a slave. That's just dumb to have duplicate binary logs really. All I have are the relay bin logs from my master.

Is there something I'm missing or doing wrong? I don't seem to find this issue anywhere else or related to it.

paddy
December 26th, 2007, 04:16 PM
Did you set replication in mysql-zrm.conf?

See http://mysqlbackup.zmanda.com/index.php/Backup_of_MySQL_Replication_Slaves

Paddy

trickykid
December 29th, 2007, 04:40 AM
Yes. replication=1 in the conf file being used. Here is the conf file without the commented sections:

comment=dbrep full
destination=/data/mysql-zrm
retention-policy=2W
replication=1
compress=1
compress-plugin=/usr/bin/gzip
databases=database
user="backup"
password="*******"
host="db-rep2.unwiredbuyer.com"
port=3306
socket=/var/lib/mysql/mysql.sock
mysql-binlog-path="/var/lib/mysql"
tmpdir=/var/tmp
verbose=1
mailto="root@localhost"
ssh-user="root"

kkg
December 29th, 2007, 05:46 AM
I'm getting the following error in the verbose output when attempting to test a full backup of a MySQL server that is a slave to our master server. I've tried changing the backup-mode in the conf file as raw, logical and commenting it out and yet it still fails:

dbrep-full:backup:ERROR: Binary logging is off. Logical backup cannot be done


The reason why ZRM cribs is because ZRM uses the --master-data option to record the binary log position on the slave. Havin this helps the slave to start from the correct position in the master's binary logs

From mysqldump documentation (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html)

--master-data[=value]

Write the binary log filename and position to the output. This option requires the RELOAD privilege and the binary log must be enabled. If the option value is equal to 1, the position and filename are written to the dump output in the form of a CHANGE MASTER statement. If the dump is from a master server and you use it to set up a slave server, the CHANGE MASTER statement causes the slave to start from the correct position in the master's binary logs. If the option value is equal to 2, the CHANGE MASTER statement is written as an SQL comment. If the value is not specified, then the default value is 1.



And of course I don't have Binary logging turned on, why should I when it's a slave. That's just dumb to have duplicate binary logs really. All I have are the relay bin logs from my master.

Is there something I'm missing or doing wrong? I don't seem to find this issue anywhere else or related to it.

The reason why we really cannot depend on the relay bin logs is that the relay bin logs on the slave will get deleted automatically.

Following is taken from mysql documentation:

"Relay logs have the same format as binary logs and can be read using mysqlbinlog. The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it."

Please see following for full details.

http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html

Hope this helps.
--kkg

trickykid
December 29th, 2007, 05:07 PM
So I'm just trying to perform and test a full backup of a database off a replication server. Why would it really need the binary logging unless it's an incremental? That just doesn't really seem feasible to me.

So in order to backup a slave MySQL, you're trying to tell me I need to have it so there are 3 writes just so one backup can make 1 read to backup the data to write elsewhere? On a busy server, having relay bin logs, normal bin logs and then the actual writes to the database all occurring, I could see this as a huge performance issue just to backup a slave replication server.

kkg
December 29th, 2007, 05:50 PM
So I'm just trying to perform and test a full backup of a database off a replication server. Why would it really need the binary logging unless it's an incremental? That just doesn't really seem feasible to me.

So in order to backup a slave MySQL, you're trying to tell me I need to have it so there are 3 writes just so one backup can make 1 read to backup the data to write elsewhere? On a busy server, having relay bin logs, normal bin logs and then the actual writes to the database all occurring, I could see this as a huge performance issue just to backup a slave replication server.

If you are interested in only a full backup of a database, then just set "replication=0" and take the full backup like you would on a regular mysql server.