In Case you missed these

Just in case you missed these posts....

Facebook has some interesting blogs on benchmarks, InnoDB and etc...

What is in the InnoDB buffer pool? 12/15/2011 02:14 PM

Dynamic padding for tpcc-mysql 11/21/2011 11:51 AM

The shutdown benchmark 11/15/2011 05:29 PM

The effect of page size on InnoDB compression 11/07/2011 11:40 AM

Bugs and Spam don't mix

A big thank you to the MySQL web team and engineers for helping combat spam and keeping the bugs.mysql.com site spam free.

reCaptcha is now installed and is a requirement when adding a comment to a current bug. We want the community to be able to review and comment on bugs with little spam as possible getting in the way.

MySQL Cluster to MySQL Server ... Part 2

I previously posted blog about the topic of a MySQL Cluster set up and replication to a MySQL Server. The first blog was just the installation, now lets test the cluster and get replication started. Both are extremely easy.

Some people might ask, “Why set up replication from a cluster? It is a HA system why replication?”

The ability to move data out of the cluster and allow others to do reporting, data exports, simple backups all can be done easily with it replicated into another MySQL server. Yes a Cluster can do all of those queries, but taking advantage of the replication to keep some users and queries out of your production database is rarely a bad thing.

Testing the Cluster Install. Of course more MySQL Documentation:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-quick.html


mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: ndbcluster
Support: YES
Comment: Clustered, fault-tolerant tables
Transactions: YES
XA: NO
Savepoints: NO

*************************** 3. row ***************************
Engine: ndbinfo
Support: YES
Comment: MySQL Cluster system information storage engine
Transactions: NO
XA: NO
Savepoints: NO

mysql> USE test;
Database changed
mysql>; CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.28 sec)

mysql> SHOW CREATE TABLE ctest \G
*************************** 1. row ***************************
Table: ctest
Create Table: CREATE TABLE `ctest` (
`i` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


That was easy enough...Table is built and using the ndbcluster engine.

So lets set up replication from a cluster to a MySQL Innodb Server.

1^st create a user for the replication.


GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'10.%' IDENTIFIED BY '<password>';
flush privileges;


For this example I am jut using a Windows Virtual Box instance. The MySQL windows installer worked great. For a guy who ever runs windows, it was nice that this was so easy. (http://dev.mysql.com/downloads/installer/)

On the Cluster server, I made sure to have my server_id and big logs set.


vi /etc/my.cnf
[mysqld]
server-id=1
log-bin = /var/lib/mysql/mysql_demo-bin


restarted the sql server and confirmed it was running:


mysql> show master status\G<
*************************** 1. row ***************************
File: mysql_demo-bin.000001
Position: 112 Binlog_Do_DB:
Binlog_Ignore_DB: 1
row in set (0.00 sec)


So lets create a simple table that we can use for test later.

PLEASE take note... This is just a table to show it replicated. If you create tables for real use in a Cluster and replicated ... ALWAYS have a primary key!


mysql> CREATE TABLE slave_test (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.28 sec)


Now back on my windows mysql server. Since it came with workbench I used this to set the server_id (server_id =2) and restart the server. I then opened the sql editor and added the master settings.


via_mysql_workbench> CHANGE MASTER TO
-> MASTER_HOST='10.132.241.18',
-> MASTER_PORT=3306,
-> MASTER_USER='slave_user',
-> MASTER_PASSWORD='<password>';

via_mysql_workbench> start slave;

via_mysql_workbench>use test;

via_mysql_workbench> SHOW CREATE TABLE slave_test ;

'slave_test', 'CREATE TABLE `slave_test` (

`i` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1'




mysql> show slave status;



...

Slave_IO_Running, Yes

Slave_SQL_Running Yes

...

So now via these blog posts.. I have a cluster with a geolocated node both on Oracle Linux 6  being replicated to another remote location MySQL server running Windows via a Virtual box instance.  I can also now use Workbench to connect to the local windows server as well as the cluster if needed. This is not a production system but it does show that Cluster can be installed easily, remotely, and takes advantage of commodity systems.

Part 1 -- The Install
Part 2 -- Testing Cluster more and Replication Setup

Congrats to three more MySQL Oracle ACE members...

It is truly amazing the amount of knowledge and talent that work with and support MySQL and the MySQL community. Thanks to all of you.

Over the last month the MySQL ACE list has grown even more:

George J. Trujillo is now an Oracle ACE and a MySQL Oracle ACE.

Wagner Bianchi  became a MySQL Oracle ACE not long ago.

Roland Bouman is the latest addition as a MySQL Oracle ACE.

Congrats to all of them !

MySQL Cluster to MySQL Server Part 1

Recently I was working on a MySQL Cluster that is replicated to a MySQL server. I will create a few blog posts to show how all of this can be done. This is of course not the only way to do this.. Any feedback or other options are welcomed in comments for the community to use.

Part one: the install

First the MySQL Documentation about MySQL Cluster is very good I will make references to it for your convenience.
You can go about this a few different ways but this is a simple RPM install:

Download all the related rpms from http://dev.mysql.com/downloads/cluster/ and install.

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-linux-rpm.html) is here to help you with the installs as well.

    rpm -ihv MySQL-Cluster-gpl-*.rpm


Ok that was easy enough ... Make sure we have what we expected...

rpm -qa | grep MySQL

MySQL-Cluster-gpl-debuginfo-7.1.17-1.el6.i686
MySQL-Cluster-gpl-client-7.1.17-1.el6.i686
MySQL-Cluster-gpl-clusterj-7.1.17-1.el6.i686
MySQL-Cluster-gpl-test-7.1.17-1.el6.i686
MySQL-Cluster-gpl-server-7.1.17-1.el6.i686
MySQL-Cluster-gpl-devel-7.1.17-1.el6.i686
MySQL-Cluster-gpl-management-7.1.17-1.el6.i686
MySQL-Cluster-gpl-extra-7.1.17-1.el6.i686
MySQL-Cluster-gpl-storage-7.1.17-1.el6.i686


Just so this can be a tiny bit of a more real world example, I also installed an external node in a remote (out of the subnet/datacenter) location.


[root@remote_cluster src]# rpm -ihv MySQL-Cluster-gpl-storage-*.rpm


OK software install is straight forward and easy, but what do you do now ?

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-configuration.html) on how to configure the cluster are straight forward and very easy to follow.

Based on that documentation, I have set all the ips in all the related configuration files ...

My Config file looks like this after removal of comments. I have a local node and a remote node for this simple example.

#/var/lib/mysql-cluster/config.ini
[ndb_mgmd]
# Management process options:
hostname=10.132.241.18         # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node log files
NodeId=1

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=4                         # Number of replicas
datadir=/var/lib/mysql-cluster # Directory for MGM node log files
DataMemory=80M                # How much memory to allocate for data storage
IndexMemory=18M               # How much memory to allocate for index storage

[ndbd]
hostname=mysql_demo.localdomain
NodeId=3

[ndbd]
hostname=10.159.37.130
NodeId=4

[mysqld]
Nodeid=10

[mysqld]
Nodeid=30

[mysqld]
Nodeid=40



Nodes... Start your engines..... ok so really, lets get it started...


MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-first-start.html) on how to start it up for the 1st time..



On Localhost

/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.56 ndb-7.1.17


On Localhost / Data Node

/usr/sbin/ndbd
2011-12-08 12:21:16 [ndbd] INFO -- Angel connected to 'localhost:1186'
2011-12-08 12:21:16 [ndbd] INFO -- Angel allocated nodeid: 3



On remote node :

./ndbd
2011-12-08 12:25:39 [ndbd] INFO -- Angel connected to '10.132.241.18:1186'
2011-12-08 12:25:40 [ndbd] INFO -- Angel allocated nodeid: 6


On Localhost Start the sql server and check the nodes.

# /etc/init.d/mysql start

# /usr/local/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration

---------------------
[ndbd(NDB)] 4 node(s)
id=3 @127.0.0.1 (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=4 (not connected, accepting connect from mysql_demo.localdomain)
id=5 @10.159.37.130 (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=6 @10.159.37.130 (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.132.241.18 (mysql-5.1.56 ndb-7.1.17)

[mysqld(API)] 3 node(s)
id=10 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)


OK lets log in and test it ?

# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-ndb-7.1.17-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select VERSION();
+-------------------------------+
| VERSION() |
+-------------------------------+
| 5.1.56-ndb-7.1.17-cluster-gpl |
+-------------------------------+
1 row in set (0.00 sec)



Of course if you need to stop and restart (MySQL Documentation:  http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-install-shutdown-restart.html) a cluster, it is a simple process.


/usr/local/bin/ndb_mgm -e shutdown
/etc/init.d/mysql stop



Others posts on cluster installs:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-installation.html

http://alexyu.se/comment/37

http://dbperf.wordpress.com/2011/03/04/mysql-cluster-setup-and-replication-between-ndb-and-non-ndb-engines-2/

Mine:
Part 1 -- The Install


Part 2 -- Testing Cluster more and Replication Setup