#####################################################################
################# 1. Инсталиране на NODE ###########################
#####################################################################
groupadd mysql;
useradd -g mysql mysql;
cd /var/tmp/;
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/mysql-cluster-gpl-7.1.15-linux-i686-glibc23.tar.gz/from/http://mysql.mirrors.ovh.net/ftp.mysql.com/ -O mysql-cluster-gpl-7.1.15-linux-i686-glibc23.tar.gz;
tar -C /usr/local -xzvf mysql-cluster-gpl-7.1.15-linux-i686-glibc23.tar.gz;
ln -s /usr/local/mysql-cluster-gpl-7.1.15-linux-i686-glibc23/ /usr/local/mysql;
export PATH=$PATH:/usr/local/mysql/bin;
echo "export PATH=\$PATH:/usr/local/mysql/bin" >> /etc/bash.bashrc;
cd /usr/local/mysql;
./scripts/mysql_install_db --user=mysql;
chown -R root .;
chown -R mysql data;
chgrp -R mysql .;
cp support-files/mysql.server /etc/init.d/mysql;
chmod +x /etc/init.d/mysql;
update-rc.d mysql defaults;
########################################################################
################# 2. Инсталиране на MANAGER ###########################
########################################################################
cd /var/tmp/;
wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/mysql-cluster-gpl-7.1.15-linux-i686-glibc23.tar.gz/from/http://mysql.mirrors.ovh.net/ftp.mysql.com/ -O mysql-cluster-gpl-7.1.15-linux-i686-glibc23.tar.gz
tar zxvf mysql-cluster-gpl-7.1.15-linux-i686-glibc23.tar.gz;
cp -Rvp /var/tmp/mysql-cluster-gpl-7.1.15-linux-i686-glibc23/bin/ndb_mgm* /usr/local/bin;
chmod +x /usr/local/bin/ndb_mgm*;
##################################№№######################################
################# 3. Конфигуриране на MANAGER ###########################
####################################№№####################################
mkdir /var/lib/mysql-cluster;
vi /var/lib/mysql-cluster/config.ini;
[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M
[tcp default]
[ndb_mgmd]
hostname=192.193.194.2 # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node log files
[ndbd]
hostname=192.193.194.3 # Hostname or IP address
datadir=/usr/local/mysql/data # Directory for this data node’s data files
[ndbd]
hostname=192.193.194.4 # Hostname or IP address
datadir=/usr/local/mysql/data # Directory for this data node’s data files
[mysqld]
hostname=192.193.194.3 # Hostname or IP address
[mysqld]
hostname=192.193.194.4 # Hostname or IP address
##################################№№######################################
################# 4. Конфигуриране на NODE ###########################
####################################№№####################################
mkdir /var/run/mysqld/
chown -R mysql.mysql /var/run/mysqld/
vi /etc/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
skip-locking
ndbcluster # run NDB storage engine
ndb-connectstring=192.193.194.2 # location of management server
#general_log_file = /var/log/mysql.log
#general_log = 1
[mysql_cluster]
ndb-connectstring=192.193.194.2 # location of management server
touch /var/log/mysql.log;chown -R mysql.adm mysql.log;
#За нова инсталация на mysql
mysqladmin -uroot password mysqlpass
#За стара инсталация на MySQL
#mysqladmin -u root -p'oldpassword' password newpass
##################################№№###########################################
################# 5. Стартиране на MySQL Cluster ###########################
####################################№№#########################################
5.1. Стартира се на MySQL Manager
ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster
5.2. Стартира се на всеки MySQL Node
/usr/local/mysql/bin/ndbd;
/etc/init.d/mysql start;
##################################№№###########################################
################# 6. Тестване на MySQL Cluster ###########################
####################################№№#########################################
mysql -uroot
create database testdb;
use testdb;
CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;
INSERT INTO cluster_test (i) VALUES (1);
SELECT * FROM cluster_test;
##################################№№###########################################
################# 7. Инсталиране на MySQL Balancer ###########################
####################################№№#########################################
7.1. На всеки Node
mysql>
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'remoterootamri1proxypass' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Тестване
mysql -uroot -h192.193.194.5 -premoterootamri1proxypass
7.2. На всеки Load balancer
## Вариант -> mysql-proxy
apt-get install mysql-proxy
vim /etc/default/mysql-proxy
ENABLED="true"
OPTIONS="
--proxy-backend-addresses=192.193.194.3:3306 \
--proxy-backend-addresses=192.193.194.4:3306 \
--daemon \
--admin-username=mysqlproxyadmin \
--admin-password=mysqlproxypass1
"
## Вариант -> haproxy
apt-get install haproxy
/etc/haproxy/haproxy.conf
global
log 127.0.0.1 daemon debug
stats socket /tmp/stats
maxconn 4096
pidfile /var/run/haproxy.pid
daemon
defaults
log global
mode tcp
option dontlognull retries 3 option redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000
listen MySQL 192.193.194.2:4040
mode tcp
option httpchk
#stats enable
#stats uri /haproxy-stats
balance roundrobin
#server mysql_master 192.193.194.3:3306
#server mysql_slave 192.193.194.4:3306
server ns1 192.193.194.3:3306 weight 10
server mail 192.193.194.4:3306 weight 10
listen stats 192.193.194.2:8000
mode http
option httpclose
balance roundrobin
stats uri /haproxy
stats realm Haproxy\ Statistics
stats auth user:password
7.3. Тестване на mysql-proxy
mysql -uroot -h127.0.0.1 -P 4040 -premoterootamri1proxypass
mysql -uroot -h192.193.194.2 -P 4040 -premoterootamri1proxypass
mysql -usocial_network -h192.193.194.2 -P 4040 -pmysqlpassword
##################################№№###########################################
################# 8. Грешки при инсталиране ###########################
####################################№№#########################################
При инсталиране на NODE
# ./scripts/mysql_install_db --user=mysql;
FATAL ERROR: Could not find mysqld
The following directories were searched:
/usr/libexec
/usr/sbin
/usr/bin
If you compiled from source, you need to run 'make install' to
copy the software into the correct location ready for operation.
If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.
Решение: rm -fr /etc/mysql
-----------------
/var/lib/mysql-cluster/ndb_1_cluster.log
2011-07-19 12:18:51 [MgmtSrvr] WARNING --- 1011 Unable to connect with connect string: nodeid=0,localhost:1186
2011-07-19 12:18:51 [MgmtSrvr] ERROR --- Failed to connect to ourself!
Решение:
Във файл /etc/hosts се добавя следния ред:
127.0.0.1 localhost
---------------------
mysql> create database testdb;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock |
+-------+------+---------------------------------------------------------------------------------+
ИЛИ
mysql> CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;
ERROR 157 (HY000): Could not connect to storage engine
Решение: да се рестартират услугите ndbd и mysql и да се изчакат няколко секунди за да се синхронизират услугите
----------------------------------
mysql> select * from a10_articles limit 1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> show warnings;
+-------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB |
| Error | 1296 | Got error 157 'Unknown error code' from NDBCLUSTER |
| Error | 1033 | Incorrect information in file: './social_network/a10_articles.frm' |
+-------+------+--------------------------------------------------------------------+
3 rows in set (0.00 sec)
Проблема е, че е стартиран ndbd без mysql
----------------------------------
ns1:/tmp# mysql -uroot social_network < social_network_filled.sql
ERROR 1005 (HY000) at line 1188: Can't create table 'social_network.a13_articles' (errno: 136)
В ndb_1_cluster.log лога се вижда следното
2011-07-19 13:14:24 [MgmtSrvr] INFO --- Node 2: index-build table 218 index: 225 processed 0 rows
2011-07-19 13:14:24 [MgmtSrvr] INFO --- Node 3: index-build table 218 index: 225 processed 0 rows
perror 136
В превод това означава: MySQL error code 136: No more room in index file
-------------------------
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock |
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB |
+-------+------+---------------------------------------------------------------------------------+
пуснах с ndbd --initial всичките нодове и се оправи, но се затри всичко!
-------------------
ERROR 1114 (HY000) at line 402: The table 'a10_users' is full
##################################№№###########################################
################# 9. Бележки при използването на клъстера ####################
####################################№№#########################################
Използване на аргумент --initial
/usr/local/mysql/bin/ndbd --initial ;
Само при първоначалното стартиране е добре да се ползва --initial защото това изтрива всички логове и бази данни от клъсетера
# Добавяне на потребител (На всеки нод)
#!/bin/bash
mysqlpassword="mysqlpassword";
mysql -e "CREATE DATABASE ads_network" -p;
mysql -e "GRANT ALL ON ads_network.* TO ads_network@'%' IDENTIFIED BY '$mysqlpassword' WITH GRANT OPTION;" -p
mysql -e "FLUSH PRIVILEGES;" -p;
# Рестартиране на manager-a
killall -15 ndb_mgmd;ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster --nodaemon;
При всяка промяна на конфигурационния файл "config.ini" е необходимо при изпълнението на ndb_mgmd да се добави и аргумент "--reload" т.е.
killall -15 ndb_mgmd;ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster --reload
# Рестартиране на node-a
killall -15 ndbd;/etc/init.d/mysql stop;/usr/local/mysql/bin/ndbd;/etc/init.d/mysql start;
При изпълнение на "ndb_show_tables" дава следната грешка:
Configuration error: Error : Could not alloc node id at 192.193.194.2 port 1186: No free node id found for mysqld(API).
Unable to connect to management server.
NDBT_ProgramExit: 1 -- Failed
Решение: няма достатъчно API Слотове
config.ini
[mysqld]
[mysqld]
Рестартиране , Спиране и Стартиране на node от ndb_mgm
2 restart -- Рестартира node 2
2 start -- Стартира node 2
2 stop -- Спира node 2
------------
ИЗПОЛЗВАНЕ НА DISK METHOD за клъстериране
SELECT * FROM INFORMATION_SCHEMA.FILES \G
select file_name, free_extents, total_extents from information_schema.files where file_type='datafile';
CREATE LOGFILE GROUP lgroup_ads
ADD UNDOFILE 'undo_lgroup_ads.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE 2M
ENGINE NDB;
CREATE TABLESPACE ts_ads_network
ADD DATAFILE 'data_ads_network.dat'
USE LOGFILE GROUP lgroup
INITIAL_SIZE 32M
ENGINE NDB;
CREATE LOGFILE GROUP lgroup
ADD UNDOFILE 'undo_lgroup_1.dat'
INITIAL_SIZE 2M
ENGINE NDB;
ALTER LOGFILE GROUP lgroup
ADD UNDOFILE 'undo_lgroup_3.dat'
INITIAL_SIZE 4000M
ENGINE NDB;
CREATE TABLESPACE ts_ads_network
ADD DATAFILE 'data_ads_network_1.dat'
USE LOGFILE GROUP lgroup
INITIAL_SIZE 20M
ENGINE NDB;
ALTER TABLESPACE ts_ads_network
ADD DATAFILE 'data_ads_network_3.dat'
INITIAL_SIZE 4000M
ENGINE NDB;
DROP database social_network;create database social_network;use social_network;
ALTER TABLESPACE ts_social_network DROP DATAFILE 'data_social_network.dat' ENGINE NDB;
ALTER TABLESPACE ts_social_network DROP DATAFILE 'data_social_network_2.dat' ENGINE NDB;
DROP TABLESPACE ts_social_network ENGINE NDB;
DROP LOGFILE GROUP lgroup ENGINE NDB;
drop table a10_articles;
ALTER TABLESPACE ts_ads_network DROP DATAFILE 'data_ads_network.dat' ENGINE NDB;
DROP TABLESPACE ts_ads_network ENGINE NDB;
DROP LOGFILE GROUP social_network ENGINE NDB;
-----------------
CREATE TABLE `articles` (
…..
) TABLESPACE ts_ads_network STORAGE DISK ENGINE NDB AUTO_INCREMENT=9573 DEFAULT CHARSET=utf8;
find $PWD -type f -exec sed -i 's/varchar(120)/varchar(200)/g' {} \;
find $PWD -type f -exec sed -i 's/ENGINE=MyISAM/TABLESPACE ts_ads_network STORAGE DISK ENGINE NDB/g' {} \;
##################################№№###########################################
################# 10. Debug при използването на клъстера №####################
####################################№№#########################################
/usr/local/mysql/bin/ndb_config --configinfo | grep -C 3 Index
/usr/local/mysql/bin/ndb_config --configinfo --config-file=/var/lib/mysql-cluster/config.ini|grep -C3 MaxNoOfTables
/usr/local/mysql/bin/ndb_config --type ndbd -q Id,MaxNoOfOrderedIndexes,MaxNoOfUniqueHashIndexes -r '\n'
ndb_show_tables | grep -c OrderedIndex
ndb_mgm> ALL REPORT MEMORY
Connected to Management Server at: 192.193.194.2:1186
Node 2: Data usage is 25%(665 32K pages of total 2560)
Node 2: Index usage is 15%(370 8K pages of total 2336)
Node 3: Data usage is 25%(665 32K pages of total 2560)
Node 3: Index usage is 15%(370 8K pages of total 2336)
Стартиране на ndb_mgmd с --nodaemon за да се debugwa всичко което идва към клъстера
ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster --nodaemon;
ndb_mgm> ALL DUMP 1000;
# Виждат се фазите на стартиране на нода
ndb_mgm> all status;
Node 2: starting (Last completed phase 4) (mysql-5.1.56 ndb-7.1.15)
Node 3: started (mysql-5.1.56 ndb-7.1.15)
##################################№№###########################################
################# 11. Performance при използването на клъстера ###############
####################################№№#########################################
Чрез ndb_size ще може да се изчисли необходимото пространство за обслужването на базата която ще бъде конвертирана към NDBCluster Engine.
При стартиране на ndb_size.pl дава следната грешка:
perl /usr/local/mysql/bin/ndb_size.pl
Can't locate Class/MethodMaker.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.10.0 /usr/local/share/perl/5.10.0 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10 /usr/local/lib/site_perl .) at /usr/local/mysql/bin/ndb_size.pl line 58.
BEGIN failed--compilation aborted at /usr/local/mysql/bin/ndb_size.pl line 58.
Решението е:
apt-get install libclass-methodmaker-perl
Директна статистика в терминала
perl /usr/local/mysql/bin/ndb_size.pl --database=test --socket=/var/run/mysqld/mysqld.sock
Статистика в HTML файл
perl /usr/local/mysql/bin/ndb_size.pl social_network localhost root > mysql_ndb_size_stat.html