mycatmysql多实例_Mycat多实例的搭建

1. 基础环境准备

1.1 环境准备&＃xff1a;

两台虚拟机 db01 db02

每台创建四个mysql实例&＃xff1a;3307 3308 3309 3310

1.2 删除历史环境&＃xff1a;

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&＃61;mysql --datadir&＃61;/data/3307/data --basedir&＃61;/app/database/mysql

mysqld --initialize-insecure  --user&＃61;mysql --datadir&＃61;/data/3308/data --basedir&＃61;/app/database/mysql

mysqld --initialize-insecure  --user&＃61;mysql --datadir&＃61;/data/3309/data --basedir&＃61;/app/database/mysql

mysqld --initialize-insecure  --user&＃61;mysql --datadir&＃61;/data/3310/data --basedir&＃61;/app/database/mysql

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

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;db01&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

cat >/data/3307/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3307/data

socket&＃61;/data/3307/mysql.sock

port&＃61;3307

log-error&＃61;/data/3307/mysql.log

log_bin&＃61;/data/3307/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;7

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/data/3308/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3308/data

port&＃61;3308

socket&＃61;/data/3308/mysql.sock

log-error&＃61;/data/3308/mysql.log

log_bin&＃61;/data/3308/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;8

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/data/3309/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3309/data

socket&＃61;/data/3309/mysql.sock

port&＃61;3309

log-error&＃61;/data/3309/mysql.log

log_bin&＃61;/data/3309/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;9

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/data/3310/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3310/data

socket&＃61;/data/3310/mysql.sock

port&＃61;3310

log-error&＃61;/data/3310/mysql.log

log_bin&＃61;/data/3310/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;10

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/etc/systemd/system/mysqld3307.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3307/my.cnf

LimitNOFILE &＃61; 5000

EOF

cat >/etc/systemd/system/mysqld3308.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3308/my.cnf

LimitNOFILE &＃61; 5000

EOF

cat >/etc/systemd/system/mysqld3309.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3309/my.cnf

LimitNOFILE &＃61; 5000

EOF

cat >/etc/systemd/system/mysqld3310.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3310/my.cnf

LimitNOFILE &＃61; 5000

EOF

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;db02&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

cat >/data/3307/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3307/data

socket&＃61;/data/3307/mysql.sock

port&＃61;3307

log-error&＃61;/data/3307/mysql.log

log_bin&＃61;/data/3307/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;17

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/data/3308/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3308/data

port&＃61;3308

socket&＃61;/data/3308/mysql.sock

log-error&＃61;/data/3308/mysql.log

log_bin&＃61;/data/3308/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;18

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/data/3309/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3309/data

socket&＃61;/data/3309/mysql.sock

port&＃61;3309

log-error&＃61;/data/3309/mysql.log

log_bin&＃61;/data/3309/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;19

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/data/3310/my.cnf<

[mysqld]

basedir&＃61;/app/database/mysql

datadir&＃61;/data/3310/data

socket&＃61;/data/3310/mysql.sock

port&＃61;3310

log-error&＃61;/data/3310/mysql.log

log_bin&＃61;/data/3310/mysql-bin

binlog_format&＃61;row

skip-name-resolve

server-id&＃61;20

gtid-mode&＃61;on

enforce-gtid-consistency&＃61;true

log-slave-updates&＃61;1

EOF

cat >/etc/systemd/system/mysqld3307.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3307/my.cnf

LimitNOFILE &＃61; 5000

EOF

cat >/etc/systemd/system/mysqld3308.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3308/my.cnf

LimitNOFILE &＃61; 5000

EOF

cat >/etc/systemd/system/mysqld3309.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3309/my.cnf

LimitNOFILE &＃61; 5000

EOF

cat >/etc/systemd/system/mysqld3310.service<

[Unit]

Description&＃61;MySQL Server

Documentation&＃61;man:mysqld(8)

Documentation&＃61;http://dev.mysql.com/doc/refman/en/using-systemd.html

After&＃61;network.target

After&＃61;syslog.target

[Install]

WantedBy&＃61;multi-user.target

[Service]

User&＃61;mysql

Group&＃61;mysql

ExecStart&＃61;/app/database/mysql/bin/mysqld --defaults-file&＃61;/data/3310/my.cnf

LimitNOFILE &＃61; 5000

EOF

1.5 修改权限&＃xff0c;启动多实例

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 &＃39;server_id&＃39;"

mysql -S /data/3308/mysql.sock -e "show variables like &＃39;server_id&＃39;"

mysql -S /data/3309/mysql.sock -e "show variables like &＃39;server_id&＃39;"

mysql -S /data/3310/mysql.sock -e "show variables like &＃39;server_id&＃39;"

来源&＃xff1a;https://www.cnblogs.com/wx1899325/p/12970284.html