Ubuntu – How to set up Mysql Cluster

MySQLndbserver

I am trying to setup mysql-cluster-gpl-7.1.17-linux-i686-glibc23.tar.gz on ubuntu 10.04 server for the past 3 weeks and not successful yet.

The links I followed are:

http://stepbysteparticles.com/index.php?option=com_content&view=article&id=48&Itemid=54

http://cyrenity.wordpress.com/2010/08/12/howto-setup-mysql-cluster-beginners-tutorial/

http://tech.jocke.no/2011/03/13/mysql-cluster-how-to-with-load-balancing-failover/

http://www.lod.com/whitepapers/mysql-cluster-howto.html

http://blog.rimuhosting.com/2011/07/06/building-database-clusters-with-mysql/

All the configurations in the above links are almost same with some slight changes and I just gave a try of all if any works but none.

I already made a post in serverfault but I am still could not able to make it work.

I always come back here if nothing works out after many tries and now I am.

Also I am not understanding whether mysql-server or mysql-client need to be installed along with mysql-cluster else mysql-cluster alone is enough for running mysqld on SQL nodes. In my case the setup is 3 nodes=(data1+sql1 + data2+sql2 + mgmt node).

Frequent errors:

/etc/init.d/mysql.server start

Output:

Starting MySQL
 * Couldn't find MySQL manager (/usr/bin/mysqlmanager) or server (/usr/bin/mysqld_safe)

I even tried copying /usr/local/mysql/bin/* to /usr/bin but still no use as it was telling . * Manager of pid-file quit without updating file. while trying to start!

While trying to start mysql daemon on data/sql node

cd /usr/local/mysql

./bin/mysqld_safe –user=mysql –defaults-file=/etc/my.cnf &

Output:

[1] 2338
root@web2:/usr/local/mysql# 111208 15:53:47 mysqld_safe Logging to syslog.
111208 15:53:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
111208 15:53:47 mysqld_safe mysqld from pid file /var/lib/mysql/web2.pid ended

[1]+  Done                    ./bin/mysqld_safe --user=mysql --defaults-file=/etc/my.cnf

Anybody take up and help me here please?. Anybody clarify if there is any problem with ubuntu or with cluster config?. I have to implement this on cloud servers once I am successful.

Thank you!

Update:1

Now on SQL/Data nodes I have had already removed mysql-server and mysql-client which I generally install as a basic package of any fresh installation. I removed them by running following command and on both of the nodes only mysql-cluster is available which I could not able to setup:

apt-get remove mysql-client mysql-client-5.1 mysql-client-core-5.1
mysql-common mysql-server mysql-server-5.1 mysql-server-core-5.1

Update:2

I have fixed the issues I had. Now I have the following things left to be done.

  1. What is the best method to Add/Remove mgmt, SQL/DATA nodes being online?

    EDIT: I tried mysql docs for adding nodes online but confused and didn't work unless I shutdown all the nodes and remove the logs under /var/lib/mysql-cluster on all the nodes.

  2. What additional configurations should I include in my my.conf file for best performance tuning like DataMemory and IndexMemory, if anybody using for production, can recommend or paste it here please?

Update:3

  1. How to convert all the table engines of the database to NDB which are having foreign keys also?

    EDIT: I found a script for converting all the table engines but all the tables of my database are not converted as for the error: ERROR 1217 (23000) at line 11: Cannot delete or update a parent row: a foreign key constraint fails

  2. Is there a way to possess both the features of NDB & InnoDB with cluster and multi-master replication with no risk(down time)?

Best Answer

Oh.., after multiple tries I got it working myself and it has taken upto now. The links are very nice tutorials and I could fix the issues I had this way.

Also I am not understanding whether mysql-server or mysql-client need to be installed along with mysql-cluster else mysql-cluster alone is enough for running mysqld on SQL nodes.

A: I am not sure if mysql-cluster works along mysql-server and mysql-client which are already installed from apt but I am sure that those or any extra package are NOT required for mysql-cluster on Ubuntu as it itself has all the packages. So I can run mysqld from cluster extracted package and nothing required for this to install additionally for SQL nodes.

./bin/mysqld_safe --user=mysql --defaults-file=/etc/my.cnf &

A: No need to run mysqld manually and instead can use /etc/init.d/mysql.server start copying from /usr/local/mysql/support-files/mysql.serverto start mysqld/SQL node.

Starting MySQL * Couldn't find MySQL manager (/usr/bin/mysqlmanager) or server (/usr/bin/mysqld_safe)

A: The above was the error encountered while trying to start mysql.server and that was because I had mysql-server and mysql-client already installed. Those should be completely removed(I'm not sure if can run cluster successfully along with them) and I did. Those were not completely removed(probably I have to use apt --purge remove). Check with dpkg --get-selections | grep mysql, if you find deinstall then those are not completely uninstalled, which should be, else one will have to face above error. Executing below command completely removed them and I could start mysql.server with no errors.

aptitude purge $(dpkg --get-selections | grep deinstall | sed s/deinstall//) #To completely remove all deinstall package
pkill -9 mysqld
/etc/init.d/mysql.server

............ * Manager of pid-file quit without updating file.

A: I faced the above error many times in my testing while trying to start mysql.server. I could avoid this by simply granting permission on data to mysql user like chown -R mysql:mysql /usr/local/mysql/data, this mistake was because I was trying extracting mysql-cluster newly number of times and thus the error as the user mysql could not create pid file under data directory for no permission and error log under /var/lib/mysql-cluster on sql/data node helped me trace this.

Should be very specific in assigning to /usr/local/mysql/data, if you just do chown -R mysql:mysql /usr/local/mysql and keep quiet, it won't work and same error is thrown!

After granting permission , check mysqld is working and running by issuing /usr/local/mysql/bin/mysql -uroot -p. Even after entering password you cannot connect and throws error, then mysql root password is not set or you forgot. Use below command to reset mysql root password, and then try connecting again:

/usr/local/mysql/bin/mysqladmin -uroot password your-new-password-here

Neither I am satisfied with the answer nor I am willing to accept and award myself as I have many more to do in this clustering. I am willing to update my question with more queries and await to award points to other who is going to answer. UPDATED QUESTION!

Thank you!

Update

We thought of using MySQL Cluster for production web servers where apache-tomcat serving some static and jsp pages but because of slow joins, no support for foreign keys and so on... issues which are crucial in our environment and works fine in Innodb engine than NDB, I am ending not using mysql cluster and looking back to replication again though it has sync issues. Thank you!

Related Question