单机多实例mysql优缺点_mysql运维之-单机多实例(多进程的方式)

本文实践的是多进程的方式做MYSQL单机多实例，一个实例一个进程，各实例有自己的配置文件，总结起来有以下几点：

端口port分开

配置文件分开

socket分开

日志分开

多serverid

都可以使用mysql用户

1.源代码编译安装&配置MYSQL环境变量(略)

2.配置多实例

MYSQL版本：5.7.28

2.1.创建多个数据目录

mkdir /data/{3307,3306,3308} -p

2.2.准备多个配置文件

root@lzr-K401LB:/etc/systemd/system# cat /data/3306/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/data/3306/data

port=3306socket=/data/3306/mysql.sock

log-error=/data/3306/data/mysql.err

log-bin=/data/3306/data/mysql-bin

server_id=6explicit_defaults_for_timestamp=true#skip-grant-tables

root@lzr-K401LB:/etc/systemd/system#

3306

root@lzr-K401LB:/etc/systemd/system# cat /data/3307/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/data/3307/data

port=3307socket=/data/3307/mysql.sock

log-error=/data/3307/data/mysql.err

log-bin=/data/3307/data/mysql-bin

server_id=7explicit_defaults_for_timestamp=true#skip-grant-tables

root@lzr-K401LB:/etc/systemd/system#

3307

root@lzr-K401LB:/etc/systemd/system# cat /data/3308/my.cnf

[mysqld]

basedir=/usr/local/mysql

datadir=/data/3308/data

port=3308socket=/data/3308/mysql.sock

log-error=/data/3308/data/mysql.err

log-bin=/data/3308/data/mysql-bin

server_id=8explicit_defaults_for_timestamp=true#skip-grant-tables

root@lzr-K401LB:/etc/systemd/system#

3308

2.3 初始化多套数据目录

cd /usr/mysql/bin

./mysql_install_db --defaults-file=/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data

./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data

./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data

2.4 文件权限

chown -R mysql.mysql /data

2.4 启动&关闭MYSQL多实例

cd /usr/mysql/bin

启动

./mysqld_safe --defaults-file=/data/3307/my.cnf &

./mysqld_safe --defaults-file=/data/3306/my.cnf &

./mysqld_safe --defaults-file=/data/3308/my.cnf &

关闭(无密码)

./mysqladmin -S /data/3306/mysql.sock shutdown

./mysqladmin -S /data/3307/mysql.sock shutdown

./mysqladmin -S /data/3308/mysql.sock shutdown

关闭(有密码)

./mysqladmin -uroot -p123456  -S /data/3306/mysql.sock shutdown

./mysqladmin -uroot -p123456  -S /data/3307/mysql.sock shutdown

./mysqladmin -uroot -p123456  -S /data/3308/mysql.sock shutdown

2.5 查看端口占用

root@lzr-K401LB:/usr/local/mysql/bin# netstat -ln | grep 330tcp60 0 :::3306 :::*LISTEN

tcp60 0 :::3307 :::*LISTEN

tcp60 0 :::3308 :::*LISTEN

unix2 [ ACC ] STREAM LISTENING 711534 /data/3306/mysql.sock

unix2 [ ACC ] STREAM LISTENING 713104 /data/3307/mysql.sock

unix2 [ ACC ] STREAM LISTENING 713814 /data/3308/mysql.sock

root@lzr-K401LB:/usr/local/mysql/bin#

View Code

2.6 设置多实例密码

cd /usr/mysql/bin

./mysqladmin -uroot -S /data/3306/mysql.sock password '123456'

./mysqladmin -uroot -S /data/3307/mysql.sock password '123456'

./mysqladmin -uroot -S /data/3308/mysql.sock password '123456'

2.7 验证库连接

root@lzr-K401LB:/usr/local/mysql/bin# ./mysql -uroot -p123456 -S /data/3306/mysql.sock

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 2Server version:5.7.28-log Source distribution

Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql> show variables like 'server_id';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 6 |

+---------------+-------+

1 row in set (0.01sec)

mysql>

3306

root@lzr-K401LB:/usr/local/mysql/bin# ./mysql -uroot -p123456 -S /data/3307/mysql.sock

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 2Server version:5.7.28-log Source distribution

Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql> show variables like 'server_id';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 7 |

+---------------+-------+

1 row in set (0.01sec)

mysql>

3307

root@lzr-K401LB:/usr/local/mysql/bin# ./mysql -uroot -p123456 -S /data/3308/mysql.sock

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 2Server version:5.7.28-log Source distribution

Copyright (c)2000, 2019, 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 clearthe current input statement.

mysql> show variables like 'server_id';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 8 |

+---------------+-------+

1 row in set (0.00sec)

mysql>

3308

2.8 忘记密码

修改my.cnf

[mysqld]

skip-grant-tables

启动mysql

修改密码

flush privileges;

alter user 'root'@'localhost' identified with mysql_native_password by '123456';

flush privileges;

2.9 配置后台服务+开机启动

配置3306-3308后台服务

root@lzr-K401LB:/etc/systemd/system# cat /etc/systemd/system/mysqld3306.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf

LimitNOFILE= 5000

View Code

添加执行权限

root@lzr-K401LB:/etc/systemd/system# chmod a+x mysqld3306.service

root@lzr-K401LB:/etc/systemd/system# chmod a+x mysqld3307.service

root@lzr-K401LB:/etc/systemd/system# chmod a+x mysqld3308.service

root@lzr-K401LB:/etc/systemd/system#

View Code

配置开机启动

root@lzr-K401LB:/etc/systemd/system# systemctl enable mysqld3306.service

Created symlink/etc/systemd/system/multi-user.target.wants/mysqld3306.service → /etc/systemd/system/mysqld3306.service.

root@lzr-K401LB:/etc/systemd/system# systemctl enable mysqld3307.service

Created symlink/etc/systemd/system/multi-user.target.wants/mysqld3307.service → /etc/systemd/system/mysqld3307.service.

root@lzr-K401LB:/etc/systemd/system# systemctl enable mysqld3308.service

Created symlink/etc/systemd/system/multi-user.target.wants/mysqld3308.service → /etc/systemd/system/mysqld3308.service.

View Code

启动服务

root@lzr-K401LB:/etc/systemd/system# systemctl start mysqld3306.service

root@lzr-K401LB:/etc/systemd/system# systemctl start mysqld3307.service

root@lzr-K401LB:/etc/systemd/system# systemctl start mysqld3308.service

root@lzr-K401LB:/etc/systemd/system# ps aux|grepmysql

mysql14853 0.0 0.0 4624 1792 ? Ss 13:24 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf

mysql15019 1.6 2.2 1321440 182324 ? Sl 13:24 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/3306/data/mysql.err --pid-file=lzr-K401LB.pid --socket=/data/3306/mysql.sock --port=3306mysql15051 0.0 0.0 4624 1696 ? Ss 13:24 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf

mysql15217 2.0 2.2 1321440 181872 ? Sl 13:24 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf --basedir=/usr/local/mysql --datadir=/data/3307/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/3307/data/mysql.err --pid-file=lzr-K401LB.pid --socket=/data/3307/mysql.sock --port=3307mysql15250 0.0 0.0 4624 1760 ? Ss 13:24 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf

mysql15416 2.3 2.2 1321440 182032 ? Sl 13:24 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf --basedir=/usr/local/mysql --datadir=/data/3308/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/3308/data/mysql.err --pid-file=lzr-K401LB.pid --socket=/data/3308/mysql.sock --port=3308root15455 0.0 0.0 16172 1004 pts/2 S+ 13:24 0:00 grep --color=auto mysql

root@lzr-K401LB:/etc/systemd/system#

View Code