Thursday, August 30, 2012

PyCON

PyCONs are in full swing !

Oracle will be at PyCON India with two MySQL Sessions:

Oracle will also have a booth  so please come by !

PyCON 2013 in Santa Clara will be March 15th - 17th.
    Tutorials: March 13–14
    Main Conference: March 15–17
    Development Sprints: March 18–21

The CFP is open so join Oracle and lets show support for MySQL and the Python community.

Monday, August 20, 2012

NoSQL & InnoDB Set up for MySQL Replication

Last week I heard about a company that has a concern with their NoSQL solution and the replication of their data. I, of course, thought of the new MySQL 5.6 release as a possible solution for their problem.

You can combine all your data across the same nodes and replicate it very easily.  All the while the data is  accessible via SQL and NOSQL. MySQL Cluster proved this in MySQL Cluster 7.2 already, so now it will also be available for MySQL 5.6.

Some great blogs and documentation already exist on this topic but they are a little older:
http://blogs.innodb.com/wp/2011/04/get-started-with-innodb-memcached-daemon-plugin/
http://blogs.innodb.com/wp/2011/04/nosql-to-innodb-with-memcached/
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-setup.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-replication.html

This feature can be tested now via the mysql-5.6.6 linux download -- mysql-5.6.6-m9-linux2.6-x86_64.tar.gz. Below is my quick example on how to set this all up. 

Get the release:

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.6-m9-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/
tar -vxzf mysql-5.6.6-m9-linux2.6-x86_64.tar.gz
cd mysql-5.6.6-m9-linux2.6-x86_64

# if you want more info
more INSTALL-BINARY
 ./scripts/mysql_install_db --user=mysql

/etc/init.d/mysql start


Make sure your system is set up for memcached.  Libevent is required for memcached.  I have the following for example:

# rpm -qa | grep libevent
libevent-headers-1.4.13-4.el6.noarch
libevent-1.4.13-4.el6.x86_64
libevent-devel-1.4.13-4.el6.x86_64
libevent-doc-1.4.13-4.el6.noarch


You will need to also keep in mind that your SELINUX could deny access during a plugin install:

 mysql> install plugin daemon_memcached soname 'libmemcached.so';
ERROR 1126 (HY000): Can't open shared library '/var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin/libmemcached.so' (errno: 2 /var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin/libmemcached.so: cannot open shared object file: Permission denied)


You can  solve this a few ways, and it is up to you how you handle SELINUX.

First edit the my.cnf file and add the location of your plugins. I also added a server_id since I will be using replication later.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
plugin_dir=/var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin
innodb_api_enable_binlog=1

server_id = 1


Confirm this :

mysql> select @@plugin_dir;
+-----------------------------------------------------+
| @@plugin_dir                                        |
+-----------------------------------------------------+
| /var/lib/mysql-5.6.6-m9-linux2.6-x86_64/lib/plugin/ |
+-----------------------------------------------------+
1 row in set (0.00 sec)


Install the configuration files:

cd mysql-5.6.6-m9-linux2.6-x86_64/lib/scripts/
mysql -p < innodb_memcached_config.sql


Now you are able to install the plugin:

mysql> install plugin daemon_memcached soname 'libmemcached.so';



So...  lets test this all out..

We are going to test with the demo_test table in the test database. This is set via the  innodb_memcached_config.sql file referenced earlier. 


First SQL:

# mysql -p
Server version: 5.6.6-m9-log MySQL Community Server (GPL)
mysql> use test
mysql> truncate demo_test;
mysql> INSERT INTO demo_test VALUES ('AA','TEST VIA SQL',1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from demo_test;
+----+--------------+------+------+------+
| c1 | c2           | c3   | c4   | c5   |
+----+--------------+------+------+------+
| AA | TEST VIA SQL |    1 |    1 |    1 |
+----+--------------+------+------+------+
1 row in set (0.01 sec)


Now via memcached:

# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set memc 10 0 9
memcached
STORED
get memc
VALUE memc 10 9
memcached
END

mysql> select * from demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | memcached    |   10 |    5 |    0 |
+------+--------------+------+------+------+
2 rows in set (0.00 sec)


Jimmy's blog post goes into more details on the table and etc.if your curious about the table structures.

If you add replication to this architecture you will now have a NOSQL & MySQL replicated system. Below is a quick example showing this...

This is our data at the start :

MySQL_Master >select * from demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | memcached    |   10 |    5 |    0 |
+------+--------------+------+------+------+


Access memcached and update the memc key/values

# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set memc 12 0 10
replicated
STORED
get memc
VALUE memc 12 10
replicated
END


We can see this change on the master.

MySQL_Master >select * from demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | replicated   |   12 |    2 |    0 |
+------+--------------+------+------+------+


Now lets check the slave....

MySQL Slave > select * from demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | memcached    |   10 |    5 |    0 |
+------+--------------+------+------+------+


Ouch a problem!
Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.' 
I normally would have been in Mixed or Row based but this was a new virtual image for this example. So this is a simple quick adjustment.

MySQL Slave > SET GLOBAL binlog_format = 'MIXED';


After a stop and start of slave.....


MySQL Slave > select * from demo_test;
+------+--------------+------+------+------+
| c1   | c2           | c3   | c4   | c5   |
+------+--------------+------+------+------+
| AA   | TEST VIA SQL |    1 |    1 |    1 |
| memc | replicated   |   12 |    2 |    0 |
+------+--------------+------+------+------+
2 rows in set (0.00 sec)



With MySQL 5.6 you get NoSQL, SQL, and replication. This allows you to take advantage of your data and not have to store it via separate data systems.

Learn more about MySQL and InnoDB and our NoSQL options at MySQL Connect.

CON8815 - A Journey into NoSQLand: MySQL’s NoSQL Implementation
    Lig Turmelle - Web Database Administrator, Kaplan Professional

CON9343 - Developing High-Throughput Services with NoSQL APIs to InnoDB and MySQL Cluster  
  Andrew Morgan - MySQL Senior Product Manager, Oracle    
  John Duncan - Software Engineer, MySQL, Oracle


Saturday, August 11, 2012

Ohio LinuxFest 2012

 I will be presenting MySQL Replication with the MySQL 5.6 features at the Ohio LinuxFest 2012 Friday September 28th. I will only be attending the Friday sessions because I will be flying off to MySQL Connect as well. I look forward to the both of these events. It will be a great opportunity to meet with the MySQL Community.

Friday, August 10, 2012

Workbench Database Migration Wizard

The linux command line is a familiar place for many MySQL DBAs. While we are comfortable with the command line, MySQL Workbench is still worth a look. The latest release at the time of this posting is 5.2.41 .

Workbench development has been rapid as of late. The MySQL Utilities are a series of customizable Python scripts and they will allow you to really take advantage of the new MySQL 5.6 upcoming features.  They come with MySQL Workbench or available via Launchpad. They have been getting a lot of attention lately because of the great features they offer.

MySQL Workbench now also offers a Data Migration Wizard. I got some time to test this new feature so I have included a simple example below.



This new feature provides the ability to migrate ODBC compliant databases to MySQL.



 You tools allows you to select your source and target databases.






Once your database connections are tested and active it will fetch the schemata list. You are then allowed to select which schema you would like to migrate.




The tools gives you a few serious of creation options as well if needed. This includes manual editing and target creation options. You can create creation scripts and etc as well. Once your ready you can process the transfer.


Once completed you get a report of all the migration work that was done.




This is a simple and quick blog post but I wanted that to convey how easy it use this tool for migration .