mysql

MySQL 多实例配置

Posted by Sunday on 2018-04-19

安装MySQL 5.7

源码安装 http://www.sundayle.com/2017/12/25/mysql/
Apt源安装

1
2
3
4
5
6
 #https://www.percona.com/doc/percona-server/LATEST/installation/apt_repo.html
sudo dpkg -i https://mirrors.tuna.tsinghua.edu.cn/percona/apt/percona-release_latest.xenial_all.deb 
sudo sed -i 's#http://www.percona.com#https://mirrors.tuna.tsinghua.edu.cn#g' /etc/apt/sources.list.d/percona-release.list

sudo apt-get update
sudo apt-get install percona-server-server-5.7 percona-server-client-5.7

数据库初始化

3307数据库初始化

1
mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307

多实例配置

1
2
3
cp /etc/my.cnf /etc/my.cnf.bak0419
配置可参考以下命令进行配置
mysqld_multi --example
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
vim /etc/my.cnf

[mysqld_multi]
mysqld     = /usr/local/webserver/mysql/bin/mysqld_safe
mysqladmin = /usr/local/webserver/mysql/bin/mysqladmin
log        = /data/logs/mysql/mysqld_multi.log
user       = multi_admin
password   = my_password

[mysqld3306]
port = 3306
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3306
socket = /tmp/mysql.sock
pid_file = /data/mysql/3306/mysql.pid
log-error = /data/logs/mysql/error.log
...
[mysqld3307]
port = 3307
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3307
socket = /tmp/mysql_3307.sock
pid_file = /data/mysql/3307/mysql.pid
log-error = /data/logs/mysql/err_3307.log
...

启动数据库实例

1
2
mysqld_multi start  3306  #启动3306实例
mysqld_multi start        #启动所有实例

关闭数据库实例

授权帐户关闭数据库，账户的用户名和密码和[mysqld_multi] 中配置的一样

1
2
mysql -uroot -hlocalhost -p -S '/tmp/mysql_3306.sock' -e "grant shutdown on *.* to 'multi_admin'@localhost identified by 'my_password';"
mysql -uroot -hlocalhost -p -S '/tmp/mysql_3307.sock' -e "grant shutdown on *.* to 'multi_admin'@localhost identified by 'my_password';"

由于在5.6中mysqld_multi 读取的[mysqld_multi] 密码为*，需要修改mysql_multi 的源文件来正确读取明文的密码
在该文件的216行中，添加-s 参数

1
sed -i 's/\(my \$com.*\)my_print_defaults/\1my_print_defaults -s/' `which mysqld_multi`

可以通过指定id 来关闭某个数据库实例，若不指定，则代表关闭所有的实例

1
2
sudo mysqld_multi stop 3306 # 关闭3306实例
sudo mysqld_multi stop      # 关闭所有实例

查看实例状态

1
2
3
sudo mysqld_multi report
MySQL (Percona Server) from group: mysqld3306 is not running
MySQL (Percona Server) from group: mysqld3307 is not running

开机启动

1
2
update-rc.d mysql defaults
update-rc.d mysql enable
1
2
3
4
5
6
7
8
9
10
11
如果systemctl启动的时候提示出错信息： 
WARNING: my_print_defaults command not found. 
Please make sure you have this command available and 
in your path. The command is available from the latest 
MySQL distribution. 
ABORT: Can't find command 'my_print_defaults'. 
This command is available from the latest MySQL 
distribution. Please make sure you have the command 
in your PATH. 

export PATH="/sbin:/usr/sbin:/bin:/usr/bin:/usr/local/webserver/mysql/bin"

启动脚本

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/sh
#
### BEGIN INIT INFO
# Provides: mysqld_multi
# Required-Start: $local_fs $network $remote_fs
# Should-Start: ypbind nscd ldap ntpd xntpd
# Required-Stop: $local_fs $network $remote_fs
# Default-Start:  2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL (Percona Server)
# Description: Percona-Server is a SQL database engine with focus on high performance.
### END INIT INFO

# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36

basedir=/usr/local/webserver/mysql
bindir=/usr/local/webserver/mysql/bin

PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export PATH

if test -x $bindir/mysqld_multi
then
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Can't execute $bindir/mysqld_multi from dir $basedir";
  exit;
fi

case "$1" in
    'start' )
        "$mysqld_multi" start $2
        ;;
    'stop' )
        "$mysqld_multi" stop $2
        ;;
    'report' )
        "$mysqld_multi" report $2
        ;;
    'status' )
        "$mysqld_multi" report $2
        ;;
    'restart' )
        "$mysqld_multi" stop $2
        "$mysqld_multi" start $2
        ;;
    *)
        echo "Usage: $0 {start|stop|status|report|restart}" >&2
        ;;
esac

https://www.jianshu.com/p/924d5f2c702f

CATALOG
    FEATURED TAGS
    mysql
    FRIENDS