Live Backups of MySQL Using Replication
Subject:   Replica requirements
Date:   2005-06-17 02:38:12
From:   leeg
Thanks for this article - very timely as far as I'm concerned as I'm about to put a MySQL-based app live :-)

What sort of performance does the replica server need relative to the master? It looks like the updates to the replica DB can happen asynchronously as another thread is polling the logfile, so perhaps it doesn't need to be an identical box to the master. But where does the point come that the backup is compromised because the replicant can't keep up?

Full Threads Oldest First

Showing messages 1 through 3 of 3.

  • Replica requirements
    2005-07-08 10:53:49  ugob [View]

    If your slave is dedicated, it doesn't have to be very powerful, as it only receives Updates + Inserts.
    • Replica requirements
      2005-07-29 06:39:58  leeg [View]

      Thanks for this article; I now have a replica of my production DB :-) However, I was using OS X (I suppose you'd call my server DAMP rather than LAMP) and had to change a couple of things. There's no /etc/my.cnf by default, you have to copy one from somewhere (there's a sample file at /usr/share/mysql/my-medium.cnf). Even so, once you've edited the files as described in this article you still explicitly have to run the 'CHANGE MASTER TO' SQL statement before you can 'START SLAVE'.
      • Russell Dyer photo Replica requirements
        2005-11-26 04:55:22  Russell Dyer | O'Reilly Author [View]

        I'm responding pretty late, but for future reference for others, here's my response.

        You are correct. The lines I presented in the article to set the values for connecting to the master are only used one, when you first restart the slave server. They are copied to the file, which is by default located in the data directory on the slave. Once the file has the connection information stored in it, the server will ignore these configuration settings or options in the my.cnf file and even at the command-line when the server is restarted. The only way to change them after the file is created is to edit the file--which is not a very good idea--or to use the CHANGE MASTER TO statement that you mentioned. Therefore, to adjust my article, you may as well not include them in the my.cnf file to begin with. Instead, enter them from the slave while logged into MySQL as root or the like:

        CHANGE MASTER TO MASTER_HOST = 'master_host';
        CHANGE MASTER TO MASTER_USER = 'replicant';

        You only need to execute these statements one time to set them since they're stored in the file. They'll survive restarts. If you want to change something like the host name, though, then rerun just the statement with that particular parameter (i.e. MASTER_HOST).

        Regarding MacIntosh, although my servers have Linux on them, my laptop now is an iBook G4. Yes, you're right: by default it is set up without the my.cnf file. You can copy some of the sample files like you did, or you can create a new my.cnf file with just the group headings (e.g., [mysqld]) and options that you need with a simple text editor like vi while logged in as root (i.e., su)from a terminal window.