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.