Once we’ve issued the command start slave;, The Replication should ideally start, But as well all know nothing works initially. So to check everything is working, issue the following command.

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.5.99
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000009
        Read_Master_Log_Pos: 37822065
             Relay_Log_File: ruturaj-vartak-relay-bin.000028
              Relay_Log_Pos: 37822202
      Relay_Master_Log_File: mysql-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test
        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: 37822065
            Relay_Log_Space: 37822202
            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: 0
1 row in set (0.00 sec)

Check the parameters

• Slave_IO_Running (Should be yes)
• Slave_SQL_Running (Should be yes)

If either of them are No, Open the /hostname.err file. This is the mysql error file, You can check it for a specific configuration error. Most often either the Master log is not present (incorrect filename) or the master log position is incorrect. You can re-specify the parameters by using change master to .. command.

But be sure you issue stop slave first and then change master to … command and finally start slave

Once both the Slave_IO and Slave_SQL parameters are yes, you can start writing, modifying anything in the test database of the server.

You can also check the hosts connected by issuing this command on the master

mysql> show slave hosts;
+-----------+----------------+------+-------------------+-----------+
| Server_id | Host           | Port | Rpl_recovery_rank | Master_id |
+-----------+----------------+------+-------------------+-----------+
| 1         | slave-server-1 | 3306 | 0                 | 2         |
+-----------+----------------+------+-------------------+-----------+

You can now check the MySQL’s data directory if the binary log is created. You should find a file something like mysql-bin.000001. You will also find a file mysql-bin.index.

In the mysql-bin.index file you will find the list of all the binary logs. i.e. If you restart the server a multiple times you will find your mysql-bin.index to have something like

./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003

Now try this query in the mysql prompt show master logs; It should show something like this, depending the no. of binary logs present.

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 | 9066115   |
| mysql-bin.000005 | 117       |
| mysql-bin.000006 | 98        |
+------------------+-----------+

Now it is essential to add a user account which the replication will use, Since we are granting a permission, you need to log in with a root access to mysql, and then run the following command.

mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'192.168%' IDENTIFIED BY 'slavepass';

Flush the privileges using mysql> flush privileges

This should ideally make your master ready for Replication.

Now that master is all set! Its time for the slave to obey the master. To setup the slave, We need to tell the Slave server which log file it is supposed to read, and yet again we need to give a unique server-id to the slave.

So open the /etc/my.cnf and add the following below the [mysqld] section.

server-id=2
replicate-do-db=test
report-host=slave-server-1

• The directive replicate-do-db: specifies that only test database is to be replicated.
• The directive report-host: specifies the hostname that will be seen on the master

Restart your MySQL server.

Now we are ready to tell the server about the master server.
Give following command to the slave server.

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.5.100',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000001',
    ->     MASTER_LOG_POS=4;

The master_log_pos is the parameter which tells the server to read the file from a particular position.
You can check the position by the following command on the master server.

mysql> show binlog events in 'mysql-bin.000009' limit 10;
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000009 | 4    | Format_desc | 2         | 98          | Server ver: 5.0.19-standard-log, Binlog ver: 4                     |
| mysql-bin.000009 | 98   | Intvar      | 2         | 126         | INSERT_ID=10077                                                    |
| mysql-bin.000009 | 126  | Query       | 2         | 430         | use `test`; insert into testtable (tname) values('ruturaj vartak') |
| mysql-bin.000009 | 430  | Query       | 2         | 511         | use `test`; truncate table testtable                               |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+

So you can MASTER_LOG_POS set anything in the Pos column.

This should set up all the required configuration of the slave. You just need to tell slave to start the replication of the slave by the following command

mysql> start slave;

To configure the master we need to start binary logging, and some minor tweaking. Before we move ahead, I assume that you have the root or Administrator access to the sytem where you are setting up replication. First let us setup the Binary logging.

To enable binary logging, we need to configure the configuration file /etc/my.cnf on Linux. If you are in Windows, I guess it should be in c:\program files\MySQL\MySQL Server 5.0\my.ini. Open the file and add the following below the [mysqld] section

log-bin=mysql-bin
server-id=1

So the configuration should look something like

[mysqld]
log-bin=mysql-bin
server-id=1
... other parameters ...

[mysql]
... parameters for mysql client ...

• The directive log-bin defines the file name of the binary log. All the binary logs that will be created in the data directory, are named something like mysql-bin.000004
• The directive server-id is to give the servers in the replication a unique numeric ID. It has to be an integer. So we’ll call our Master Server as 1.

This would set the master, all we require is to restart the Server. You can restart the server (in linux) with either of the following commands

• service mysql restart
• /etc/rc.d/init.d/mysql restart
• mysqladmin -u root -p shutdown
  and then mysqld_safe &

In windows, you’d be having an easier interface

If you are here reading Replication for MySQL, if you know what type of replication is available in other databases, then it may be a bit troubling for you to understand. However, One point will make everything clear.

In MySQL replication is that of the Binary Log, which has user’s commands logged in it.

Generally replication is used when there are many servers to balance the load and have the same data. In such cases, Usually A single master and multiple Slaves are used.

So let us consider we have 3 servers,

• Server A: The Master Server (192.168.5.100)
• Server B: The Slave Server no. 1 (192.168.5.101)
• Server C: The Slave Server no. 2 (192.168.5.102)

The Servers B and C are peers to each other.

We’ll set up replication of the database test. When I say database “test”, it means its all tables are replicated as well. I’ll come up to the specific configurations, etc … but later. I assume we are starting with an empty database.

To setup replication, we need to configure the following

• Master
  • Binary log
  • …and some configuration
• Slave: Configuration of the master for the slave

Stuff about MySQL…
Configuration, Tips, Replications, etc.