# MySQL Cluster on FreeBSD



## vand777 (Jun 3, 2011)

Hi

Has anyone installed and successfully used MySQL Cluster on FreeBSD? I tried to google the topic but didn't find any articles about it, guides, opinions? 

Is it possible? If yes, do you have a link to a good guide how to properly configure it on FreeBSD?

Thanks in advance!


----------



## DungeonMaster3000 (Jun 3, 2011)

I haven't but a combination of these two should articles should be able to get you started.

http://dev.mysql.com/doc/refman/5.5/en/freebsd-installation.html
http://www.howtoforge.com/mysql5_master_master_replication_debian_etch

Good luck.
Tarran


----------



## vand777 (Jun 3, 2011)

Thank you. I saw these links and few more.

I've heard that MySQL Cluster technology was not stable on any problem in the past and it is not officially supported on FreeBSD. I could not find out the latest opinions when googling. It looks like nobody is using it.

Is it safer for me to look at multi-master replication instead?


----------



## vand777 (Jun 3, 2011)

In my first post I've been talking about this product:
http://dev.mysql.com/downloads/cluster/


----------



## DungeonMaster3000 (Jun 3, 2011)

Ok then - this is just an idea, not sure of the implications and would be in a master/slave relationship. But what about a mixture of hast + carp + mysql. When falls over to the secondary server amend to failback script to start mysql also. Just an idea if you can't get the clustered version working.


----------



## vand777 (Jun 4, 2011)

Thanks. I'm thinking more about master-master replication now.I need load balancing and fail-over tolerance. Both at the same time.

P.S. Found interesting article. A little bit old but very good for newbies in replication (like me) imho.

http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html


----------



## meroverance (Sep 23, 2011)

I was able to get mysql cluster 7.1 to work with FreeBSD and I listed the steps below.  I also saw a few weird things with it though.  I never could get the response times down on some of the query times, granted they were in milliseconds but they add up in a production environment compared to the cached times running the MyISAM/local engine.  I did really like the geosynchronous replication and how all the mysql nodes were able to respond was very nice and helpful.  

There are 3 nodes or individual pieces that are needed to have mysql cluster work.  They are master node, data node, and mysql node.  Though all 3 pieces can work on the same box, they suggest that the data node and master node not be on the same physical machine.  The data nodes will take a lot of memory to run your normal sized database and yes you can store some of the slower data on the hard disk now.  You only need 1 master node to run but if it shuts down, no new node connections can be made to the database but the cluster will still continue to function.  I went ahead and had 2 master nodes to keep the extra redundancy.  For the data nodes, you can run on 1, but it is NOT recommended.  You'll need at least 2 and above that, they will need to be in pairs (2,4,8,...).  This is because with 2 data nodes, you'll have 2 separate copies of the database, with 4 nodes you'll still have 2 separate copies but the databases will be split between the nodes to help increase your speed.  Using the config.ini file, you can increase the number of database copies but I'm not going to cover that in this post.

Installing mysql cluster 7.1 on FreeBSD

Until they port mysql cluster into FreeBSD, this is how I learned to install mysql cluster 7.1.  If anyone finds a better way, please let me know.

1. Download the tar.gz file from mysql.com for mysql cluster 7.1 source code thatâ€™s architecture-independent.

2. While you have that downloading and I know it's weird, install mysql 5.1 server so that way it will set up all the user and base information for your new cluster. (*cd /usr/ports/databases/mysql51-server && make install clean*)

3. Once the install is completed, make a copy of the /usr/local/etc/rc.d/mysql-server to your home directory (you'll need it later). 

4. Uninstall mysql 5.1 server and mysql 5.1 client (*make deinstall*)

5. By now the tar.gz file should be downloaded, and place it into your production directory.  I used /usr/mysql/mysql_cluster

6. *tar xzf mysql-cluster-gpl-7.1.13.tar.gz* to untar the file.

7. Make sure you're in the new mysql directory and run this commmand to start the setup of mysql cluster.
`[FILE]./configure --with-plugins=all --with-system-type=freebsd --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static[/FILE]`

8. Now do a *make install clean*

9. Make a another directory for the data storage files.  I used /data/mysql_storage.  This is for storing the log files and extra files mysql needs to keep running after reboot.  You can also store this in /var/db/mysql/storage if you like.  Just make sure your directory is chmod and chown to the mysql user and that user has full rights to the directory.  

Do this setup on all of the different nodes you have for your set up.

Setting up the /usr/local/etc/my.cnf and /usr/local/etc/config.ini

Instead of going through all the options for these 2 files, use this configurator to help you make your own files.
http://www.severalnines.com/config/

The config.ini is for the master node only and the my.cnf is for all 3 nodes but each node will have slightly different my.cnf files.

Master Node

Once you have the configs setup, run */usr/local/libexec/ndb_mgmd -f /usr/local/etc/config.ini* to start the master node up.

WARNING: The master node must be the first one running!

That's it, the master node is running and to access it use */usr/local/libexec/ndb_mgm*

You don't have to use the full path, but I did it to show you the location of each of them.

With ndb_mgm, you control your new cluster.  It shows you how many servers can connect, who is allowed to connect, and who it running.  It also lets you do a rolling restart and shutdown the node from the group safely.

Data Node

Make sure you have the config files set up on the data node and then run one of these commands. The ndbmtd is for multithreaded application of the data node.  I tried both of them and I did see a slight increase in performance. using the ndbmtd with 2 data nodes.

#Single threaded
`/usr/local/libexec/ndbd â€“initial`

#Multithreaded
`/usr/local/libexec/ndbmtd â€“initial`

Wait for the master to have each of the nodes start up and running before you can do anything else.

WARNING: These servers that are running the data node will be loaded up on memory and IO, so I wouldn't recommend using them for anything else besides this.  Just give them as much memory as you can spare.

Mysql Node

After you have the data nodes up and running on the master node and the config files are loaded.  Copy the /usr/local/etc/rc.d/mysql-server back to this location.  Double check the mysql directories all have the correct permissions and start the demon. 
`/usr/local/etc/rc.d/mysql-server start`

You should see the new mysql node show up on your master node and then you know you're up and running.  Rinse and repeat as necessary.  

Set up a new database as usual, and when you set up a table, make sure to specify the engine as NDBCLUSTER.  On the other mysql nodes that need to access the same database, just create the same database name and the tables will populate.

WARNING: The grant permissions are still separate on the cluster so you will have to do your grant commands on each of the mysql nodes.


----------



## meroverance (Jan 12, 2012)

For those of you who want to test the new version of cluster, like me.  I have a successful setup of MySQL 7.2.  I like the setup compared to the 7.1 version.

*Installing mysql cluster 7.2 on FreeBSD*

 `# cd /usr/ports/devel/cmake && make install clean`
 Download 7.2 generic source files from mysql.com that is architecture-independent.  
 Place the tar file in /usr/local
 `# tar â€“zxvf filename` to untar the file (the version Iâ€™m using is  mysql-cluster-gpl-7.2.2)
 `# cd /usr/local/mysql-cluster-gpl-7.2.2`
 The following command will do all the configuration of the mysql and these are the settings that worked for me.  if you want to change the different locations like your data dir, make sure to change them here.  Also, here's where I found all the settings for cmake http://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html
`# cmake -DBUILD_CONFIG=mysql_release -DMYSQL_DATADIR=/data/db/mysql -DINSTALL_MANDIR=/usr/local/man -DINSTALL_SBINDIR=/usr/local/libexec -DINSTALL_MYSQLSHAREDIR=/usr/local/share/mysql -DINSTALL_SCRIPTDIR=/usr/local/bin -DINSTALL_LIBDIR=/usr/local/lib/mysql -DINSTALL_INCLUDEDIR=/usr/local/include/mysql -DINSTALL_BINDIR=/usr/local/bin .`

 `# make install`
For the configuration files, my.cnf and config.ini, I used severalnines.com config at http://www.severalnines.com/cluster-configurator/ 

*Master Node*

 Make sure your settings are complete and at /usr/local/etc/config.ini
 `# ndb_mgmd -f /usr/local/etc/config.ini`
 Add 
	
	



```
ndb_mgmd -f /usr/local/etc/config.ini
```
 to /etc/rc.conf
You now have a master node up and running of your cluster.  Make sure the master node is the first one up as well. The master node only handles added a new node to the cluster or being an arbitrator unless you specify one. Other than that, the master node is very low overhead and no data flows throw it.

To Reload the config file in the Master Node

 `# ndb_mgm`  	(to access the cluster manager)
 `# shutdown`  	(to stop the entire cluster)
 `# exit` 		( to exit the cluster manager)
 make your changes to /usr/local/etc/config.ini
 `# ndb_mgmd --reload -f /usr/local/etc/config.ini`
*Data Node*

MySQL recommends that the Data Node runs on it's own server.  You can run the Data node on a regular processing server, but the memory usage and performance degradation of the cluster is usually not worth it.
To finish the setup for the Data Node

 Make your cluster file storage for my cluster.  The commands for my example are below.
`# mkdir /usr/local/mysql/mysql-cluster/`
`# mkdir /usr/local/mysql/mysql-cluster/backup`
 Make your data directory for the storage of your cluster data.  MySQL recommends this being on it's own hard drives to be as quick as possible.  The command for my example are below.
`# mkdir /data/mysql`
 Copy over your my.cnf file and `# chmod 700 /usr/local/etc/my.cnf`.  All the my.cnf is the connect string for your master/masters nodes.  Again, http://www.severalnines.com/cluster-configurator/ is where I configured mine.
 `# /usr/local/bin/ndbmtd --defaults-file=/usr/local/etc/my.cnf --initial` (To start for the first time)
 Add to the /etc/rc.conf

```
/usr/local/bin/ndbmtd --defaults-file=/usr/local/etc/my.cnf
```

Now you should be able to log into your `# ndb_mgm` on the Master Node and see your Data Nodes starting.  By default, the Data Nodes will not finish the start up process without the other Data Nodes either on and joined to the cluster or in the start up process themselves.  

*MySQL Node*

This is the node that accesses the cluster and the MySQL node can also be one of your Master nodes too.  This is the last node to join the cluster and cannot join till the others have finished their start ups.  

 Add to /etc/rc.conf

```
mysql_enable="YES"
```

 In /usr/local/bin/mysql_install_db, replace â€œ/usr/local/mysql/â€ with â€œ/â€
 Copy in your /usr/local/etc/my.cnf and `# chmod 700 /usr/local/etc/my.cnf`
 `# ln -s /usr /usr/local/mysql/usr`
 `# cp /usr/local/share/mysql/english/errmsg.sys /usr/local/mysql/usr/local/share/mysql/`
 I've included my /usr/local/etc/rc.d/mysql-server because I had to make a few changes to the usual one that is built without the cluster. http://pastie.org/3175260 and copy it to /usr/local/etc/rc.d/mysql-cluster
 `# /usr/local/etc/rc.d/mysql-server start`


----------

