Mysql Cluster 7.1 -- Инсталиране, Конфигуриране, Използване, Debug-ване, Performance и още- Бележки

#####################################################################
################# 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

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
Mysql Cluster 7.1 - Инсталиране, Конфигуриране, Използване, Debug-ване, Performance и още- Бележки, 5.0 out of 5 based on 1 rating

Вашият коментар

Вашият email адрес няма да бъде публикуван Задължителните полета са отбелязани с *