With mysql replication, a slave mysql server continuously accesses the binary log on a master mysql server to update its own databases. This is a simple way to provide a "live backup" that is useful e.g. when the master server crashes.

The following outlines (for mysql 4.1.1, but it should also work for later releases) the steps to take, based on my own (limited) experience. For more information, see e.g. the mysql documentation.

We assume that master.vub.ac.be contains the databases dba, dbb, dbc of which we want to mirror only dbb and dbc to slave.vub.ac.be. The exact file locations (e.g. of my.cnf) below may be gentoo-specific.

• Modifying the master server
• Modifying the slave server

Modifying the master server

1. Ensure that binary logging is on and that server-id is defined in /etc/mysql/my.cnf:

      [mysqld]
      ..
      server-id = 1
      log-bin
    

   The binary log files will be in the mysql data directory (/var/lib/mysql on gentoo). To look at a binary log file, use mysqlbinlog, e.g.

    mysqlbinlog /var/lib/mysql/master-bin.000005
    

2. Ensure that the master server is accessible from outside localhost; in /etc/mysql/my.cnf:

      bind-address = 134.210.10.99
    

3. Define a mysql user for the slave:

      mysql -u root -pxyz <<EOF
      GRANT REPLICATION SLAVE, REPLICATION CLIENT
      ON *.* 
      TO 'replicant'@'slave.vub.ac.be' 
      IDENTIFIED BY 'some_password';
      EOF
    

4. Dump the databases to mirror.

      mysqldump --user=root --password=xyz \
        --extended-insert --master-data --lock-tables \
        --databases dbb dbc >/tmp/master.dump
    

   Thanks to the --master-data option, the master.dump file will contain a line such as the following

      CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000011', MASTER_LOG_POS=524;
    

   which, when executed on the slave, will tell it from which point in the master's binary logs it should start mirroring. The master.dump file also contains database creation statements:

      CREATE DATABASE dbb;
      CREATE DATABASE dbc;
    

   so one does not have to create these explicitly on the slave.

Modifying the slave server

1. Ensure that server-id is defined and different from that of the master server; in /etc/mysql/my.cnf:

      [mysqld]
      ..
      server-id = 2
    

2. Ensure that the slave and the master have the same values for character-set-server and default-character-set; e.g. if latin1 was the value for the master, /etc/mysql/my.cnf should contain

      [mysqld]
      ..
      character-set-server = latin1
      default-character-set = latin1
    

3. In /etc/mysql/my.cnf, define which databases should (not) be mirrored:

      [mysqld]
      ..
      server-id = 2
      replicate-ignore-db=dba
      replicate-wild-do-table=dbb.%
      replicate-wild-do-table=dbc.%
    

4. Drop any existing databases with the same name

      mysqladmin -u root -pxyz -f drop dbb
      mysqladmin -u root -pxyz -f drop dbc
    

5. Copy and load the master backup file:

      scp master.vub.ac.be:/tmp/master.sql /tmp/master.sql
      mysql -u root -pxyz </tmp/master.sql
    

6. Finally, start up the slave.

    mysql -u root -pxyz <<EOF
    STOP SLAVE;
    CHANGE MASTER TO
      MASTER_HOST='master.vub.ac.be',
      MASTER_USER='replicant',
      MASTER_PASSWORD='some_password';
    START SLAVE;
    

7. Check whether all is well in the mysqld error file /var/log/mysql/mysqld.err.