【Web 集群实战】16_安装并配置多实例 MySQL 数据库

Web集群实战

1. 安装 MySQL 多实例

可参考文章【Web 集群实战】12_MySQL 的安装与配置

2. 创建 MySQL 多实例的数据文件目录

[root@ylt001 ~]# mkdir -p /data/{3306,3307}/data
[root@ylt001 ~]# tree /data/
/data
|-- 3306
|   `-- data
`-- 3307
    `-- data
4 directories, 0 files

3. 创建 MySQL 多实例的配置文件

[root@ylt001 3306]# cat my.cnf
[client]
port      = 3306
socket    = /data/3306/mysql.sock
[mysqld]
port      = 3306
socket    = /data/3306/mysql.sock
basedir         = /application/mysql
datadir         = /data/3306/data
pid-file        = /data/3306/mysql.pid
relay-log       = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
log-error=/data/3306/mysql_ylt3306.err
pid-file=/data/3306/mysqld.pid

[root@ylt001 3306]# cp /data/3306/my.cnf /data/3307/
[root@ylt001 3306]# sed -i 's/3306/3307/g' /data/3307/my.cnf
[root@ylt001 3306]# sed -i 's/server-id = 1/sever-id = 3/g' /data/3307/my.cnf
[root@ylt001 3306]# touch /data/{3306/mysql_ylt3306.err,3307/mysql_ylt3307.err}
[root@ylt001 3306]# tree /data
/data
|-- 3306
|   |-- data
|   |-- my.cnf
|   `-- mysql_ylt3306.err
`-- 3307
    |-- data
    |-- my.cnf
    `-- mysql_ylt3307.err
4 directories, 4 files

4. 创建 MySQL 多实例的启动文件

[root@ylt001 3306]# cat mysql
#!/bin/sh
############
#this scripts is created by ylt at 2018-09-30
#init
port=3306
mysql_user="root"
mysql_pwd="password"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
 if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
          /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
 else
        printf "MySQL is running...\n"
        exit
 fi
}
#stop function
function_stop_mysql()
{
 if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit
 else
        printf "Stopping MySQL...\n"
        ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
 fi
}
#restart function
function_restart_mysql()
{
 printf "Restarting MySQL...\n"
 function_stop_mysql
 sleep 2
 function_start_mysql
}
case $1 in
start)
 function_start_mysql
;;
stop)
 function_stop_mysql
;;
restart)
 function_restart_mysql
;;
*)
 printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac

[root@ylt001 3306]# cp /data/3306/mysql /data/3307/
[root@ylt001 3306]# sed -i 's/3306/3307/g' /data/3307/mysql
[root@ylt001 3306]# tree /data
/data
|-- 3306
|   |-- data
|   |-- my.cnf
|   |-- mysql_ylt3306.err
|   `-- mysql
`-- 3307
    |-- data
    |-- my.cnf
    |-- mysql_ylt3307.err
    `-- mysql
4 directories, 6 files

5. 配置 MySQL 多实例的文件权限

• 授权 mysql 用户和组管理整个多实例的根目录 /data

[root@ylt001 3306]# chown -R mysql:mysql /data
[root@ylt001 3306]# find /data -name mysql|xargs ls -l
-rw-r--r-- 1 mysql mysql 1012 Sep 30 13:38 /data/3306/mysql
-rw-r--r-- 1 mysql mysql 1012 Sep 30 13:38 /data/3307/mysql

• 授权 MySQL 多实例所有启动文件的 mysql 可执行

[root@ylt001 3306]# find /data -name mysql|xargs chmod 700

• 检查上述结果

[root@ylt001 3306]# find /data -name mysql -exec ls -l {} \;
-rwx------ 1 mysql mysql 1012 Sep 30 13:38 /data/3307/mysql
-rwx------ 1 mysql mysql 1012 Sep 30 13:38 /data/3306/mysql

6. MySQL 相关命令加入全局路径的配置

• 确认 mysql 命令所在路径

[root@ylt001 3306]# ls /application/mysql/bin/mysql
/application/mysql/bin/mysql

• 法一：在 PATH 变量前面增加 /application/mysql/bin 路径，并追加到 /etc/profile 文件中

[root@ylt001 3306]# echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
[root@ylt001 3306]# tail -1 /etc/profile
export PATH=/application/mysql/bin:$PATH
[root@ylt001 3306]# source /etc/profile
[root@ylt001 3306]# echo $PATH
/application/mysql/bin:/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

• 法二：设置软链接

[root@ylt001 3306]# ln -s /application/mysql/bin/* /usr/local/sbin/

7. 初始化 MySQL 多实例的数据库文件

[root@ylt001 3306]# cd /application/mysql/scripts/
[root@ylt001 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
Installing MySQL system tables...
180930 13:46:31 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
180930 13:46:31 [Note] /application/mysql/bin/mysqld (mysqld 5.5.61) starting as process 102410 ...
OK
Filling help tables...
180930 13:46:31 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
180930 13:46:31 [Note] /application/mysql/bin/mysqld (mysqld 5.5.61) starting as process 102417 ...
OK
...
[root@ylt001 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
Installing MySQL system tables...
180930 13:47:48 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
180930 13:47:48 [Note] /application/mysql/bin/mysqld (mysqld 5.5.61) starting as process 102521 ...
OK
Filling help tables...
180930 13:47:48 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
180930 13:47:48 [Note] /application/mysql/bin/mysqld (mysqld 5.5.61) starting as process 102528 ...
OK
...

[root@ylt001 scripts]# tree /data
/data
|-- 3306
|   |-- data
|   |   |-- mysql
|   |   |   |-- columns_priv.MYD
|   |   |   |-- columns_priv.MYI
|   |   |   |-- columns_priv.frm
|   |   |   |-- db.MYD
|   |   |   |-- db.MYI
|   |   |   |-- db.frm
...

8. 启动 MySQL 多实例的数据库

• 启动数据库

[root@ylt001 3306]# /data/3306/mysql start
Starting MySQL...
[root@ylt001 3306]# /data/3307/mysql start
Starting MySQL...
[root@ylt001 3306]# netstat -lntup|grep 330
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      106726/mysqld
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      107676/mysqld

• 正常停止数据库
  
  • 禁止使用 pkill、kill -9、killall -9 等命令强制杀死数据库，会引起数据库无法启动等故障发生

[root@ylt001 3306]# /data/3306/mysql stop
Stopping MySQL...

• 重启数据库

[root@ylt001 ~]# /data/3306/mysql restart
Restarting MySQL...
Stopping MySQL...
Starting MySQL...

9. 配置 MySQL 多实例数据库开机自启动

[root@ylt001 3306]# echo "#mysql multi instances" >> /etc/rc.local
[root@ylt001 3306]# echo "/data/3306/mysql start" >> /etc/rc.local
[root@ylt001 3306]# echo "/data/3307/mysql start" >> /etc/rc.local
[root@ylt001 3306]# tail -3 /etc/rc.local
#mysql multi instances
/data/3306/mysql start
/data/3307/mysql start

10. 登录 MySQL 测试

[root@ylt001 ~]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.61 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>

MySQL 安全配置

[root@ylt001 ~]# mysqladmin -u root -S /data/3306/mysql.sock password 'password'
# 为 mysql 设置密码
[root@ylt001 ~]# mysql -uroot -p -S /data/3306/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.61 MySQL Community Server (GPL)
...
mysql>

• 降低 MySQL 启动文件的权限

[root@ylt001 3306]# find /data -type f -name "mysql" -exec chmod 700 {} \;
[root@ylt001 3306]# find /data -type f -name "mysql" -exec chown root.root {} \;
[root@ylt001 3306]# find /data -type f -name "mysql" -exec ls -l {} \;
-rwx------ 1 root root 1003 Sep 30 14:20 /data/3307/mysql
-rwx------ 1 root root 1003 Sep 30 14:21 /data/3306/mysql