mysql easy replication

MySQL offers quite simple installation of replication // we need at least two servers that sees each other over tcp/ip and a bit of configuration

on master server (one that we write data in) this is minimal (a bit more) required configuration:

[mysqld]

server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
# http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_expire_logs_days

expire_logs_days   = 10 

# http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#sysvar_max_binlog_size

max_binlog_size    = 100M
binlog_do_db       = my_database

the last line indicates that we would like to replicate only one databse on master server

after that we need to grant replication previlegies to slave server with command

GRANT REPLICATION SLAVE ON *.* TO 'slave-user'@'%' IDENTIFIED BY 'slave-pass';

FLUSH PRIVILEGES; 

we could also specify exact ip of slave server, but i prefere setting this into iptables - firehol to be exact is the tool for lazy linux admins

interface eth0 inet
    group with src "ip.of.slave.server"
    server "mysql" accept
    group end

next we need to configure slave server 

 

[mysqld]

server-id       = 2
relay-log-index = slave-relay-bin.index
relay-log       = slave-relay-bin

we are almost all set -- we just need to get current database state to slave server and start replication

1. on master we need to get current "log and position"

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.07 sec)

 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000034 |     1539 | my_databse   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
 

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

2. we make export of master database and import it on slave (i use good old phpMyAdmin for the job)

3. and we set and start replication on slave

mysql> CHANGE MASTER TO MASTER_HOST='ip.of.master.server', MASTER_PORT=3306, MASTER_USER='slave-user', MASTER_PASSWORD='slave-pass', MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=1539;

mysql> START SLAVE;
 

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ip.of.master.server
                  Master_User: master-user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000034
          Read_Master_Log_Pos: 2060
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 774
        Relay_Master_Log_File: mysql-bin.000034
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2060
              Relay_Log_Space: 930
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)