MySQL Replication: How to do

On a New Server. That's it. Four steps.

On a New Server

Configuring replication on a new server is a straightforward process. The tasks you need to perform are:

1. Create a replication account on each server.

2. Add configuration entries in my.cnf on each server.

3. Restart the master and verify the creation of a binary log.

4. Restart the slave and verify that replication is working.

For the sake of clarity, we'll use the hostnames master and slave for our master and slave, respectively.

Account creation

When the slave connects to the master, it must authenticate itself just like any other MySQL client, so it needs a username and password. We'll create an account named repl with a password of c0pyIT! on both the master and slave.

Why create the account on the slave? Should the master ever fail, you'll want the slave to become the new master. When the old master is repaired, it can be put back online as a slave of the new master (which is the old slave). If the account didn't exist on the new master, the new slave wouldn't be able to replicate.

So, on each server let's create the account and give it only the minimum privileges necessary for replication: REPLICATION SLAVE and REPLICATION CLIENT. (In MySQL 3.23 you'd use USAGE and FILE.)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  repl@"192.168.1.0/255.255.255.0" IDENTIFIED BY 'c0pyIT!'; Query OK, 0 rows affected (0.00 sec)

After creating the account, verify that the settings are correct:

mysql> SHOW GRANTS FOR repl; +----------------------------------------------------------------------------------+ |Grants for repl@"192.168.1.0/255.255.255.0"                                         | +----------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'...' IDENTIFIED BY ...| +-------------------------------------------------------------------------+ 1 row in set (0.00 sec)

If that command returns no rows or doesn't list the privileges, double-check that you entered the GRANT command properly.

Configuration file entries

The next step is to update the my.cnf file on each server. You need to tell the master to enable binary logging and to tell the slave about its master, login credentials, and so on.

Finally, each server needs to be assigned an ID number, known as a server ID. As you'll see later, the server ID is recorded in each server's binary log entries so that any other server can know which server first executed and logged a query. The server ID can be any number in the range 1-4294967295.[4]

So, on the master, make sure the following lines are present in the [mysqld] section of the my.cnf file:

log-bin server-id = 1

The log-bin option tells MySQL to enable binary logging. By default, MySQL places the log file in its data directory. To place the logs elsewhere, you can supply a path and filename:

log-bin = /var/db/repl/log-bin

The slave requires a bit more information than the master. Add the following lines to its my.cnf file:

server-id = 2 master-host = master.example.com master-user = repl master-password = c0pyIT! master-port = 3306

The slave's settings are self-explanatory. It just needs to know how to contact the master and authenticate itself. Later we'll look at some optional replication settings that can also appear in my.cnf.

Restart master

With the settings on the master, it's time to stop and start MySQL and verify that the binary log appears. No replication-specific method is necessary; simply stop and restart MySQL using your normal scripts that handle MySQL when the machine boots and shuts down.

If you didn't specify a path and filename after log-bin in your my.cnf file, MySQL writes the log files in your data directory. Otherwise, the logs are written in the location you specified.

You should find a binary log file with a .001 extension. By default, the filename will be hostname-bin. On the host master.example.com, the first log file will be master-bin.001. If you haven't run any write queries yet, the file will be less than 100 bytes in size. Each log file contains a short header and some meta information.

If you then execute a few write queries, you should notice the size of the binary log file increasing. If not, check the error log for hints about what might have gone wrong.

You can use the mysqlbinlog utility to examine the data stored in a binary log file. It reads the entries and prints out the SQL for each one. It also prints some comments that contain other helpful information. For example, running it on a fresh log produces output like this:

$ mysqlbinlog master-bin.001 # at 4 #020922 14:59:11 server id 1  log_pos 4        \  Start: binlog v 3, server v 4.0.4-beta-log created 020922 14:59:11

The first comment indicates that this entry is at offset 4 in the log. The second comment indicates when the log was created, the server ID, the log version, and the server version.

Restart slave

With the master logging properly, a simple restart of the slave should be sufficient to get replication running. When a MySQL server is started, it checks to see whether it should connect to a master and begin (or continue) replicating queries. Upon connecting to the master, MySQL logs a message in its error log to indicate whether the connection succeeded or failed:

021103 13:58:10  Slave I/O thread: connected to master 'repl@master:3306', replication started in log 'log-bin.001' at position 4

This entry indicates that the slave has connected to the master and begun reading the binary log file master-bin.001 at position (or offset) 4, that of the first query.

Run some write queries on the master and verify that the data on the slave reflects those changes. Once the slave is happily replicating from the master, it can continue to do so indefinitely.

REF: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html