Saturday, September 29, 2012

MySQL Connect 2012 Table of Contents

In case you have missed any of the recent blog posts about MySQL 5.6 and MySQL 7.3 from MySQL Connect.

MySQL Connect 2012 Table of Contents:
Keep in mind for next year: Conference Tips

Friday, September 14, 2012

MySQL Connect Schedule

So the MySQL Connect Conference is just around the corner. For those of you that do not get the newsletter:

The Oracle INFORMATION INDEPTH NEWSLETTER MySQL Edition was recently sent out. Take advantage of this newsletter because it highlights the schedule nicely with links into the abstracts. It also lists the receptions, demo pods.


http://www.oracle.com/us/corporate/newsletter/samples/mysql-1384701.html

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.


Tuesday, September 11, 2012

Chinese Planet

A big welcome to the China MySQL Community.  We have just launched the Chinese MySQL Planet.  Zhaoyang Jian (an Oracle Ace Director) has started us off with his blogs so please feel free to submit your Chinese Feeds.

Sunday, September 9, 2012

SCALE 11X CFP

The Southern California Linux Expo ( SCALE ) call for papers is now open.

They had a great MySQL day last year and with the help of the MySQL Community they can have another one this year ! The event will be held Februrary 22-24, 2013 at the Hilton Los Angeles Airport Hotel.


Wednesday, September 5, 2012

MySQL Tech Tours

The MySQL Tech tours are back! The postponed Denver Tech Tour has been rescheduled and we have added another in Austin. See the links before for more information. 

MySQL Tech Tours

Improving Your Products with New MySQL Features and Faster Performance. 

September 20, 2012 - Denver Area
Learn more and register for the event »

September 25, 2012 - Austin
Learn more and register for the event »