mycat mysql多实例_Mycat-多实例的搭建

1. 基础环境准备

1.1 环境准备：

两台虚拟机 db01 db02

每台创建四个mysql实例：3307 3308 3309 3310

1.2 删除历史环境：

pkill mysqld

rm -rf /data/33{07..10}

mv /etc/my.cnf /etc/my.cnf.bak

1.3 创建相关目录初始化数据

mkdir /data/33{07..10}/data -p

mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/app/database/mysql

1.4 准备配置文件和启动脚本

========db01==============

cat >/data/3307/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3307/data

socket=/data/3307/mysql.sock

port=3307

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

log_bin=/data/3307/mysql-bin

binlog_format=row

skip-name-resolve

server-id=7

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/data/3308/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3308/data

port=3308

socket=/data/3308/mysql.sock

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

log_bin=/data/3308/mysql-bin

binlog_format=row

skip-name-resolve

server-id=8

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/data/3309/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3309/data

socket=/data/3309/mysql.sock

port=3309

log-error=/data/3309/mysql.log

log_bin=/data/3309/mysql-bin

binlog_format=row

skip-name-resolve

server-id=9

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/data/3310/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3310/data

socket=/data/3310/mysql.sock

port=3310

log-error=/data/3310/mysql.log

log_bin=/data/3310/mysql-bin

binlog_format=row

skip-name-resolve

server-id=10

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/etc/systemd/system/mysqld3307.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

LimitNOFILE = 5000

EOF

cat >/etc/systemd/system/mysqld3308.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

LimitNOFILE = 5000

EOF

cat >/etc/systemd/system/mysqld3309.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

LimitNOFILE = 5000

EOF

cat >/etc/systemd/system/mysqld3310.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf

LimitNOFILE = 5000

EOF

========db02===============

cat >/data/3307/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3307/data

socket=/data/3307/mysql.sock

port=3307

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

log_bin=/data/3307/mysql-bin

binlog_format=row

skip-name-resolve

server-id=17

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/data/3308/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3308/data

port=3308

socket=/data/3308/mysql.sock

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

log_bin=/data/3308/mysql-bin

binlog_format=row

skip-name-resolve

server-id=18

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/data/3309/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3309/data

socket=/data/3309/mysql.sock

port=3309

log-error=/data/3309/mysql.log

log_bin=/data/3309/mysql-bin

binlog_format=row

skip-name-resolve

server-id=19

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/data/3310/my.cnf<

[mysqld]

basedir=/app/database/mysql

datadir=/data/3310/data

socket=/data/3310/mysql.sock

port=3310

log-error=/data/3310/mysql.log

log_bin=/data/3310/mysql-bin

binlog_format=row

skip-name-resolve

server-id=20

gtid-mode=on

enforce-gtid-consistency=true

log-slave-updates=1

EOF

cat >/etc/systemd/system/mysqld3307.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf

LimitNOFILE = 5000

EOF

cat >/etc/systemd/system/mysqld3308.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf

LimitNOFILE = 5000

EOF

cat >/etc/systemd/system/mysqld3309.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

LimitNOFILE = 5000

EOF

cat >/etc/systemd/system/mysqld3310.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=/app/database/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf

LimitNOFILE = 5000

EOF

1.5 修改权限，启动多实例

chown -R mysql.mysql /data/*

systemctl start mysqld3307

systemctl start mysqld3308

systemctl start mysqld3309

systemctl start mysqld3310

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"

mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"

来源：https://www.cnblogs.com/wx1899325/p/12970284.html