MySQL Replication under Debian

Always when we talk about MySQL replication we talk about high-availability (HA). Why we choose Mysql Replication and not Mysql Clustering in a high-availability environment? Because Mysql Cluster have some limitations on his NDB storage system and probably we want to use INNODB (ACID compliant storage system) and foreign keys.
What is Replication ? Is a Database syncronization between two (or more) MySQLnodes.
I will start explain you in this step by step tutorial how to do a master slave configuration between 2 nodes.

Installing Mysql Server and Client on Debian
As root run on both nodes

apt-get install mysql-server-5.0 mysql-client-5.0

This will install you the MySQL 5.0 Server and Client.
Now is time to set up a DNS hostname for both machines. You can do that directly from your internal DNS or directly on your hosts. Open /etc/hosts and set your hostnames. In my case I choose rb-master and rb-slave as hostnames and my hosts files looks, on both servers, like:

127.0.0.1 localhost
10.1.4.52 rb-slave.randombugs.com rb-slave
10.1.4.51 rb-master.randombugs.com rb-master

Setting up the master server

Open /etc/mysql/my.cnf and add/edit the following values:

bind-address = 0.0.0.0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

and look after skip-networking and if you have it enabled comment it please.
bind-address tells to mysql on what interface will listen for incoming connections. You also can use 10.1.4.51 (your IP address) if you don’t need any other connections from other interfaces.
server-id it will help mysql to identify individual servers in a group

If you use INNODB as storage engine the you can add to INNODB section:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

Now restart the mysql server

/etc/init.d/mysql restart

Add a replication user

We need this user for the slave server to get all the updates and other configurations from master.

$mysql -u root -p
mysql> GRANT REPLICATION SLAVE,RELOAD,REPLICATION CLIENT,SUPER ON *.* TO ‘rbuser’@'%’ IDENTIFIED BY ‘rbpassword’;
Query OK, 0 rows affected (0.02 sec)

Setting up the slave server

Open /etc/mysql/my.cnf and add/edit the next values

bind-address = 0.0.0.0
server-id = 2

master-host = rb-master
master-user = rbuser
master-password = rbpassword

I hope you know what means all master-* configurations values and is not necessarly to explain them

after that just restart mysql server

/etc/init.d/mysql restart

Now start mysql client and try to load data from the master:

$mysql -u root -p
load data from master;

Now relog on rb-master and run

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000024 | 144591603 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

Relog on rb-slave and run on mysql:

mysql> show slave status \G;
*************************** 1. row ***************************
             Slave_IO_State:
                Master_Host: rb-master
                Master_User: rb-slave
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000024
        Read_Master_Log_Pos: 144591603
             Relay_Log_File: rb-slave relay-bin.000027
              Relay_Log_Pos: 631396708
      Relay_Master_Log_File: mysql-bin.000024
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 144591603
            Relay_Log_Space: 0
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec

and you will need to have Position (from show master status) ~= Read_Master_Log_Pos (from show master slave).

And now your servers should be ready for sql scripts to be uploaded.

Good luck!