为什么多实例

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

多实例脚本

mysql_instance.shview raw
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
#!/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