Results 1 to 6 of 6

Thread: MySQL Replication Server Failing to Backup

  1. #1
    Join Date
    Oct 2006
    Posts
    22

    Default MySQL Replication Server Failing to Backup

    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.

  2. #2
    Join Date
    Oct 2005
    Posts
    1,559

    Default

    Did you set replication in mysql-zrm.conf?

    See [url]http://mysqlbackup.zmanda.com/index.php/Backup_of_MySQL_Replication_Slaves[/url]

    Paddy

  3. #3
    Join Date
    Oct 2006
    Posts
    22

    Default

    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="[email protected]"
    ssh-user="root"

  4. #4
    Join Date
    Oct 2006
    Posts
    199

    Default

    Quote Originally Posted by trickykid View Post
    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 ([url]http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html[/url])

    --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.


    Quote Originally Posted by trickykid View Post
    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.

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

    Hope this helps.
    --kkg

  5. #5
    Join Date
    Oct 2006
    Posts
    22

    Default

    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.

  6. #6
    Join Date
    Oct 2006
    Posts
    199

    Default

    Quote Originally Posted by trickykid View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •