MySQL 部署分布式架构 MyCAT (一)

架构

环境

主机名

IP

db1

192.168.31.205

db2

192.168.31.206

前期准备

开启防火墙，安装配置 mysql （db1,db2）

1firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.31.0/24" accept"
2firewall-cmd --reload
3
4mkdir /software
5# 把软件 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 上传到  /software
6cd /usr/local/
7tar zxf /software/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
8mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
9
10# 初始化数据
11mkdir /data/33{07..10}/data -p
12mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql
13mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql
14mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql
15mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/usr/local/mysql

配置 MySQL my.cnf配置 及启动脚本(db1)

1cat >/data/3307/my.cnf<<EOF
2[mysqld]
3basedir=/usr/local/mysql
4datadir=/data/3307/data
5socket=/data/3307/mysql.sock
6port=3307
7log-error=/data/3307/mysql.log
8log_bin=/data/3307/mysql-bin
9binlog_format=row
10skip-name-resolve
11server-id=7
12gtid-mode=on
13enforce-gtid-consistency=true
14log-slave-updates=1
15EOF
16
17cat >/data/3308/my.cnf<<EOF
18[mysqld]
19basedir=/usr/local/mysql
20datadir=/data/3308/data
21port=3308
22socket=/data/3308/mysql.sock
23log-error=/data/3308/mysql.log
24log_bin=/data/3308/mysql-bin
25binlog_format=row
26skip-name-resolve
27server-id=8
28gtid-mode=on
29enforce-gtid-consistency=true
30log-slave-updates=1
31EOF
32
33cat >/data/3309/my.cnf<<EOF
34[mysqld]
35basedir=/usr/local/mysql
36datadir=/data/3309/data
37socket=/data/3309/mysql.sock
38port=3309
39log-error=/data/3309/mysql.log
40log_bin=/data/3309/mysql-bin
41binlog_format=row
42skip-name-resolve
43server-id=9
44gtid-mode=on
45enforce-gtid-consistency=true
46log-slave-updates=1
47EOF
48
49cat >/data/3310/my.cnf<<EOF
50[mysqld]
51basedir=/usr/local/mysql
52datadir=/data/3310/data
53socket=/data/3310/mysql.sock
54port=3310
55log-error=/data/3310/mysql.log
56log_bin=/data/3310/mysql-bin
57binlog_format=row
58skip-name-resolve
59server-id=10
60gtid-mode=on
61enforce-gtid-consistency=true
62log-slave-updates=1
63EOF
64
65cat >/etc/systemd/system/mysqld3307.service<<EOF
66[Unit]
67Description=MySQL Server
68Documentation=man:mysqld(8)
69Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
70After=network.target
71After=syslog.target
72[Install]
73WantedBy=multi-user.target
74[Service]
75User=mysql
76Group=mysql
77ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
78LimitNOFILE = 5000
79EOF
80
81cat >/etc/systemd/system/mysqld3308.service<<EOF
82[Unit]
83Description=MySQL Server
84Documentation=man:mysqld(8)
85Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
86After=network.target
87After=syslog.target
88[Install]
89WantedBy=multi-user.target
90[Service]
91User=mysql
92Group=mysql
93ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
94LimitNOFILE = 5000
95EOF
96
97cat >/etc/systemd/system/mysqld3309.service<<EOF
98[Unit]
99Description=MySQL Server
100Documentation=man:mysqld(8)
101Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
102After=network.target
103After=syslog.target
104[Install]
105WantedBy=multi-user.target
106[Service]
107User=mysql
108Group=mysql
109ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
110LimitNOFILE = 5000
111EOF
112cat >/etc/systemd/system/mysqld3310.service<<EOF
113[Unit]
114Description=MySQL Server
115Documentation=man:mysqld(8)
116Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
117After=network.target
118After=syslog.target
119
120[Install]
121WantedBy=multi-user.target
122[Service]
123User=mysql
124Group=mysql
125ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
126LimitNOFILE = 5000
127EOF

配置 MySQL my.cnf配置 及启动脚本(db2)

1cat >/data/3307/my.cnf<<EOF
2[mysqld]
3basedir=/usr/local/mysql
4datadir=/data/3307/data
5socket=/data/3307/mysql.sock
6port=3307
7log-error=/data/3307/mysql.log
8log_bin=/data/3307/mysql-bin
9binlog_format=row
10skip-name-resolve
11server-id=17
12gtid-mode=on
13enforce-gtid-consistency=true
14log-slave-updates=1
15EOF
16
17cat >/data/3308/my.cnf<<EOF
18[mysqld]
19basedir=/usr/local/mysql
20datadir=/data/3308/data
21port=3308
22socket=/data/3308/mysql.sock
23log-error=/data/3308/mysql.log
24log_bin=/data/3308/mysql-bin
25binlog_format=row
26skip-name-resolve
27server-id=18
28gtid-mode=on
29enforce-gtid-consistency=true
30log-slave-updates=1
31EOF
32
33cat >/data/3309/my.cnf<<EOF
34[mysqld]
35basedir=/usr/local/mysql
36datadir=/data/3309/data
37socket=/data/3309/mysql.sock
38port=3309
39log-error=/data/3309/mysql.log
40log_bin=/data/3309/mysql-bin
41binlog_format=row
42skip-name-resolve
43server-id=19
44gtid-mode=on
45enforce-gtid-consistency=true
46log-slave-updates=1
47EOF
48
49cat >/data/3310/my.cnf<<EOF
50[mysqld]
51basedir=/usr/local/mysql
52datadir=/data/3310/data
53socket=/data/3310/mysql.sock
54port=3310
55log-error=/data/3310/mysql.log
56log_bin=/data/3310/mysql-bin
57binlog_format=row
58skip-name-resolve
59server-id=20
60gtid-mode=on
61enforce-gtid-consistency=true
62log-slave-updates=1
63EOF
64
65cat >/etc/systemd/system/mysqld3307.service<<EOF
66[Unit]
67Description=MySQL Server
68Documentation=man:mysqld(8)
69Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
70After=network.target
71After=syslog.target
72[Install]
73WantedBy=multi-user.target
74[Service]
75User=mysql
76Group=mysql
77ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
78LimitNOFILE = 5000
79EOF
80
81cat >/etc/systemd/system/mysqld3308.service<<EOF
82[Unit]
83Description=MySQL Server
84Documentation=man:mysqld(8)
85Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
86After=network.target
87After=syslog.target
88[Install]
89WantedBy=multi-user.target
90[Service]
91User=mysql
92Group=mysql
93ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
94LimitNOFILE = 5000
95EOF
96
97cat >/etc/systemd/system/mysqld3309.service<<EOF
98[Unit]
99Description=MySQL Server
100Documentation=man:mysqld(8)
101Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
102After=network.target
103After=syslog.target
104[Install]
105WantedBy=multi-user.target
106[Service]
107User=mysql
108Group=mysql
109ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
110LimitNOFILE = 5000
111EOF
112cat >/etc/systemd/system/mysqld3310.service<<EOF
113[Unit]
114Description=MySQL Server
115Documentation=man:mysqld(8)
116Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
117After=network.target
118After=syslog.target
119
120[Install]
121WantedBy=multi-user.target
122[Service]
123User=mysql
124Group=mysql
125ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
126LimitNOFILE = 5000
127EOF

修改权限，启动多实例(db1, db2)

1chown -R mysql.mysql /data/*
2
3systemctl start mysqld3307
4systemctl start mysqld3308
5systemctl start mysqld3309
6systemctl start mysqld3310
7
8mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
9mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
10mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
11mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"

分片规划

1shard1：
2    Master：192.168.31.205:3307
3    slave1：192.168.31.205:3309
4    Standby Master：192.168.31.206:3307
5    slave2：192.168.31.206:3309
6shard2：
7    Master：192.168.31.206:3308
8    slave1：192.168.31.206:3310
9    Standby Master：192.168.31.205:3308
10    slave2：192.168.31.205:3310

配置 shard1

1# 192.168.31.205:3307 <-----> 192.168.31.206:3307（互为主从）
2# db2:
3mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.31.%' identified by '123';"
4mysql  -S /data/3307/mysql.sock -e "grant all  on *.* to root@'192.168.31.%' identified by '123'  with grant option;"
5# db1:
6mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.31.206', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
7mysql  -S /data/3307/mysql.sock -e "start slave;"
8mysql  -S /data/3307/mysql.sock -e "show slave status\G"
9# db2:
10mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.31.205', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
11mysql  -S /data/3307/mysql.sock -e "start slave;"
12mysql  -S /data/3307/mysql.sock -e "show slave status\G"
13
14# 192.168.31.205:3309 ------> 192.168.31.205:3307（3307 为主库，3309为从库）
15# db1:
16mysql  -S /data/3309/mysql.sock  -e "CHANGE MASTER TO MASTER_HOST='192.168.31.205', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
17mysql  -S /data/3309/mysql.sock  -e "start slave;"
18mysql  -S /data/3309/mysql.sock  -e "show slave status\G"
19
20# 192.168.31.206:3309 ------> 192.168.31.206:3307（3307 为主库，3309为从库）
21# db2:
22mysql  -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.31.206', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
23mysql  -S /data/3309/mysql.sock -e "start slave;"
24mysql  -S /data/3309/mysql.sock -e "show slave status\G"

配置 shard2

1# 192.168.31.206:3308 <-----> 192.168.31.205:3308（互为主从）
2# db2:
3mysql  -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'192.168.31.%' identified by '123';"
4mysql  -S /data/3308/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"
5# db1: 
6mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.31.206', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
7mysql  -S /data/3308/mysql.sock -e "start slave;"
8mysql  -S /data/3308/mysql.sock -e "show slave status\G"
9# db2:
10mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.31.205', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
11mysql  -S /data/3308/mysql.sock -e "start slave;"
12mysql  -S /data/3308/mysql.sock -e "show slave status\G"
13
14# 192.168.31.206:3310 -----> 192.168.31.206:3308（3308 为主库，3310为从库）
15# db2:
16mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.31.206', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
17mysql  -S /data/3310/mysql.sock -e "start slave;"
18mysql  -S /data/3310/mysql.sock -e "show slave status\G"
19
20# 192.168.31.205:3310 -----> 192.168.31.205:3308（3308 为主库，3310为从库）
21# db1: 
22mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.31.205', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
23mysql  -S /data/3310/mysql.sock -e "start slave;"
24mysql  -S /data/3310/mysql.sock -e "show slave status\G"

检测主从状态

1mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
2mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
3mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
4mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes