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