mysql – GTID replication

GTID replication is new in mysql 5.6 and adds an unique ID to every transaction on the database. That transaction ID is then used to ensure the transaction is applied on the slave. So this removes the need to know where the master is in which logfile. GTID ensures there is consistency and automatically determines at which transaction the slave is at and which transaction is next on the list.

Setting up GTID Replication

  • Create user for replication
    Mysql> CREATE USER 'repl'@'%.labo.exitas' IDENTIFIED BY 'RandomPassword';
    Mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.labo.exitas';
  • Adjustments needed to /etc/my.cnf ( on top of standard parameters for replication )
    • the server_id needs to be unique, Most of the time I set it to last octet of the IP.
    • Report-host and port determine how this mysql server will be reported in the mysql utilities. So set it to the current hostname and port.
    • gtid_mode should be enabled so that each transaction now has a unique id.
    • log-slave-updates should be enabled if you plan on further replicate the changes to other servers.
    • enforce-gtid-consistency should be enabled or GTID can not be set on. This makes sure all transactions are consistent and that transactions that combine actions on myISAM and InnoDB tables can’t be run.
    • don’t forget all the other parameters required for replication
    • RESULT

      # Replication
      server-id=111
      report-host=mysql02.labo.exitas
      report-port=3306 
      # GTID 
      gtid_mode=ON 
      log-slave-updates=ON
      enforce-gtid-consistency=true
  • Start the slave with auto positioning ( thanks to gtid )
    Mysql> CHANGE MASTER TO MASTER_HOST='mysql01.labo.exitas', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='RandomPassword';
    Mysql> START SLAVE;

How to monitor GTID Replication

First of all, the mysql-utilities that are provided are really excellent in giving you a view of the status of the different servers. I tend to use these all the time.

  • Install mysql-utilities
    (root) # yum install mysql-utilities.noarch
  • Check Topology
    (root) # mysqlrplshow --master=admin:admin@mysql01.labo.exitas --discover-slaves-login=admin:admin --verbose
    
    # master on mysql01.labo.exitas: ... connected.
    # Finding slaves for master: mysql01.labo.exitas:3306
    
    # Replication Topology Graph
    mysql01.labo.exitas:3306 (MASTER)
       |
       +--- mysql02.labo.exitas:3306 [IO: Yes, SQL: Yes] - (SLAVE)
    
  • Show Health
    # mysqlrpladmin --master admin:admin@mysql01.labo.exitas --discover-slaves-login=admin:admin health
    
    # Discovering slaves for master at mysql01.labo.exitas:3306
    # Discovering slave at mysql02.labo.exitas:3306
    # Found slave: mysql02.labo.exitas:3306
    # Checking privileges.
    #
    # Replication Topology Health:
    +--------------------------+-------+---------+--------+------------+---------+
    | host                     | port  | role    | state  | gtid_mode  | health  |
    +--------------------------+-------+---------+--------+------------+---------+
    | mysql01.labo.exitas      | 3306  | MASTER  | UP     | ON         | OK      |
    | mysql02.labo.exitas      | 3306  | SLAVE   | UP     | ON         | OK      |
    +--------------------------+-------+---------+--------+------------+---------+
    
  • Check GTID Status
    # mysqlrpladmin --master=admin:admin@mysql01.labo.exitas --discover-slaves-login=admin:admin gtid
    
    # Discovering slaves for master at mysql01.labo.exitas:3306
    # Discovering slave at mysql02.labo.exitas:3306
    # Found slave: mysql02.labo.exitas:3306
    # Checking privileges.
    #
    # UUIDS for all servers:
    +--------------------------+-------+---------+---------------------------------------+
    | host                     | port  | role    | uuid                                  |
    +--------------------------+-------+---------+---------------------------------------+
    | mysql01.labo.exitas      | 3306  | MASTER  | 9d8cc26b-ad2d-11e4-b175-005056b248f8  |
    | mysql02.labo.exitas      | 3306  | SLAVE   | 98cfd355-ad36-11e4-b1af-005056b21877  |
    +--------------------------+-------+---------+---------------------------------------+
    #
    # Transactions executed on the server:
    +--------------------------+-------+---------+--------------------------------------------+
    | host                     | port  | role    | gtid                                       |
    +--------------------------+-------+---------+--------------------------------------------+
    | mysql01.labo.exitas      | 3306  | MASTER  | 9d8cc26b-ad2d-11e4-b175-005056b248f8:1-18  |
    | mysql02.labo.exitas      | 3306  | SLAVE   | 98cfd355-ad36-11e4-b1af-005056b21877:1-3   |
    | mysql02.labo.exitas      | 3306  | SLAVE   | 9d8cc26b-ad2d-11e4-b175-005056b248f8:1-18  |
    +--------------------------+-------+---------+--------------------------------------------+
    
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s