集群环境搭建配置文档
MYSQL CLUSTER 7.3.6
网络组申请的mysql集群环境，Red Hat linux 6.3 64位操作系统，规划4台虚拟主机 配置： CPU 4核，内存6G,存储100G
MYSQL CLUSTER 7.3.6 目前最新版本
一、节点分布情况：
MGM:10.40.20.147 管理节点
NDBD1:10.40.20.148 存储节点
NDBD2:10.40.20.149 存储节点
SQL1:10.40.20.149 SQL节点
SQL2:10.40.20.150 SQL节点
MYsql数据库集群由三部分组成，管理节点、存储节点和SQL节点。
管理节点是用来管理存储节点和SQL节点的，存储节点和SQL节点都要与管理节点通信；
存储节点是用来负载均衡存储数据，所用的存储引擎为NDB；
SQL节点是对外接口，用来与应用程序进行通信，应用程序只能访问它；
配置文件有两种：管理节点配置文件和普通节点配置文件，
config.ini是管理节点配置文件；my.cnf是数据节点和SQL节点配置文件。
config.ini文件中[ndb_mgmd]指明管理节点地址，[ndbd]指明数据节点地址，[mysqld]指明SQL节点地址。
二、下载安装包：
PC端，先官网下载页面 http://dev.mysql.com/downloads/cluster/ ，选择 red hat linux 下面的 MySQL-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar
压缩文件，下载到本地。
三、关闭防火墙和selinux：
3.1关闭防火墙(在四台服务器上操作)
[root@linux147 ~]# service iptables stop |
3.2默认不启动防火墙(在四台服务器上操作)
[root@linux147 ]# chkconfig iptables off |
关闭selinux
# 关闭 selinux
1 | vim /etc/selinux/config #（改为SELINUX=disabled）保存退出， |
四、环境清理（在各节点服务器上都要执行）
清除MySQL旧版本：
首先使用如下命令来清理之前操作系统自带的MySQL安装：
1 | yum -y remove mysql |
|
然后使用如下命令：
1 | [root@localhost src]# rpm -qa | grep mysql* |
2 | mysql-libs-5.1.61-4.el6.i686 |
3 | apr-util-mysql-1.3.9-3.el6_0.1.i686 |
对于找到的2个剩余MySQL包，按照如下的命令格式予以删除：
[root@linux147 tmp]# rpm -e --nodeps mysql-libs-5.1.66-2.el6_3.x86_64
[root@linux147 tmp]# rpm -e --nodeps mod_auth_mysql-3.0.0-11.el6_0.1.x86_64
[root@linux147 tmp]# rpm -e --nodeps php-mysql-5.3.3-22.el6.x86_64
[root@linux147 tmp]# rpm -e --nodepmysql-connector-odbc-5.1.5r1144-7.el6.x86_64
[root@linux147 tmp]# rpm -e --nodeps rsyslog-mysql-5.8.10-6.el6.x86_64
[root@linux147 tmp]# rpm -e --nodeps qt-mysql-4.6.2-25.el6.x86_64
[root@linux147 tmp]# rpm -e --nodeps mysql-connector-java-5.1.17-6.el6.noarch
[root@linux147 tmp]# rpm -e --nodeps dovecot-mysql-2.0.9-5.el6.x86_64
五、软件准备（在各台服务器上都要执行）
1.将 mysql-cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar上传到各服务器的某个目录下（如 /tmp ) 下面，然后解压并移到 /tmp目录下
1 | tar xvf MySql-Cluster-gpl-7.3.6-2.el6.x86_64.rpm-bundle.tar |
移动解压内容到mysql目录
[root@linux150 tmp]# mkdir /tmp/mysql
[root@linux150 tmp]# mv /tmp/MySQL*.rpm /tmp/mysql/
2 安装 My Cluster：
[root@linux148 tmp]# cd mysql
每台机器都安装
[root@linux147 mysql]# rpm -ivh MySQL-Cluster-server-gpl-7.3.6-2.el6.x86_64.rpm
2014-09-11 22:43:48 9148 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-11 22:43:48 9148 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-11 22:43:48 9148 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-09-11 22:43:48 9148 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-11 22:43:48 9148 [Note] InnoDB: Using Linux native AIO
2014-09-11 22:43:48 9148 [Note] InnoDB: Using CPU crc32 instructions
2014-09-11 22:43:48 9148 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-11 22:43:48 9148 [Note] InnoDB: Completed initialization of buffer pool
2014-09-11 22:43:48 9148 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2014-09-11 22:43:48 9148 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2014-09-11 22:43:48 9148 [Note] InnoDB: Database physically writes the file full: wait...
2014-09-11 22:43:48 9148 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2014-09-11 22:43:48 9148 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2014-09-11 22:43:48 9148 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2014-09-11 22:43:48 9148 [Warning] InnoDB: New log files created, LSN=45781
2014-09-11 22:43:48 9148 [Note] InnoDB: Doublewrite buffer not found: creating new
2014-09-11 22:43:48 9148 [Note] InnoDB: Doublewrite buffer created
2014-09-11 22:43:48 9148 [Note] InnoDB: 128 rollback segment(s) are active.
2014-09-11 22:43:48 9148 [Warning] InnoDB: Creating foreign key constraint system tables.
2014-09-11 22:43:48 9148 [Note] InnoDB: Foreign key constraint system tables created
2014-09-11 22:43:48 9148 [Note] InnoDB: Creating tablespace and datafile system tables.
2014-09-11 22:43:48 9148 [Note] InnoDB: Tablespace and datafile system tables created.
2014-09-11 22:43:48 9148 [Note] InnoDB: Waiting for purge to start
2014-09-11 22:43:48 9148 [Note] InnoDB: 5.6.19 started; log sequence number 0
A random root password has been set. You will find it in '/root/.mysql_secret'.
2014-09-11 22:43:49 9148 [Note] Binlog end
2014-09-11 22:43:49 9148 [Note] InnoDB: FTS optimize thread exiting.
2014-09-11 22:43:49 9148 [Note] InnoDB: Starting shutdown...
2014-09-11 22:43:50 9148 [Note] InnoDB: Shutdown completed; log sequence number 1625977
2014-09-11 22:43:50 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-09-11 22:43:50 9171 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-09-11 22:43:50 9171 [Note] InnoDB: The InnoDB memory heap is disabled
2014-09-11 22:43:50 9171 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-09-11 22:43:50 9171 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-09-11 22:43:50 9171 [Note] InnoDB: Using Linux native AIO
2014-09-11 22:43:50 9171 [Note] InnoDB: Using CPU crc32 instructions
2014-09-11 22:43:50 9171 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-09-11 22:43:50 9171 [Note] InnoDB: Completed initialization of buffer pool
2014-09-11 22:43:50 9171 [Note] InnoDB: Highest supported file format is Barracuda.
2014-09-11 22:43:50 9171 [Note] InnoDB: 128 rollback segment(s) are active.
2014-09-11 22:43:50 9171 [Note] InnoDB: Waiting for purge to start
2014-09-11 22:43:50 9171 [Note] InnoDB: 5.6.19 started; log sequence number 1625977
2014-09-11 22:43:51 9171 [Note] Binlog end
2014-09-11 22:43:51 9171 [Note] InnoDB: FTS optimize thread exiting.
2014-09-11 22:43:51 9171 [Note] InnoDB: Starting shutdown...
2014-09-11 22:43:52 9171 [Note] InnoDB: Shutdown completed; log sequence number 1625987
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.
You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.
Also, the account for the anonymous user has been removed.
In addition, you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test database.
This is strongly recommended for production servers.
See the manual for more instructions.
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
SQL节点安装
[root@linux147 mysql]# rpm -ivh MySQL-Cluster-client-gpl-7.3.6-2.el6.x86_64.rpm
安装完成后
[root@linux149 mysql]# chmod -R 1777 /usr/lib64/mysql
[root@linux149 mysql]# chmod -R 1777 /usr/bin/mysql
[root@linux149 mysql]# chmod -R 1777 /usr/share/mysql
# 重启服务器
1 | init 6 |
五、配置节点
5.1 配置管理节点（10.40.20.147）
# 删除自带的 /etc/my.cnf 【切记：一定要删除掉！ ndb_mgmd启动的时候，也会加上/etc/my.cnf的配置内容】
1 | rm -rf /etc/my.cnf |
# 新建配置文件存放目录
1 | mkdir /usr/local/mysql/etc |
2 | chown mysql:mysql /usr/local/mysql/etc |
创建mysql用户及用户组
[root@linux147]# groupadd mysql
[root@linux147]#useradd -g mysql -s /usr/sbin/nologin mysql
[root@linux147]#mkdir /usr/local/mysql
[root@linux147 local]# chown -R mysql:mysql /usr/local/mysql
mysql用户密码：mysql
修改mysql的root密码
[root@linux150 ~]# mysql -u root
mysql> set password for 'root'@'localhost'=password('root');
# 创建配置文件 config.ini
1 | vim /usr/local/mysql/etc/config.ini |
# config.ini内容如下：
[ndb_mgmd default]
datadir = /home/mysql/data
[ndbd default]
NoOfReplicas = 2
DataMemory = 3G
IndexMemory = 1G
datadir = /home/mysql/data
[ndb_mgmd]
NodeId = 1
HostName = 10.40.20.147
[ndbd]
NodeId = 11
HostName = 10.40.20.148
[ndbd]
NodeId = 12
HostName = 10.40.20.149
[mysqld]
NodeId = 21
HostName = 10.40.20.149
[mysqld]
NodeId = 22
HostName = 10.40.20.150
5.2 配置数据节点（10.40.20.148,10.40.20.149）
# 修改配置文件
1 | vim /etc/my.cnf |
# my.cnf内容如下：
[mysqld]
ndbcluster
ndb-connectstring = 10.40.20.147
[mysql_cluster]
ndb-connectstring = 10.40.20.147
5.3 配置SQL节点（10.40.20.149,10.40.20.150）
# 修改配置文件
1 | vim /etc/my.cnf |
# my.cnf内容如下：
[client]
Socket=/usr/lib64/mysql/mysql.sock
[mysqld]
Max_connections=100
socket=/usr/lib64/mysql/mysql.sock
datadir = /usr/lib64/mysql
ndbcluster
ndb-connectstring = 10.40.20.147
[mysql_cluster]
ndb-connectstring = 10.40.20.147
六、启动节点
启动需要按照如下顺序进行：
Management Node > Data Node > SQL Node
6.1 启动管理节点（10.40.20.147）
# --initial 首次加载、/usr/local/mysql/etc/config.ini文件有改变，其它时候不要加，除非是在备份、恢复或配置变化后重启时，其他时候不要加，不然数据就清空。。。
启动管理节点（10.40.20.147）
[root@linux147 /]# ndb_mgmd -f /usr/local/mysql/etc/config.ini --initial （首次加载 加参数--initial)
正常启动
[root@linux147 /]# ndb_mgmd -f /usr/local/mysql/etc/config.ini
其他操作：
#查看ndb_mgmd是否启动
[root@linux147 /]# ps -ef|grep ndb_mgmd
#ndb_mgmd默认启动1186端口查看
[root@linux147 /]# netstat -ntlp |grep ndb_mgmd
查看集群状态
[root@linux147 /]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
关闭管理节点：ndb_mgm -e shutdown (安全关闭管理节点和数据节点）
6.2 启动数据节点（10.40.20.148,10.40.20.149）
# 只是在第一次启动或在备份／恢复或配置变化后重启ndbd时，才加–initial参数！
启动数据节点（10.40.20.147，10.40.20.148）
ndbd -initial
#正常启动方式
ndbd
#查看ndbd启动状态
ps-ef|grep ndbd
6.3 启动SQL节点（10.40.20.149,10.40.150）
#启动sql节点
root@linux149 mysql]# mysqld_safe --defaults-file=/etc/my.cnf --explicit_defaults_for_timestamp &
方法2：[root@linux149 mysql]# mysqld --defaults-file=/etc/my.cnf --user=root
[root@linux149 mysql]# /usr/bin/mysql_install_db --user=mysql
service mysql start
# 启动 SQL 节点
1 | service mysqld start |
# 关闭 SQL 节点
1 | service mysqld stop |
# 重启 SQL 节点
1 | service mysqld restart |
# 检验mysql是否运行
1 | service mysqld status |
# 为sql指定密码
1 | /usr/mysql/bin/mysqladmin -u root password 'new-password' |
2 |
|
3 | /usr/mysql/bin/mysqladmin -u root -h 'host' password 'new-password' |
# 启动命令行窗口
1 | /usr/mysql/bin/mysql -u root –p |
--修改mysql root密码方法：
mysqld_safe --user=mysql --skip-grant-tables --skip-networking&
Mysql -u root
Mysql> uses mysql;
Mysql>update user set password=password('root') where user='root';
mysql> flush privileges;
基于ndb的简单集群环境测试
创建数据库
mysql> show databases；
mysql> create database bjf；
mysql> use bjf；
mysql> create table test1(id int ,name varchar(200)) engine=ndb;
Query OK, 0 rows affected (0.19 sec)
mysql> insert into test1 values(1,'aaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(2,'bbbba');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(3,'cccc');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+------+-------+
| id | name |
+------+-------+
| 1 | aaa |
| 3 | cccc |
| 2 | bbbba |
+------+-------+
3 rows in set (0.00 sec)
查看10.40.20.149和10.40.20.150 sql节点的数据库信息完全一致。
注意事项：
1、事务处理（写入，修改，删除时超出范围会提示）
MaxNoOfConcurrentOperations设置的值默认为32768
可以通过修改config.ini
配套修改MaxNoOfLocalOperations值，建议该值为前值的1.1倍
REPLICATION复制
在主mysql节点上，创建一个用户
mysql> grant replication slave,replication client on *.* to 'slave'@'10.40.20.148' identified by 'slave';
mysql> grant file,select,replication slave on *.* to slave@10.40.20.148 identified by 'slave';
MY.CNF文件配置
（master）
[client]
socket=/usr/lib64/mysql/mysql.sock
user=root
password=root
[mysqld]
server-id=1
log-bin=mysqlmaster-bin.log
sync_binlog=1
innodb_buffer_pool_size=512M
innodb_flush_log_at_trx_commit=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
log_bin_trust_function_creators=1
default-storage-engine=ndbcluster
max_connections=100
socket=/usr/lib64/mysql/mysql.sock
datadir=/usr/lib64/mysql
ndbcluster
ndb-connectstring=10.40.20.147
[mysql_cluster]
ndb-connectstring=10.40.20.147
（slave）
[client]
socket=/usr/lib64/mysql/mysql.sock
user=root
password=root
[mysqld]
server-id=2
log_bin=mysql-bin
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1
max_connections=100
socket=/usr/lib64/mysql/mysql.sock
datadir=/usr/lib64/mysql
ndbcluster
ndb-connectstring=10.40.20.147
[mysql_cluster]
ndb-connectstring=10.40.20.147
启动slave
mysql> change master to master_host='10.40.20.147',
-> master_user='slave',
-> master_password='slave',
-> master_log_file='mysqlmaster-bin.000009',
-> master_log_pos=0;
用show slavestatus查看slave的设置是否正确。
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.40.20.147
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /usr/lib64/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql> show processlist \G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Daemon
Time: 508
State: Waiting for ndbcluster to start
Info: NULL
*************************** 2. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 3. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 69
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
主从成功查看：
mysql> mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.40.20.147
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysqlmaster-bin.000015
Read_Master_Log_Pos: 1546
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 994
Relay_Master_Log_File: mysqlmaster-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1546
Relay_Log_Space: 1167
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 82bb784a-4306-11e4-ab58-005056a1a2f1
Master_Info_File: /usr/lib64/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
Slave端出现了不同步情况后，先stop slave需要重新找到pos点和文件，mysql下修改master_log_file=
和master_log_pos=
再start slave
Show processlist 语句检查i/o和sql线程的status字段值。
MYSQL-PROXY 读写分离测试
Mysql-proxy 实现读写分离。主数据库处理事务性查询、从数据库处理select查询。
生产上需要建立ha方式。
通过rw-splitting.lua脚本实现读写分离，稳定性欠佳，在高并发或有错误连接的情况下，进程很容易自动关闭，keepalive参数进程进行自动恢复，可以在每个服务器上安装一个提供自身使用，效率低。
Mysql-proxy安装在10.40.20.149上
安装：通过源码方式安装
安装后 /OPT/MYSQL-PROXY 配置etc/mysql-proxy.cnf 文件
[mysql-proxy]
admin-username=root
admin-password=root
daemon=true
keepalive=true
log-file=/opt/mysql-proxy/log
log-level=debug
proxy-backend-addresses=10.40.20.147:3306
proxy-read-only-backend-address=10.40.20.148:3306
proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua
admin-lua-script=/opt/mysql-proxy/scripts/admin.lua
配置mysql-proxy.sh启动脚本
#!/bin/sh
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql
# Source function library.
. /etc/rc.d/init.d/functions
#PROXY_PATH=/usr/local/bin
PROXY_PATH=/opt/mysql-proxy/bin
prog="mysql-proxy"
# Source networking configuration.
. /etc/sysconfig/network
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
#PROXY_OPTIONS="--daemon"
PROXY_OPTIONS="--proxy-read-only-backend-addresses=10.40.20.148:3306 --proxy-backend-addresses=10.40.20.147:3306 --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"
PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH
# By default it's all good
RETVAL=0
# See how we were called.
case "$1" in
start)
# Start daemon.
echo -n $"Starting $prog: "
$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --user=mysql --pid-file=$PROXY_PID --log-level=warning --log-file=/opt/mysql-proxy/log/mysql-proxy.log
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
;;
stop)
# Stop daemons.
echo -n $"Stopping $prog: "
killproc $prog
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
;;
restart)
$0 stop
sleep 3
$0 start
;;
condrestart)
[ -e /var/lock/subsys/mysql-proxy ] && $0 restart
;;
status)
status mysql-proxy
RETVAL=$?
;;
*)
echo "Usage: $0 {start|stop|restart|status|condrestart}"
RETVAL=1
;;
esac
exit $RETVAL
读写分离脚本rw-splitting.lua 脚本修改
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //默认4
max_idle_connections = 1,//默认8
is_debug = false
}
end
启动命令 /mysql-proxy.sh start
测试过程：
主服务器10.40.20.147，授权给10.40.20.149
Mysql>grant all on *.* to ‘root’@’10.40.20.149’ identified by ‘root’
登录slave，关闭slave同步进程
Stop slave；
远程连接到mysql-proxy服务器
Mysql -uroot -proot -P4040 -h 10.40.20.149
Mysql> use test;
Insert into test(name) values(‘bjf’);
Insert into test(name) values(‘bjf02’);
退出后在登录，执行查询
Mysql>select * from test;
无法看到刚才插入的记录。
再登录主服务器10.40.20.147
Mysql>select * from test;
可以看到新加的记录
登录到slave服务器
Mysql>select *from test;
无法查看到新加记录。
由此验证，已经实现了ｍｙｓｑｌ的读写分离，所有的写操作在10.40.20.147,所有的读操作在10.40.20.148 ，分担主服务器压力。
Amoeba (变形虫）mysql的读写分离
目前还不支持事务
暂时不支持存储过程（近期会支持）
不适合从amoeba导数据的场景或者对大数据量查询的query并不合适（比如一次请求返回10w以上甚至更多的数据场合）
暂时不支持分库分表，目前只能做到分数据库实例，每个被切分的节点需要保持库结构一致
具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到达目标库，可并发请求多台数据库合并结果。
主要解决：
降低数据切分带来复杂多数据库结构
提供切分规则并降低数据切分规则给应用带来的影响
降低db与客户端的连接数
读写分离
Jdk 安装，最好是1.6及1.7版本
1、设置etc/profile文件，增加java_home 利用find / 查找出.dt.jar路径
JAVA_HOME=/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.9.x86_64
PAHT=$JAVA_HOME/bin:$PATH
CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export JAVA_HOME
export PATH
export CLASSPATH
启用
[root@linux150 bin]# ./launcher
./launcher: line 11: JAVA_HOME: command not found
2014-09-28 23:10:43 [INFO] Project Name=Amoeba-MySQL, PID=8652 , starting...
log4j:WARN log4j config load completed from file:/tmp/amoeba-mysql-3.0.5-RC/conf/log4j.xml
2014-09-28 23:10:44,834 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA
log4j:WARN ip access config load completed from file:/tmp/amoeba-mysql-3.0.5-RC/conf/access_list.conf
2014-09-28 23:10:45,207 INFO net.ServerableConnectionManager - Server listening on 0.0.0.0/0.0.0.0:8066.
主从服务器授权给amoeba服务器
Mysql>grant all on *.* to 'root'@10.40.20.149 identified by 'root';
(主从都需要设置)
连接到amoeba服务器：
[root@linux148 ~]# mysql -uroot -proot -h10.40.20.150 -P 8066
连接到10.40.20.148 slave机器：
Stop slave ；关闭复制
从10.40.20.148连接到150的amoeba服务器
Use test;
Insert into test(name) values(‘0000’);
Select * from test;
再到10.40.20.148 mysql查看
Select * from test; 无插入数据
再到10.40.20.147 master
基于amoeba的数据垂直切分和数据水平切分
扩展性极强
225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
