Mysql多实例脚本

为什么多实例

1. [单实例]缺少DBA，无法优化单实例性能；
2. [单实例]系统本身设计问题，影响单实例性能；
3. [单实例]预料之外，而且不能重启数据库；
4. [多实例]克服以上缺点；
5. [多实例]某个实例出现问题，不会影响到其他实例；

多实例脚本

mysql_instance.shview raw

#!/bin/bash

PORT=${MYSQL_PORT}
DB_PATH=${MYSQL_PATH}
CNF_PATH=/etc/my.cnf.${PORT}
SOCK_FILE=/tmp/mysql_${PORT}.sock
DB_PASS=${DB_PASSWORD}

echo "初始化数据库"
mysql_install_db --user=mysql --datadir=${DB_PATH}

cat > ${CNF_PATH} << EOF
[mysqld]
port=${PORT}
datadir=${DB_PATH}
socket=${SOCK_FILE}
user=mysql
symbolic-links=0
innodb_file_per_table
max_connections=400
[mysqld_safe]
log-error=/var/log/mysqld_${PORT}.log
pid-file=/var/run/mysqld/mysqld_${PORT}.pid
EOF

echo "启动守护进程"
mysqld_safe --defaults-file=${CNF_PATH} 2>&1 > /dev/null &

while true
do
    if [ -S ${SOCK_FILE} ]; then
        echo "."
        break
    fi
    echo -n "."
    sleep 1
done

echo "初始化密码"
mysqladmin -u root password ${DB_PASS} -S ${SOCK_FILE}

echo "权限设置"
cat > tmp.sql << EOF
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '${DB_PASS}'; 
flush privileges;
EOF
mysql -uroot -p${DB_PASS} -S ${SOCK_FILE} -e "source tmp.sql;"

echo "完成"
## 关闭数据库
## mysqladmin -u root -p${DB_PASS} -S ${SOCK_FILE} shutdown