mysql集群 mysql客户端 配置_mysql 集群自动化配置

#!/bin/sh

#文 件 名: autogen_mysql.sh

#功 能: 自动生成Mysql集群配置文件,生成环境自动部署,自动运行脚本等

#创建时间: 2012-02-05

#自动化生成的文件列表:

#(执行autogen_mysql.sh后,会生成autogen目录，里面会存放自动生成的相关文件)

#1. ConfigAllServer.sh     自动配置所有Mysql集群节点脚本，包括NDB节点，mysql节点，管理节点

#2. RunAllServer.sh        自动启动所有Mysql集群节点脚本，包括NDB节点，mysql节点，管理节点

#3. mgm_node.sh         启动，关闭，重启管理节点脚本.在执行ConfigAllServer.sh时，会将该脚本自动拷贝到管理节点的/etc/目录下

#4. mysql_node.sh         启动或关闭Mysql节点脚本.在执行ConfigAllServer.sh时，会将该脚本自动拷贝到Mysql节点的/etc/目录下

#5. ndb_node_start.sh     启动NDB节点脚本.在执行ConfigAllServer.sh时，会将该脚本自动拷贝到NDB节点的/etc/目录下

#6. config.ini             管理配置文件.在执行ConfigAllServer.sh时，会将该配置文件自动拷贝到管理节点的/var/lib/mysql/目录下

#7. my_ndb.cnf             NDB节点配置文件.在执行ConfigAllServer.sh时，会将该脚本自动拷贝到NDB节点的/etc/目录下，并命名为my.cnf

#8. my_mysql.cnf         Mysql节点配置脚本.在执行ConfigAllServer.sh时，会将该脚本自动拷贝到Mysql节点的/etc/目录下，并命名为my.cnf

#注意事项

#1. 执行ConfigAllServer.sh, RunAllServer.sh这两个脚本的主机需要修改/etc/ssh/ssh_config,将StrictHostKeyChecking设置为no,防止弹出提示框引起自动化无法执行完毕

#　测试　sysbench --mysql-user=uProfile --mysql-password=123456 --test=oltp --mysql-host=10.74.213.38 --oltp-test-mode=complex --mysql-table-engine=ndbcluster --oltp-table-size=5000000 --mysql-db=uProfile --num-threads=64 --max-requests=500000 prepare

# -------------------------------------------------以下根据实际情况进行设定----------------------------------------------------

# ROOT权限密码,该密码为所有集群节点通用密码,所有服务器ROOT将使用统一密码,便于自动化控制，否则上面提到的ConfigAllServer.sh, RunAllServer.sh这两个文件无法正常运行

ROOTPASSWORD=123qwe

# 管理节点IP地址

MGM_IP=192.168.254.5

# NDB节点IP数组，数据之间用空格隔开,如'10.0.0.1 10.0.0.2'

NDB_IPS='192.168.254.101 192.168.254.102'

# MySQL节点IP数组，数据之间用空格隔开,如'10.0.0.1 10.0.0.2'

SQL_IPS='192.168.254.10 192.168.254.11'

#--------------------------------------------------------------------------------------------------------------------------------

rm -fr ./autogen_mysql

mkdir ./autogen_mysql

#Create my_ndb.cnf

cat > ./autogen_mysql/my_ndb.cnf << end

[mysqld]

max_connections = 2000

slow_query_log = /var/lib/mysql/data/slow_query.log

long_query_time = 1

ndbcluster

ndb-connectstring=$MGM_IP

[mysql_cluster]

ndb-connectstring=$MGM_IP

end

#Create my_mysql.cnf

cat > ./autogen_mysql/my_mysql.cnf << end

[mysqld]

ndbcluster

ndb-connectstring=$MGM_IP:1186

max_connections=2000

skip-name-resolve

#tmp_table_size=1024M

#max_heap_table_size=1024M

[mysql_cluster]

ndb-connectstring=$MGM_IP:1186

end

id=10

NDB_LIST=

for ndb_ip in $NDB_IPS;do

NDB_LIST=$NDB_LIST"[NDBD]"$'\n'

NDB_LIST=$NDB_LIST"NodeId=$id"$'\n'

NDB_LIST=$NDB_LIST"HostName=$ndb_ip"$'\n'

id=$(expr $id + 1)

done

id=50

SQL_LIST=

for sql_ip in $SQL_IPS;do

SQL_LIST=$SQL_LIST"[MYSQLD]"$'\n'

SQL_LIST=$SQL_LIST"NodeId=$id"$'\n'

SQL_LIST=$SQL_LIST"HostName=$sql_ip"$'\n'

id=$(expr $id + 1)

done

#Create config.ini

cat > ./autogen_mysql/config.ini << end

[NDBD DEFAULT]

NoOfReplicas: 2

DataDir: /home/Mysql/Data

backupdatadir: /home/Mysql/Backup

FileSystemPath: /home/Mysql/File

# Data Memory, Index Memory, and String Memory #

DataMemory: 5120M

IndexMemory: 512M

StringMemory: 5

# Transaction Parameters #

MaxNoOfConcurrentTransactions: 40960

MaxNoOfConcurrentOperations: 100000

MaxNoOfLocalOperations: 100000

# Transaction Temporary Storage #

MaxNoOfConcurrentIndexOperations: 8192

MaxNoOfFiredTriggers: 4000

TransactionBufferMemory: 1M

# Scans and buffering #

MaxNoOfConcurrentScans: 300

MaxNoOfLocalScans: 1000

BatchSizePerLocalScan: 64

LongMessageBuffer: 1M

# Logging and Checkpointing #

NoOfFragmentLogFiles: 300

FragmentLogFileSize: 16M

MaxNoOfOpenFiles: 40

InitialNoOfOpenFiles: 27

MaxNoOfSavedMessages: 25

# Metadata Objects #

MaxNoOfAttributes: 1500

MaxNoOfTables: 400

MaxNoOfOrderedIndexes: 200

MaxNoOfUniqueHashIndexes: 200

MaxNoOfTriggers: 770

# Boolean Parameters #

LockPagesInMainMemory: 0

StopOnError: 1

Diskless: 0

ODirect: 0

# Controlling Timeouts, Intervals, and Disk Paging #

TimeBetweenWatchDogCheck: 6000

TimeBetweenWatchDogCheckInitial: 6000

StartPartialTimeout: 30000

StartPartitionedTimeout: 60000

StartFailureTimeout: 1000000

HeartbeatIntervalDbDb: 5000

HeartbeatIntervalDbApi: 5000

TimeBetweenLocalCheckpoints: 20

TimeBetweenGlobalCheckpoints: 2000

TransactionInactiveTimeout: 0

TransactionDeadlockDetectionTimeout: 1200

DiskSyncSize: 4M

DiskCheckpointSpeed: 10M

DiskCheckpointSpeedInRestart: 100M

ArbitrationTimeout: 10

# Buffering and Logging #

UndoIndexBuffer: 2M

UndoDataBuffer: 1M

RedoBuffer: 32M

LogLevelStartup: 15

LogLevelShutdown: 3

LogLevelStatistic: 0

LogLevelCheckpoint: 0

LogLevelNodeRestart: 0

LogLevelConnection: 0

LogLevelError: 15

LogLevelCongestion: 0

LogLevelInfo: 3

MemReportFrequency: 0

# Backup Parameters #

BackupDataBufferSize: 2M

BackupLogBufferSize: 2M

BackupMemory: 64M

BackupWriteSize: 32K

BackupMaxWriteSize: 256K

[TCP DEFAULT]

SendBufferMemory=10M

ReceiveBufferMemory=2M

[NDB_MGMD DEFAULT]

PortNumber=1186

Datadir=/home/Mysql/Data

[NDB_MGMD]

NodeId=1

HostName=$MGM_IP

$NDB_LIST

$SQL_LIST

[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

end

#Create ndb_node_start.sh

cat > ./autogen_mysql/ndb_node_start.sh << end

#!/bin/sh

service iptables stop

chkconfig iptables off

ndbd &

if ! grep ndb_node_start /etc/rc.local > /dev/null

then

echo "sh /etc/ndb_node_start.sh" >> /etc/rc.local

fi

end

#Create mysql_node.sh

cat > ./autogen_mysql/mysql_node.sh << end

#!/bin/sh

service iptables stop

chkconfig iptables off

case "\$1" in

start)

mysqld_safe &

;;

stop)

mysqladmin -uroot -p$ROOTPASSWORD shutdown

;;

*)

echo "Usage: \$0 {start|stop}"

exit 1

esac

if ! grep mysql_node /etc/rc.local > /dev/null

then

echo "sh /etc/mysql_node.sh start" >> /etc/rc.local

fi

end

#Create mgm_node.sh

cat > ./autogen_mysql/mgm_node.sh << end

#!/bin/sh

service iptables stop

chkconfig iptables off

case "\$1" in

start)

ndb_mgmd -f /var/lib/mysql/config.ini --configdir=/var/lib/mysql/

;;

stop)

ndb_mgm -e shutdown

;;

restart)

ndb_mgm -e shutdown

ndb_mgmd -f /var/lib/mysql/config.ini --configdir=/var/lib/mysql/ --reload

;;

*)

echo "Usage: \$0 {start|stop|restart}"

exit 1

esac

if ! grep mgm_node /etc/rc.local > /dev/null

then

echo "sh /etc/mgm_node.sh start" >> /etc/rc.local

fi

end

#Create ConfigAllServer.sh

SCP_LIST=

for ndb_ip in $NDB_IPS;do

SCP_LIST=$SCP_LIST"spawn scp my_ndb.cnf root@$ndb_ip:/etc/my.cnf"$'\n'

SCP_LIST=$SCP_LIST"expect \"*password*\""$'\n'

SCP_LIST=$SCP_LIST"send \"$ROOTPASSWORD\r\""$'\n'

SCP_LIST=$SCP_LIST"expect eof"$'\n'$'\n'

SCP_LIST=$SCP_LIST"spawn scp ndb_node_start.sh root@$ndb_ip:/etc/ndb_node_start.sh"$'\n'

SCP_LIST=$SCP_LIST"expect \"*password*\""$'\n'

SCP_LIST=$SCP_LIST"send \"$ROOTPASSWORD\r\""$'\n'

SCP_LIST=$SCP_LIST"expect eof"$'\n'$'\n'

done

for sql_ip in $SQL_IPS;do

SCP_LIST=$SCP_LIST"spawn scp my_mysql.cnf root@$sql_ip:/etc/my.cnf"$'\n'

SCP_LIST=$SCP_LIST"expect \"*password*\""$'\n'

SCP_LIST=$SCP_LIST"send \"$ROOTPASSWORD\r\""$'\n'

SCP_LIST=$SCP_LIST"expect eof"$'\n'$'\n'

SCP_LIST=$SCP_LIST"spawn scp mysql_node.sh root@$sql_ip:/etc/mysql_node.sh"$'\n'

SCP_LIST=$SCP_LIST"expect \"*password*\""$'\n'

SCP_LIST=$SCP_LIST"send \"$ROOTPASSWORD\r\""$'\n'

SCP_LIST=$SCP_LIST"expect eof"$'\n'$'\n'

done

cat > ./autogen_mysql/ConfigAllServer.sh << end

#!/bin/sh

/usr/bin/expect << endexpect

set timeout 10

spawn scp config.ini root@$MGM_IP:/var/lib/mysql/config.ini

expect "*password*"

send "$ROOTPASSWORD\r"

expect eof

spawn scp mgm_node.sh root@$MGM_IP:/etc/mgm_node.sh

expect "*password*"

send "$ROOTPASSWORD\r"

expect eof

$SCP_LIST

endexpect

end

#Create RunAllServer.sh

RUN_LIST=

for ndp_ip in $NDB_IPS;do

RUN_LIST=$RUN_LIST"spawn ssh root@$ndp_ip"$'\n'

RUN_LIST=$RUN_LIST"expect \"*password*\""$'\n'

RUN_LIST=$RUN_LIST"send \"$ROOTPASSWORD\r\""$'\n'

RUN_LIST=$RUN_LIST"expect \"#\""$'\n'

RUN_LIST=$RUN_LIST"send \"sh /etc/ndb_node_start.sh\r\""$'\n'

RUN_LIST=$RUN_LIST"expect eof"$'\n'

RUN_LIST=$RUN_LIST"send \"exit\r\""$'\n'$'\n'

done

for sql_ip in $SQL_IPS;do

RUN_LIST=$RUN_LIST"spawn ssh root@$sql_ip"$'\n'

RUN_LIST=$RUN_LIST"expect \"*password*\""$'\n'

RUN_LIST=$RUN_LIST"send \"$ROOTPASSWORD\r\""$'\n'

RUN_LIST=$RUN_LIST"expect \"#\""$'\n'

RUN_LIST=$RUN_LIST"send \"sh /etc/mysql_node.sh start\r\""$'\n'

RUN_LIST=$RUN_LIST"expect eof"$'\n'

RUN_LIST=$RUN_LIST"send \"exit\r\""$'\n'$'\n'

done

cat > ./autogen_mysql/RunAllServer.sh << end

#!/bin/sh

/usr/bin/expect << endexpect

set timeout 10

spawn ssh root@$MGM_IP

expect "*password*"

send "$ROOTPASSWORD\r"

expect "#"

send "sh /etc/mgm_node.sh start\r"

expect eof

send "exit\r"

$RUN_LIST

endexpect

end