Wednesday, September 12, 2012

MySQL Replication 101 Overview

Since MySQL Connect is just around the corner and several of the sessions are related to the replication features in MySQL 5.6, I figured I would put together a quick MySQL Replication 101 Overview for any of the new users.

Topology

First you must decide what type of Topology will be serve you and your applications needs.  Below are a list of typical topologies. For this blog post example we will set up a couple of versions.


Single Master and on slave:
Single Master and muliple slaves:
Circular:

Chain:


 MySQL Configuration file edits
 
MySQL 5.6 will allow the us to take advantage of the database for our repository information. The  "master-info-repository=TABLE" and the "relay-log-info-repository=TABLE" reference allows this. A unique value is required for server id per server.


Master my.cnf

# vi /etc/my.cnf
        [mysqld]
        server-id=1
        log-bin = /var/lib/mysql/yoda-bin
        master-info-repository=TABLE

[mysql]
prompt=mysql_yoda>\\_


  
Start and Log into MySQL

    master_yoda>show master status\G
*************************** 1. row ***************************
            File: yoda-bin.000001
            Position: 114
            Binlog_Do_DB:
            Binlog_Ignore_DB:
            1 row in set (0.00 sec)



Master SQL



mysql_yoda> show variables like 'master_info_repository';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| master_info_repository | TABLE |
+------------------------+-------+
 

mysql_yoda>CREATE USER 'replication'@'192.168.0.%' IDENTIFIED BY 'slavepass';
mysql_yoda>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql_yoda>flush privileges;


Now lets create a copy of the database to start from.



# mysqldump -p  --all-databases  --master-data=2 > /tmp/replication_example.sql
THIS LOCKS THE DATABASE! 
or try  the MySQL utilities 


mysqldbexport --server=root@server1:3306 --format=csv db1 --export=data

If you do not want to lock the database as you make a copy, Oracle offers MySQL Enterprise Backup  You can test this for 30 days as well for free.

Remember to adjust all firewall rules if required for MySQL Port. (3306)


SLAVE my.cnf
  vi /etc/my.cnf
        [mysqld]
        Server-id=2
        relay-log=/var/lib/mysql/luke-relay-bin
        relay-log-info-repository =TABLE
[mysql]
prompt=mysql_luke>\\_

# mysql --user=root  -p  <  /tmp/replication_example.sql

SLAVE SQL

mysql_luke> CHANGE MASTER TO
  MASTER_HOST='yoda',
  MASTER_USER='replication',
  MASTER_PASSWORD='slavepass',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='yoda-bin.000002',
  MASTER_LOG_POS=83415,
  MASTER_CONNECT_RETRY=10;

mysql_luke> start slave;

We gathered this info from the mysqldump file via the  “ --master-data=2 ” flag.

mysql_luke> show slave status\G

mysql_luke> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: yoda
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: yoda-bin.000003
          Read_Master_Log_Pos: 323
               Relay_Log_File: luke-relay-bin.000004
                Relay_Log_Pos: 475
        Relay_Master_Log_File: yoda-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                 Master_Server_Id: 1
    Master_UUID: 75d407df-2be4-11e1-9668-b4be9bce39b0
      Seconds_Behind_Master: 0
        SQL_Remaining_Delay: NULL 


Now we also have some alternative options to the above set up.

-- If you can afford to have both the master and slave database offline, MySQL Workbench comes with MySQL Utilties (also via launchpad) which has  mysqlreplicate

mysqlreplicate \ --master=root@master.example.com \ --slave=root@slave.example.com \ --rpl-user=repl:xyzzy
-- If you wanted to create a Chain or Circular topology, the slave ( or master 2 ) will also need a bin log which I have added below. Then you can set up another slave that is using "Luke", per the example, as the master.

SLAVE my.cnf
  vi /etc/my.cnf
        [mysqld]
        Server-id=2
        relay-log=/var/lib/mysql/luke-relay-bin
        relay-log-info-repository =TABLE 
        log-bin = /var/lib/mysql/luke-bin

To adjust the server examples above to a Circular Topology we will need to make a few more variable edits. The edits below will prevent index collisions.

SQL
   mysql_yoda> SET GLOBAL auto_increment_offset=2;
   mysql_yoda> SET GLOBAL auto_increment_increment=1;  
 
   mysql_luke> SET GLOBAL auto_increment_offset=2;
   mysql_luke> SET GLOBAL auto_increment_increment=2;


mysql_yoda>show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+--------------------------+-------+ mysql_luke>show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 2     |
+--------------------------+-------+

mysql_luke>SHOW MASTER STATUS\G
*************************** 1. row ***************************
        File: luke-bin.000005
        Position: 295

mysql_yoda>CHANGE MASTER TO
  MASTER_HOST='luke',
  MASTER_USER='replication2',
  MASTER_PASSWORD='slavepass',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='luke-bin.000005',
  MASTER_LOG_POS=295,
  MASTER_CONNECT_RETRY=10;
mysql_yoda> start slave;


This was a simple overview but it gets you started. If you would like to dig further, below is a list of related MySQL 5.6 and replication urls.


Posted by at